Data Formats: JSON, CSV, Parquet, and Avro

Data formats define how data is structured, encoded, and stored in files. They determine how efficiently data can be read, written, queried, and transferred. In Azure, choosing the right format impacts storage costs, query performance (especially with services like Azure Synapse Serverless SQL or PolyBase), and compatibility with tools like Azure Data Factory, Azure Databricks, and Power BI.

The DP-900 exam expects you to understand the characteristics, use cases, and trade-offs of JSON, CSV, Parquet, and Avro. You will not be asked to write code but to identify which format is best for a given scenario.

CSV is a plain-text format where each line represents a row, and columns are separated by a delimiter (typically a comma). It is human-readable and widely supported by almost every data tool.

How it works internally: - Each record is on a separate line. - Fields are separated by commas. If a field contains a comma, newline, or double quote, the field must be enclosed in double quotes. Double quotes inside a field are escaped by doubling them. - There is no standard for encoding, but UTF-8 is common. No schema is embedded; the first row may contain column headers, but this is not guaranteed. - CSV is row-oriented: all columns of a row are stored together.

Key characteristics: - Simple, easy to produce and consume. - No schema enforcement—data types are inferred (or all treated as strings). - No compression by default, but can be compressed with gzip, etc. - Inefficient for large datasets: reading a single column requires reading all rows; no indexing; parsing overhead.

Use cases: - Small datasets, manual editing, data exchange between legacy systems. - Export from databases, import into Excel.

Limitations: - No support for nested or hierarchical data. - No data type metadata—dates, numbers, and booleans are all strings. - No built-in compression; file sizes are large. - Parsing is error-prone due to lack of strict standard.

JSON is a lightweight text format for structured data using key-value pairs. It supports nested objects and arrays. It is language-independent but uses conventions familiar to programmers.

How it works internally: - Data is organized in objects {} (unordered collection of key-value pairs) and arrays [] (ordered list of values). - Keys are strings; values can be strings, numbers, booleans, null, objects, or arrays. - Whitespace is insignificant (except within strings). - JSON is self-describing: the structure is embedded in the data.

Key characteristics: - Human-readable (though can become verbose). - Schema-on-read: no predefined schema; each object can have different keys. - Supports nested structures, ideal for hierarchical data (e.g., IoT device readings, social media posts). - No built-in compression; files can be large. - Parsing is slower than binary formats.

Use cases: - Web APIs (REST), NoSQL databases (Azure Cosmos DB), configuration files. - Data from IoT devices, log events, telemetry.

Limitations: - Verbose: repeated keys in every record increase file size. - No indexing; full scan required for queries. - Not optimized for analytical queries (columnar access).

Parquet is a binary, columnar storage format optimized for analytics. It is designed for efficient compression and encoding of data, especially for large datasets in data lakes.

How it works internally: - Parquet stores data by columns, not rows. All values for a column are stored together in contiguous pages. - Within each column chunk, data is further divided into pages (typically 1 MB uncompressed). Pages contain the actual data values and optional statistics (min, max, null count). - Parquet uses a row group structure: data is horizontally partitioned into row groups (e.g., 128 MB each). Within each row group, column chunks are stored separately. - Encoding techniques: dictionary encoding (replaces repeated values with integer IDs), run-length encoding (RLE), bit packing, delta encoding. These are applied per page. - Compression codecs: Snappy (default), gzip, LZO, LZ4, ZSTD. Snappy offers a good balance of speed and compression ratio. - Schema is stored in the file metadata (footer). The footer contains schema, column metadata, and row group offsets. This allows readers to skip irrelevant row groups or columns. - Parquet supports nested schemas using a repetition and definition level mechanism (Dremel paper).

Key characteristics: - Columnar: only read the columns needed for a query, drastically reducing I/O. - Highly compressed: columnar layout and encoding reduce storage. - Schema embedded: data types are explicit (int32, int64, float, double, byte_array, etc.). - Predicate pushdown: statistics in metadata allow skipping row groups that don't satisfy filter conditions. - Splittable: files can be split for parallel processing (e.g., by row groups).

Use cases: - Data warehousing, analytics, big data processing (Azure Synapse, Databricks, HDInsight). - Storing large volumes of structured data in Azure Data Lake Storage.

