# What makes a Gold table ready for business in Databricks

> Every daily rate in our Gold table was right, yet one supplier's weekly average read 12.0% against 4.8%. The four checks we'd run before certifying the table.

Published: 2026-11-04
Author: Ihor Seleznov
Tags: Databricks, Knowledge Share
Canonical: https://www.techfabric.com/blog/what-makes-a-gold-table-ready-for-business-in-databricks

---

A supplier's weekly rejection rate can come out wrong in a Gold table, the finished layer dashboards read, even when every daily rate in it is right. In a receiving example we ran, one supplier's daily rates averaged 12.0% for the week, while its loads for that week gave 4.8%, because it delivered 2 loads on Monday and 10 on each other day.

We think a Gold table is ready for business when four checks pass, and that a Databricks job should mark it as certified in Unity Catalog only after they do. We'd recompute its numbers from the cleaned Silver data at every level a dashboard shows, from one supplier's day to the plant's week, because that supplier's daily rates were right and its weekly average wasn't. We'd also mark each day as open or closed, so a reader knows which numbers can still move, and once a day closes we'd hold it to a snapshot and to Business Central, the ERP.

We'd put these checks behind the Gold tables in our [plant-reporting demonstration](https://www.techfabric.com/case-studies/plant-leadership-reporting-databricks). There, Gold tables feed nine leadership dashboards and a Genie space for plain-language questions, with one definition per metric, and Business Central stays the system of record. To test the checks, we built a smaller receiving example with one closed week and an open Monday of scale tickets (the weight records from the scale) for two suppliers and the matching Business Central receipts. Locally the run returned CERTIFY only when all four checks passed and HOLD otherwise.

![A stack of bronze, silver and gold bars; the gold bar on top has four hallmarks, next to a certification seal.](/blog-media/how-we-certify-a-gold-table-as-ready-for-business/hero.png)

## Weekly rejection rates from daily load counts

Supplier S2 delivered 2 loads on Monday, 19 October, and 10 on each of the next four days, with one rejected load on Monday and one on Friday. If Gold stores each day's rate and a dashboard averages them for the week, it shows 12.0%. The supplier had 2 rejected loads out of 42, which is 4.8%.

Supplier S1 delivered 12 loads every day, so both methods gave 3.3%. Averaging daily rates only goes wrong when daily volumes differ.

