Data Modeling

The medallion architecture

Raw source data is not safe to query and not safe to delete. The medallion architecture resolves that tension by refining data through three named layers — bronze, silver, gold — each with a different contract about cleanliness, structure, and audience. It is the organizing skeleton for every transformation that follows in chapter 07.

Each hop has a job, and only that job:

• Bronze — raw, immutable landing. Data lands exactly as it arrived: CDC change events from marketplace-api, Kafka payloads from marketplace-events, JSON pulls from payments. One table per source stream, append-only, schema-on-read, no business logic. Its only promises are completeness and replayability — if silver logic is wrong, you reprocess from bronze rather than re-pulling from the source. This is the audit floor and the reason a DPE can sleep.
• Silver — cleaned, conformed, integrated. Here the transforms happen: deduplicate on the contract's event_id, cast strings to typed columns, apply the CDC merge that collapses an insert/update/delete change stream into a current-state table and a history table (the SCD2 work of the next section), conform keys across sources, enforce data contracts. Silver is the integration layer — entity-centric, not source-centric. An analyst could query it; mostly machines do.
• Gold — business-facing marts. Dimensional models and metric tables shaped for a question, not a source: a marketplace mart, a billing mart, a supply mart, wide ML feature tables. Aggregated, denormalized, documented, governed. This is what the semantic layer (ch. 07 and ch. 08) and dashboards point at.

Modeling paradigms compared

Bronze and silver are mechanical; the interesting choice is how to shape gold. Three paradigms dominate, and a mature platform uses more than one.

