how-to-schema-and-warehouse

Input - four source systems, none of them tidy

A real data platform is not handed clean tables - it is handed exports from whatever systems happened to produce them. Everrest arrives as four raw dumps with their own key formats, their own casing, and their own defects. The job: turn this into one warehouse the whole company can trust and query.

Generate sample data - defects planted in the RAW layer

The quirks are ingestion problems, so they live in the raw dump - which is exactly why the warehouse build has real work to do. A seeded generator produces the four sources; each defect is documented in its docstring, turning Step 5 into a recall test: did the model resolve everything the raw layer broke?

# Quirk 3: the merchant master uses a different key format than transactions.
ext["merchant_key"] = ext.merchant_id.str.replace("M", "MER-")   # M0001 -> MER-0001

# Quirk 4: PH payments logged in local time -> paid_at lands 8h BEFORE the order.
pay.loc[ph, "paid_at"] = pay.loc[ph, "paid_at"] - pd.Timedelta(hours=8)

# Quirk 6: 800 customers cloned under a new id, identical signup/channel/region.
clones = customers.sample(800, random_state=SEED).copy()
clones["customer_id"] = [f"C9{i:05d}" for i in range(1, len(clones) + 1)]

Objective - a trust question, not a diagram

Anyone can draw an ERD. The real question an infrastructure lead has to answer is whether the platform can be built on at all.

Find-skills - the modeling toolbox

Before writing DDL, assemble the tools that serve this objective: an engine that queries files directly, a contract library that turns integrity rules into code, and a promotion path to permanent CI gates.

Build - raw dump to queryable star schema

One SQL file does the whole transform: stage the four sources, reconcile the keys, normalize the labels, dedupe identities, fix the timezone, then assemble one fact and four conformed dimensions. Grab the real code below.

The star schema

One fact at a clear grain (one order line) surrounded by four conformed dimensions. Every downstream question is now a simple join instead of a wrangling exercise.

-- Quirk 3: reconcile 'MER-0001' -> 'M0001' via legacy_id.
-- Quirk 2: normalize 14 dirty labels -> 8 canonical categories.
CREATE OR REPLACE TABLE dim_merchant AS
SELECT legacy_id AS merchant_id, clean_category(category) AS category, tier
FROM stg_merchants;

-- Quirk 6: collapse duplicate identities on a (signup, channel, region) fingerprint.
SELECT min(customer_id) OVER (PARTITION BY signup_ts, channel, region) AS customer_id

-- Fact: orphan keys are FLAGGED, not silently dropped.
(dp.product_id IS NULL) AS is_orphan_product

Prove it's queryable: the same question, before and after

The whole point of modeling. On the raw dump, "revenue by category" splits into 14 mislabeled buckets. On the star schema, one clean join returns 8 whole categories.

A real star-schema join (fact_orders x dim_merchant): top merchants by revenue - and the M0007 bulk-wholesale outlier surfaces immediately, correctly attributed.

What the model fixed

Nothing lost. Every one of 107,841 transactions is accounted for through the lineage; orphans are flagged, not silently dropped.

14 → 8. Dirty label variants normalized in dim_merchant, so every category rollup is whole.

Keys checked. 2.5% of line items and 1% of orders are orphaned - quarantined with a flag the contract can enforce.

People, not rows. Duplicate identities collapsed on a signup/channel/region fingerprint so acquisition counts are honest.

Time made sane. PH payments no longer land 8 hours before their order once paid_at is shifted back to UTC.

Reconciliation catches fraud. M0333's reported payments diverge sharply from its modeled order value - a fabrication the warehouse surfaces.

product_id:
  foreign_key: dim_product.product_id   # enforced for non-orphan rows
checks:
  - name: referential_integrity_product
    rule: "every non-orphan row has product_id in dim_product"
  - name: no_negative_payment_latency
    rule: "paid_ts_utc >= order_ts for all rows"
  - name: category_domain
    rule: "dim_merchant.category in the 8 canonical categories"
# build result: contract PASS - 0 referential-integrity violations

Expert review - a check that changes the model

A panel of five senior reviewer agents - each with 10+ years in data engineering, architecture, and business - tore into the naive first pass (naive_query.py, kept in the repo) that just queried the raw dump. It ran clean and returned plausible numbers, which is what made it dangerous. Every fix below is in the warehouse above.

-- v1 (before): query the raw dump, join on merchant_id
SELECT count(*) FROM t JOIN m ON t.merchant_id = m.merchant_key;
--   -> 0 rows. MER-0001 never equals M0001. Silent, catastrophic.

-- v2 (after): reconcile the key in dim_merchant, then join the star
SELECT dm.category, sum(f.net_amount)
FROM fact_orders f JOIN dim_merchant dm USING (merchant_id);   -- every row resolves