Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 38 ยท Projects and Practice

Project: A Parquet Data Lake Pipeline

Build a small data lake with DuckDB: land daily files, validate and dedupe, write partitioned Parquet, compact small files, expose a view and run quality checks daily.

This project ties the whole chapter together. You will design a small data lake: folders of Parquet files that any tool can read, produced by a daily DuckDB job. There is no server, no cluster and no vendor. The deliverable is a layout, a daily script, a catalog view for analysts, and checks that stop bad data.

The real DuckDB does not run in the browser here, so the file commands (read_csv, COPY, globs) are read-only. The decision logic (dedupe, validation, partition planning, compaction choices) is portable SQL that you can run on small stand-in tables.

The scenario

A mobile app drops one CSV per hour into incoming/ with events: event_id, user_id, event_type, event_time, amount. Files can be re-sent, can contain malformed rows, and arrive late. Analysts want fast queries by date and event type.

Step 1: lay out the lake

TEXT
lake/
  incoming/                 CSV drops from the app (never edited)
  bronze/events/            raw Parquet copy, partitioned by load date
  silver/events/            cleaned, deduplicated, partitioned by event date
  gold/daily_summary.parquet  small aggregated table for dashboards
  quarantine/               rejected rows with a reason
  _state/                   watermarks, run logs

The names bronze, silver and gold are a common convention: bronze = as received, silver = clean, gold = business-ready. They match the raw, staging and mart layers from the ETL lesson.

flowchart LR
    I["incoming CSV"] --> B["bronze: raw Parquet"]
    B --> V{"valid?"}
    V -- no --> Q["quarantine"]
    V -- yes --> S["silver: clean, deduped, partitioned"]
    S --> G["gold: daily summary"]
    S --> W["view for analysts"]

Bad rows leave the happy path and are kept for inspection.

Step 2: land incoming files as bronze

SQL
COPY (
  SELECT *, filename AS source_file, current_date AS load_date
  FROM read_csv('lake/incoming/*.csv', all_varchar = true, filename = true, union_by_name = true)
)
TO 'lake/bronze/events'
(FORMAT parquet, PARTITION_BY (load_date), OVERWRITE_OR_IGNORE, COMPRESSION zstd);

all_varchar stops a malformed value from failing the read. union_by_name handles files whose columns changed order or gained a column. Once a file is landed, move it to an archive folder so it is not read twice.

Step 3: validate and split

Rows that fail validation go to quarantine with a reason, instead of being dropped silently or blocking the run. The same logic in portable SQL:

SQL Validate rows and tag a rejection reason

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

Rows with a non-NULL reason go to quarantine/, the others continue. In DuckDB you would write each group with its own COPY (SELECT ... WHERE reject_reason IS NOT NULL) TO 'lake/quarantine/...'. Use TRY_CAST and try_strptime to produce the checks cleanly.

Step 4: dedupe into silver

The app resends files, so the same event_id can arrive twice. Keep one row per id, choosing the earliest load:

SQL Deduplicate by event_id

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

In DuckDB: SELECT * FROM tagged QUALIFY row_number() OVER (PARTITION BY event_id ORDER BY source_file) = 1. If silver already contains earlier data, an anti-join (WHERE event_id NOT IN (SELECT event_id FROM silver)) avoids rewriting old rows.

Then write silver partitioned by event date, not load date, because analysts filter on when things happened:

SQL
COPY (
  SELECT event_id, user_id::INTEGER AS user_id, event_type,
         event_time::TIMESTAMP AS event_time,
         event_time::DATE AS event_date,
         TRY_CAST(nullif(amount, '') AS DECIMAL(12,2)) AS amount
  FROM clean_rows
  ORDER BY event_time
)
TO 'lake/silver/events'
(FORMAT parquet, PARTITION_BY (event_date), OVERWRITE_OR_IGNORE, COMPRESSION zstd);

Late events land in older date folders. To keep it idempotent, rewrite only the affected dates: compute the set of dates present in today's rows, read those partitions, merge, and overwrite them.

Step 5: compact small files

Hourly drops create many tiny files per day. Small files slow queries and increase request counts. Compaction rewrites a day's files into one:

SQL
COPY (SELECT * FROM read_parquet('lake/silver/events/event_date=2026-03-01/*.parquet'))
TO 'lake/silver/events_compacted/event_date=2026-03-01/part.parquet'
(FORMAT parquet, COMPRESSION zstd, ROW_GROUP_SIZE 200000);

A planner query shows which partitions need compaction (many files, small average size). The idea, on stand-in metadata:

SQL Find partitions worth compacting

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

In DuckDB you build file_stats from parquet_file_metadata or glob('lake/silver/events/**/*.parquet') plus file sizes.

Step 6: gold and the analyst view

SQL
COPY (
  SELECT event_date, event_type, count(*) AS events,
         count(DISTINCT user_id) AS users, sum(amount) AS amount
  FROM read_parquet('lake/silver/events/*/*.parquet', hive_partitioning = true)
  GROUP BY ALL ORDER BY ALL
) TO 'lake/gold/daily_summary.parquet' (FORMAT parquet);

-- a catalog database file to share with analysts: only views, no data
ATTACH 'lake/catalog.duckdb' AS cat;
CREATE OR REPLACE VIEW cat.events AS
SELECT * FROM read_parquet('lake/silver/events/*/*.parquet', hive_partitioning = true);

Analysts open catalog.duckdb read-only and write SELECT * FROM events WHERE event_date = '2026-03-01': the view hides paths and options, and partition pruning does the rest.

Gold in portable SQL:

SQL Daily summary from clean events

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

Step 7: run it daily

Python
import duckdb, datetime, pathlib

def run(day: datetime.date):
    con = duckdb.connect()                # in-memory: the lake is the storage
    con.execute("SET memory_limit='2GB'; SET threads=4;")
    for step in sorted(pathlib.Path("pipeline").glob("*.sql")):
        con.execute(step.read_text().replace("{{day}}", day.isoformat()))
    checks = con.execute(open("pipeline/checks.sql").read()).fetchall()
    if checks:
        raise RuntimeError(f"data quality failed: {checks[:5]}")

run(datetime.date.today())

Schedule it with cron, systemd timers, GitHub Actions or an orchestrator. Write a run log (_state/runs.parquet) with counts per layer so you can chart row counts over time and catch volume problems (see data quality).

What you built

A pipeline without servers: durable Parquet storage, replayable from the raw layer, with quarantine, dedupe, partition pruning, compaction and checks. The next scale step is usually not a new engine but object storage plus a table format (Delta or Iceberg, read by DuckDB extensions) if you need ACID updates on the files.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the purpose of the quarantine folder?

    Quarantining bad rows keeps the main path clean without hiding problems.
  2. 2. Why partition silver by event date rather than load date?

    Partition by the column most queries filter on so pruning works.
  3. 3. Why compact small files?

    Fewer, larger files mean less metadata overhead and faster reads.
  4. 4. What does the catalog.duckdb file contain in this design?

    Views hide file paths and options; the data stays in Parquet.

Summary

  • Layer the lake: incoming, bronze, silver, gold, with quarantine for rejects.
  • Validate to a reason column, dedupe by key, write partitioned Parquet by the query-relevant date.
  • Compact small files, build gold aggregates, and expose a view in a small catalog database.
  • Run the SQL steps daily from a script that fails on quality problems.

Next: Practice: Analytics Query Workout