Home › Learn › Data platform architecture › Gold

Data platform architecture · component · the same on every platform

Gold: shaped for the business

Building gold: state the grain, facts and dimensions, surrogate keys, point-in-time joins, inferred members, idempotent rebuilds and reconciliation.

↑ The whole pictureMetadata storeIngestionBronze to silverGold

In plain words

Tables built for the questions people ask

Silver says what happened, one clean row per thing. Gold arranges it for a purpose: "how much did we sell, by store and month?", "which customers are at risk?", "what goes on the board report?". The classic shape is a star: a fact table of events with numbers, surrounded by dimension tables that describe them.

Control plane: manifest → metadata DB → generators → pipelinesSourcesLandingBronzeSilverGoldSemanticExperiencesyou are here: Silver + Gold
Gold is built from silver only, never from bronze.

Step 1

Say the grain first

The grain is one sentence: what one row of the fact table means. "One row per order line." "One row per customer per day." Every column must be true at that grain, and every number must add up correctly at it. Most gold bugs are grain bugs: a join that turns one order line into three.

Step 2

Facts and dimensions

TableHoldsKey
fact_salesOne row per order line: quantity, amountkeys to each dimension + order id and line
dim_customerWho: name, city, segment, with history from silver's SCD2customer_sk (surrogate key)
dim_productWhat: name, category, brandproduct_sk
dim_storeWhere: store, city, regionstore_sk
dim_dateWhen: day, week, month, holiday, fiscal perioddate_key

A surrogate key is a number made by the platform for each version of a dimension row, so the fact joins to the right version even after the customer moves city. Conformed dimensions are shared: the same dim_customer serves sales, returns and marketing, so 'customer' means one thing everywhere. See star schema.

Step 3

Build it so it can run twice

-- Grain: one row per order line. Rebuilt per day, so re-running a day replaces it exactly.
INSERT OVERWRITE gold.fact_sales PARTITION (order_date = '2026-10-27')
SELECT                                              -- order_date is fixed by PARTITION (...), so it is not selected
  COALESCE(c.customer_sk, -1)  AS customer_sk,     -- -1 = inferred member (customer not arrived yet)
  p.product_sk,
  o.order_id, o.line_no, o.quantity, o.amount_inr
FROM silver.order_lines o
LEFT JOIN silver.customer c                         -- point-in-time join to the SCD2 version
  ON  c.customer_id = o.customer_id
  AND o.placed_at >= c.valid_from
  AND (c.valid_to IS NULL OR o.placed_at < c.valid_to)
JOIN gold.dim_product p ON p.product_id = o.product_id AND p.is_current
WHERE o.order_date = '2026-10-27';

Spark SQL. Overwriting one day's partition makes the rebuild idempotent: run it twice, get the same rows once.

Step 4

Reconcile every run

SELECT
  (SELECT count(*)        FROM silver.order_lines WHERE order_date = '2026-10-27') AS silver_rows,
  (SELECT count(*)        FROM gold.fact_sales     WHERE order_date = '2026-10-27') AS gold_rows,
  (SELECT sum(amount_inr) FROM silver.order_lines WHERE order_date = '2026-10-27') AS silver_amount,
  (SELECT sum(amount_inr) FROM gold.fact_sales     WHERE order_date = '2026-10-27') AS gold_amount;
-- fail the run if rows differ, or amounts differ by more than the agreed tolerance

Counts and sums must agree with silver, and silver with the source (minus quarantine), within an agreed tolerance. When they don't, the run fails and names the hop where they diverged.

Beyond the star

Marts, aggregates and data products

What goes wrong

Common problems

The fan-out join

Revenue triples after a new dimension is joined in.

Naive fix

Add DISTINCT until it looks right.

Right fix

State the grain; make sure every dimension key is unique for the join (test it); join at the grain.

Orders vanish from the report

A late customer feed means the inner join drops new orders.

Naive fix

Wait for all feeds before building.

Right fix

Inferred member (-1) with a left join; count placeholders and alert if they don't clear.

Last year's numbers change

A customer moved city and history was rewritten.

Naive fix

Freeze a copy of last year.

Right fix

SCD2 in silver and a point-in-time join in gold.

A day loaded twice

Gold totals are double for one day.

Naive fix

Delete by hand.

Right fix

Overwrite by partition (or MERGE) and reconcile against silver every run.

Concept vs product

On each platform

JobAWSAzureGoogle CloudDatabricksOpen source
Modellingdbt on Redshift or Athenadbt on Fabric, Fabric WarehouseDataform or dbt on BigQuerydbt or Lakeflow Declarative Pipelinesdbt + DuckDB
Gold tablesRedshift, Iceberg on S3Fabric Warehouse / LakehouseBigQueryDelta in Unity CatalogDelta, DuckDB, Postgres
Semantic layerdbt semantic layerPower BI semantic modelsLookerMetric viewsCube Core
DashboardsQuickSightPower BILookerAI/BI dashboardsSuperset or Metabase

Product names change often; check the current docs before you build.

FAQ

Questions people ask

Do I always need a star schema in gold?

No, but it is the safest default for analytics. Wide, flat tables are fine for a single dashboard or ML features, as long as they are built from the shared facts and dimensions.

Where do surrogate keys come from?

The platform makes them when it builds the dimension, one per version of a row. Don't reuse the source system's id as the dimension key once you keep history.

Should gold be views or tables?

Tables for anything large or used often (rebuilt by partition); views for thin, cheap layers on top.

How is gold different from the semantic layer?

Gold holds the tables. The semantic layer holds the definitions on top of them (metrics, joins, names), so every tool computes 'revenue' the same way.

Next

Where to go next

↑ The whole picture · The medallion layers · Key concepts · The Data Engineering path

See it run: DataTwin Studio plays this component step by step on a real recorded run, including what happens when the same file arrives twice.