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.
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.
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
| Table | Holds | Key |
|---|---|---|
| fact_sales | One row per order line: quantity, amount | keys to each dimension + order id and line |
| dim_customer | Who: name, city, segment, with history from silver's SCD2 | customer_sk (surrogate key) |
| dim_product | What: name, category, brand | product_sk |
| dim_store | Where: store, city, region | store_sk |
| dim_date | When: day, week, month, holiday, fiscal period | date_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.
- Point-in-time join: the order joins to the customer version that was valid when it was placed.
- Inferred members: if the order arrives before its customer, it joins to a placeholder (-1) instead of disappearing; when the customer arrives, the next rebuild fixes it.
- Only from silver: gold never reads bronze, so it inherits every check silver passed.
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
- Aggregates (daily sales by store) make dashboards fast; they are rebuilt from the fact, never edited.
- Marts are subject areas for one team, built from the shared facts and dimensions.
- Data products are gold tables published for others with an owner, a contract, a freshness promise and documentation.
- Metric definitions (what 'net revenue' means) go one level up, into the semantic layer, not into each dashboard.
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
| Job | AWS | Azure | Google Cloud | Databricks | Open source |
|---|---|---|---|---|---|
| Modelling | dbt on Redshift or Athena | dbt on Fabric, Fabric Warehouse | Dataform or dbt on BigQuery | dbt or Lakeflow Declarative Pipelines | dbt + DuckDB |
| Gold tables | Redshift, Iceberg on S3 | Fabric Warehouse / Lakehouse | BigQuery | Delta in Unity Catalog | Delta, DuckDB, Postgres |
| Semantic layer | dbt semantic layer | Power BI semantic models | Looker | Metric views | Cube Core |
| Dashboards | QuickSight | Power BI | Looker | AI/BI dashboards | Superset 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.