Gold layer

Overview

The gold layer is the final tier of a medallion data architecture. It holds curated, aggregated and modelled tables that are ready for business use. Dashboards, reports, machine learning features and applications read from gold instead of reading raw files. Gold tables are small compared with the layers before them, clean, documented and shaped around questions people actually ask.

The idea is simple. Raw data arrives messy, so it is first stored as is in the bronze layer, then cleaned and conformed in the silver layer, and finally reshaped into business-ready products in gold. This is the last step of the medallion architecture. A good gold layer gives every team the same numbers, the same definitions and fast queries. It is also the most useful input for tabular AI, because a well-modelled table is exactly what a model that learns from rows and columns needs.

What it is

A gold layer is a set of tables, views or files that represent business concepts rather than source systems. Examples are daily revenue by region, customer lifetime value, monthly active users, a star schema for sales, or a feature table with one row per customer.

Think of a restaurant kitchen. Bronze is the delivery of raw vegetables, still in crates. Silver is the prep station, where everything is washed, peeled and cut to standard sizes. Gold is the plated dish that goes to the table. The guest does not want to see crates.

Typical properties of gold tables

• Business-oriented. Names and columns use business language, such as net_revenue and active_customer_flag.
• Aggregated or denormalised. Data is summarised or joined so queries are cheap.
• Stable contracts. Column names, types and meanings change rarely and only with notice.
• Trusted. Quality checks run before data is published.
• Documented. Each table has an owner, a description and a refresh schedule.
• Governed. Access is controlled, and sensitive fields are masked or removed.

What gold is not

Gold is not a dumping ground for every possible table. It is not the place to fix raw data problems, because those belong in silver. It is also not a copy of a data warehouse that nobody uses. If a gold table has no consumer, it should not exist.

Where it came from

The words bronze, silver and gold became popular with the lakehouse movement, which tried to combine the low-cost storage of a data lake with the reliability of a data warehouse. Teams noticed that data naturally goes through stages of quality, and they named the stages with a medal metaphor that anyone could remember. The pattern is closely tied to open table formats such as Delta Lake and Apache Iceberg, which give lake files transactions and schema rules.

The gold layer itself has older roots. Data warehouses have long had data marts, which are subject-specific tables built for one department. Kimball-style dimensional modelling, with fact tables and dimension tables, goes back decades and is still the most common way to shape gold data. Aggregate tables, cubes and summary tables are earlier names for much the same idea. The lakehouse era mostly changed where these tables live and how they are built, not why.

Tools such as dbt, Apache Airflow and Apache Spark became the usual way to build and schedule gold transformations.

How it works

Building a gold layer is a repeatable pipeline. Here is the usual flow, step by step.

Step 1: Start from trusted silver tables

Gold reads only from silver, or sometimes from other gold tables. It does not read raw bronze files. This keeps cleaning logic in one place. If a customer id is fixed in silver, every gold table gets the fix.

Step 2: Agree on business definitions

Before writing code, the business must define the metric. What is an active customer? Is revenue before or after refunds? Which time zone defines a day? Writing this down avoids the classic problem where two dashboards show two different totals.

An everyday comparison is a family budget. If one person counts rent as a bill and another counts it as savings, the monthly total will never match. Definitions fix that.

Step 3: Model the data

Common gold designs include:

• Star schema. A central fact table, such as sales, surrounded by dimension tables, such as customer, product and date.
• Wide denormalised table. One flat table with many columns, easy for BI tools and for machine learning.
• Aggregate table. Pre-computed totals, such as revenue per day per region.
• Feature table. One row per entity, such as a customer, with many numeric and categorical features.
• Data product. A table or API with a clear owner, contract and service level.

Step 4: Transform and aggregate

Transformations are usually SQL or DataFrame code. They join silver tables, group, window and compute metrics. Large jobs run on an engine like Spark, often through PySpark, or in a warehouse such as Snowflake.

Step 5: Test and validate

Before a table is published, tests check that keys are unique, values are not null where they should not be, totals reconcile with the source, and row counts are within expected bounds. Failing tests stop the publish so that bad data never reaches dashboards.

Step 6: Publish atomically

Publishing should be all or nothing. Table formats with transactions let a job write a new version and switch readers over in one step, so a dashboard never sees half a table. See Delta Lake for how a transaction log makes this possible.

Step 7: Serve and monitor

Consumers read the tables through SQL, BI tools, notebooks or APIs. Monitoring watches freshness, volume and distribution, so owners hear about problems before users do.

Key concepts and terms

A worked example

Suppose a shop has a silver table silver.orders with one row per order line and a silver table silver.customers. The business wants a gold table of daily revenue per country and a gold feature table for customers.

