Nine spreads: pipelines, ingestion, storage, SQL modelling, transformation with dbt, orchestration with Airflow, streaming, data quality, and the modern stack.
"ELT into a warehouse, modelled in layers with dbt, orchestrated by Airflow, tested for quality, and observable end to end."
Use the tabs or the ← → keys.
# the smallest honest pipeline: extract → load raw → transform with SQL (Python + DuckDB)
import duckdb, requests, pandas as pd
rows = requests.get("https://api.example.com/orders?since=2026-10-01", timeout=30).json()
con = duckdb.connect("warehouse.duckdb")
con.execute("CREATE SCHEMA IF NOT EXISTS raw")
con.register("df", pd.DataFrame(rows))
con.execute("CREATE OR REPLACE TABLE raw.orders AS SELECT *, now() AS _loaded_at FROM df")
con.execute("""
CREATE OR REPLACE TABLE daily_revenue AS
SELECT date_trunc('day', created_at) AS day, sum(total_cents)/100.0 AS revenue
FROM raw.orders WHERE status = 'paid' GROUP BY 1 ORDER BY 1""")
print(con.sql("SELECT * FROM daily_revenue LIMIT 5"))
"Extract, load raw with a load timestamp, transform in SQL, and every step is idempotent so we can re-run safely."
-- incremental load with a watermark and an upsert (PostgreSQL warehouse or staging) -- 1. read the last position SELECT coalesce(max(updated_at), '1970-01-01') AS wm FROM raw.orders; -- 2. extract from source: SELECT * FROM orders WHERE updated_at > :wm - interval '5 minutes' (small overlap) -- 3. upsert so reruns and overlaps are harmless INSERT INTO raw.orders (id, status, total_cents, updated_at) VALUES (...) ON CONFLICT (id) DO UPDATE SET status = EXCLUDED.status, total_cents = EXCLUDED.total_cents, updated_at = EXCLUDED.updated_at WHERE raw.orders.updated_at < EXCLUDED.updated_at;
# dlt: declarative ingestion with schema inference and incremental state
import dlt
@dlt.resource(primary_key="id", write_disposition="merge")
def orders(updated_at=dlt.sources.incremental("updated_at", initial_value="2026-01-01")):
for page in api_pages("/orders", since=updated_at.last_value):
yield page
dlt.pipeline("shop", destination="duckdb", dataset_name="raw").run(orders())
"Incremental with a watermark and overlap, upserted by key, and CDC where we need deletes."
$ pip install duckdb pyarrow pandas $ python - <<'EOF' import duckdb duckdb.sql("COPY (SELECT * FROM read_csv_auto('orders.csv')) TO 'orders.parquet' (FORMAT parquet, COMPRESSION zstd)") print(duckdb.sql("SELECT status, count(*) FROM 'orders.parquet' GROUP BY 1")) -- query files directly EOF $ ls -lh orders.csv orders.parquet # Parquet is usually several times smaller
# partitioned Parquet in a lake: skip whole folders when filtering
s3://acme-lake/silver/orders/
year=2026/month=10/day=29/part-0001.parquet
year=2026/month=10/day=30/part-0001.parquet
-- WHERE day = '2026-10-30' reads one folder, not everything
| Need | Reach for |
|---|---|
| app transactions | PostgreSQL / MySQL (row store) |
| analytics, BI | BigQuery · Snowflake · Redshift · ClickHouse · DuckDB |
| cheap raw storage | S3 / GCS / ADLS + Parquet |
| big-data compute | Spark · Trino · Databricks |
"Raw lands as Parquet partitioned by date, gets cleaned in silver, and business marts live in the warehouse."
State the grain first: "one row per order line". Everything else follows. Mixed grain is the root of most wrong numbers.
CREATE TABLE dim_customer ( customer_key BIGINT PRIMARY KEY, -- surrogate key customer_id TEXT NOT NULL, -- natural/business key name TEXT, segment TEXT, valid_from DATE, valid_to DATE, is_current BOOLEAN -- SCD type 2: keep history ); CREATE TABLE fact_order_lines ( order_id TEXT, line_no INT, date_key INT, customer_key BIGINT, product_key BIGINT, qty INT, revenue_cents BIGINT, PRIMARY KEY (order_id, line_no) -- the grain ); -- the question a star schema answers well SELECT d.month, p.category, c.segment, sum(f.revenue_cents)/100.0 AS revenue FROM fact_order_lines f JOIN dim_date d USING (date_key) JOIN dim_product p USING (product_key) JOIN dim_customer c USING (customer_key) WHERE c.is_current GROUP BY 1, 2, 3 ORDER BY revenue DESC;
"Grain declared first, facts for measures, conforming dimensions, and history kept with SCD 2 where it matters."
$ pip install dbt-duckdb # or dbt-postgres, dbt-bigquery, dbt-snowflake $ dbt init shop && cd shop $ dbt run ; dbt test ; dbt build # run models, run tests, or both in order $ dbt docs generate && dbt docs serve # browsable lineage graph
-- models/staging/stg_orders.sql
select
id::bigint as order_id,
user_id::bigint as user_id,
status,
total_cents / 100.0 as total,
created_at::timestamp as created_at
from {{ source('raw', 'orders') }}
where id is not null
-- models/marts/fct_orders.sql (incremental: only process new rows)
{{ config(materialized='incremental', unique_key='order_id') }}
select o.order_id, o.created_at, u.segment, o.total
from {{ ref('stg_orders') }} o
join {{ ref('stg_users') }} u using (user_id)
{% if is_incremental() %} where o.created_at > (select max(created_at) from {{ this }}) {% endif %}
# models/marts/schema.yml: tests are assertions about the data
models:
- name: fct_orders
columns:
- name: order_id
tests: [unique, not_null]
- name: segment
tests: [{ accepted_values: { values: [free, pro, team] } }]
"Staging, intermediate and mart layers in dbt, incremental where big, with tests on keys and accepted values."
# dags/daily_shop.py (Airflow 2.x TaskFlow API)
from datetime import datetime, timedelta
from airflow.decorators import dag, task
from airflow.operators.bash import BashOperator
@dag(schedule="0 3 * * *", start_date=datetime(2026, 10, 1), catchup=False,
default_args={"retries": 3, "retry_delay": timedelta(minutes=5)}, tags=["shop"])
def daily_shop():
@task
def extract(ds=None): # ds = the logical date, e.g. "2026-10-30"
return extract_orders_to_s3(day=ds) # returns a path, not data
@task
def load(path: str):
copy_s3_to_warehouse(path, table="raw.orders")
build = BashOperator(task_id="dbt_build", bash_command="cd /opt/shop && dbt build --target prod")
load(extract()) >> build
daily_shop()
$ airflow dags test daily_shop 2026-10-30 $ airflow dags backfill daily_shop -s 2026-10-01 -e 2026-10-07
"A DAG per pipeline, idempotent date-parameterized tasks, retries, and alerts on failed tests and stale data."
$ docker run -d --name redpanda -p 9092:9092 redpandadata/redpanda start --overprovisioned --smp 1 --kafka-addr 0.0.0.0:9092 $ pip install confluent-kafka
from confluent_kafka import Producer, Consumer
import json
p = Producer({"bootstrap.servers": "localhost:9092"})
p.produce("page_views", key="user42", value=json.dumps({"user": 42, "url": "/pricing", "ts": 1761800000}))
p.flush()
c = Consumer({"bootstrap.servers": "localhost:9092", "group.id": "warehouse-loader",
"auto.offset.reset": "earliest", "enable.auto.commit": False})
c.subscribe(["page_views"])
while True:
msg = c.poll(1.0)
if msg is None or msg.error(): continue
event = json.loads(msg.value())
write_to_warehouse(event) # must be idempotent (key by event id)
c.commit(msg) # commit the offset AFTER the write succeeds
-- streaming SQL (Flink SQL): page views per minute, tolerating 30 s of lateness SELECT window_start, url, count(*) AS views FROM TABLE(TUMBLE(TABLE page_views, DESCRIPTOR(ts), INTERVAL '1' MINUTE)) GROUP BY window_start, window_end, url;
"Commit offsets after a successful idempotent write, windows on event time with a lateness allowance, and batch where seconds don't matter."
-- plain SQL checks that return rows only when something is wrong -- 1) duplicates in a primary key SELECT order_id, count(*) FROM fct_orders GROUP BY 1 HAVING count(*) > 1; -- 2) orphaned foreign keys SELECT f.order_id FROM fct_orders f LEFT JOIN dim_users u USING (user_id) WHERE u.user_id IS NULL; -- 3) freshness: the newest row should be recent SELECT 1 WHERE (SELECT max(created_at) FROM fct_orders) < now() - interval '6 hours'; -- 4) volume anomaly: today's count vs the 7-day average WITH d AS (SELECT created_at::date AS day, count(*) n FROM fct_orders GROUP BY 1) SELECT * FROM d WHERE day = current_date AND n < 0.5 * (SELECT avg(n) FROM d WHERE day >= current_date - 7 AND day < current_date);
# Great Expectations style assertion in Python
import great_expectations as gx
v = gx.get_context().sources.pandas_default.read_csv("orders.csv")
v.expect_column_values_to_not_be_null("order_id")
v.expect_column_values_to_be_between("total", min_value=0, max_value=100000)
v.expect_column_values_to_be_in_set("status", ["open", "paid", "refunded"])
print(v.validate().success)
"Freshness, volume, uniqueness and reconciliation checks gate each run, with contracts at the source and alerts to an owner."
$ pip install dlt[duckdb] dbt-duckdb dagster dagster-webserver $ docker run -d -p 3000:3000 --name metabase metabase/metabase $ dbt build && dagster dev
"Boring and reliable first: ingest, model, test, schedule, document, and only then reach for streaming or Spark."