Templates in development

Written for Databricks, Snowflake and Fabric and tested on DuckDB. None has run on a real platform yet, and none is released.

Aggregation / gold

Aggregation / gold: metrics at a set grain.

Join conformed silver tables and aggregate them into gold tables at a declared grain. Metric definitions live in one place, and the totals reconcile with their sources.

Template in development
Model and serve
Databricks first
Platform notesSQL sketch
ShareLinkedInXRedditBluesky

Shape card

09 / 10

  1. Source
  2. Bronze
  3. Silver (used)
  4. Gold (used)
  5. Apps
Group
Model and serve
Works across
Silver to Gold
Reads
Conformed silver tables
Writes
Gold metrics tables
Slots
7 typed slots
Tested by
Golden tests on DuckDB
Platforms
Databricks first, then Snowflake and Microsoft Fabric

Overview

What Aggregation / gold does.

Build the metrics tables that dashboards and reports read, at a declared grain.

What it does

Aggregation / gold joins conformed silver tables and aggregates them into gold tables at a declared grain, such as revenue per day per region. Metric definitions live in one place, so every report uses the same numbers.

It is the last step before BI, and the table Emolysis builds when you ask a business question. The agent proposes the grain and the measures, you confirm them, and the template builds and tests the table that a dashboard reads.

Use it when

  • Any table that a dashboard, a semantic model or a report reads.
  • Metrics that several teams use and must agree on.
  • Pre-aggregating a large fact table so that reports are fast and cheap to run.

Use another shape when

Slots

Inputs, outputs and slots.

What the Aggregation / gold template reads, what it writes and the typed slots it asks you for. The agent fills most slots from your catalog and your request.

In

Inputs

  • Conformed silver tables: facts and the dimensions they join to
  • The declared grain, the measures and their filters
  • The refresh mode

Aggregation / gold

  1. Source
  2. Bronze
  3. Silver (used)
  4. Gold (used)
  5. Apps

Out

Outputs

  • A gold table with one row per combination of the grain columns
  • Totals reconciled against the source tables
  • A table a dashboard or semantic model can read directly

Template slots

7 slots · names and types may change before release

grain_columns
columns
What one row represents, for example the order date and the region.
measures
expressions
Each measure and how it aggregates: a sum, a count, a distinct count or a ratio of sums.
fact_table
table
The silver fact table the measures come from.
dimension_joins
expressions
The dimension tables to join and the join conditions.
filters
expressions
Conditions that rows must meet to count, such as a settled payment status.
refresh_mode
enum
Full rebuild, incremental by partition, or managed by the platform.
target_table
table
The gold table to write.

Slot types are identifier, table, column, columns, expression, expressions, string, integer, boolean, duration and enum. What is a template slot.

Testing

Tested before it ships.

Every template comes with golden tests that run on DuckDB on your machine before anything reaches your platform.

How a run goes

The golden test uses input whose totals are known in advance, then checks every number the table produces.

  1. 01Fixtures go in: small tables with the hard cases for the shape.
  2. 02The template runs on DuckDB on your machine, with no cluster and no warehouse compute.
  3. 03The result is compared with the expected output, row for row.
  4. 04Green can deploy. Red sends the agent back to the template.
What is a golden testGolden tests in the docs

What the Aggregation / gold test checks

  1. Case 01

    A one-to-many join, such as orders to payments, does not inflate the order count or the revenue.

  2. Case 02

    Every aggregate equals its hand-computed total at the declared grain.

  3. Case 03

    A ratio is computed from summed numerators and denominators, not by averaging averages.

  4. Case 04

    Rows with a null group key land in an explicit unknown group instead of vanishing.

  5. Case 05

    Amounts in two currencies are never added together.

Each template is also judged per platform: yellow when it passes on DuckDB, green when it also passes on the real platform. Nothing is released, so no template has a green mark yet.

Platforms

Written for the platform you run.

The same shape has a different correct form on each platform. This is what the Aggregation / gold template uses on each one, and the platform facts that decide it.

Databricks

In development

The Databricks template builds Delta gold tables or materialized views, governed in Unity Catalog.

  • A materialized view in Databricks SQL refreshes on a schedule and can update incrementally where the query allows it. A plain Delta table keeps full control of the refresh.
  • Unity Catalog grants decide who can read the gold table, so the template's notes list the SELECT grant that dashboard consumers need.
  • Clustering or partitioning by the column that dashboards filter on most, usually the date, keeps queries fast.
  • Delta CHECK constraints can enforce rules such as non-negative amounts, where the business has such a rule.
Emolysis for Databricks

Snowflake

Planned