Gold aggregate in SQL

CREATE OR REPLACE TABLE gold.daily_revenue AS
SELECT
  CAST(o.order_ts AS DATE)            AS order_date,
  c.country_code                      AS country_code,
  COUNT(DISTINCT o.order_id)          AS orders,
  SUM(o.quantity * o.unit_price)      AS gross_revenue,
  SUM(o.refund_amount)                AS refunds,
  SUM(o.quantity * o.unit_price) - SUM(o.refund_amount) AS net_revenue
FROM silver.orders o
JOIN silver.customers c
  ON o.customer_id = c.customer_id
WHERE o.status <> 'cancelled'
GROUP BY 1, 2;

The grain of this table is one row per date per country. That is written in the table comment so that nobody guesses.

Gold feature table in PySpark

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("gold_features").getOrCreate()

orders = spark.table("silver.orders")

features = (
    orders.groupBy("customer_id")
    .agg(
        F.countDistinct("order_id").alias("order_count"),
        F.sum(F.col("quantity") * F.col("unit_price")).alias("total_spend"),
        F.max("order_ts").alias("last_order_ts"),
        F.avg("unit_price").alias("avg_unit_price"),
    )
    .withColumn(
        "days_since_last_order",
        F.datediff(F.current_date(), F.col("last_order_ts")),
    )
)

features.write.mode("overwrite").saveAsTable("gold.customer_features")

Tests

-- Key must be unique
SELECT order_date, country_code, COUNT(*)
FROM gold.daily_revenue
GROUP BY 1, 2
HAVING COUNT(*) > 1;

-- Net revenue must not exceed gross revenue
SELECT *
FROM gold.daily_revenue
WHERE net_revenue > gross_revenue;

If either query returns rows, the pipeline stops.

A numeric walk-through

Say silver has three order lines for one day in one country:

Gross revenue is 20 + 5 + 12 = 37. Refunds are 4. Net revenue is 33. Distinct orders are 2. The gold row is therefore: orders 2, gross 37, refunds 4, net 33. Anyone can check this by hand, and that is the point. Gold numbers must be explainable.

Strengths and limitations

Strengths

• One version of the truth. Shared definitions end metric arguments.
• Speed. Pre-aggregated tables answer queries quickly and cheaply.
• Simplicity for consumers. Analysts need not know source system quirks.
• Reusability. The same table serves dashboards, apps and models.
• Governance. A small curated surface is easier to secure and audit.
• Cost control. Heavy computation happens once, not in every dashboard query.

Limitations

• Loss of detail. Aggregation hides rows. If a question needs detail, gold may not answer it.
• Staleness. Gold is only as fresh as its last refresh.
• Rigidity. A table built for one question can be awkward for the next.
• Maintenance. Each table needs an owner, tests and documentation.
• Table sprawl. Without discipline, teams create many near-duplicate gold tables.
• Upfront cost. Agreeing on definitions takes meetings and time.
• Backfills. A definition change may require recomputing a lot of history.

When gold is the wrong tool

Gold is a poor fit when the question is new and nobody knows the metric yet. In early exploration, analysts need row-level data and room to experiment, so silver is the better home. Gold is also a poor fit for very low-latency use cases, such as per-event fraud checks, where a serving system with its own online store is more suitable. And if only one person ever reads a table, a notebook may be enough. Promote a result to gold when at least two teams need it and the definition is stable.

Signs a gold layer is healthy

• New dashboards reuse existing tables most of the time.
• Metric definitions are found in one place.
• Incidents are caught by tests before users notice.
• Owners respond to questions within a day.
• Old tables are retired on schedule.
• Models and reports agree on the same numbers.

Signs a gold layer is in trouble

• Several tables claim to hold the same metric.
• Nobody can say who owns a given table.
• Dashboards break after silent schema changes.
• Analysts copy gold tables into private spreadsheets.
• Backfills take weeks because logic is scattered.
• Teams avoid gold and query raw files instead.

How it compares with alternatives

Many teams combine gold tables with a semantic layer. The gold tables store heavy aggregates, and the semantic layer exposes friendly metric names on top.

Role in tabular foundation models

Gold tables are close to the ideal input for tabular foundation models. A tabular foundation model is pre-trained on many tables so it can handle a new table without training from scratch. It reads rows and columns, deals with mixed types and missing values, and can use in-context learning, where a few labelled example rows in the prompt are enough to predict new rows. A gold feature table with one row per entity fits this setting very well.

Row and column structure

