Lesson 1.2: Relational Joins and SQL over DataFrames with DuckDB

Written lesson

Rebuild the tidy tables

Each lesson notebook is self-contained, so we start by recreating the two tidy tables from Lesson 1.1. This time the unemployment source is missing three months for Quebec, as real feeds often are.

Python · Colab cell

!pip install -q polars duckdb pandas pyarrow
import polars as pl, duckdb, pandas as pd, numpy as np

np.random.seed(42)
dates = pd.date_range("2020-01-01", periods=48, freq="ME")

inflation = (
    pl.DataFrame({
        "date": dates,
        "Ontario": np.random.normal(0.02, 0.005, 48).cumsum() + 2.0,
        "Quebec":  np.random.normal(0.02, 0.006, 48).cumsum() + 1.8,
    })
    .unpivot(index="date", variable_name="province", value_name="inflation_rate")
)

unemployment = pl.DataFrame({
    "record_date": np.tile(dates, 2),
    "province": ["Ontario"] * 48 + ["Quebec"] * 48,
    "unemp_rate": np.random.normal(5.5, 0.5, 96),
})
# Drop three Quebec months to mimic a gap in the feed
gap = [dates[10], dates[11], dates[30]]
unemployment = unemployment.filter(
    ~((pl.col("province") == "Quebec") & pl.col("record_date").is_in(gap))
)
print(inflation.shape, unemployment.shape)   # (96, 3) (93, 3)

SQL joins on DataFrames

DuckDB can query any Polars or pandas DataFrame that exists in your Python session, by name, as if it were a SQL table. Nothing is copied to a server; .pl() returns the result as a Polars DataFrame.

Python · Colab cell

query = """
    SELECT i.date, i.province, i.inflation_rate, u.unemp_rate
    FROM inflation AS i
    LEFT JOIN unemployment AS u
      ON i.date = u.record_date
     AND i.province = u.province
    ORDER BY i.province, i.date
"""
panel = duckdb.sql(query).pl()
print(panel.head(6))
print("Rows:", panel.height)

The LEFT JOIN keeps every row of the left table (inflation) and fills NULL where unemployment is missing. An INNER JOIN would silently drop those three Quebec months.

Join	Keeps	Risk in causal work
`INNER`	only keys found in both tables	silently drops units/periods, can change who is "treated"
`LEFT`	every row of the left table	creates `NULL`s you must decide how to handle
`ANTI`	left rows with no match	the audit tool: shows exactly what would be lost

Auditing a join: what got lost?

Never trust a join you have not audited. Two checks catch most problems: count the rows on each side, and list the keys that failed to match.

Python · Colab cell

missing = duckdb.sql("""
    SELECT i.province, i.date
    FROM inflation i
    ANTI JOIN unemployment u
      ON i.date = u.record_date AND i.province = u.province
""").pl()
print(missing)

audit = duckdb.sql("""
    SELECT province,
           COUNT(*)                         AS months,
           COUNT(unemp_rate)                AS months_with_unemployment,
           COUNT(*) - COUNT(unemp_rate)     AS gaps
    FROM panel
    GROUP BY province ORDER BY province
""").pl()
print(audit)

The why

If the missing months are not random (for example, a survey stopped during a recession), dropping them changes the sample in a way that is correlated with the outcome. That is a selection problem, the same one you will meet again in Module 4. Make missingness visible, then decide on purpose.

Timing: lags, leads and publication delays

A covariate must describe the world before treatment, otherwise you may control for something the treatment itself caused (a "bad control", Module 5). The safest habit is to build lagged covariates explicitly, inside each unit, with a window function.

Python · Colab cell

panel_lagged = duckdb.sql("""
    SELECT *,
           LAG(unemp_rate, 1) OVER (PARTITION BY province ORDER BY date) AS unemp_lag1,
           LAG(unemp_rate, 3) OVER (PARTITION BY province ORDER BY date) AS unemp_lag3
    FROM panel
    ORDER BY province, date
""").pl()
print(panel_lagged.head(5))

PARTITION BY province is essential: without it, the first Quebec month would receive Ontario's last value as its "lag", a classic leakage across units.

The same logic in Polars uses .over():

Python · Colab cell

panel_lagged_pl = panel.sort("province", "date").with_columns(
    pl.col("unemp_rate").shift(1).over("province").alias("unemp_lag1")
)
assert panel_lagged_pl["unemp_lag1"].equals(panel_lagged["unemp_lag1"], check_names=False)
print("Polars and DuckDB agree.")

Exercise: an ASOF join

Exercise

Statistics are published with a delay. Suppose a policy committee meets on the dates below, and the unemployment figure for a month is only published 35 days after month-end. For each meeting, find the latest unemployment rate for Ontario that was actually known on the meeting day.

Hint: create a published column, then use DuckDB's ASOF LEFT JOIN ... ON m.meeting >= u.published.

Show a solution

Python · Colab cell

meetings = pl.DataFrame({
    "meeting": pd.to_datetime(["2021-03-10", "2021-06-09", "2021-09-08", "2021-12-08"])
})
known = unemployment.filter(pl.col("province") == "Ontario").with_columns(
    (pl.col("record_date") + pl.duration(days=35)).alias("published")
)
asof = duckdb.sql("""
    SELECT m.meeting, u.record_date AS reference_month, u.published, u.unemp_rate
    FROM meetings m
    ASOF LEFT JOIN known u
      ON m.meeting >= u.published
    ORDER BY m.meeting
""").pl()
print(asof)

Checklist and recap

I can join DataFrames with SQL in DuckDB and get a Polars result back.
I audit every join with row counts and an ANTI JOIN.
I build lags within each unit (PARTITION BY / .over()).
I know the difference between when data refers to and when it was known.