• Kimball dimensional (star schema). Split the world into facts (measurable events at a declared grain — a rental-hour, a bid) and dimensions (the descriptive context you filter and group by — customer, machine, date). Facts hold foreign keys to dimensions; queries fan out from a central fact table into surrounding dims, forming a star. Optimized for analyst query ergonomics.
• Data Vault 2.0. Decompose into hubs (business keys, e.g. a machine's natural key), links (relationships between hubs, e.g. machine↔rental), and satellites (the descriptive, time-stamped attributes that hang off hubs and links). Built for auditability and schema agility under many fast-changing sources; not pleasant to query directly — you still build dimensional marts on top.
• One Big Table (OBT) / wide denormalized. Pre-join everything into a single very wide table, one row per event with every attribute inlined. No joins at query time. Cheap and fast on columnar engines, brilliant for ML feature tables and embedded analytics; painful for history, storage, and any change to a dimension's definition.

Slowly-changing dimensions

Dimensions describe entities — but entities change. A customer upgrades tiers; a host relocates; an offer's price and a machine's DLPerf score move with the market. How a dimension table handles those changes is its slowly-changing dimension (SCD) type.

The reason SCD2 is load-bearing at GridDP, not a nicety: billing and analytics constantly ask "what was true at the time of the event?" What was the offer.price at the hour this rental was metered? What was the machine's DLPerf score when the customer chose it? With a Type 1 dimension you only know today's price, so you mis-bill historical rentals and mis-train the ranking model. SCD2 makes the dimension temporal: every fact joins to the dimension version that was current at the fact's event time.

The mechanics tie straight back to CDC from chapter 04. Debezium streams each offer.price UPDATE off the Postgres WAL into bronze; the silver merge turns that change stream into the versioned rows above. A new natural-key change closes the prior row (sets valid_to and is_current = false) and inserts a fresh version with a new surrogate key. The merge:

-- 1. Close the currently-open version when an incoming change differs.
MERGE INTO dim_offer AS tgt
USING (
  -- latest CDC state per offer from bronze, this batch
  SELECT offer_id, machine_id, price_usd_hr, is_interruptible,
         min_bid_usd_hr, cdc_commit_ts
  FROM   bronze_marketplace_cdc
  WHERE  table_name = 'offer'
    AND  op IN ('c','u')               -- create / update
    AND  cdc_commit_ts > (SELECT COALESCE(MAX(valid_from), '1970-01-01') FROM dim_offer)
) AS src
ON  tgt.offer_id = src.offer_id
AND tgt.is_current = TRUE
WHEN MATCHED AND (
       tgt.price_usd_hr     <> src.price_usd_hr
    OR tgt.is_interruptible <> src.is_interruptible
    OR tgt.min_bid_usd_hr   <> src.min_bid_usd_hr )   -- a tracked attribute changed
  THEN UPDATE SET valid_to = src.cdc_commit_ts, is_current = FALSE;

-- 2. Insert the new version row (new surrogate key) for changed / new offers.
INSERT INTO dim_offer
  (offer_sk, offer_id, machine_id, price_usd_hr, is_interruptible,
   min_bid_usd_hr, valid_from, valid_to, is_current)
SELECT
  nextval('offer_sk_seq'), src.offer_id, src.machine_id, src.price_usd_hr,
  src.is_interruptible, src.min_bid_usd_hr,
  src.cdc_commit_ts, TIMESTAMP '9999-12-31 00:00:00', TRUE
FROM bronze_marketplace_cdc AS src
WHERE /* offer is new, or its current row was just closed above */ TRUE;

Fact-table grain

The first decision in any fact table — before columns, before keys — is declaring the grain: what does exactly one row mean? Skip this and you get tables where some rows are per-event and some pre-aggregated, additivity breaks, and no one trusts a SUM(). Kimball's rule: declare the grain, then add only dimensions and measures true at that grain.

GridDP's three grains are the ones memorized in Start Here:

Each grain demands the right treatment of its measures — the additivity classification:

• Additive — sums correctly across every dimension, including time. amount_usd, billed_hours, energy_wh, gpu_seconds. These are the safe, default measures: SUM them across any slice.
• Semi-additive — sums across some dimensions but not time. A point-in-time balance like ledger_entry-derived account balance, or "active rentals at the hour": you sum across customers but you average (or take the last value) across time, never sum hourly snapshots into a day.
• Non-additive — cannot be summed across any dimension; must be recomputed from its components. Utilization % is the canonical GridDP trap: you cannot average hourly avg_util_pct to get daily utilization (hours have different GPU-second denominators). Store the additive numerator and denominator (busy_gpu_seconds, total_gpu_seconds) and divide at query time. Same for fill rate and take-rate %.

The GridDP dimensional model

Putting it together: the gold layer is a set of star schemas sharing conformed dimensions, with the three fact tables at their centers. Here is the full ERD.

The DDL for the load-bearing tables (Stack-A Iceberg / ANSI-SQL flavor; partition hints noted):

CREATE TABLE dim_customer (
  customer_sk    BIGINT       NOT NULL,           -- surrogate (PK)
  customer_id    VARCHAR      NOT NULL,           -- natural key from marketplace-api.account
  tier           VARCHAR,                          -- free / pro / enterprise
  kyc_level      VARCHAR,                          -- from payments KYC/AML source
  country        VARCHAR,
  is_provider    BOOLEAN,                          -- two-sided: a customer can also be a host owner
  valid_from     TIMESTAMP    NOT NULL,
  valid_to       TIMESTAMP    NOT NULL DEFAULT TIMESTAMP '9999-12-31 00:00:00',
  is_current     BOOLEAN      NOT NULL,
  PRIMARY KEY (customer_sk)
);                                                 -- small dim; no partitioning needed

CREATE TABLE dim_host (
  host_sk        BIGINT       NOT NULL,
  host_id        VARCHAR      NOT NULL,           -- natural key (provider)
  region         VARCHAR,
  payout_rail    VARCHAR,                          -- fiat / crypto (ch.01 payments)
  reliability_tier VARCHAR,
  valid_from     TIMESTAMP    NOT NULL,
  valid_to       TIMESTAMP    NOT NULL DEFAULT TIMESTAMP '9999-12-31 00:00:00',
  is_current     BOOLEAN      NOT NULL,
  PRIMARY KEY (host_sk)
);

CREATE TABLE dim_offer (
  offer_sk         BIGINT     NOT NULL,
  offer_id         VARCHAR    NOT NULL,           -- natural key
  machine_sk       BIGINT     NOT NULL,           -- FK -> dim_machine
  price_usd_hr     DECIMAL(10,4),
  is_interruptible BOOLEAN,
  min_bid_usd_hr   DECIMAL(10,4),
  valid_from       TIMESTAMP  NOT NULL,
  valid_to         TIMESTAMP  NOT NULL DEFAULT TIMESTAMP '9999-12-31 00:00:00',
  is_current       BOOLEAN    NOT NULL,
  PRIMARY KEY (offer_sk)
);

CREATE TABLE dim_machine (
  machine_sk   BIGINT   NOT NULL,                 -- surrogate (PK)
  machine_id   VARCHAR  NOT NULL,                 -- natural key (the physical box)
  host_sk      BIGINT   NOT NULL,                 -- FK -> dim_host
  gpu_model    VARCHAR,                            -- e.g. RTX 4090, H100
  gpu_count    INT,
  cpu_cores    INT,
  ram_gb       INT,
  datacenter   BOOLEAN,                            -- true=DC, false=hobbyist/residential
  PRIMARY KEY (machine_sk)
);

CREATE TABLE dim_gpu (
  gpu_sk       BIGINT   NOT NULL,                 -- one row per physical card
  machine_sk   BIGINT   NOT NULL,                 -- FK -> dim_machine
  gpu_index    INT      NOT NULL,                 -- which slot in the box (0..n)
  vram_gb      INT,
  pcie_gen     INT,
  PRIMARY KEY (gpu_sk)
);

-- GRAIN: one row per rental-hour. The billing atom. Reconciles to ledger_entry.
CREATE TABLE fct_rental_meter (
  rental_meter_sk BIGINT     NOT NULL,
  rental_id       VARCHAR    NOT NULL,            -- degenerate dimension (the rental)
  customer_sk     BIGINT     NOT NULL,            -- FK -> dim_customer (version at event time)
  machine_sk      BIGINT     NOT NULL,            -- FK -> dim_machine
  offer_sk        BIGINT     NOT NULL,            -- FK -> dim_offer  (SCD2: price AT this hour)
  date_sk         INT        NOT NULL,            -- FK -> dim_date (hour grain)
  meter_hour      TIMESTAMP  NOT NULL,
  billed_hours    DECIMAL(8,4),                   -- additive
  gpu_seconds     BIGINT,                         -- additive
  amount_usd      DECIMAL(12,4),                  -- additive; ties to invoice/ledger
  PRIMARY KEY (rental_meter_sk)
) PARTITIONED BY (days(meter_hour));              -- partition by day

-- GRAIN: one row per bid event.
CREATE TABLE fct_bid_event (
  bid_event_sk  BIGINT     NOT NULL,
  bid_id        VARCHAR    NOT NULL,              -- degenerate dimension
  customer_sk   BIGINT     NOT NULL,              -- FK -> dim_customer
  offer_sk      BIGINT     NOT NULL,              -- FK -> dim_offer
  machine_sk    BIGINT     NOT NULL,              -- FK -> dim_machine
  date_sk       INT        NOT NULL,              -- FK -> dim_date
  event_time    TIMESTAMP  NOT NULL,              -- producer-stamped (ch.04)
  event_type    VARCHAR,                          -- placed / outbid / cleared / cancelled
  bid_usd_hr    DECIMAL(10,4),                    -- additive only within same event_type
  cleared_flag  BOOLEAN,
  PRIMARY KEY (bid_event_sk)
) PARTITIONED BY (days(event_time));

-- GRAIN: one row per machine + GPU + hour. The rolled-up firehose (raw stays in ClickHouse).
CREATE TABLE fct_telemetry_hourly (
  machine_sk        BIGINT    NOT NULL,           -- FK -> dim_machine
  gpu_index         INT       NOT NULL,
  date_sk           INT       NOT NULL,           -- FK -> dim_date (hour grain)
  metric_hour       TIMESTAMP NOT NULL,
  busy_gpu_seconds  BIGINT,                        -- additive NUMERATOR for utilization
  total_gpu_seconds BIGINT,                        -- additive DENOMINATOR (do NOT store the %)
  max_temp_c        SMALLINT,                      -- non-additive across time (it's a max)
  energy_wh         DECIMAL(12,3),                 -- additive
  dlperf_avg        DECIMAL(8,2),                  -- semi-additive (average, not sum)
  sample_count      INT,                           -- rows rolled up; gap detection
  PRIMARY KEY (machine_sk, gpu_index, date_sk)
) PARTITIONED BY (days(metric_hour));

Conformed dimensions

Notice that dim_customer, dim_machine, and dim_date appear in multiple stars — the marketplace mart, the billing mart, and the supply mart all reference the same physical dimension tables. That is the definition of a conformed dimension: a single, shared dimension used identically across fact tables and marts.

Conformance is what lets metrics from different domains line up. "GMV per customer tier" (billing mart) and "bids per customer tier" (marketplace mart) only join meaningfully if both use the same dim_customer with the same tier definition and the same surrogate keys. The moment two teams build their own customer dimension with subtly different tier logic, the company gets two different "enterprise customer" counts in two dashboards — and weeks are lost reconciling numbers that should have been one number.

dim_date is the simplest and most important conformed dimension: one row per hour, with calendar attributes (day, week, month, quarter, fiscal period, is-weekend). Every fact in GridDP joins to it, so "by month" means the same month everywhere — including the fiscal calendar Finance reconciles against.

Modeling the firehose alongside the marts

The dimensional model above is a row-and-column world built for joins. The telemetry firehose is a different world: ~150 M samples/day, time-series access, served from ClickHouse per chapter 05. The modeling question is how the two worlds meet — because supply ops and Finance both need to join physical GPU behavior to business facts.

The answer is the rollup as a bridge. Raw per-sample telemetry never enters the warehouse; it stays in ClickHouse where time-series queries are cheap. A scheduled job aggregates raw samples into fct_telemetry_hourly — one row per machine_id + gpu_index + hour — and that table flows into the lakehouse/warehouse, keyed so it joins cleanly to dim_machine (via machine_sk) and aligns hour-for-hour with fct_rental_meter.

This single design decision earns its keep three ways. It keeps the firehose out of an engine that would choke on it (the chapter-01 anti-pattern). It makes telemetry joinable to business data — you can now answer "was this billed rental-hour actually delivering compute?" by joining fct_rental_meter to fct_telemetry_hourly on machine_sk + hour, the exact reconciliation Finance and supply ops need. And it shrinks ~150 M raw rows to ~400 k hourly rows — a ~375× reduction — so the warehouse-side telemetry fact is small enough to be a first-class star participant.

Takeaway

• The medallion architecture refines data through bronze (raw, immutable, replayable), silver (cleaned, deduped, conformed, CDC-merged), and gold (business-facing marts) — separating fidelity, truth, and shape.
• GridDP models gold as Kimball dimensional stars for BI and metrics, with OBT reserved for ML feature tables; Data Vault's agility tax isn't warranted here.
• SCD2 dimensions make history queryable — joining a fact to the dimension version current at event time is what lets GridDP bill a rental at the offer price that was live that hour, not today's.
• Every fact declares its grain first; the three canonical grains are telemetry-hourly, rental-hour (the billing atom), and bid event. Non-additive ratios like utilization are stored as additive numerator/denominator parts.
• Conformed dimensions (dim_customer, dim_machine, dim_date) shared across marts are the structural defense against metric divergence.
• The firehose joins the marts through an hourly rollup — raw stays in ClickHouse, fct_telemetry_hourly bridges into the warehouse at the billing grain.

The model is the contract for what the data should look like. Next we build the machinery that produces and maintains it on a schedule, with tests and lineage. → Transformation & Orchestration