Fact constellation schema

Example

Copy code
CREATE TABLE dim_date    (date_key INTEGER PRIMARY KEY, full_date DATE);
CREATE TABLE dim_product (product_key INTEGER PRIMARY KEY, product_name VARCHAR);
CREATE TABLE dim_store   (store_key INTEGER PRIMARY KEY, store_name VARCHAR);

CREATE TABLE fact_sales (
    date_key INTEGER REFERENCES dim_date(date_key),
    product_key INTEGER REFERENCES dim_product(product_key),
    store_key INTEGER REFERENCES dim_store(store_key),
    revenue DECIMAL(12,2)
);

CREATE TABLE fact_inventory (
    date_key INTEGER REFERENCES dim_date(date_key),
    product_key INTEGER REFERENCES dim_product(product_key),
    store_key INTEGER REFERENCES dim_store(store_key),
    units_on_hand INTEGER
);

-- Compare sales against inventory using the shared dimensions
SELECT d.full_date, p.product_name, s.revenue, i.units_on_hand
FROM fact_sales s
JOIN fact_inventory i USING (date_key, product_key, store_key)
JOIN dim_date d USING (date_key)
JOIN dim_product p USING (product_key);

A fact constellation is really just what Kimball's bus architecture produces at scale: as a warehouse grows to cover more business processes, it naturally accumulates multiple fact tables that conform to a shared set of dimensions, rather than existing as a single, isolated star.

DuckDB angle

There's nothing special DuckDB needs for a fact constellation — it's the same tables and joins as a single star schema, just more of them — though joining two fact tables directly (as above) only works cleanly when they share the same grain on the join keys; otherwise, aggregate each to a common grain before joining.