Batch File Ingestion

Why this matters

Batch file ingestion moves files (CSV, JSON, Parquet, etc.) from sources like S3, GCS, Azure Blob, or SFTP into your data lake or warehouse on a schedule. Data Engineers do this to onboard partners, load application exports, backfill history, and power analytics reliably.

• Real tasks: set up daily partner file drops, validate schema, partition data by date, and load into staging/warehouse with deduplication.
• Impact: correctness, recoverability, and predictable costs vs. always-on streaming.

Concept explained simply

Batch file ingestion is a repeatable recipe: discover files, validate and transform them, store them in a structured location, then load into query-ready tables. You run it on a schedule (for example, every day at 01:00).

Core patterns and formats

• Sources: S3/GCS/Blob storage, SFTP, shared folders, app exports.
• Formats: CSV (simple, fragile), JSON Lines (semi-structured), Parquet/Avro (columnar/binary, strong types), compressed with gzip or snappy.
• Partitioning: typically by event_date or ingestion_date (YYYY/MM/DD). Keep small files combined; target ~100–512 MB per file for efficient reads.
• Workflow: land (raw) → validate → normalize → load (staging) → merge into warehouse (dim/fact).
• Idempotency: safe to re-run without duplicating rows. Use unique keys + upsert/merge, or load into a temp area then atomically replace.

Worked examples

MERGE INTO warehouse.sales t
USING warehouse.sales_stg s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...)
VALUES (...);

Implementation steps (template)

1. Define contract: file naming, columns, types, delimiter, compression, delivery time.
2. Choose partitions: event_date if available; else ingestion_date.
3. Plan idempotency: unique key + merge, or replace partitions atomically.
4. Add validations: schema, row count thresholds, null checks, domain checks.
5. Handle errors: quarantine invalid files; continue for the rest.
6. Orchestrate: run daily with retries and alerting.
7. Catalog and document: what lands where, retention, and SLA.

Data validation and schema handling

• Header and types: verify exact column names and parse types.
• Row-level checks: primary key not null, amounts non-negative, timestamps parseable.
• Schema drift: on new columns, extend schema compatibly; on missing columns, backfill defaults or block and escalate.
• Bad rows: separate to a dead-letter file with reason; keep ingestion moving.

File partitioning and naming

Use stable, sortable names and directories so listing is predictable and idempotent.

raw/vendor/source=vendorA/dataset=sales/event_date=YYYY-MM-DD/part-0001.parquet
curated/vendorA/sales/event_date=YYYY/MM/DD/part-0001.snappy.parquet

• Include event_date, source, dataset in path.
• Atomicity: write to a temp path, then move/rename into the final partition.

Idempotency and deduplication

• Prefer upsert with a stable business key.
• De-duplicate in staging using window functions (keep latest by updated_at).
• Reruns should not create duplicates; protect with unique constraints when available.

-- Example dedup in staging
CREATE TABLE sales_stg_dedup AS
SELECT * EXCEPT(rn) FROM (
  SELECT s.*, ROW_NUMBER() OVER (
    PARTITION BY order_id ORDER BY updated_at DESC
  ) rn
  FROM sales_stg s
)
WHERE rn = 1;

Monitoring and retries

• Track counts: files discovered, files ingested, rows loaded, rows rejected.
• Alert on: missing expected file by SLA, schema mismatches, spike in rejects.
• Retry policy: exponential backoff for transient storage/SFTP errors.

Security and compliance basics

• Credentials: store securely; rotate regularly.
• PII: encrypt at rest; mask or tokenize sensitive fields early.
• Access: least privilege to buckets/containers and tables.

Exercises

Do these to lock in the skill. The same tasks are listed below with expected outputs.

Common mistakes (and how to self-check)

• Only filtering duplicates by file name. Self-check: re-run pipeline on the same day; do you get extra rows?
• Huge numbers of tiny files. Self-check: average file size; aim for hundreds of MB, not KB.
• No quarantine. Self-check: where do bad rows/files go?
• Coupling to ingestion order. Self-check: can files arrive late without breaking logic?
• Ignoring time zones. Self-check: are partitions based on UTC consistently?

Practical projects

• Partner dropzone: simulate an SFTP drop of daily transactions; land, validate, convert to Parquet, and upsert into a warehouse table.
• Historical backfill: ingest a month of archived CSVs using a watermark loop with idempotent merges.
• Schema evolution: accept a new optional column mid-week; keep pipeline running and reflect it downstream.

Mini challenge

You receive two files for the same day: sales_2024-05-15_part1.csv and sales_2024-05-15_part2.csv. Part1 included late fixes overlapping with Part2. Describe:

• Partitioning path you will use
• How you will deduplicate with updated_at logic
• How a re-run will avoid new duplicates

Who this is for

• Junior to mid Data Engineers who need reliable scheduled file loads
• Analytics Engineers owning pipelines for partner data
• Ops-minded engineers improving robustness and cost predictability

Prerequisites

• Comfort with SQL (SELECT, INSERT, MERGE/UPSERT)
• Basic understanding of object storage and folders/paths
• Familiarity with CSV/JSON and compression (gzip)

Learning path

1. This lesson: batch file ingestion fundamentals
2. Next: schema evolution and data contracts in ingestion
3. Then: orchestration, SLAs, and alerting
4. Finally: cost optimization and performance tuning

Next steps

• Complete the exercises below and compare with the provided solutions.
• Build one practical project and share your design doc for peer review.
• Take the quick test to confirm readiness.

Quick Test

Take the test below to check your understanding. Available to everyone. If you are logged in, your progress and score will be saved automatically.