Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 36 ยท Ingestion and Batch Processing

Ingesting Files: CSV, JSON and Beyond

Build a robust file ingestion step: chunked reading, explicit schemas, quarantining bad rows, handling duplicates and late files, with runnable pandas examples.

File drops are the oldest ingestion method and still one of the most common: a partner emails or uploads a CSV every morning, a legacy system exports JSON lines, an object store fills with log files. Files look easy, yet they cause a large share of pipeline incidents because nobody controls how they are produced. This lesson turns "read a file" into a dependable ingestion step.

What can go wrong with a file

  • The header changes, or columns are reordered.
  • A number column suddenly contains N/A.
  • The file arrives twice, or late, or truncated.
  • The encoding or delimiter differs from yesterday.
  • The file is bigger than memory.

A robust ingestor assumes all of these will happen.

Read with an explicit schema

Letting pandas guess types means yesterday's data and today's data may get different types. State what you expect.

Python Explicit types and parse failures

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The pattern: read as text, convert with errors="coerce", split into good and bad. The original text of failures is preserved so you can see exactly what arrived.

Check the header before trusting the body

A silently reordered column corrupts every number. Verify the header against the expected list first, and fail loudly.

Python Header contract check

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A new extra column is usually harmless; a missing expected column is not. Decide which is which and write it down.

Process big files in chunks

Use chunksize to read a file piece by piece, keeping memory constant. The example simulates a large file by repeating rows.

Python Chunked aggregation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Aggregates that can be combined (sums, counts, minimum, maximum) work well in chunks. Averages must be carried as a sum and a count, then divided at the end.

JSON Lines

For event data, JSON Lines (one JSON object per line) beats a single giant JSON array: you can stream it, split it, append to it and recover from a damaged line.

Python Parse JSON Lines, skipping bad lines

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Line numbers in the reject list make a problem easy to find in the source.

Make ingestion idempotent: remember what you loaded

Files arrive twice. Keep a manifest of processed files (name plus a content hash) so a re-delivery is recognized and skipped, and a corrected file with the same name but different content is noticed.

Python A processed-files manifest

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Pair this with a partition overwrite from Idempotent Pipeline Design, so reloading a changed file replaces rather than duplicates.

Land first, then process

Do not parse and transform in the same step that fetches the file. First land the original bytes untouched in a raw area (a "bronze" folder, see Pipeline Architecture Patterns), then run a separate parsing step. If the parser has a bug, you fix it and rerun from the saved file without asking the partner to send it again.

flowchart LR
    A["Partner drop"] --> B["Land raw file<br/>(unchanged)"]
    B --> C["Validate header"]
    C --> D["Parse and type"]
    D --> E["Good rows to table"]
    D --> F["Bad rows to quarantine"]

Land, validate, parse, split.

Beyond CSV and JSON

Other file families you will meet: Excel workbooks (multiple sheets, merged cells; read with an explicit sheet name), XML (use an XML parser, never regular expressions), fixed-width text (columns defined by character positions), and columnar binary formats such as Parquet, which are the subject of the storage chapter. Binary columnar formats carry their schema inside the file, which removes most guessing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why read a CSV as text first and then convert columns?

    Reading as text avoids type guessing and lets you coerce, then quarantine rows that fail while keeping the original values.
  2. 2. Which aggregates combine correctly across chunks without extra bookkeeping?

    Sums and counts add up across chunks. An average needs the total sum and total count divided at the end.
  3. 3. What is the benefit of JSON Lines over one big JSON array?

    Each line is an independent record, so you can stream it and isolate damaged lines.
  4. 4. Why keep a manifest of processed files with a hash?

    The hash identifies content so duplicates are skipped and corrections are noticed.

Summary

  • Files are unreliable: assume header changes, bad values, duplicates, lateness and large size.
  • Read as text, convert with coercion, split good rows from quarantined rows, and check the header first.
  • Use chunking for large files and JSON Lines for event streams.
  • Land raw files untouched, track a manifest, and keep loads idempotent.

Next: Ingesting from APIs