A gold table has a clear grain. One row means one customer, one order or one day per region. Models such as TabPFN, TabICL and TabSTAR treat each row as an example and each column as a feature. If the grain is unclear, the model sees duplicates or mixed entities and learns the wrong patterns. A documented grain is therefore a quality control for AI as well as for BI.

Mixed types

Gold tables mix numbers, categories, dates, flags and short text. Tabular foundation models are built for that mix. Good gold design helps by using consistent types, such as real dates instead of strings, and by keeping categories at sensible cardinality.

Missing values

Gold tables often have nulls, for example a customer with no orders yet has no last order date. A tabular foundation model can read missing values directly, and can also do imputation, which means predicting the likely value of a blank cell from the rest of the row. This is useful in gold when a source did not report a field. See imputation networks for the idea. Good practice is to keep nulls as nulls in gold and add flags if the missing pattern carries meaning.

No column names

Some tabular foundation models are trained to work even when column names are absent or meaningless, because many real tables have names like col_17. Gold tables are the opposite, with readable names. A model that can use names, such as one that embeds column headers, can gain extra signal from a gold table. A model that ignores names still works. Either way, gold tables are a safe input.

Pre-training on synthetic or real tables

Foundation models for tables are often pre-trained on large numbers of synthetic tables drawn from random causal structures, plus real tables. Gold-style tables, with clean features and a clear target, resemble the training tasks, so the transfer is smooth. You can read more on the model research and model architecture pages.

In-context learning

With in-context learning, you pass labelled rows from a gold table as context and ask for predictions on new rows, with no gradient updates. Because gold tables are small and tidy, they fit within the context size of these models. A small gold sample often beats a huge raw dump.

Continual learning

Gold tables are refreshed on a schedule. A model that supports continual learning can adapt to new data over time without forgetting old patterns. Versioned gold tables, such as those with time travel in Delta Lake, make it easy to reproduce what the model saw at any date.

Anomaly detection

Gold aggregates are also a natural place to detect anomalies. A model can flag a day where revenue per region falls far outside the usual pattern, or a customer row whose features look unlike all others. See anomaly detection. Running checks on gold catches problems that row-level checks in silver miss, such as a missing region for a whole day.

Sector and semantic classification

Gold dimension tables often hold category fields such as industry or product group. A tabular model can classify a record into a sector based on its numeric and categorical columns, and can help map messy labels to a standard list. Clean, governed gold dimensions give such classifiers solid ground truth to learn from. Benchmarks for this kind of work are summarised on the model benchmarks page.

Practical link

In short, bronze and silver prepare the data, and gold presents it in the shape a tabular foundation model prefers. If a team wants to try a tabular foundation model on a business question, the gold feature table is usually the right place to start.

A sketch of a gold-to-model workflow

Here is how a team might use a gold table with a tabular foundation model, step by step.

1. Pick a gold feature table, such as gold.customer_features, with one row per customer.
2. Join a label table, such as whether the customer churned in the next 90 days, using an as-of date to avoid leakage.
3. Split rows by time, not at random, so that the test set is later than the training set.
4. Sample a few thousand training rows as context for the model.
5. Ask the model to predict the label for the test rows.
6. Compare with a simple baseline such as logistic regression or XGBoost.
7. Write predictions back to a new gold table, such as gold.customer_churn_scores, so dashboards can read them.

The last step matters. Model outputs are data products too, and they belong in the same governed layer as other business tables.

What gold gives models that raw data does not

Where gold can mislead a model

Gold is not always the best input. Heavy aggregation removes signal: a monthly average hides a spike that mattered. Business rules baked into a metric, such as capping a value, may remove outliers a model would have used. For this reason, many teams keep a wide detail table in gold or silver for modelling, next to the aggregates used for reports. The rule of thumb is to give the model the least processed data that is still clean and well defined.

Practical guidance and best practices

Design

• Write the grain of each table in its description.
• Name tables and columns in business terms.
• Prefer a few well-modelled tables over many one-off ones.
• Keep gold reading only from silver or other gold tables.
• Use stable keys and avoid reusing ids.
• Separate facts from dimensions when that helps reuse.
• Store timestamps in one time zone, usually UTC, and convert at the edge.

Build

• Use declarative transformations with version control.
• Make jobs idempotent, so rerunning gives the same result.
• Use incremental loads for big tables, with a safe full refresh option.
• Prefer merge or replace-by-partition to avoid duplicates.
• Publish through atomic table operations.
• Use dbt or similar for modular SQL and built-in tests.
• Schedule with an orchestrator such as Apache Airflow.

Quality

• Test uniqueness, not-null, accepted values and relationships.
• Reconcile totals against silver and against a trusted external number.
• Add freshness and volume monitors.
• Track schema changes and review them.
• Quarantine failures instead of publishing partial data.