The Snowflake template uses dynamic tables with a target lag, or tables refreshed by tasks.

  • A dynamic table declares the query and a target lag, and Snowflake keeps it fresh, incrementally when the query allows it.
  • A table refreshed by a task gives explicit control over when and how the gold table is rebuilt.
  • The warehouse that does the refresh bills while it runs, so the target lag is a cost decision as well as a freshness one.
  • Consumers need SELECT on the table and USAGE on its database and schema, which the template lists in its notes.
Emolysis for Snowflake

Microsoft Fabric

Planned

The Fabric template builds lakehouse or warehouse tables that feed semantic models.

  • A lakehouse gold table is a Delta table. A warehouse gold table is built with T-SQL, for example CREATE TABLE AS SELECT.
  • Semantic models read these tables, so the declared grain and the measure names carry through to reports.
  • A Data Pipeline schedules the refresh. Fabric capacity is shared, so the schedule is also a capacity decision.
  • The SQL analytics endpoint exposes lakehouse gold tables to T-SQL tools without a copy.
Emolysis for Microsoft Fabric

Whatever the platform, the template is tested on DuckDB first. The platform notes describe each product in general terms, so check the details against the version you run.

Mistakes

Mistakes it prevents.

The failures that most often hit a pipeline of this shape in production. Each one is a case in the template's golden test or a rule in its design.

  1. Double counting from one-to-many joins

    Joining orders to their payments repeats each order once per payment, and a revenue sum multiplies. The template aggregates to the right grain before it joins, and the test has a one-to-many case.

  2. Averages of averages

    The average of daily averages is not the average of all rows. Ratios are computed from summed parts.

  3. Two grains mixed in one table

    Detail rows and totals in one table make every sum ambiguous. The grain is a slot, and each table has one.

  4. Rows lost because a group key is null

    Grouping or inner-joining on a null key makes the row disappear and the total too small. The template maps null keys to an explicit unknown member.

  5. Amounts in different currencies or time zones added together

    A sum across currencies has no meaning, and dates in two zones fall on different days. The template converts first or keeps them apart.

SQL sketch

The idea in SQL.

Aggregate at the grain before joining down

Illustrative sketch in generic SQL. Platform templates may build a materialized view or a dynamic table instead of a rebuilt table.

Illustrative sketch · SQL
CREATE OR REPLACE TABLE gold.revenue_by_day_region AS
WITH paid AS (
  -- Collapse payments to the order grain first, so a join cannot multiply orders.
  SELECT order_id, SUM(amount) AS paid_amount
  FROM silver.payments
  WHERE status = 'settled'
  GROUP BY order_id
)
SELECT
  CAST(o.ordered_at AS DATE)          AS order_date,
  COALESCE(c.region, 'unknown')       AS region,
  COUNT(*)                            AS orders,
  SUM(COALESCE(p.paid_amount, 0))     AS revenue
FROM silver.orders AS o
LEFT JOIN silver.customers AS c ON c.customer_id = o.customer_id
LEFT JOIN paid AS p ON p.order_id = o.order_id
GROUP BY CAST(o.ordered_at AS DATE), COALESCE(c.region, 'unknown');

Related

Shapes that work alongside it.

Real pipelines are several shapes in a row. These are the ones that most often sit next to this one.

08

Model and serve

Dedupe / conform

The step before: clean silver rows for the aggregates to read.

Explore Dedupe / conform
04

Sync and history

SCD Type 2

For dimensions whose attributes must be as they were at the time.

Explore SCD Type 2
10

Model and serve

Reverse ETL

To push the same gold data into business applications.

Explore Reverse ETL

Glossary

Keep reading

FAQ

Questions about Aggregation / gold.

What is a gold table?

A gold table holds business-ready data: metrics, aggregates and dimensional models at a declared grain, built for dashboards, semantic models and reports. Gold tables apply business definitions once, so every report uses the same numbers.

How do I avoid double counting in an aggregation?

Declare the grain first, then make every table you join sit at that grain or finer before you aggregate. Collapse the many side of a one-to-many join to the grain of the one side, and check totals against a source. The template's golden test uses input with known totals, including totals that cross a one-to-many join.

What does grain mean?

The grain of a table is what one row represents, such as one order line, one customer per day or one region per month. Declaring it first prevents the most common aggregation mistakes: double counting through joins and mixing detail with totals in one table.

Materialized view, dynamic table or a rebuilt table?

A materialized view or a dynamic table lets the platform keep the result fresh and is the least work when the query allows it. A table you rebuild or refresh yourself gives full control over timing and cost. The refresh_mode slot lets the template use whichever your platform and your freshness needs favor.

In development · Not released

Test first, then deploy.

The Aggregation / gold template is written for Databricks, Snowflake and Microsoft Fabric, and its golden tests pass on DuckDB. Nothing is released yet. Early access is by invitation, for teams that want to help shape it.

Apply for early accessAll ten shapes