💼 Business context
PesaFlow is a Nairobi-based fintech that processes payments in both Kenyan Shillings (KES) and US Dollars (USD) for small businesses across East Africa. Their finance team needs a daily reconciliation report by 6am each morning — before the Nairobi markets open — showing yesterday's transaction volumes, revenue, failed transaction rates, and average processing latency per currency.
The raw transactions land in a Postgres database managed by the product engineering team. The data engineering team owns everything downstream: extraction, transformation, data quality, and the mart that analysts query. The pipeline must be idempotent — re-running it for the same date should overwrite, never duplicate.
A production Airflow DAG that triggers at 2am daily, extracts transactions from Postgres via PySpark JDBC, writes Parquet to a local data lake partitioned by date, runs dbt models on DuckDB to produce a daily summary mart, and notifies the finance team via Slack when the report is ready.
🏗️ Architecture
The pipeline has five distinct layers. Each layer has a single responsibility; failures in one layer do not corrupt data in the layers above.
PesaFlow Daily Batch Pipeline
10k rows/day
Orchestrator
+ clean + write
partitioned by date
mart_daily_summary
🐘 Step 1 — Postgres setup & seed data
Start with a realistic Postgres schema. The transactions table stores every payment attempt: successful, failed, and pending. We seed it with Faker so you have plausible KES and USD amounts, realistic timestamps, and a mix of statuses.
Schema — create the transactions table
Run this once against your Postgres instance. The table is partitioned by txn_date (a generated column) so JDBC predicates push down to the storage level.
Seed — insert 10k rows with Faker
Run python seed_postgres.py once. It generates 90 days of history: ~10,000 transactions per day, 60% KES and 40% USD, with a 4% failure rate and realistic latency distribution.
⚡ Step 2 — PySpark extraction & Parquet write
The PySpark job is the core of the pipeline. It reads yesterday's transactions from Postgres using a JDBC predicate pushdown, cleans the data, and writes Parquet files partitioned by date to the data lake. This job is designed to be idempotent — it overwrites the existing partition on each run.
predicates with numPartitions, lowerBound, upperBound, and partitionColumn to parallelise the JDBC read across multiple Spark tasks. For PesaFlow's 10k rows/day, a single predicate is sufficient.
🔧 Step 3 — dbt models on DuckDB
The dbt project has three layers following the medallion convention. dbt runs against DuckDB, which reads the Parquet files directly — no separate loading step required. DuckDB's Parquet scanner is columnar and pushes predicates down to the file level.
stg_transactions.sql — raw Parquet scan
mart_daily_summary.sql — analyst-facing mart
🌬️ Step 4 — Airflow DAG orchestration
The Airflow DAG wires the four tasks together. It uses BashOperator for PySpark (keeping the DAG simple) and EmailOperator for notification. The DAG is idempotent — set catchup=False to prevent Airflow from backfilling every missed run on first deploy.
Start the stack with docker compose up, then trigger the DAG:
docker compose up -d
python seed_postgres.py
airflow dags trigger batch_reconciliation --conf '{"run_date":"2026-04-26"}'
🦆 Step 5 — Query the mart with DuckDB
Once dbt has refreshed the mart, analysts can query it directly via the DuckDB CLI or any JDBC-compatible tool. The query below retrieves yesterday's summary — the exact report the finance team sees each morning.
🐳 Docker Compose setup
The entire local stack — Postgres, Airflow webserver, Airflow scheduler, and DuckDB volume — runs with a single docker compose up.
🔧 Troubleshooting
The three most common issues when running this pipeline for the first time:
postgresql-42.6.0.jar from Maven Central and place it at /opt/jars/postgresql-42.6.0.jar. Pass --jars /opt/jars/postgresql-42.6.0.jar to spark-submit, or set spark.jars in SparkSession config. Confirm with: jar tf /opt/jars/postgresql-42.6.0.jar | grep Driverdriver-memory 4g is insufficient for your dataset, add numPartitions=8 to the JDBC read to split the extract into 8 parallel tasks. Set spark.sql.shuffle.partitions=8 to match. Also consider setting fetchsize=10000 in JDBC properties to control how many rows Postgres sends per network round trip.~/.dbt/profiles.yml. The DuckDB profile must point to the correct database file path. Example correct entry:pesaflow: target: prod outputs: prod: type: duckdb path: /opt/airflow/data/pesaflow.duckdb threads: 4.Also ensure the
dbt-duckdb adapter is installed: pip install dbt-duckdb==1.7.3.🏆 Challenge tasks
After PySpark writes the Parquet output, add a Great Expectations checkpoint that validates:
- No null
txn_idvalues amountis always > 0currencyis in {KES, USD}- Row count is between 8,000 and 15,000 per day
Hint: use ge.from_pandas(df_clean.toPandas()) for small datasets, or configure a Spark datasource for large ones. Fail the Airflow task if the checkpoint does not pass.
Replace the simple EmailOperator with a PythonOperator that:
- Queries
mart_daily_summarywith DuckDB Python API - Converts the result to a Pandas DataFrame, then to a CSV
- Sends the CSV as an attachment via
smtplib
Use MailHog (port 8025) to verify the email locally without a real SMTP server.
🧠 Quiz — 4 questions
.mode("overwrite") with .partitionBy("txn_date"). What does this guarantee?materialized='view' while the mart uses materialized='table'?catchup=False. What would happen if you changed it to catchup=True after deploying the DAG late?Next: Capstone — End-to-End Streaming Pipeline (Lesson 35).
🎤 Interview Q&A
.mode("overwrite") with .partitionBy("txn_date") — Spark overwrites only the target date partition, leaving all other partitions untouched. The dbt mart model is materialised as a table and dbt replaces it fully on each run rather than appending. The Airflow DAG passes the logical date ({{ ds }}) to every task, so each task knows exactly which date to process and overwrite. The Postgres seed uses ON CONFLICT DO NOTHING so re-running the seed is safe. The net effect: you can trigger the DAG for any past date and it will produce correct results without creating duplicates.
{{ ds }} macro expand to?
{{ ds }} is an Airflow Jinja template variable that expands to the logical date of the DAG run in YYYY-MM-DD format. For a DAG scheduled at 0 2 * * * that triggers on 2026-04-27, {{ ds }} expands to 2026-04-26 — yesterday's date. This is intentional: Airflow's logical date represents the start of the data interval the DAG is processing, not the time the DAG actually runs. Because our daily pipeline processes yesterday's data, {{ ds }} gives us exactly the right date to pass to PySpark and dbt without any manual date arithmetic. This makes backfilling trivial: trigger the DAG with a specific run_date and {{ ds }} automatically resolves to the correct processing date.
✅ Key takeaways
- A production batch pipeline has five distinct layers — source, extraction, transformation, orchestration, and serving — each with a single responsibility. Isolating layers means you can swap DuckDB for BigQuery or Airflow for Prefect without rewriting the whole pipeline.
- PySpark JDBC reads from Postgres using predicate pushdown on a partitioned column (
txn_date), extracting only the target day's rows. This keeps extract time proportional to the daily volume, not the total table size. - Parquet with Hive-style partitioning (
txn_date=YYYY-MM-DD/) is the canonical data lake format: DuckDB reads it with columnar projection, dbt references it as a source without loading, and the directory structure is human-readable. - dbt's three-layer convention (staging → intermediate → mart) separates raw cleaning from business logic from analyst-facing aggregates. This makes it easy to add new KPIs (e.g., 7-day rolling revenue) without touching the staging layer.
- Airflow's
{{ ds }}macro makes pipelines naturally backfill-safe: trigger the DAG for any historical date and every task processes the correct data window without manual intervention. - Idempotency at every layer — Spark's
overwritemode, dbt'smaterialized='table'replacement, and Postgres'sON CONFLICT DO NOTHINGseed — means re-running the pipeline for any date is always safe and produces correct results. This is the most important property of a production batch pipeline.
From Postgres to Parquet to DuckDB — in production
Join the Best AI Program: The AI-Era Data Engineer — 5 months, fully online, with placement assistance. Week 1 is free.
Explore The AI-Era Data Engineer →