![Supplier S2's five daily rejection rates average to 12.0%, while 2 rejected loads out of 42 give 4.8% for the week.](/blog-media/how-we-certify-a-gold-table-as-ready-for-business/ratio-rollup.png)

So our Gold table stores counts that add up across days: the loads, the rejected loads and the accepted net weight. We define the rejection rate once, in a [Unity Catalog metric view](https://docs.databricks.com/aws/en/uc-semantics/metric-views/), and the query engine computes it for whatever a dashboard or a Genie Agent groups by, from a supplier's day to the plant's week:

```yaml
version: 1.1
comment: 'Supplier receiving at plant P1. Weight measures count accepted loads only.'
source: plant.gold.supplier_receiving_daily
fields:
  - name: Business Day
    expr: business_day
  - name: Week
    expr: DATE_TRUNC('WEEK', business_day)
  - name: Supplier
    expr: supplier_id
  - name: Period Status
    expr: period_status
    comment: 'closed = final; open = scale tickets can still arrive'
measures:
  - name: Loads
    expr: SUM(loads)
  - name: Rejection Rate
    expr: try_divide(SUM(rejected_loads), SUM(loads))
    comment: 'Rejected loads divided by all weighed loads, at any grain'
  - name: Accepted Net Tonnes
    expr: SUM(accepted_net_kg) / 1000
```

The rollup check calculates the rejection rate for each supplier by day, each supplier by week and the plant by week, and compares it with the same rate calculated from the Silver data. A test of the stored daily rates alone would have passed the averaged figure. At the weekly level it failed for S2 and for the plant, where the average gave 7.7% against an actual 3.9%.

## A final or provisional status for each day

At 14:00 on Monday, 26 October, the refresh had only part of the day's loads, and Monday showed 176.4 accepted tonnes against a daily average of 427.98 tonnes the week before. On a chart without a status, that looks like a collapse in receiving.

We gave each Gold row a status. In our example scale tickets can arrive up to 24 hours after the day ends, so a day stays open until then and is closed after that. The metric view exposes the status as a field, so a dashboard can label open days as provisional or leave them out of week-on-week comparisons. The status check confirms that no day is marked closed before the day has ended and its 24-hour window has passed, and it returned HOLD when we marked the open Monday as closed.

![Daily accepted tonnes for one week, with the open Monday shown as provisional at 176.4 tonnes and the closed days solid.](/blog-media/how-we-certify-a-gold-table-as-ready-for-business/open-period.png)

When a day closes, we keep a snapshot of its Gold figures, and the closed-day check compares every later refresh with it. In one scenario, a scale ticket for Tuesday arrived the following Monday and the refresh restated S1's Tuesday from 267,150 kg to 291,600 kg. Business Central had the receipt too, so the tie-out still balanced and only the closed-day check returned HOLD. We want a late load for a closed day to come in as a recorded adjustment that someone approves, so a leader who already read that day's number can see why it moved. Our [row-balance article](https://www.techfabric.com/blog/how-we-balance-rows-between-bronze-silver-and-gold) covers the other conditions for closing a day, such as receipts still set aside for repair.

## Closed days reconciled with Business Central

Finance will compare these numbers with Business Central anyway, so we run that comparison in the pipeline first. For each closed day, Gold's accepted weight minus the posted receipts minus the open receiving exceptions, loads someone has logged as weighed but not yet received, has to be zero:

```sql
WITH gold AS (
  SELECT business_day, SUM(accepted_net_kg) AS gold_kg
  FROM gold_supplier_receiving_daily
  WHERE period_status = 'closed'
  GROUP BY business_day
), erp AS (
  SELECT posting_day AS business_day, SUM(posted_kg) AS erp_kg
  FROM bc_posted_receipts
  GROUP BY posting_day
), explained AS (
  SELECT business_day, SUM(net_kg) AS explained_kg
  FROM receiving_exceptions
  WHERE status = 'open'
  GROUP BY business_day
)
SELECT g.business_day,
       g.gold_kg,
       COALESCE(e.erp_kg, 0) AS erp_kg,
       COALESCE(x.explained_kg, 0) AS explained_kg,
       g.gold_kg - COALESCE(e.erp_kg, 0) - COALESCE(x.explained_kg, 0) AS unexplained_kg
FROM gold g
LEFT JOIN erp e ON e.business_day = g.business_day
LEFT JOIN explained x ON x.business_day = g.business_day
WHERE g.gold_kg - COALESCE(e.erp_kg, 0) - COALESCE(x.explained_kg, 0) <> 0;
```

On Thursday, one S2 load was weighed and accepted at the scale, but its receipt wasn't posted yet. Gold showed 449,850 kg and Business Central 431,700 kg, and an open exception with an owner explained the 18,150 kg between them, so the unexplained weight was zero and the tie-out balanced. It's the kind of load the case study's receiving-exceptions report flags. When we posted one Wednesday receipt 1,000 kg short, the query returned that day with 1,000 kg unexplained, and the run returned HOLD.

```mermaid
%% caption: For each closed day, Gold's accepted weight minus Business Central's posted receipts minus open receiving exceptions has to be zero, or the run returns HOLD.
flowchart TD
  A["Gold accepted kg for a closed day"] --> D["Unexplained kg"]
  B["Business Central posted kg"] --> D
  C["Open receiving exceptions kg"] --> D
  D -->|"zero"| E["Tie-out balances"]
  D -->|"1,000 kg"| F["HOLD"]
  class D accent
```

Our example assumes that Business Central's posting date is the plant's business day. A plant whose receipts post the next morning has to map the posting date to the business day before the comparison.

## The certification tag, set and removed by a job

Databricks' [certification status tag](https://docs.databricks.com/aws/en/data-governance/unity-catalog/certify-deprecate-data) shows a check mark next to a certified table in the workspace, and the documentation says certification "helps Genie One improve its answers by prioritizing the assets your organization vouches for." We designed a job to set it. In that design the readiness checks run as one task after the Gold refresh, and that task fails if any check finds a problem. A certify task depends on it with Run if set to [All succeeded](https://docs.databricks.com/aws/en/jobs/run-if), and a second task with At least one failed removes the tag:

```sql
SET TAG ON TABLE plant.gold.supplier_receiving_daily
  `system.certification_status` = `certified`;

UNSET TAG ON TABLE plant.gold.supplier_receiving_daily `system.certification_status`;
```

The identity the job runs as needs the ASSIGN permission on the certification tag, which Databricks requires before anyone can apply it. Without the removal step, a table that failed today keeps yesterday's check mark. The tag is a signal for people and Genie, and it doesn't stop a dashboard from reading the table. When a failed check must keep numbers off a dashboard, the job has to publish to the table the dashboard reads only after the checks pass.

![The baseline passes all four readiness checks and is certified, and each injected defect fails one check and is held.](/blog-media/how-we-certify-a-gold-table-as-ready-for-business/readiness-gate.png)

We ran the clean data and four deliberately broken versions of it on our own machines in DuckDB 2.0.0, and each broken version failed only the check aimed at it. The metric view, the tags and the job haven't run in a Databricks workspace yet.

Start with one Gold table a leader reads every week. Store the counts behind each rate, give each day a status, keep a snapshot of closed days and compare them with the ERP, and let a job set the check mark.

## Frequently asked questions

### Why not store the daily rejection rate in the Gold table?

Daily rates don't add up across days, so a weekly figure built from them ends up as their average, which can be wrong when daily volumes differ. Supplier S2's daily rates averaged 12.0% for the week while its loads gave 4.8%. We store the number of loads and the number of rejected loads and define the rate once in a metric view, so every grouping divides the sums.

### When does a day in Gold become final?

In our example a day stays open for 24 hours after it ends, because scale tickets can arrive that late, and then it's closed. The status check returns HOLD if any day is marked closed before that window ends, as it did when we closed the open Monday at the 14:00 refresh.

### What happens when a scale ticket arrives for a closed day?

The closed-day check compares the refreshed Gold row with the snapshot taken when the day closed, and it returns HOLD on any difference. In our late-ticket scenario Business Central had the receipt too, so the tie-out balanced and only this check caught the restatement. We want a late load for a closed day to come in as a recorded adjustment that someone approves.

### Does the certification tag stop a dashboard from reading a failed table?

No. The certification tag is a signal for people in the workspace and for Genie, and it doesn't restrict reads. If a failed check must keep numbers off a dashboard, the job has to publish to the table the dashboard reads only after the checks pass.

### Does the tie-out catch a day that exists only in Business Central?

Not as written. The query starts from Gold's closed days, so a day with receipts posted in Business Central and no Gold row isn't reported. A production version should start from the calendar of closed days.