Common Data Sources and Data Formats

Purpose of This Lesson

Data engineering begins with recognizing how data is exposed and choosing an appropriate way to extract, transport, store, and query it. This lesson covers common source interfaces—JDBC, ODBC, APIs, files, and streams—and the characteristics of CSV, JSON, Avro, and Parquet. These distinctions are especially useful for AWS Certified Data Engineer Associate scenarios involving ingestion, interoperability, schema flexibility, and analytical performance.

Key Concepts

• JDBC (Java Database Connectivity): A common interface for accessing databases from Java applications. It is platform independent because Java can run across many platforms, but it is language dependent because the application must use Java.
• ODBC (Open Database Connectivity): A database access interface that is language independent, but platform dependent because it requires database- and platform-specific drivers.
• Raw log files: Files such as logs written to Amazon S3 can be ingested as source data without first converting them into another format.
• External APIs: An extraction application can retrieve data from an external system through its application programming interface rather than through JDBC or ODBC.
• Streams: Systems such as Apache Kafka and Amazon Kinesis can provide data continuously as it arrives, requiring stream-capable ingestion software.
• CSV/TSV: Human-readable, text-based tabular formats. Rows represent records and delimiters separate values; the delimiter may be a comma, tab, pipe, or another character.
• JSON: A human-readable, lightweight format for structured or semi-structured data represented with key-value pairs. It supports flexible schemas and nested structures.
• Avro: A compact binary format that stores data together with its schema. This is useful when schema evolution, efficient serialization, or processing across systems matters.
• Parquet: A column-oriented storage format optimized for analytical queries that read only selected columns from large datasets.

Data Sources and Ingestion Interfaces

JDBC and ODBC

JDBC and ODBC both provide standardized ways for extraction tools to interact with databases, but their tradeoffs differ:

Interface	Language characteristic	Platform characteristic	Typical fit
JDBC	Java dependent	Platform independent through Java	A Java-based extraction application
ODBC	Language independent	Platform dependent because of drivers	Non-Java applications needing database connectivity

Many data extraction tools support both interfaces, although the details may be hidden behind the tool’s configuration layer. For assessment questions, identify whether the application is Java-based and whether driver or platform compatibility is the key concern.

Other Common Sources

A data pipeline may also ingest:

• Raw log files, including logs stored in Amazon S3.
• Data exposed by an external application’s API.
• Real-time streams from platforms such as Apache Kafka or Amazon Kinesis.

The source type affects the ingestion design. A file-based source can be processed in batches, while a stream requires software that can continuously receive and process incoming events. An API may require request and response handling rather than database queries.

Data Format Characteristics and Use Cases

CSV and Delimited Text

CSV is a text-based, human-readable representation of tabular data. Each line generally represents a row, and delimiters separate the values. The delimiter does not have to be a comma: tab-separated values are commonly called TSV, and pipe-delimited files are also used.

Commas inside field values create parsing concerns. Producers and consumers must agree on quoting and escaping rules. CSV is a strong choice when:

• Data is small to medium sized.
• Humans need to read or edit the file.
• Systems need a broadly supported interchange format.
• Data is being imported or exported from databases or spreadsheets.
• Tools such as Microsoft Excel, Python Pandas, R, or ETL products need to consume the data.

CSV is not encoded for compactness or analytical efficiency, so it is usually less suitable than binary or columnar formats for large-scale analytical storage.

JSON

JSON is also text based and human readable, but it represents data with key-value pairs rather than requiring every record to have the same simple set of delimited fields. It can represent semi-structured data, nested objects, and flexible records.

JSON is commonly used for communication between web clients and backend services, RESTful APIs, application configuration files, and systems that need nested or changing structures. It is widely supported by languages such as JavaScript, Python, and Java, and it is a natural fit for many NoSQL databases, including MongoDB.

Avro

Avro is a binary serialization format that packages data with its schema. The receiving system can use the embedded schema to interpret the data without relying entirely on the original system’s context.

Avro is useful for big-data and real-time processing systems, including Apache Kafka, Apache Spark, Apache Flink, and Hadoop. Its binary representation supports efficient transport and compact storage. Its schema packaging is particularly valuable when the data structure may change over time. If the schema is stable and already available to both sides, repeatedly storing the same schema with every data package may add unnecessary overhead.

Parquet

Parquet stores data by columns rather than by rows. This is advantageous for analytical workloads in which a query reads only a small subset of the available columns. Column values often have similar types and patterns, which also enables efficient encoding and compression.

Parquet is especially useful for large datasets with many columns when queries typically access only selected fields. It is used with systems such as Hadoop, Apache Spark, Apache Hive, Apache Impala, and Amazon Redshift Spectrum. Its value is primarily analytical scan efficiency, not human readability.

Exam- or Assessment-Relevant Takeaways

• For JDBC versus ODBC, remember the two dimensions separately: JDBC is Java dependent and platform independent; ODBC is language independent and platform dependent because of drivers.
• A source may be a database connection, raw file, API, or continuous stream. Do not assume every ingestion problem is a database query problem.
• CSV is broadly interoperable and editable, but it is text based and requires careful delimiter, quoting, and escaping handling.
• JSON is preferable to CSV when records are semi-structured, nested, or require a flexible schema.
• Avro combines binary serialization with the schema, making it useful for transport and evolving schemas.
• Parquet is column oriented and is a strong analytical-storage choice when queries read selected columns from large datasets.
• Distinguish row-oriented versus column-oriented storage from text versus binary representation. JSON and CSV are human-readable text formats; Avro is binary and schema-associated; Parquet is column oriented for analytics.

Tool / Feature Decision Guide

Scenario	Preferred choice	Decisive reason
Java application extracts from a database	JDBC	It is designed for Java database connectivity and is platform independent through Java.
Non-Java application needs a common database interface	ODBC	It is language independent, subject to the availability of appropriate drivers.
Humans and many unrelated systems must read or edit tabular data	CSV or another delimited text format	It is simple, human readable, and broadly supported.
Records contain nested objects or varying fields	JSON	It supports semi-structured data, key-value pairs, and nested structures.
Data must be serialized efficiently and the schema may evolve	Avro	It stores the data and schema together in a compact binary representation.
Large analytical dataset queries only a few of many columns	Parquet	Column-oriented storage reduces the need to read irrelevant columns and supports efficient compression.
Data arrives continuously from an event platform	Stream ingestion using Kafka or Kinesis-compatible tooling	The pipeline must receive and process data as it arrives rather than treating it only as a static file.

Common Traps / Misconceptions

• “Platform independent” and “language independent” mean the same thing. They do not. JDBC is platform independent but Java dependent; ODBC is language independent but driver and platform dependent.
• Every CSV file uses commas. CSV-style data may use tabs, pipes, or other delimiters. The parser must match the actual delimiter and escaping rules.
• CSV and JSON have the same structure. CSV is primarily simple tabular data, while JSON supports semi-structured and nested records.
• Avro is just compressed JSON. Avro is a binary format that includes schema information and is intended for efficient serialization and processing.
• Parquet is simply a smaller CSV. Its important distinction is column-oriented organization, which benefits analytical queries that select specific columns.
• A schema embedded in Avro is always beneficial. It is especially useful when schema evolution or independent interpretation is needed; for an unchanging schema, repeated schema storage can add overhead.
• Streams and files can be ingested identically. Streams require continuous ingestion and processing behavior, while files are generally handled as bounded objects or batches.

Real-World Engineer / Analyst Notes

• Confirm the actual delimiter and field-escaping rules before loading delimited text. A comma inside a legitimate value can otherwise shift every subsequent field.
• Treat format selection as a workload decision, not merely a file-extension decision. A human exchange file and a large analytical table have different requirements.
• Keep source characteristics visible in pipeline design: APIs have interface semantics, databases have connectivity drivers, files have bounded contents, and streams have ongoing arrival.
• For wide analytical datasets, storing data in Parquet can avoid reading columns that a query does not need. This is particularly relevant to distributed analytics systems and Amazon Redshift Spectrum workflows.
• When using Avro, consider whether consumers may need the schema packaged with each data unit and whether schema changes are expected.
• Many managed extraction tools abstract JDBC and ODBC details. Even when the interface is hidden, understanding the distinction helps diagnose driver, language, and compatibility issues.

Quick Reference Summary

• JDBC: Java dependent, platform independent; database access for Java applications.
• ODBC: Language independent, platform dependent through drivers; useful across programming languages.
• Other sources: Raw log files, external APIs, Apache Kafka streams, and Amazon Kinesis streams.
• CSV/TSV: Human-readable, delimited, broadly interoperable, best for small-to-medium tabular exchanges.
• JSON: Human-readable, key-value based, semi-structured, nested, and schema-flexible.
• Avro: Binary, stores data with schema, efficient for serialization and schema evolution.
• Parquet: Column oriented, compressed and encoded efficiently, optimized for analytical queries over large data.

Flashcards

Q: A Java application needs to extract records from an external relational database. Which connectivity interface is the natural fit, and why?

A: JDBC is the natural fit because it is designed for Java database connectivity and provides platform independence through Java.

Q: When would you choose ODBC instead of JDBC for database extraction?

A: Choose ODBC when the extraction application is not limited to Java and needs a language-independent database interface. The tradeoff is dependence on suitable platform- and database-specific drivers.

Q: What is the key difference between JDBC’s platform and language characteristics?

A: JDBC is platform independent but language dependent: Java can run across platforms, but JDBC access is based on Java.

Q: A pipeline receives data continuously from Amazon Kinesis or Apache Kafka. What source characteristic must the ingestion software support?

A: It must support streaming ingestion so it can receive and process data as it arrives, rather than only reading a bounded file.

Q: Why might a team use CSV for exchanging data between unrelated systems?

A: CSV is human readable, easy to edit, and supported by databases, spreadsheets, Python Pandas, R, and many ETL tools. Its broad compatibility makes it a practical common interchange format.

Q: What is the parsing trap when a CSV field contains a comma?

A: A parser can mistake the embedded comma for a field delimiter unless the producer and consumer agree on quoting and escaping rules. The delimiter may also be changed to a tab, pipe, or another character.

Q: When is JSON a better choice than CSV?

A: JSON is preferable when records are semi-structured, have varying fields, or contain nested structures that do not fit naturally into a flat table.

Q: Compare the human readability and structure of CSV and JSON.

A: Both are human-readable text formats, but CSV is primarily flat, delimited tabular data while JSON uses key-value pairs and can represent flexible, nested structures.

Q: A data structure is expected to change and must be transported efficiently between processing systems. Which format is a strong candidate?

A: Avro is a strong candidate because it is binary and stores the data together with its schema, supporting efficient serialization and interpretation across systems.

Q: What is the main tradeoff of storing the schema with every Avro data package?

A: It improves independent interpretation and helps with schema evolution, but it can consume extra space when the schema is stable and already known by all consumers.

Q: An analytical query reads three columns from a table containing one hundred columns. Which format is especially appropriate for storage, and why?

A: Parquet is appropriate because it stores data by columns, allowing analytical processing to focus on the selected columns rather than reading complete rows.

Q: Why does Parquet often compress and encode analytical data efficiently?

A: Values in the same column tend to have similar types and patterns, making column-wise compression and encoding effective.

Q: Compare Avro and Parquet based on their primary strengths.

A: Avro emphasizes binary serialization and carrying the schema with the data, while Parquet emphasizes column-oriented storage and efficient analytical queries over selected columns.

Q: Which format is the least suitable when a user needs to open and edit the data directly in a text editor: CSV, JSON, Avro, or Parquet?

A: Avro and Parquet are binary formats, so they are not intended for direct text-editor inspection or editing. CSV and JSON are the human-readable choices.

Practice Questions

Question 1

A data engineering team is building a Java-based extractor for a database hosted on a different operating system. Which option best matches the connectivity requirement?

A. ODBC, because it is Java dependent and platform independent
B. JDBC, because it is Java dependent and platform independent through Java
C. CSV, because it provides database connectivity
D. Parquet, because it is a database access interface

Correct answer: B

Explanation: JDBC is Java dependent but platform independent through the Java runtime. CSV and Parquet are data formats, not database connectivity interfaces.

Question 2

An API returns customer records where some records contain nested preferences and others contain additional optional fields. Which format best represents this response?

A. CSV
B. TSV
C. JSON
D. Parquet

Correct answer: C

Explanation: JSON supports key-value pairs, optional or varying fields, and nested structures. CSV and TSV are primarily flat, delimited formats.

Question 3

A data platform stores a very wide dataset, but most analytical queries select fewer than ten columns. Which storage format is the strongest choice based on the lesson?

A. Parquet
B. CSV
C. JSON configuration files
D. Raw comma-delimited logs

Correct answer: A

Explanation: Parquet is column oriented, so queries can focus on the selected columns and benefit from column-wise encoding and compression.

Question 4

A streaming pipeline must transport records between processing systems, and the record schema may evolve over time. Which format and rationale are most appropriate?

A. CSV, because it embeds a binary schema
B. JSON, because it is always more compact than binary formats
C. Avro, because it packages binary data with its schema
D. Parquet, because it is primarily a stream interface

Correct answer: C

Explanation: Avro is a binary format that stores data and schema together, making it useful for efficient serialization and changing schemas. Parquet is a column-oriented analytical storage format, not a stream interface.

WordPress Metadata

Suggested Slug:
aws-data-engineering-data-sources-formats

Meta Description:
Learn how JDBC, ODBC, APIs, files, streams, CSV, JSON, Avro, and Parquet support different AWS data engineering ingestion and analytics scenarios.

Tags:
AWS Data Engineering, AWS Certified Data Engineer, JDBC, ODBC, CSV, JSON, Apache Avro, Apache Parquet, Data Ingestion, Data Formats, Data Streams, Amazon Kinesis