Data Formats and Serialization

Data Engineering & Modern Data Platforms · Lesson 11 · Data Engineering Foundations

Lesson Overview

The warehouse grain from Dimensional Modeling and Data Warehousing is a contract about rows. A file format is a contract about bytes. TechLearningPro already has three of them in production, and they do not serve the same job. Mobile clients post learning events as JSON. A nightly partner export of assigned seats arrives as CSV. The finance mart that must scan a month of order lines without reading every column wants a columnar file. Someone will eventually suggest "just use JSON everywhere" because one parser is easier to hire for. That choice makes the October revenue scan read every byte of every payload, including emails the scan does not need, and it makes a renamed field a silent null.

This lesson compares CSV, JSON, Avro, Parquet, and ORC as they actually work: text versus binary, row versus column, schema in the file versus schema in a registry versus no schema at all, and compression as a codec applied after an encoding. It shows how Avro resolves a reader schema against a writer schema, how Parquet stores row groups and column statistics so an engine can skip data, and how ORC occupies the same analytical role with a different history. It also shows the failures that are boring and expensive: a CSV that changes column order, a JSON number that arrives as a string, a Parquet lake of million-row-sized files that are each 200 KB, and a "compatible" schema change that added a field without a default.

Module 2 starts by moving these bytes. Data Integration Patterns and ETL and ELT Architecture will land files, load tables, and schedule transforms. They will assume you can choose a format per zone. Raw ingestion keeps the producer's bytes. The curated zone stores the grain you already declared, usually in Parquet, partitioned by a date the business recognizes. The format does not change the meaning of an order line. A bad format makes that meaning expensive, fragile, or impossible to evolve. This lesson is the last foundations chapter because every pipeline after it is a decision about schema, encoding, and files.

Learning Objectives

By the end of this lesson, you should be able to:

1. Separate serialization (how a record becomes bytes) from file layout (how many records, what metadata, and how a reader finds a column).
2. Explain what CSV does not store: types, a required schema, nesting, and a reliable distinction between missing and empty.
3. Choose JSON or newline-delimited JSON for an API or a log, and name the ambiguities of numbers, duplicates, and schemas.
4. Describe an Avro object-container file and how reader and writer schemas resolve added and removed fields.
5. Explain Parquet row groups, column chunks, encodings, and footer statistics, and why a filter can skip a row group.
6. Compare ORC with Parquet at the level of stripes, indexes, and ecosystem, and pick one columnar format for a lake.
7. Distinguish compression codecs from Parquet encodings, and choose a codec for a scan-heavy table.
8. State a schema-evolution rule for the landing zone and for the curated zone, including what "backward compatible" means for Avro.
9. Partition and size files so analytical reads prune without creating a small-file incident.
10. Map TechLearningPro zones to formats: raw JSON or CSV as received, event contracts in Avro or a documented JSON schema, curated facts in Parquet.
11. Keep lesson 10's grain and SCD attributes intact inside the file schema.
12. Hand Module 2 a landing layout, a curated layout, and a compatibility check that runs before a new field is published.

Prerequisites

• Dimensional Modeling and Data Warehousing: grain, type-2 attributes, and the order-line fact you will store in a file.
• Data Modeling Fundamentals: which fields are snapshots versus current state. A file can freeze the wrong one just as a table can.
• Enough Python to read a standard-library example. The walkthrough uses the csv and json modules and Apache Arrow's pyarrow package, which is a real library, not a platform-specific SDK.
• No Spark cluster is required. Where Spark's writer is the common production path, the lesson says so and uses documented DataFrameWriter formats (parquet, orc, json, csv).

Detailed Core Concepts

What a format has to answer

Every interchange asks the same questions. What are the field names and types? Are records independent, so a reader can start in the middle? Can a reader load three columns and ignore forty? What happens when a producer adds a field? How much CPU buys how much less disk and network? Who writes the schema: the file, a registry, or a wiki page nobody updates?

Row-oriented formats store one record's fields together. CSV, JSON lines, and Avro object files are row-oriented. They are a natural fit for a producer appending events and for a consumer that needs the whole event. Column-oriented formats store a column's values together across many rows. Parquet and ORC are column-oriented. They are a natural fit for "sum of extended_amount_cents where sold_date is in October," because the engine reads the amount column and the date column and can skip row groups whose date statistics fall outside October.

Schema-on-read means the file may not carry a full contract. CSV is the extreme: the reader imposes names and types, and a bad imposition is how a zip code becomes a float. Schema-on-write means the producer and the file agree before the byte is stored. Avro, Parquet, and ORC carry a schema. The curated zone should be schema-on-write. The raw zone should keep the producer's payload untouched and attach metadata beside it: source, ingest time, and a content hash. Transforming JSON into a "cleaned" JSON in the raw zone destroys the ability to replay when the cleaning was wrong. That replay is the ELT argument Module 2 will make. The format choice is what makes replay possible.

CSV

