An ETL pipeline that deliberately mixes engines so I could feel the difference between them rather than read about it:

  • DuckDB as the SQL engine, reading CSVs and moving data into Postgres
  • Pandas or Spark as the processing engine, with both paths implemented so they can be compared on the same data
  • Postgres as both data lake (landing schema) and warehouse (marts)
  • Kimball-style star schema for the dimensional model
  • Everything dockerized

What I learned

DuckDB was the surprise. Loading millions of rows was fast, handing data to Postgres was a single SQL statement, and converting into Pandas or Spark was seamless. It removed most of the friction I expected to spend the evening on.

It was also the first time the star schema stopped being a diagram in a book and became obviously useful: aggregations collapsed to a single join, and building a proper date dimension made time-based queries trivial instead of fiddly.

Not everything worked. Adding foreign key constraints through DuckDB’s Postgres extension failed, because add constraints is not implemented, so the relationships get altered in Postgres directly. Worth knowing before you plan around it.

The longer write-up, covering Airflow, DBT, Snowflake, Kafka, Terraform, and the AWS data stack, is here.