💼 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.

What you will build

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

Postgres
Raw transactions
10k rows/day
→
Airflow DAG
schedule: 0 2 * * *
Orchestrator
→
PySpark
JDBC extract
+ clean + write
→
Parquet Lake
./data/lake/
partitioned by date
→
dbt + DuckDB
stg → int → mart
mart_daily_summary
1
Source — Postgres
Raw transactions table. Seeded with Faker. Read via PySpark JDBC driver. This is the single source of truth for all financial records.
2
Extraction — PySpark + JDBC
Reads yesterday's partition from Postgres. Cleans nulls, parses ISO timestamps, casts amounts to Decimal(18,4). Writes Parquet partitioned by txn_date to ./data/lake/transactions/.
3
Transformation — dbt on DuckDB
Three-layer dbt project: stg_transactions (raw Parquet scan), int_transactions_enriched (currency conversion, latency calc), mart_daily_summary (group by date + currency, compute KPIs).
4
Orchestration — Airflow
DAG id: batch_reconciliation. Four tasks in sequence: extract_task → transform_task → dbt_task → notify_task. Retries=2 on each task. SLA: 3 hours (must finish before 5am).
5
Serving — DuckDB + Analysts
Analysts query mart_daily_summary directly with DuckDB CLI or Metabase. mart_daily_summary is a materialised table refreshed on each dbt run. Query time: <200ms for a full year of data.

🐘 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.

Step 1a

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.

schema.sql Step 1 · Postgres DDL
Step 1b

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.

seed_postgres.py Step 1 · Faker seed script
expected output
Seeded 900,000 rows across 90 days.

⚡ 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.

extract_transform.py Step 2 · PySpark JDBC → Parquet
expected output
Extracting transactions for date: 2026-04-26 Extracted 10,000 rows from Postgres. Clean rows: 9,987 rows Parquet written to ./data/lake/transactions/txn_date=2026-04-26/
JDBC partitioning tip: For large tables (> 5M rows), replace 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

models/staging/stg_transactions.sql Step 3 · dbt staging model

mart_daily_summary.sql — analyst-facing mart

models/marts/mart_daily_summary.sql Step 3 · dbt mart — daily KPIs per currency

🌬️ 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.

dags/batch_reconciliation.py Step 4 · Airflow DAG — schedule 0 2 * * *
Running the DAG locally

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.

query_mart.sql Step 5 · DuckDB — analyst query
expected output — ASCII table
┌─────────────┬──────────┬────────────┬──────────────┬─────────────┬──────────────────┬─────────────┬───────────────┬───────────────┐ │ report_date │ currency │ total_txns │ success_txns │ failed_txns │ total_revenue │ failed_pct% │ avg_latency_ms│ p95_latency_ms│ ├─────────────┼──────────┼────────────┼──────────────┼─────────────┼──────────────────┼─────────────┼───────────────┼───────────────┤ │ 2026-04-26 │ KES │ 6,002 │ 5,764 │ 238 │ 407,832,145.60 │ 3.97 │ 842.3 │ 2,541.0│ │ 2026-04-26 │ USD │ 3,985 │ 3,827 │ 158 │ 2,891,047.88│ 3.97 │ 839.1 │ 2,538.4│ └─────────────┴──────────┴────────────┴──────────────┴─────────────┴──────────────────┴─────────────┴───────────────┴───────────────┘

🐳 Docker Compose setup

The entire local stack — Postgres, Airflow webserver, Airflow scheduler, and DuckDB volume — runs with a single docker compose up.

docker-compose.yml Local dev stack

🔧 Troubleshooting

The three most common issues when running this pipeline for the first time:

JDBC driver ClassNotFoundException
PySpark cannot find the Postgres JDBC jar. Fix: download 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 Driver
PySpark OOM on large extracts
If driver-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 cannot connect to DuckDB
Check ~/.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

Extend the capstone
Challenge 1 — Great Expectations data quality checkpoint

After PySpark writes the Parquet output, add a Great Expectations checkpoint that validates:

  • No null txn_id values
  • amount is always > 0
  • currency is 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.

Challenge 2 — EmailOperator with mart snapshot attachment

Replace the simple EmailOperator with a PythonOperator that:

  1. Queries mart_daily_summary with DuckDB Python API
  2. Converts the result to a Pandas DataFrame, then to a CSV
  3. 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

Question 1 of 4
The PySpark Parquet write uses .mode("overwrite") with .partitionBy("txn_date"). What does this guarantee?
Question 2 of 4
Why does the dbt stg_transactions model use materialized='view' while the mart uses materialized='table'?
Question 3 of 4
The Airflow DAG sets catchup=False. What would happen if you changed it to catchup=True after deploying the DAG late?
Question 4 of 4
DuckDB reads the mart_daily_summary Parquet with a query time of <200ms for a full year. What is the primary reason DuckDB is fast for this analytical workload?
0
/ 4
Batch pipeline mastered!

Next: Capstone — End-to-End Streaming Pipeline (Lesson 35).

🎤 Interview Q&A

Medium How do you make a batch pipeline idempotent?
+
An idempotent pipeline produces the same output regardless of how many times it runs for the same input date. In this capstone we achieve idempotency at every layer: the PySpark job uses .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.
Hard Why use PySpark for extraction rather than reading Postgres directly in dbt?
+
dbt has a Postgres adapter and can read from Postgres directly. For PesaFlow's 10k rows per day, this would work fine. We use PySpark for three reasons that matter at scale. First, PySpark can parallelise the JDBC read across multiple partitions, extracting millions of rows in parallel whereas dbt runs a single sequential query. Second, PySpark writes Parquet to a durable, schema-on-read data lake — decoupling the analytical workload from the Postgres OLTP database. If Postgres has a maintenance window or schema change, the dbt models still work against existing Parquet. Third, PySpark is where data quality transformations (null drops, type casts, anomaly filters) belong — keeping dbt focused on business logic transformations. This separation of concerns — extract in Spark, transform in dbt — is the standard ELT pattern in modern data engineering teams.
Easy What does the Airflow {{ 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 overwrite mode, dbt's materialized='table' replacement, and Postgres's ON CONFLICT DO NOTHING seed — 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.
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

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 →