Data Warehouse Interview Questions and Answers

import os
import shutil

import duckdb

db = duckdb.connect()
JAN24, JAN25 = "yellow_tripdata_2024-01.parquet", "yellow_tripdata_2025-01.parquet"   # NYC TLC yellow taxi trips

print("W1 row storage vs columnar storage: the same January 2024 trips")
db.execute(f"COPY (SELECT * FROM '{JAN24}') TO 'trips.csv' (HEADER)")
mb = lambda f: os.path.getsize(f) / 1e6
print(f"   CSV {mb('trips.csv'):,.0f} MB | Parquet {mb(JAN24):,.0f} MB | rows {db.sql(f'SELECT COUNT(*) FROM {JAN24!r}').fetchone()[0]:,}")
os.remove("trips.csv")

print("\nW2 partition pruning: one day out of a month partitioned by pickup date")
shutil.rmtree("lake", ignore_errors=True)
db.execute(f"""COPY (SELECT *, CAST(tpep_pickup_datetime AS DATE) AS pickup_date FROM '{JAN24}'
                    WHERE tpep_pickup_datetime >= '2024-01-01' AND tpep_pickup_datetime < '2024-02-01')
               TO 'lake' (FORMAT parquet, PARTITION_BY (pickup_date))""")
plan = db.execute("""EXPLAIN ANALYZE SELECT COUNT(*) FROM read_parquet('lake/*/*.parquet', hive_partitioning = true)
                     WHERE pickup_date = DATE '2024-01-15'""").fetchall()[0][1]
print("   partition folders:", len(os.listdir("lake")), "|", next(l.strip("│ ") for l in plan.splitlines() if "Scanning Files" in l))

print("\nW3 reloading a day: plain INSERT vs delete-then-insert")
db.execute("CREATE TABLE fact_trips AS SELECT * FROM read_parquet('lake/*/*.parquet', hive_partitioning = true) WHERE false")
load_day = "INSERT INTO fact_trips SELECT * FROM read_parquet('lake/*/*.parquet', hive_partitioning = true) WHERE pickup_date = DATE '2024-01-15'"
db.execute(load_day)
db.execute(load_day)                                           # the job is retried after a failure
print(f"   rows for 15 January after two plain inserts: {db.sql('SELECT COUNT(*) FROM fact_trips').fetchone()[0]:,}")
db.execute("DELETE FROM fact_trips")
for _ in range(2):                                             # the same retry, made idempotent
    db.execute("BEGIN")
    db.execute("DELETE FROM fact_trips WHERE pickup_date = DATE '2024-01-15'")
    db.execute(load_day)
    db.execute("COMMIT")
print(f"   rows after two delete-then-insert loads:    {db.sql('SELECT COUNT(*) FROM fact_trips').fetchone()[0]:,}")

print("\nW4 schema drift between January 2024 and January 2025")
cols = lambda f: [r[0] for r in db.sql(f"DESCRIBE SELECT * FROM '{f}'").fetchall()]
print("   columns only in 2025:", sorted(set(cols(JAN25)) - set(cols(JAN24))))
print("  ", db.sql(f"""SELECT YEAR(tpep_pickup_datetime) AS year, COUNT(*) AS trips, COUNT(cbd_congestion_fee) AS with_cbd_fee
                       FROM read_parquet(['{JAN24}', '{JAN25}'], union_by_name = true)
                       WHERE MONTH(tpep_pickup_datetime) = 1 AND YEAR(tpep_pickup_datetime) IN (2024, 2025)
                       GROUP BY year ORDER BY year""").fetchall())

print("\nW5 rows in the January 2024 file that are not from January 2024")
print("  ", db.sql(f"""SELECT COUNT(*), MIN(tpep_pickup_datetime), MAX(tpep_pickup_datetime) FROM '{JAN24}'
                       WHERE tpep_pickup_datetime < '2024-01-01' OR tpep_pickup_datetime >= '2024-02-01'""").fetchone())

print("\nW6 a daily summary table answers daily questions from far fewer rows")
db.execute("""CREATE TABLE daily AS SELECT pickup_date, COUNT(*) AS trips, SUM(total_amount) AS revenue
              FROM read_parquet('lake/*/*.parquet', hive_partitioning = true) GROUP BY pickup_date""")
raw = db.sql("SELECT ROUND(SUM(total_amount)) FROM read_parquet('lake/*/*.parquet', hive_partitioning = true) WHERE pickup_date BETWEEN '2024-01-08' AND '2024-01-14'").fetchone()[0]
summary = db.sql("SELECT ROUND(SUM(revenue)) FROM daily WHERE pickup_date BETWEEN '2024-01-08' AND '2024-01-14'").fetchone()[0]
print(f"   summary rows {db.sql('SELECT COUNT(*) FROM daily').fetchone()[0]} | week of 8 January: raw {raw:,.0f} vs summary {summary:,.0f}")
shutil.rmtree("lake")

W1 row storage vs columnar storage: the same January 2024 trips
   CSV 299 MB | Parquet 50 MB | rows 2,964,624

W2 partition pruning: one day out of a month partitioned by pickup date
   partition folders: 31 | Scanning Files: 1/31

W3 reloading a day: plain INSERT vs delete-then-insert
   rows for 15 January after two plain inserts: 154,066
   rows after two delete-then-insert loads:    77,033

W4 schema drift between January 2024 and January 2025
   columns only in 2025: ['cbd_congestion_fee']
   [(2024, 2964606, 0), (2025, 3475204, 3475204)]

W5 rows in the January 2024 file that are not from January 2024
   (18, datetime.datetime(2002, 12, 31, 22, 59, 39), datetime.datetime(2024, 2, 1, 0, 1, 15))

W6 a daily summary table answers daily questions from far fewer rows
   summary rows 31 | week of 8 January: raw 18,008,730 vs summary 18,008,730