Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 38 ยท Patterns and Pipelines

The ETL Pipeline Pattern

Design a small DuckDB data pipeline with raw, staging and mart layers: land files, clean and type columns with SQL, build tables, and export Parquet for others.

A pipeline takes messy inputs and turns them into trustworthy tables. DuckDB is a great pipeline engine because one process can read CSV, JSON and Parquet, transform with SQL, and write Parquet, with no cluster. This lesson gives you a layout that scales from a script to a scheduled job.

The real DuckDB does not run on this site, so the file-reading statements (read_csv, COPY) are read-only. The transformation SQL is portable and runs here against small inline tables.

Three layers

flowchart LR
    A["Source files (CSV / JSON / API dumps)"] --> B["raw: loaded as is"]
    B --> C["staging: typed, cleaned, deduplicated"]
    C --> D["marts: business tables for reports"]
    D --> E["Parquet export / BI tool"]

Each layer has one job, so failures are easy to locate.

  • raw: a copy of the input with minimal changes (all columns text if needed, plus the file name and load time). Never edit it; it is your audit trail and lets you rebuild.
  • staging: one cleaned table per source: correct types, trimmed text, parsed dates, renamed columns, duplicates removed.
  • marts: tables shaped for questions: facts, dimensions, daily summaries.

In DuckDB these can be schemas inside one database file:

SQL
CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS staging;
CREATE SCHEMA IF NOT EXISTS marts;

Step 1: land the raw data

SQL
CREATE OR REPLACE TABLE raw.orders AS
SELECT *, filename AS source_file, now() AS loaded_at
FROM read_csv('incoming/orders_*.csv', all_varchar = true, filename = true);

all_varchar = true keeps every field as text so a stray N/A in a numeric column cannot break the load. The type problems are handled next, in SQL you control.

Step 2: stage and clean

Staging does the unglamorous work. DuckDB's TRY_CAST returns NULL instead of raising an error for bad values, which makes bad rows visible instead of fatal:

SQL
CREATE OR REPLACE TABLE staging.orders AS
SELECT
    TRY_CAST(order_id AS INTEGER)       AS order_id,
    trim(lower(customer_email))         AS customer_email,
    TRY_CAST(order_date AS DATE)        AS order_date,
    TRY_CAST(amount AS DECIMAL(12,2))   AS amount,
    nullif(trim(status), '')            AS status
FROM raw.orders
QUALIFY row_number() OVER (PARTITION BY order_id ORDER BY loaded_at DESC) = 1;

The same ideas in portable SQL, using CAST, CASE and a dedupe by the latest row per key:

SQL Staging: clean, type and deduplicate

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

Order 2 appears twice; the latest load wins (amount 25.0). Order 3 has an empty status, converted to NULL.

Step 3: build marts

Marts are what people query. Keep them simple, denormalized and documented:

SQL Mart: daily revenue by status

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

In DuckDB, FILTER makes this shorter: sum(amount) FILTER (status = 'paid').

Step 4: export

SQL
COPY marts.daily_revenue TO 'out/daily_revenue.parquet' (FORMAT parquet, COMPRESSION zstd);
COPY marts.daily_revenue TO 'out/daily_revenue.csv' (HEADER, DELIMITER ',');

A BI tool, a notebook or another service can read the Parquet file; the pipeline's job is done.

Making it repeatable

Put the SQL in files and run them in order:

TEXT
pipeline/
  01_raw.sql
  02_staging.sql
  03_marts.sql
  04_export.sql
  run.py
Python
import duckdb, pathlib

con = duckdb.connect("warehouse.duckdb")
for path in sorted(pathlib.Path("pipeline").glob("*.sql")):
    print("running", path.name)
    con.execute(path.read_text())       # a file with several statements runs them all
con.close()

Guidelines:

  • Idempotent: use CREATE OR REPLACE TABLE so rerunning gives the same result.
  • Transactional: wrap writes in BEGIN; ... COMMIT; so a failure leaves the previous mart intact.
  • Parameterized: pass dates and folders in as variables (SET VARIABLE run_date = '2026-03-01'; ... getvariable('run_date')) or through Python formatting of trusted values.
  • Observable: count rows at each stage and log them; fail on surprises (see data quality).
  • Version controlled: the SQL files are the pipeline; review and test them like code.

Tools like dbt and SQLMesh can orchestrate this exact structure on DuckDB if the project grows. The patterns stay the same.

Reusable pieces

CREATE MACRO captures logic once:

SQL
CREATE OR REPLACE MACRO clean_email(e) AS nullif(lower(trim(e)), '');
CREATE OR REPLACE MACRO safe_div(a, b) AS CASE WHEN b = 0 THEN NULL ELSE a / b END;

SELECT clean_email(email), safe_div(revenue, orders) FROM staging.daily;

Table macros return result sets: CREATE MACRO recent(n) AS TABLE SELECT * FROM staging.orders ORDER BY order_date DESC LIMIT n;.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why keep a raw layer that you never edit?

    An untouched copy of the input is the source of truth for reprocessing and debugging.
  2. 2. What does TRY_CAST do on an invalid value?

    TRY_CAST returns NULL, so bad values can be counted and inspected instead of failing the whole load.
  3. 3. Which keeps a rerun from duplicating data?

    Idempotent steps give the same result however many times they run.
  4. 4. What is a mart?

    Marts hold summarized, easy-to-query data for reports and dashboards.

Summary

  • Layer your pipeline: raw (as is), staging (clean, typed), marts (for questions).
  • Land files with all_varchar, then clean with TRY_CAST, trim, nullif and dedupe by key.
  • Export marts as Parquet; keep SQL in ordered files, idempotent and observable.
  • Macros share logic between steps.

Next: Incremental Loads and Partitioned Output