CSV is text, usually one record per line, fields separated by a comma, with rules for quotes when a field contains a comma or a newline. RFC 4180 describes a common profile. It is not what every exporter implements. Excel, mainframes, and marketing tools differ on separators, quoting, encodings, and whether a header exists. A header is a courtesy, not a schema. Column order is the real contract in files that omit names, and it is a fragile contract in files that have names if a reader still binds by position.

Types are not in the file. 4900 might be cents, a course id, or a string that lost its leading zeros. Dates arrive as 10/11/2026, which is October or November depending on the country of the exporter. Empty fields are ambiguous between null and empty string. There is no standard nesting: an order and its lines become either a second file or a quoted blob. CSV is still the right format when a human or a legacy partner must open the file, the row count is modest, and the schema is frozen in a written agreement that includes encoding (UTF-8), separator, header, date format, null token, and whether the file is a full snapshot or a delta.

TechLearningPro's seat-assignment partner export is that case. It is not the format of the event firehose, and it is not the format of fact_order_line.

JSON and NDJSON

JSON is a text encoding of objects, arrays, strings, numbers, booleans, and null. Field names are in every object, so a reader can ignore unknown fields and a human can open one event. Nesting matches API payloads: an order object with a lines array. The cost is size and CPU. Names repeat on every row. Whitespace and pretty-printing belong in debugging, not in a nightly dump of millions of events.

A single JSON array of millions of events is a poor file. The reader often parses the whole document to reach the last element, and a truncated write corrupts the only array. Newline-delimited JSON, one object per line, is the practical log and landing format. Each line is a record. A failed tail can be quarantined without discarding the file. Compression, if applied, should be a standard codec around the file or around blocks your reader can split. Gzip on one giant NDJSON object is common and not friendly to parallel splits. That is acceptable for a raw landing file you read once per batch. It is unacceptable as the layout of a table scanned all day.

JSON numbers are a known trap. Many encoders emit large integers that parsers turn into floating point. Identifiers belong in JSON strings if they can exceed the integer range your parser guarantees. Timestamps belong in an agreed form, usually ISO-8601 with a time zone, not in a bare local datetime. Duplicate keys are invalid in spirit and accepted by some parsers as "last one wins." A schema can live beside JSON as JSON Schema, which is a real specification, or as the Avro or Protobuf contract you convert into. The landing file can remain JSON while the contract lives in a registry. The curated file should not rely on every reader to remember the wiki.

Avro