Limitations: - Not human-readable; requires tools to read. - Overhead for small datasets: metadata and columnar structure add overhead for small files. - Write performance is slower than row-oriented formats due to columnar assembly.

Avro is a binary, row-oriented format with a schema embedded in the file. It is designed for fast serialization and data exchange in streaming and messaging systems.

How it works internally: - Avro files consist of a header followed by data blocks. The header contains the schema (in JSON) and optionally a sync marker (a 16-byte random value). - Data blocks are groups of records. Each block starts with a sync marker, followed by the block size (in bytes), the number of records in the block, and the serialized records. - Records are serialized in binary using the schema. The schema defines the record structure, field names, and data types. Avro supports primitive types (null, boolean, int, long, float, double, bytes, string) and complex types (record, enum, array, map, union, fixed). - Avro uses a compact binary encoding: integers are variable-length (ZigZag encoding), strings are length-prefixed UTF-8. - The schema is stored in JSON in the header, making the file self-describing. Schema evolution is supported: readers can use a different schema than the writer, as long as they are compatible (field additions, deletions, type promotions). - Avro files are splittable: each data block can be read independently because the sync marker allows locating block boundaries.

Key characteristics: - Row-oriented: all fields of a record are stored together, making writes fast. - Self-describing: schema is embedded, so any reader can interpret the data. - Compact binary: smaller than JSON but larger than Parquet for analytical queries. - Supports schema evolution: add or remove fields without breaking readers. - Splittable for parallel processing.

Use cases: - Streaming data pipelines (Azure Event Hubs, Kafka, Azure Data Factory). - Data exchange between systems where schema must be preserved. - Log collection and serialization in Hadoop/Spark.

Limitations: - Not columnar: reading a single column requires reading all rows. - Larger than Parquet for analytical storage. - Less efficient compression than columnar formats.

| Feature | CSV | JSON | Parquet | Avro | |---------|-----|------|---------|------| | Readability | High | High | Low | Low | | Schema | None/optional | Self-describing | Embedded | Embedded | | Storage | Row | Row | Column | Row | | Compression | None/optional | None/optional | Excellent | Good | | Splittable | Yes (with gzip) | No (unless line-delimited) | Yes | Yes | | Nested data | No | Yes | Yes | Yes | | Schema evolution | No | No | Limited | Yes | | Best for | Small/simple data | APIs, NoSQL | Analytics | Streaming, exchange |

A large retail company ingests terabytes of sales data daily from thousands of stores. They use Azure Data Lake Storage Gen2 as the single source of truth. Initially, they stored data as CSV files because it was easy to export from legacy systems. However, queries in Azure Synapse Serverless SQL took minutes even for simple aggregations. The data engineering team converted the data to Parquet format with Snappy compression. They partitioned the data by date and store ID. After conversion, query times dropped from minutes to seconds. Storage costs decreased by 60% due to better compression. The key lesson: Parquet is essential for analytical workloads. Misconfiguration like using too many small files (less than 10 MB) still caused performance issues, so they implemented a job to coalesce files into 200-500 MB chunks.

A financial services firm processes millions of stock trade messages per second. They use Azure Event Hubs to capture streaming data. By default, Event Hubs Capture writes data in Avro format to Azure Blob Storage. The Avro schema includes fields like trade ID, symbol, price, volume, and timestamp. As new trade types are added, the schema evolves without breaking existing pipelines. Downstream, Azure Databricks reads the Avro files for real-time analytics. The row-oriented nature of Avro allows fast writes, which is critical for high-throughput ingestion. A common mistake is to use JSON for capture, which increases storage costs and parsing overhead. Avro's compact binary encoding reduces storage by 40% compared to JSON.

A smart city project collects sensor data from thousands of devices: temperature, humidity, traffic counts, etc. Devices send data as JSON messages via Azure IoT Hub. The raw JSON is stored in Azure Data Lake Storage for archival. However, for analytics, the data is converted to Parquet using Azure Data Factory mapping data flows. The conversion flattens nested JSON into columns and applies schema. This enables fast queries in Power BI dashboards. The challenge is that JSON files are often small (1-2 KB each), leading to the "small file problem" in Parquet. The solution is to batch JSON files and write larger Parquet files (100-200 MB) using a tumbling window in Data Factory.