Cost of a dimension change: star against OBT

# /// script
# requires-python = ">=3.10"
# dependencies = ["duckdb"]
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""The cost of a dimension change: star against one big table.

A 20-million-row fact table and a 2,000-row product dimension, stored as a
star and as one big table (OBT) with the category copied into every row.
Rename one category in each; then run the same analytic query on both.
"""
import argparse, json, os, tempfile, time
import duckdb

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--facts", type=int, default=20_000_000, help="Fact rows.")
p.add_argument("--products", type=int, default=2_000, help="Products in the dimension.")
p.add_argument("--categories", type=int, default=50, help="Product categories.")
p.add_argument("--path", default=os.path.join(tempfile.gettempdir(), "star-vs-obt.duckdb"), help="Database file.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()
import atexit, shutil
atexit.register(lambda: shutil.rmtree(args.path, ignore_errors=True) if os.path.isdir(args.path) else os.path.exists(args.path) and os.remove(args.path))   # leave nothing behind

if os.path.exists(args.path): os.remove(args.path)
con = duckdb.connect(args.path)
con.execute(f"""CREATE TABLE dim_product AS SELECT i AS product_id, 'cat_' || (i % {args.categories}) AS category,
    'brand_' || (i % 97) AS brand FROM range({args.products}) t(i)""")
con.execute(f"""CREATE TABLE fact AS SELECT i AS sale_id, (hash(i) % {args.products})::INT AS product_id,
    (hash(i * 7) % 3650)::INT AS day, (hash(i * 13) % 10000) / 100.0 AS amount FROM range({args.facts}) t(i)""")
con.execute("CREATE TABLE obt AS SELECT f.*, d.category, d.brand FROM fact f JOIN dim_product d USING (product_id)")
con.execute("CHECKPOINT")
size = lambda: os.path.getsize(args.path)

def timed(sql):
    t = time.perf_counter(); con.execute(sql); return time.perf_counter() - t

# Rename one category
before = size()
star_s = timed("UPDATE dim_product SET category = 'cat_renamed' WHERE category = 'cat_7'"); con.execute("CHECKPOINT")
star_bytes = size() - before
before = size()
obt_s = timed("""CREATE OR REPLACE TABLE obt AS SELECT * REPLACE (CASE WHEN category = 'cat_7' THEN 'cat_renamed' ELSE category END AS category)
                FROM obt""")
con.execute("CHECKPOINT")
# the rewrite writes the whole table again: count its storage blocks (DuckDB blocks are 256 KB)
obt_written = con.execute("SELECT COUNT(DISTINCT block_id) FROM pragma_storage_info('obt') WHERE block_id >= 0").fetchone()[0] * 262144
affected = con.execute("SELECT COUNT(*) FROM obt WHERE category = 'cat_renamed'").fetchone()[0]

QUERY_STAR = """SELECT d.category, d.brand, f.day % 12 AS month, COUNT(*), SUM(f.amount)
                FROM fact f JOIN dim_product d USING (product_id) GROUP BY ALL"""
QUERY_OBT = "SELECT category, brand, day % 12 AS month, COUNT(*), SUM(amount) FROM obt GROUP BY ALL"
reads = {}
for name, sql in (("star", QUERY_STAR), ("obt", QUERY_OBT)):
    timed(sql); reads[name] = min(timed(sql) for _ in range(3))
print(f"rename one category: star updates 1 table of {args.products:,} rows in {star_s * 1000:.1f} ms; "
      f"OBT rewrites {args.facts:,} rows ({affected:,} affected) in {obt_s:.2f} s, writing {obt_written / 1e6:,.0f} MB")
print(f"5-way analytic query: star {reads['star']:.3f} s   OBT {reads['obt']:.3f} s   ({reads['star'] / reads['obt']:.2f}x)")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"summary": [{"star_update_ms": star_s * 1000, "obt_rewrite_s": obt_s, "obt_written_mb": obt_written / 1e6,
                   "affected": affected, "star_read_s": reads["star"], "obt_read_s": reads["obt"],
                   "read_ratio": reads["star"] / reads["obt"], "update_ratio": obt_s / star_s}]}}, f)