Avro is a binary row format with a schema. In an object-container file, the header stores the writer schema as JSON, then blocks of binary records and sync markers. A reader does not need a side channel to parse that file. On a bus, the schema often lives in a registry and the message carries an id, which is a product of the registry (Confluent's schema registry is the widely documented implementation) rather than a part of the Avro container spec. Do not invent a private header. Use the container file when the artifact is a file. Use the registry's documented wire format when the artifact is a Kafka message. Module 4 covers the bus. This lesson covers the compatibility idea the bus will reuse.

Avro resolution compares the writer's schema (what was stored) with the reader's schema (what the program expects). If the reader expects a field the writer did not write, the reader's default is used. A new field without a default is not readable from old data. If the writer wrote a field the reader does not expect, the reader skips it. That pair of rules is why teams talk about compatibility directions:

Avro aliases let a reader schema accept an old name. A rename without an alias is a new field plus a missing old field. Enum symbols and type changes have their own rules in the Avro specification. The safe operational policy for TechLearningPro events is: add optional fields with defaults, do not recycle a field name for a new meaning, and do not change a long identifier to a float. The registry setting that matches "new consumers and old consumers both keep working" is full compatibility. Backward compatibility is the minimum if you only promise that a new job can read last year's files.

Avro is a poor analytical scan format. Records are rows. There are no column statistics. You land Avro, or you use it on the bus, and you convert to Parquet for the mart.

Parquet

Parquet is a columnar file format designed for analytical engines. A file contains row groups. Each row group contains a column chunk per column. Each column chunk contains pages. Encodings such as dictionary, run-length, and delta packing reduce repeated values before a compression codec such as Snappy, Gzip, or Zstandard runs on the page or the chunk. The footer stores the schema, the location of each chunk, and statistics such as min, max, and null count. A reader planning sold_date >= 2026-10-01 can skip a row group whose maximum date is in September. That skip is a statistics prune, not a guarantee: if statistics are missing or coarse, the engine reads the chunk and filters. Predicate pushdown is only as honest as the stats and the row-group size.

Dictionary encoding helps event_name and track_name, which repeat. It does little for a unique order_line_id. Compressing an already unique high-cardinality column still helps somewhat and will not match the ratio you see on a status column. Choosing Zstandard for curated Parquet is a sound default in current engines that support it. Snappy remains common because older Spark defaults used it and it is fast to decompress on a scan. Gzip is slower and widely readable. The right comparison is scan CPU plus bytes read on your engine, not a blog's compression percentage on a different table.

Nested data uses definition and repetition levels, the Dremel model Parquet adopted. A struct of course attributes inside a line row is representable. Deeply nested API JSON, dumped into Parquet without flattening, produces a schema analysts cannot query and a writer that is painful to evolve. Flatten to the grain of the fact. Keep the raw nested JSON in the landing zone.

Schema evolution in Parquet is mostly about names and nullability, and it is engine-specific in the details. Adding a nullable column is the change curated tables can accept: old files are read as null for that column by engines that match fields by name, which includes current Spark Parquet reads. Dropping a column means new files omit it and old readers must tolerate absence. Renaming a column is a break unless you rewrite files or the reader has a mapping. Changing a column from integer cents to a double rupee amount is a break in meaning and usually a break in type. Widen types only when the engine documents the promotion, and prefer not to. Partition columns (sold_month=2026-10) live in the directory, not only inside the file. They must agree with the column inside the file. A partition named October that contains September rows will be pruned incorrectly and trusted completely.

ORC

ORC is the other major open columnar format, built in the Hive ecosystem. Data is organized into stripes, commonly targeted around tens to hundreds of megabytes, with indexes and optional bloom filters that help skip rows inside a stripe. Type information and statistics live in the file. Compression choices include Zlib, Snappy, LZ4, and Zstandard, depending on the writer version. Spark and Hive can read and write ORC through documented format names. Apache Arrow can too, in current pyarrow.orc writers, when you need a Python path.

For a new TechLearningPro lake, pick Parquet unless a platform you already run is ORC end to end. Mixing Parquet facts and ORC facts doubles the readers, the evolution rules, and the compaction jobs. ORC's bloom filters and stripe indexes are real advantages for some Hive-era filters. They are not a reason to split one warehouse across two columnar formats. The grain, the partition, and the codec matter more than the logo once you have chosen.

Compression, encoding, and small files

Encoding is a representation of a column (dictionary, delta, plain). Compression is a general codec applied to those bytes. You want both on analytical files. You do not want a codec on data that is already compressed, such as image bytes stored as a column, where the CPU is wasted. You do not want a unique tiny file per course per hour. Object storage and the driver of a query pay a fixed cost per file. A month of events split into hundreds of thousands of 100 KB Parquet files can be slower than the same bytes in a few hundred files of a few hundred megabytes, even though the small files are "more parallel" in a slide. Aim curated row groups and files at a size your engine scans efficiently, often on the order of 128 MB to 1 GB per file, and compact the landing-day partitions that arrived as a trickle.

Partition by a column you filter and whose cardinality is bounded: sold_month or ingest_date. Do not partition by learner_id. You will create a directory per learner and the small-file problem will have a name. Bucketing or clustering, where the engine supports it, is the tool for high-cardinality join keys. It is not a directory per key.

How this sits on the models you already built

A Parquet file of fact_order_line carries order_line_id, the sold date, dimension keys or the attributes you intentionally flattened, quantity, and extended_amount_cents. It does not become a new grain because the format is columnar. Type-2 history is either the course_key you stored at load or the title_at_purchase snapshot. Rewriting old Parquet files to the current title is a type-1 restatement performed with a compaction job. If you did not mean to restate history, the compaction job must not "fix" titles from today's catalog.

Personal data follows the same boundary as lesson 10. Email does not belong in every analytical Parquet extract. Raw JSON that contains email is access-controlled as learner data, retained for a documented window, and not copied into the finance mart "because the parser already had it."

Architecture and Design Diagrams

mermaidCopy
flowchart TD
  API[Mobile and web clients JSON]
  PARTNER[Partner seat file CSV]
  BUS[Event contract Avro or JSON Schema]
  RAW[Raw zone immutable bytes]
  VAL[Validate against contract]
  CUR[Curated Parquet at declared grain]
  ORCNOTE[ORC only if the platform is already ORC]
  WH[Warehouse tables or external tables]
  ETL[Module 2 schedule and replay]

  API --> RAW
  PARTNER --> RAW
  BUS --> RAW
  RAW --> VAL --> CUR
  CUR --> WH
  ORCNOTE -.-> CUR
  ETL --> RAW
  ETL --> CUR

A Parquet file's read path for a date filter:

mermaidCopy
flowchart LR
  FOOT[Footer schema and stats]
  RG1[Row group September max date]
  RG2[Row group October]
  COL[Read amount and course columns only]
  FOOT -->|skip| RG1
  FOOT --> RG2 --> COL

Schema evolution for an event, backward compatible:

mermaidCopy
sequenceDiagram
  participant P as Producer
  participant F as Avro file or registry
  participant C as New consumer

  Note over F: Writer schema without lesson_position
  P->>F: Old events
  Note over C: Reader schema adds lesson_position with default null
  C->>F: Reads old events, default fills the new field
  P->>F: New events include lesson_position
  C->>F: Reads new events, uses the written value

Technical Explanation

Landing CSV without lying about types

A partner file seats_2026-11-10.csv is agreed as UTF-8, comma-separated, header row, RFC-style quoting, dates as YYYY-MM-DD, null as an empty field, and columns account_id,order_line_id,seat_id,learner_id,assigned_on. The loader uses Python's csv module so quoting is not a hand-rolled split.

pythonCopy
import csv
from datetime import datetime, date

EXPECTED = ["account_id", "order_line_id", "seat_id", "learner_id", "assigned_on"]

def load_seats(path: str) -> list[dict]:
    with open(path, newline="", encoding="utf-8") as handle:
        reader = csv.DictReader(handle)
        if reader.fieldnames != EXPECTED:
            raise ValueError(f"header {reader.fieldnames} != {EXPECTED}")
        rows = []
        for line_no, row in enumerate(reader, start=2):
            learner = row["learner_id"].strip()
            assigned = row["assigned_on"].strip()
            rows.append({
                "account_id": int(row["account_id"]),
                "order_line_id": int(row["order_line_id"]),
                "seat_id": int(row["seat_id"]),
                "learner_id": int(learner) if learner else None,
                "assigned_on": date.fromisoformat(assigned) if assigned else None,
                "source_line": line_no,
            })
    return rows

DictReader binds by header name, so a partner who inserts a column in the middle does not silently shift learner_id onto seat_id, provided you reject unexpected headers. Binding by position would accept that file and corrupt assignees. int on an empty learner id is rejected by the branch that allows null. A file that contains 1.23E+11 because a spreadsheet formatted an id is a failed int conversion, which is what you want. Do not "helpfully" parse it as float.

The raw CSV is stored as received, next to a manifest: file name, byte size, sha256, ingest timestamp, and the agreement version. The parsed rows go to a staging table. If the agreement changes, the old manifest still points at the old bytes.

NDJSON events

pythonCopy
import json
from pathlib import Path

def iter_events(path: Path):
    with path.open(encoding="utf-8") as handle:
        for line_no, line in enumerate(handle, start=1):
            if not line.strip():
                continue
            try:
                payload = json.loads(line)
            except json.JSONDecodeError as exc:
                yield {"_error": str(exc), "_line": line_no}
                continue
            if not isinstance(payload, dict):
                yield {"_error": "record is not an object", "_line": line_no}
                continue
            yield payload

Quarantine error lines. Do not fail a ten-million-line landing because line 4,002 is truncated, and do not drop the line without a count. Identifiers in these payloads are strings in the producer contract even when they look numeric. The staging cast to bigint happens where the contract says the string is a decimal integer, and it fails the record, not the type of a neighboring column.

Avro with a defaulted field

The container schema below is valid Avro. course_id is a union with null and has a default, so a reader schema that adds another optional field with a default can still read files written with this writer schema. fastavro is a widely used Python package that reads and writes this container. The standard library does not include Avro.

pythonCopy
from fastavro import parse_schema, writer

event_schema = {
    "type": "record",
    "name": "LearningEvent",
    "namespace": "com.techlearningpro.learning",
    "fields": [
        {"name": "event_id", "type": "string"},
        {"name": "learner_id", "type": "long"},
        {"name": "event_name", "type": "string"},
        {"name": "event_time_ms", "type": {"type": "long", "logicalType": "timestamp-millis"}},
        {"name": "course_id", "type": ["null", "long"], "default": None},
    ],
}

def write_events(path: str, records: list[dict]) -> None:
    parsed = parse_schema(event_schema)
    with open(path, "wb") as handle:
        writer(handle, parsed, records)

A compatible evolution adds {"name": "lesson_slug", "type": ["null", "string"], "default": None}. Old files still read. A breaking evolution adds {"name": "lesson_slug", "type": "string"} with no default and no null. New readers then cannot decode old files. Renaming event_name to name without an alias is the same class of break. Producers and consumers should be tested against a fixture file written by the previous schema, not only against records they just wrote.

Logical types such as timestamp-millis are annotations on a long. A reader that ignores logical types still sees a long. Document the unit in the contract so a consumer does not treat milliseconds as microseconds. Avro will not stop that mistake.

Parquet at the curated grain

Apache Arrow's Python package writes Parquet that Spark, DuckDB, and warehouse loaders read. The schema below is the line grain, flattened with the attributes the mart is allowed to repeat. order_line_id keeps the load idempotent when the job overwrites a partition.

pythonCopy
from datetime import date
import pyarrow as pa
import pyarrow.parquet as pq

line_schema = pa.schema([
    pa.field("order_line_id", pa.int64(), nullable=False),
    pa.field("order_id", pa.int64(), nullable=False),
    pa.field("sold_date", pa.date32(), nullable=False),
    pa.field("course_key", pa.int64(), nullable=False),
    pa.field("account_key", pa.int64(), nullable=False),
    pa.field("quantity", pa.int32(), nullable=False),
    pa.field("unit_price_cents", pa.int64(), nullable=False),
    pa.field("extended_amount_cents", pa.int64(), nullable=False),
    pa.field("track_name", pa.string(), nullable=False),
])

def write_lines(path: str, rows: list[dict]) -> None:
    table = pa.Table.from_pylist(rows, schema=line_schema)
    pq.write_table(
        table,
        path,
        compression="zstd",
        compression_level=3,
        row_group_size=100_000,
        use_dictionary=["track_name"],
    )

def scan_october_amounts(path: str):
    table = pq.read_table(
        path,
        columns=["order_line_id", "extended_amount_cents", "sold_date"],
        filters=[
            ("sold_date", ">=", date(2026, 10, 1)),
            ("sold_date", "<", date(2026, 11, 1)),
        ],
    )
    return table

columns= is column pruning. filters= is a pushdown to row groups that have statistics. Neither changes the sum's definition. The sum is still lesson 10's extended amount at line grain. use_dictionary on track_name is an encoding choice. Zstandard is the codec. If a reader in the estate cannot read Zstandard Parquet, use Snappy and record the constraint. Do not guess from a laptop library's default and then surprise the warehouse.

A Spark job that writes the same grain uses the documented writer, not a custom output format:

pythonCopy
(df.repartition("sold_month")
   .write
   .mode("overwrite")
   .option("compression", "zstd")
   .partitionBy("sold_month")
   .parquet("s3://tlp-curated/fact_order_line/"))

mode("overwrite") on a partition directory replaces that partition. It is idempotent when the job recomputes the whole month. It is destructive when pointed at the table root by mistake. The Module 2 review checks the path. Dynamic partition overwrite, where the engine replaces only partitions present in the dataframe, is a writer option you turn on deliberately (partitionOverwriteMode in Spark) so a late day does not erase the rest of the month. Know which mode you scheduled.

ORC, if you had to match an existing Hive table, is the same writer with .orc(...) and a compression option the ORC writer documents (zlib or zstd, depending on version). The schema and the grain stay the Parquet example's schema. The file layout changes. Do not maintain both for one fact.

Evolution and partitions in the curated zone

Adding discount_cents as a nullable long, backfilled as zero for old files or left null and treated as zero in the metric with a documented rule, is a compatible change for name-based Parquet readers. The reconciliation query must change in the same release as the column, or October's extended amount will disagree with the operational check that still subtracts discounts. Schema evolution is a metric change when the column participates in money.

Rewriting last year's files to fill the new column is a batch you schedule, not a side effect of a query. Until the rewrite, readers see nulls. A view can COALESCE(discount_cents, 0) so dashboards do not fork. The view is part of the contract.

Partition paths:

textCopy
raw/learning_events/ingest_date=2026-11-10/events-0001.jsonl
raw/partner_seats/ingest_date=2026-11-10/seats.csv
curated/fact_order_line/sold_month=2026-10/part-000.parquet

ingest_date is when TechLearningPro received the bytes. sold_month is the business date. A late September sale that arrives in November lands in the raw November ingest partition and in the curated September sold partition. Those are supposed to differ. A job that partitions curated data by ingest date will hide the sale from September's finance scan and duplicate it if a second job also "fixes" the business date.

What not to put in the file

Do not store the Avro writer schema only in a chat log. It is in the container header or the registry. Do not store Parquet without running a reader from a second engine if the file is an interchange, because a writer-only test misses a codec the other side cannot read. Do not compress with a codec and also base64 the payload into JSON "for safety." You pay the size twice and lose pushdown. Do not encrypt by inventing a column cipher. Use the object store's documented server-side encryption and IAM. A homemade encryption wrapper is not a format.

Practical Implementation or Design Walkthrough

Problem

Design the byte layout for three TechLearningPro flows: partner seat CSV, learning-event JSON, and the curated order-line fact. The curated fact must preserve line grain, survive a replay, accept an added nullable column later, and let an October scan skip other months.

Steps

1. Write the partner CSV agreement on one page: encoding, separator, header, types, nulls, date format, full snapshot versus delta. Reject files whose header differs.
2. Land JSON events as NDJSON, immutable, partitioned by ingest date, with a manifest of byte size and hash. Quarantine bad lines with a count.
3. Define the event contract. If the producer can adopt Avro, use a container or a registry and require defaults on new fields. If the producer stays on JSON, publish a JSON Schema and keep the same compatibility rule: new fields optional, names never reused for a new meaning.
4. Convert to Parquet at line grain after the warehouse load has resolved dimension keys and extended amount. Include only the columns the mart is allowed to hold.
5. Partition curated data by sold_month. Compact to files large enough that a month is not tens of thousands of objects. Dictionary-encode track_name. Compress with Zstandard or Snappy, chosen for the readers you actually run.
6. Prove prune behavior: write two row groups or two month partitions, read with a date filter and a column list, and confirm the scanned row count matches October.
7. Add discount_cents with null default semantics. Read an old file with the new schema in the engine you will use in Module 2. Record whether null means zero.
8. Replay the curated write for one month with overwrite of that partition only. Show that the other month remains and that the replayed month has one row per order_line_id.

Validation

• Header drift fails the CSV load before any insert.
• A JSON line that is not an object increments the quarantine count and does not appear in curated data.
• An Avro or JSON Schema change without a default fails a compatibility check against last month's fixture.
• The October sum of extended_amount_cents equals the warehouse reconciliation from lesson 10. The file is not a second revenue definition.
• A query that selects only amount and course id does not need email, and email is not in the file.
• File counts per month stay within the bound you set in the runbook. The compaction task is part of the pipeline, not a cleanup someone remembers.

Module 2 handoff

The integration job has three stages that map to formats. Extract stores raw CSV and NDJSON and does not reshape them. Load parses into staging tables with the type rules above, or registers the Parquet path as an external table. Transform builds the dimensional model and writes curated Parquet. ELT is the order "load raw bytes, then transform." ETL would transform before the durable landing, which this design refuses for events, because a bad transform must be replayable from raw. ETL and ELT Architecture will defend that order. The format lesson's contribution is the raw versus curated split that makes the order possible.

Enterprise Real-World Scenarios

TechLearningPro partner CSV and the shifted column

A training-partner export added country in the third position and kept the old header-less writer for one file. The loader mapped by position. learner_id received country codes, the cast to int failed for IN, and a "lenient" fallback stored null learners for an entire day of assignments. Seats looked unassigned. The operational unique keys prevented duplicate seats. They did not prevent a wrong null. The replacement loader is the DictReader check in this lesson: expected header, reject extras and omissions, no float coercion of ids. The raw file stayed in the ingest partition so the day could be reprocessed after the partner resent a header. Because curated Parquet had not yet been written for that ingest, the mart never published the nulls. Landing raw before curated is what made the recovery a replay instead of a forensic reconstruction from a mutated file.

The file was 40 MB, which is a CSV-appropriate size. The same design does not extend to the event stream at hundreds of millions of lines. Volume is part of the format decision, and so is the failure mode of a silent positional shift, which exists at any size.

TechLearningPro curated Parquet and a small-file month

The first curated write of learning completions used a Spark default that emitted many small files per sold_month, and the partition key was learner_id for "faster learner lookups." Directory listings dominated the morning job. A lookup of one learner still scanned metadata for every learner directory. The repair partitioned by sold_month, removed the learner directory, and compacted each month to a handful of Parquet files with Zstandard and a dictionary on event_name. Learner-specific access stayed a filter on learner_id inside the month, which column statistics and the engine's predicate handling serve well enough at this volume. A true point lookup by learner remains a database or a search index, not a data-lake directory tree.

In the same quarter a producer added lesson_slug to the JSON events without telling the curated job. Raw NDJSON kept the new field because it stores the object as received. The curated Parquet schema did not have the column, so the transform dropped it until the schema change was reviewed. That drop was visible because the transform logged unknown fields. A silent SELECT * into a loosely typed frame would have promoted the column in some partitions and not others, and downstream unions would have failed or, worse, filled nulls for the months that had not seen the field yet. The policy now matches Avro's conservative rule even for JSON: optional new fields, a compatibility fixture, and a curated schema updated in the same change as the metric that reads the field.

Access control: raw events include the account email the client sent for support correlation. The curated completion file does not. The raw bucket's IAM policy is the retention boundary. Copying raw JSON into the finance prefix "so analysts can explore" would bypass the column decision Parquet made easy. Exploration happens through a governed sample or a staging role, not through a world-readable bucket of payloads.

What you should copy from a large lake, and what you should not

The pattern worth copying is immutable raw files, a columnar curated zone, partition pruning on a business date, schema evolution by additive optional fields, and compaction. The pattern not worth copying is a format zoo: Parquet, ORC, Avro, and CSV all registered as the same table because each team picked a writer. One curated columnar format, one raw convention, and a registry or schema file for events. TechLearningPro can run that with Arrow and a warehouse loader. It does not need a new storage product to obey the rule.

Best Practices

• Keep raw files immutable and in the producer's format. Transform downstream.
• Agree CSV in writing: encoding, delimiter, header, null, types, dates. Parse by header name and reject drift.
• Use NDJSON for event landing. Quarantine bad lines with a counter.
• Put analytical facts in one columnar format. Parquet is the default here. Use ORC when the existing platform is ORC, not in addition.
• Add fields as optional with defaults. Do not rename or reuse a field for a new meaning without a rewrite and a new name.
• Store identifiers that must round-trip as strings in JSON and as integers only after a validated cast. Store money as integers of minor units, matching lessons 9 and 10.
• Dictionary-encode low-cardinality columns. Compress curated Parquet with a codec every reader in the path supports.
• Partition by coarse business dates. Compact small files. Do not partition by learner or order id.
• Separate ingest date from event or sold date in the path.
• Prove column pruning and row-group skipping on a fixture before you claim the layout is cheap.
• Keep email and other personal columns out of mart files that do not need them. Restrict the raw bucket.
• Version the schema next to the code that writes it, and test new readers against old fixtures.
• Make partition overwrite cover the partition you recomputed, not the table.
• Reconcile curated sums to the warehouse control total. The file format is not a new source of truth.

Common Mistakes and Anti-Patterns

CSV split on commas. Quoted titles contain commas. Consequence: shifted columns and quiet wrong ids. Fix: a real CSV parser and a header contract.

One giant JSON array as the only copy. Truncation and replay are painful, and readers pull the whole document. Consequence: a bad tail loses the batch, and a scan cannot be parallel in any useful way. Fix: NDJSON in raw, columnar in curated.

Avro field added without a default, then old files read with the new schema. Resolution fails or the job fills an exception path you did not monitor. Consequence: backfill stops. Fix: default or null union, and a fixture test.

Renaming a Parquet column in the writer only. Old partitions still have the old name. Consequence: nulls or duplicate columns depending on the reader. Fix: a new field name plus a view, or a rewrite of old files with a documented mapping.

Partition by high-cardinality id. Millions of directories. Consequence: the query spends its time listing. Fix: partition by month or day, filter the id inside the file.

A small file per task per batch with no compaction. Drivers and object storage choke on metadata. Consequence: the morning scan misses its window on a dataset that would fit in memory. Fix: compact to large Parquet files as part of the job.

Gzip NDJSON treated as a random-access table. The codec is fine for raw landing and hostile as the only analytical layout. Consequence: full decompress to read one day. Fix: raw can stay gzipped NDJSON; curated is columnar and splittable by row group.

Two curated formats for one fact. Parquet and ORC copies drift. Consequence: two sums. Fix: one writer.

Flattening current catalog title into historical Parquet during compaction. That is an unplanned type-1 change. Consequence: last year's track report moves. Fix: compact bytes, do not "enrich" history from current dimensions unless the SCD policy says to.

Landing already transformed. A bug in the transform has nothing to replay. Consequence: you ask the partner to resend, or you cannot. Fix: raw bytes first, ELT second.

Troubleshooting and Design Review

Design review for a new dataset:

1. Is this raw landing or curated grain? What is the replay story?
2. What is the schema, where is it stored, and what change is backward compatible?
3. Which columns are personal, and are they absent from the mart file?
4. What is the partition key, and what is its cardinality per month?
5. What file size and codec will the readers actually support?
6. Which reconciliation query must match after the write, and which partition does a replay overwrite?

Interview Questions and Answers

Q1. When would you choose CSV, JSON, Avro, or Parquet for TechLearningPro?

CSV fits a modest partner exchange with a written header and type agreement. JSON, as NDJSON, fits API landing and logs where the producer is a client and humans still open a line. Avro fits a binary row contract with schema resolution, especially when events move through a registry and you need additive evolution. Parquet fits curated analytical tables: columnar reads, statistics, and compression. ORC is the alternative columnar choice when the platform is already ORC. The event bus is not Parquet, and the finance scan is not CSV. Raw landing stays in the producer format so ELT can replay.

Q2. What does a Parquet reader skip when a query filters sold_date and selects only the amount?

Column pruning skips the other column chunks. Row-group statistics in the footer let the reader skip groups whose min and max dates fall outside the filter, when those stats exist. Inside a group that qualifies, pages are decompressed for the columns requested. This is not a row-level index for every value. If you partitioned by sold_month, entire files or directories can be skipped before the footer is read. A function wrapped around the date, or a partition key that is not the filter, defeats that skip. The sum is still only correct if the file's grain is the fact grain.

Q3. How does Avro read a file written with an older schema after you add a field?

The reader schema includes the new field with a default, often null via a union. Resolution fills the default when the writer schema omitted the field, and it uses the written value when the writer included it. A new field without a default cannot be filled from old data, so old files fail that reader. Fields the reader does not know are skipped, which is why old readers can tolerate some additions. Renames need aliases. The compatibility direction you configure in a registry should match that promise, and a fixture written by the old schema should be in the test.

Q4. Why is a single JSON array a weak landing format for events?

Parsers typically need a complete document, so a truncated upload loses every event in the array, and parallel readers cannot take a line range cleanly. Field names repeat, which is fine for a payload and wasteful as a scan format. NDJSON makes one event one line, so quarantine is local. Neither JSON shape gives column statistics. Curated scans belong in Parquet or ORC after validation. Keep the NDJSON immutable so a bad transform can be replayed.

Q5. What is the difference between a Parquet encoding and a compression codec?

Encoding is column-specific representation: dictionary for repeated track names, deltas for sorted integers, plain bytes when nothing repeats. The compression codec, such as Snappy, Gzip, or Zstandard, then compresses those encoded pages. Dictionary encoding is why a low-cardinality column shrinks before the codec runs. A codec on already compressed media wastes CPU. Analytical tables usually want both a suitable encoding and one codec every downstream reader supports. The Spark or Arrow default is not a decision until you name the readers.

Q6. A month of data is slow even though each file is tiny and "parallel." What happened?

Per-file overhead dominates: directory listings, footers, and task startup. Partitioning by learner_id makes that worse by creating a directory per learner. The repair is a coarse partition such as sold_month and compaction into large files, with row groups big enough for statistics to be useful. Parallelism comes from several large files, or from row groups, not from a million objects. Point lookups by learner are a different serving path.

Q7. How do you add discount_cents to curated Parquet without breaking old months?

Add it as a nullable integer. Readers that match by name treat old files as null for that column. Publish a view that defines whether null means zero. Change the reconciliation in the same release if the money formula changed. Do not rename extended_amount_cents in place. Do not write a required field and expect September's files to satisfy it. A backfill rewrite is optional and scheduled. Until it runs, the view holds the definition.

Q8. Why keep raw CSV or JSON if the curated Parquet is what people query?

The raw object is the replay log. A bad cast, a wrong grain, or a premature drop of a new field can be rebuilt from it. If you transform before landing, the only copy is the mistake. Raw files also answer audit questions about what the partner actually sent, which a cleaned table cannot. They need access control and retention because they may contain personal fields the mart correctly omitted.

Q9. ORC and Parquet both store columns. Why not write both?

They solve the same curated-scan problem with different layouts, stripes versus row groups, and different writer defaults. Two copies drift and double the compaction and evolution work. Choose Parquet unless an existing Hive or Spark estate is standardized on ORC, in which case follow that estate for the shared tables. The grain, the partition, the codec, and the schema policy decide cost and correctness more than the format logo once one columnar format is in place.

Q10. A producer sends learner ids as JSON numbers and some arrive in scientific notation in a downstream CSV. Where do you stop that?

In the contract: ids are JSON strings. In the CSV agreement: no spreadsheet round-trip, integer tokens only, header-based parse, failed cast quarantined. Do not parse ids with a floating-point type at any stage. The raw line is the evidence when a cast fails. A lenient loader that nulls a failed id will publish unassigned seats that look like a product problem.

Q11. How do file partitions interact with late events and with lesson 10's business date?

Raw partitions use ingest date so the landing is append-only by arrival. Curated fact partitions use the sold or event date so finance scans September in one place. A late event is written into the old business-date partition and that partition is recomputed. Overwrite mode must target that partition. Using ingest date as the only curated partition hides late business activity in the wrong month and makes the dimensional grain's date meaningless on disk.

Q12. What should Module 2 inherit from this lesson so ETL does not invent a format?

A zone map: raw CSV and NDJSON immutable, optional Avro where a registry exists, curated Parquet at the declared grain, one codec, a compatibility fixture for schema changes, compaction, and partition overwrite rules. ELT loads raw first and transforms after. The reconciliation to extended_amount_cents still gates publish. Integration patterns decide transports and retries. They do not get a private JSON mart that bypasses the grain. The next module can start from this layout on day one.

Hands-On Exercises

Exercise 1. CSV contract and NDJSON quarantine

Problem. Load a seat file and an event log that both contain one deliberate defect.

Tasks.

1. Write a CSV with a header, a quoted comma inside a title if you include titles, and a second file whose column order changed. Show that name-based loading with an expected header rejects the second file.
2. Write NDJSON with one broken line. Count quarantined lines and parse the good ones.
3. State where the raw bytes live and why you do not delete them after a successful curated write.
4. Note which fields are personal and must not be copied into a finance extract.

Acceptance. A shifted CSV does not load. A bad JSON line does not drop the whole file. Good lines have typed ids, not floats.

Exercise 2. Parquet grain, prune, and an added column

Problem. Write fact_order_line Parquet for two months and read October only.

Tasks.

1. Use pyarrow with an explicit schema, Zstandard or Snappy, and a dictionary on track_name.
2. Write September and October data in separate partitions or clearly separated row groups.
3. Read only extended_amount_cents and the date filter for October. Record how many rows returned versus rows written.
4. Add nullable discount_cents to a new schema and read the old file. Document the nulls.
5. Describe the Module 2 overwrite behavior for a replay of October only.
6. Tie the October sum to the lesson 10 reconciliation sentence.

Rubric.

Lesson Summary

CSV is a partner format with a written contract and no types. NDJSON is a landing format for events, one record per line, immutable so a transform can be replayed. Avro adds a real schema and resolution rules: new fields need defaults if old data must remain readable. Parquet, or ORC when that is the platform standard, stores the curated fact in columns, with encodings, a compression codec, and statistics that skip irrelevant row groups. None of those choices may change the order-line grain, the sold date, or the ban on restating history during compaction.

Partition raw data by ingest date and curated facts by business month. Compact small files. Add columns as optional. Keep personal fields out of the finance file. Reconcile the Parquet sum to the warehouse sum before anyone builds a dashboard on the path.

That layout is the input to Module 2. Data Integration Patterns will move these files and tables between systems. ETL and ELT Architecture will place the transform after the raw landing and schedule the dimension-then-fact load from lesson 10. Later modules add Spark, Kafka, and lakehouse table formats on top of the same choices: schema, grain, replay, and a columnar curated zone. Foundations ends here. The bytes are ready to move.

Further Reading

• RFC 4180, Common Format and MIME Type for Comma-Separated Values (CSV) Files, as a baseline, not as what every exporter emits.
• ECMA-404 / JSON, and JSON Schema when a text payload needs a real contract.
• Apache Avro, specification, especially schema resolution and the object-container file.
• Apache Parquet, documentation, for row groups, encodings, and compression.
• Apache ORC, specification, for stripes and indexes if you compare formats.
• Apache Arrow, Parquet in PyArrow, for the writer and reader used in the walkthrough.
• Apache Spark, SQL data sources, for parquet, orc, json, and csv writers Module 2 will schedule.
• Confluent Schema Registry documentation on compatibility modes (backward, forward, full) if you place Avro on a bus. Use the registry's documented API rather than a private wrapper.