Governance

• Assign an owner to every table.
• Document sources, logic and refresh times.
• Apply row and column level security where needed.
• Mask or drop personal data that gold consumers do not need.
• Register tables in a data catalog so people can find them.
• Capture lineage, so you can see which dashboards depend on which columns.

For machine learning

• Keep a point-in-time correct feature table, so that features do not leak future information.
• Version gold snapshots used for training.
• Include a stable id and an as-of date.
• Keep raw values as well as derived ones when possible, since models can learn their own transformations.
• Do not encode missing values as zero unless zero is true.

Cost and performance

• Partition by date when queries filter by date.
• Compact small files regularly.
• Cluster or order data on common filter columns.
• Cache hot aggregates.
• Retire tables nobody reads.

Naming conventions that help

Good names save hours. A few habits that work well:

• Use a clear prefix or schema, such as gold. or a domain schema like gold_sales..
• Use singular names for dimensions, such as dim_customer, and plural or event names for facts, such as fact_orders.
• Add units to measure columns when they are not obvious, such as weight_kg or amount_usd.
• Use _ts for timestamps, _date for dates and _flag for booleans.
• Avoid abbreviations only the author understands.
• Keep the same column name for the same concept in every table, so joins are easy to read.

An incremental load pattern

Recomputing a whole table every night is simple but wasteful. A common approach is to process only new or changed partitions and merge them into the gold table.

MERGE INTO gold.daily_revenue AS t
USING staging.daily_revenue_updates AS s
  ON t.order_date = s.order_date AND t.country_code = s.country_code
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *;

The staging table holds only the days that changed, for example the last three days to allow for late data. A weekly full refresh can act as a safety net. Late-arriving data is a real concern, so decide how many days back each run looks, and write that number down.

Handling slowly changing dimensions

Customers move, products change category and prices are updated. If a gold report needs to show the value at the time of the sale, the dimension needs history. The usual design adds valid_from, valid_to and is_current columns, and the fact table joins on the date range. If history is not needed, overwrite in place and say so in the documentation. Mixing the two approaches silently is a frequent cause of reports that do not match last year.

A release checklist for a new gold table

1. The business question and owner are written down.
2. The grain and primary key are documented.
3. Source silver tables are listed.
4. Tests for keys, nulls and totals pass.
5. A sample was reviewed by a business user.
6. Access rules are set and sensitive columns handled.
7. The table is registered in the catalog with a description.
8. Freshness and volume monitors are on.
9. A rollback plan exists, such as restoring a previous table version.
10. A deprecation date is set for any table it replaces.

Measuring whether gold is working

A gold layer should be judged by outcomes, not by table count. Useful signals include how many dashboards read from gold instead of silver, how often numbers are disputed, how long a new report takes to build, how many incidents come from stale or broken tables, and what share of queries hit curated tables. If analysts keep going back to raw data, the gold layer is missing something they need, and the right response is to ask what and add it.

Gold at different scales

A small team may have ten gold tables built with a handful of SQL files and a daily schedule. A large company may have hundreds, organised by domain, each owned by a team that treats its tables as data products. The principles stay the same. What changes is the amount of process around ownership, contracts and change management. Start small, and add process when the pain of not having it is visible.

Gold and data privacy

Because gold is widely read, it is the layer where privacy rules matter most. Drop direct identifiers that consumers do not need, replace them with pseudonymous keys, and aggregate to a level where individuals cannot be singled out. Keep a restricted detail table for the few people who really need it. For model training, the same care applies: a feature table should contain what the model needs and no more.

Documentation that people read

Short documentation beats long documentation nobody opens. For each table, write three things: what one row means, how the main metrics are calculated, and what the known caveats are. Put the text where users already look, such as column comments in the catalog. Update it in the same change that updates the code.

Common mistakes

1. Skipping silver. Building gold straight from raw data repeats cleaning logic in many places.
2. Unclear grain. Joining tables with different grains inflates totals, a bug known as fan-out.
3. Metric drift. Two tables compute revenue differently and nobody notices.
4. No tests. The first sign of bad data is an angry executive.
5. Too many gold tables. Near-duplicates confuse users and cost money.
6. Hiding business logic in dashboards. Logic should live in tables or a semantic layer, not in each report.
7. Overwriting history. Changing a definition without versioning breaks old reports.
8. Time zone surprises. Daily totals shift when the time zone is not fixed.
9. Leaking the future into features. A feature built with data after the prediction date makes a model look better than it is.
10. Ignoring access control. Gold often has the most valuable data and deserves the strictest rules.
11. Treating nulls as zeros. This changes averages and misleads models.
12. No owner. Tables without owners decay.

