Module 4: Load to the warehouse · Lesson 4.4 · about 40 min
Postgres, DuckDB and ClickHouse side by side
Load the same ad data into three open-source databases, upsert the way each one expects, benchmark them fairly, and hide the differences behind one Warehouse resource.
Module 4 · about 40 minutes · read first: 4.3. You’ll be able to bulk-load and upsert into Postgres, DuckDB and ClickHouse, explain ClickHouse’s unusual update rules, run a fair benchmark, and write one Warehouse resource that targets any of them.
1. Why this matters
Low-cost data stacks for medium-sized companies are built on open-source databases. To recommend one honestly you need to know them by hand, not by blog post. In this lesson you load the same ad table into three of them, upsert it the way each expects, and measure.
2. Postgres
Start it in Docker:
docker run -d --name pg -e POSTGRES_PASSWORD=pg -p 5432:5432 postgres:16
COPY is Postgres’s bulk-load command: it streams many rows in one go, the way fast_executemany did for SQL Server. Then insert ... on conflict upserts:
import io, psycopg
with psycopg.connect("postgresql://postgres:pg@localhost:5432/postgres") as con, con.cursor() as cur:
cur.execute("create table if not exists stg_ads_load (like fct_ads_daily including defaults)")
cur.execute("truncate stg_ads_load")
buf = io.StringIO(); df.write_csv(buf); buf.seek(0) # df: a Polars DataFrame
with cur.copy("copy stg_ads_load from stdin with (format csv, header true)") as cp:
cp.write(buf.read()) # COPY: the fast path
cur.execute("""
insert into fct_ads_daily select * from stg_ads_load
on conflict (ad_date, campaign_id) do update set
spend = excluded.spend, clicks = excluded.clicks, conversions = excluded.conversions, updated_at = now()
where (fct_ads_daily.spend, fct_ads_daily.clicks, fct_ads_daily.conversions)
is distinct from (excluded.spend, excluded.clicks, excluded.conversions)""")
on conflict needs a primary key or unique constraint on (ad_date, campaign_id). excluded means “the row you tried to insert”. is distinct from is a null-safe “not equal”, so unchanged rows are skipped. (Keep the column order of the staging table and target the same, or list the columns.)
3. DuckDB
DuckDB runs inside your Python process: no server, one file.
import duckdb
con = duckdb.connect("data/wh.duckdb")
con.sql("""create table if not exists fct_ads_daily (
ad_date date, campaign_id varchar, spend decimal(18,2), clicks integer, impressions bigint,
conversions integer, conversion_value decimal(18,2), updated_at timestamp,
primary key (ad_date, campaign_id))""")
con.sql("""insert or replace into fct_ads_daily -- upsert: needs the primary key above
select ad_date, campaign_id, spend, clicks, impressions, conversions, conversion_value, now()
from 'data/staging/ads/*.parquet'""")
DuckDB reads Parquet files directly in from, and it can attach a Postgres database (attach 'postgresql://…' as pg (type postgres)) to read or write it, which is handy for heavy scans over a Postgres warehouse.
4. ClickHouse
docker run -d --name ch -p 8123:8123 -p 9000:9000 -e CLICKHOUSE_PASSWORD=ch clickhouse/clickhouse-server
create table fct_ads_daily (
ad_date Date, campaign_id LowCardinality(String), spend Decimal(18,2), clicks UInt32,
impressions UInt64, conversions UInt32, conversion_value Decimal(18,2), updated_at DateTime64(3)
) engine = ReplacingMergeTree(updated_at)
order by (campaign_id, ad_date);
ClickHouse does not update rows in place. You insert the new version, and a ReplacingMergeTree keeps the row with the latest updated_at for each sort key, eventually, when background merges run. Until then, both versions exist.
updated_at 06:00). One row.updated_at 06:00 next day). Now two rows sit in two separate parts on disk.select sum(spend) returns 175: both versions counted. Wrong.select sum(spend) from fct_ads_daily final merges at read time and returns 95. Or use argMax(spend, updated_at) per key.import clickhouse_connect
ch = clickhouse_connect.get_client(host="localhost", username="default", password="ch")
ch.insert_df("fct_ads_daily", df.to_pandas())
ch.query("select campaign_id, sum(spend) from fct_ads_daily final group by campaign_id")
That is ClickHouse’s trade-off: extremely fast scans and inserts, unusual update rules.
5. Benchmark fairly
Benchmarks lie easily. Same data, same query, several runs, and report the cold run (data read from disk) separately from the warm ones (data already in memory). Report the median of the warm runs and the spread. A difference under about 20 % on a laptop is usually noise.
6. One interface, many warehouses
Hide the differences behind one Dagster resource (Lesson 3.3):
class Warehouse(dg.ConfigurableResource):
def upsert(self, table: str, df: pl.DataFrame, keys: list[str]) -> dict: ...
def replace_partition(self, table: str, df: pl.DataFrame, col: str, value) -> dict: ...
def query(self, sql: str) -> pl.DataFrame: ...
class AzureSqlWarehouse(Warehouse): ... # Lessons 4.2 and 4.3: staging + MERGE
class PostgresWarehouse(Warehouse): ... # COPY + ON CONFLICT
class DuckDBWarehouse(Warehouse): ... # INSERT OR REPLACE
The asset calls warehouse.upsert(...); the environment chooses which class it gets. Each returns a dict of counts for the audit table.
SQL dialects differ in details that bite: MERGE versus ON CONFLICT; datetime2 versus timestamptz; top versus limit; string joining; quoting of names; and case (Postgres turns unquoted names to lower case). Keep warehouse-specific SQL inside the resource classes and, from Module 5, in dbt adapters. SQLGlot, a Python library, can translate simple queries between dialects, which helps with migrations.
- COPY
- Postgres's bulk-load command: streams many rows at once instead of one insert each.
- ON CONFLICT
- Postgres's upsert: if the key exists, update instead of failing.
- ReplacingMergeTree
- A ClickHouse table that keeps the newest version per key, but only after background merges; use FINAL for exact answers now.
- Warm median
- The middle timing of repeated runs once the data is in memory, the fair number to compare.
- Physical meaning
- A client asks "should we move from Postgres to ClickHouse for speed?" You can answer with your own numbers on their queries, and with the catch: their daily corrections would need FINAL or argMax everywhere, which their analysts would have to learn.
“The fastest database in a benchmark is the best choice.” Speed is one input. A 15 % difference on a laptop is noise, and even a real 3× gap may not matter if the dashboard already answers in half a second. Ecosystem, skills, update rules and cost usually decide (see Lesson 4.1).
Maths and principle
Principle: benchmark fairly: same data, same query, cold and warm runs reported apart, the median of several warm runs.
Equation: for warm timings , report
with the spread (fastest to slowest). The median ignores one unlucky run; the mean does not.
Analogy: weighing yourself several times and taking the middle reading, not the first one taken with your coat on.
Where it breaks: caches. The first run reads from disk; later runs hit memory. Comparing one database’s cold run with another’s warm run is meaningless.
Try it: runs of 2.1, 0.40, 0.42, 0.39 and 0.41 s. Cold time and warm median? (Cold 2.1 s; warm median (0.40 + 0.41) ÷ 2 = 0.405 s.)
7. Try it
- Implement
PostgresWarehouseandDuckDBWarehousewithupsertandreplace_partition. Reuse Lessons 4.2 and 4.3 forAzureSqlWarehouse. - Load 120 days of ads and restaurant data into all three (and into ClickHouse by hand). Time the loads.
- Run the same four queries everywhere: total spend by channel, daily trend, top 10 campaigns by ROAS (return on ad spend: conversion value ÷ spend), and a 7-day rolling sum with a window function. Cold, then warm, median of 5.
- Put the results table and a short, honest interpretation in the README. Note every query that needed a dialect change.
- Wire the resource into Dagster with
P2D_WAREHOUSE=duckdb|postgres|azuresql.
What to check: every warehouse returns the same totals for the same query (to the cent); rerunning the upsert gives zero inserts everywhere; ClickHouse without FINAL shows the double count right after a correction, and with FINAL does not. Commit as feat(warehouse): swappable Postgres/DuckDB/Azure SQL resource and benchmark.
- Postgres: bulk-load with COPY into staging, then INSERT … ON CONFLICT to upsert.
- DuckDB: INSERT OR REPLACE with a primary key; it reads Parquet directly and can attach Postgres.
- ClickHouse: inserts new versions; ReplacingMergeTree removes old ones eventually, so use FINAL or argMax for exact answers.
- Benchmark fairly: cold and warm apart, median of several runs; differences under about 20 % are noise.
- One Warehouse resource with upsert, replace_partition and query keeps assets identical across databases.
Check yourself
Sources
- PostgreSQL documentation: COPY and INSERT … ON CONFLICT https://www.postgresql.org/docs/current/sql-copy.html
- DuckDB documentation: INSERT (INSERT OR REPLACE) and the PostgreSQL extension https://duckdb.org/docs/
- ClickHouse documentation: ReplacingMergeTree https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree
- Kleppmann, M. (2017), Designing Data-Intensive Applications, O'Reilly, Chapter 3 (storage engines: B-trees and LSM-style merges)
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. All company data in the examples is synthetic. Tool versions and prices change: check the official docs before you rely on them.