Frequently asked questions

What is the gold layer in a data lakehouse?

The gold layer is the last tier of the medallion architecture. It contains curated, aggregated and modelled tables that are ready for dashboards, reports, applications and machine learning. Data reaches gold after it has been ingested in bronze and cleaned in silver, so gold tables carry trusted business definitions and stable schemas.

How is gold different from silver?

Silver holds cleaned and conformed data at a detailed level, usually close to the grain of the source systems. Gold reshapes that data around business questions, with aggregates, joins and metrics. Silver is built for reuse by technical teams, while gold is built for consumers who want answers rather than raw records.

Do I need all three medallion layers?

Not always. Small projects may merge layers, and some pipelines add extra layers. The value of the pattern is the separation of concerns: keep raw data, clean data and business data apart. If you skip a layer, you should still know where cleaning happens and where business logic lives.

Should gold tables be aggregated or detailed?

Both are common. Aggregate tables serve dashboards well, while wide denormalised tables at row level serve analysis and machine learning. The key rule is to state the grain clearly. A team can keep an aggregate gold table for reports and a detailed gold feature table for models, built from the same silver sources.

How often should gold tables be refreshed?

It depends on the use. Executive dashboards might need a daily refresh, operational monitoring might need minutes, and monthly finance tables might refresh once a month after close. Pick the slowest schedule that meets the need, because faster refresh costs more. Document the schedule and monitor freshness so users know what to expect.

Can I use gold tables for machine learning?

Yes. A gold feature table with one row per entity is often the best starting point. Make sure features are computed as of the right date to avoid leakage, keep versions of the snapshots, and retain missing values honestly. Tabular foundation models can then use the table directly, even with only a few labelled rows.

What tools build a gold layer?

Common choices are SQL engines and warehouses, Apache Spark with PySpark, and transformation tools like dbt. Storage is often an open table format such as Delta Lake or Apache Iceberg. An orchestrator schedules the jobs and a catalog documents the results.

How do I test a gold table?

Test uniqueness of keys, non-null on required columns, allowed values, relationships between tables, and reconciliation of totals with the source. Add checks for freshness and for sudden changes in row counts or distributions. Run the tests before publishing, and stop the pipeline if they fail.

What is the difference between gold tables and a data warehouse?

A data warehouse is a system, while gold is a layer of tables that can live in a warehouse or in a lakehouse. Many gold tables look like warehouse marts, with facts and dimensions. The lakehouse approach stores them in open formats next to raw and silver data, so one platform serves all stages.

Who owns the gold layer?

Ownership is usually shared. A data engineering team builds and runs the pipelines, while business or analytics owners define the metrics and approve changes. Each gold table should have a named owner and a documented contact, so questions and incidents have a clear destination.

Glossary

• Aggregate: A summary value such as a sum, count or average over a group of rows.
• Atomic publish: Making a new table version visible in one all-or-nothing step.
• Backfill: Recomputing historical data after a logic change.
• Bronze layer: The first medallion tier, holding raw ingested data.
• Data contract: An agreed schema and meaning between a data producer and its consumers.
• Data mart: A table set designed for one subject or team.
• Denormalisation: Joining data into wider tables to reduce joins at query time.
• Dimension: A descriptive entity such as a customer or product.
• Fact: A measurable event such as a sale.
• Fan-out: Duplicated rows after a join on mismatched grains.
• Feature table: A table of model inputs, one row per entity.
• Grain: The meaning of one row in a table.
• Idempotent: Safe to run many times with the same result.
• In-context learning: Predicting from examples placed in the model input without retraining.
• Lineage: The path from output columns back to sources.
• Medallion architecture: A bronze, silver and gold layering pattern.
• Semantic layer: A shared definition of metrics over tables.
• Silver layer: The cleaned and conformed tier of the medallion pattern.
• Star schema: A fact table linked to dimension tables.
• Surrogate key: A generated identifier for a dimension row.

Summary

The gold layer is where data becomes a product. It sits at the end of the medallion architecture, built from clean silver tables, and offers aggregated, modelled and documented tables for reports, applications and machine learning. Its value comes from shared definitions, tests, clear grain and careful publishing.

Build gold with business names, stable contracts and automated checks. Keep it lean, with an owner for every table. For tabular AI, gold feature tables are an excellent input, since they give models clean rows, typed columns, honest missing values and a clear target. Tabular foundation models can use them for prediction, imputation, anomaly detection and classification with little extra work. Start with a few high-value tables, prove them with tests, and grow from there.

Related

It belongs to Table formats and lakehouse in my stack, under Data engineering and lakehouse. Others in the same group: