Skip to content

Databricks Gold Tables, Materialized Views and Data Quality

Step 15 of 30

Databricks Gold Tables, Materialized Views and Data Quality

Introduction

This lesson completes the Data Transformation and Modeling section of the official exam outline (about 22% of the scored questions). The silver data cleaning and typing lesson established the inputs for Databricks data quality checks. Now you build the gold layer that business users consume, and you learn how to enforce data quality with Lakeflow Spark Declarative Pipelines (formerly Delta Live Tables, or DLT) expectations and Delta table constraints. The exam guide uses the current product name; older blog posts and some documentation still say DLT, so you should recognize both.

After this lesson you will be able to choose between tables, views, materialized views and streaming tables, design gold objects for BI teams, write a declarative pipeline in SQL and Python, and select the right expectation action (warn, drop or fail) for a data quality rule.

Learning objectives

  • Explain what belongs in the gold layer and who consumes it.
  • Choose between a table, a view, a materialized view and a streaming table for a given requirement.
  • Describe the declarative approach of Lakeflow Spark Declarative Pipelines and its dataset types.
  • Write expectations in SQL (CONSTRAINT ... EXPECT ... ON VIOLATION) and in Python (expect, expect_or_drop, expect_or_fail).
  • Distinguish pipeline expectations from Delta CHECK and NOT NULL constraints.
  • Design validation rules for silver and gold datasets and monitor their results.

1. The gold layer

Gold tables answer business questions. They are shaped for a use case, not for storage fidelity: aggregated, denormalized, named in business terms, and stable enough that dashboards and models depend on them.

Gold object Example Consumer
Aggregate table daily_revenue_by_country Finance dashboard
Dimension / fact model dim_customer, fact_sales BI semantic layer
Feature table customer_30d_features Data scientists
Reporting view v_active_subscriptions Analysts running ad hoc SQL

Good gold design follows a few rules. Prefer validated silver (or other gold) sources so that cleaning rules are reused. This is an architectural convention, not an engine restriction on reading bronze. A direct bronze-to-gold path must still implement the same validation contract. Give columns clear business names and documented comments. Keep one table per business grain (per day and country, per customer). Register gold objects in Unity Catalog for permissions, lineage and discovery, for example main.gold.daily_revenue_by_country. The Unity Catalog tables and access control lesson explains the privileges consumers and refresh identities need.

Concrete example. BrightGrid, an energy supplier, has silver tables meter_readings (one row per reading) and contracts. The BI team needs monthly consumption per contract region. Gold monthly_consumption_by_region has columns month, region, kwh_total, active_contracts and refreshes every night. Analysts query it without knowing anything about readings or deduplication.

2. Tables, views, materialized views and streaming tables

All of these are Unity Catalog objects, but they differ in when the data is computed and stored.

Object Stores data? Computed Best for
Table (Delta) Yes When you write to it (INSERT, CTAS, MERGE) General storage; full control over loads
View No (stores the query) Evaluated at query time, subject to applicable caches Reusable logic, simple security layers, always-fresh results on small or cheap queries
Materialized view Yes (precomputed result) Refreshed on a schedule or pipeline update; Databricks uses incremental refresh where it can Expensive aggregations and joins queried often, such as gold dashboards
Streaming table Yes Incremental processing through flows; append-only ingestion is a common pattern Ingestion and bronze/silver layers fed by files or message buses

How to choose:

  • Query is cheap and must always show the latest data: a view.
  • Query is expensive (large joins, aggregations) and many users read the result, a few minutes of staleness is fine: a materialized view.
  • Source is an append-only stream or Auto Loader folder and you want incremental processing: a streaming table.
  • You need arbitrary UPDATE, DELETE or MERGE logic written by your own code: a regular Delta table.

A materialized view stores the result of its defining query as of a refresh; supported queries can refresh incrementally, while others need full recomputation. Measure both refresh cost and serving latency. A normal append flow into a streaming table processes new input without reevaluating historical rows. It does not automatically apply arbitrary historical source corrections. AUTO CDC flows can apply updates/deletes to streaming tables, and a full refresh can replay retained source history; therefore “never reprocesses” is too strong. Materialized views and streaming tables created with SQL need to be maintained by a pipeline or by Databricks SQL refresh commands (REFRESH MATERIALIZED VIEW, REFRESH STREAMING TABLE).

SQL
-- Gold: expensive aggregate, read by many dashboards
CREATE MATERIALIZED VIEW main.gold.daily_revenue_by_country AS
SELECT to_date(order_ts) AS order_date, country,
       SUM(amount) AS revenue, COUNT(*) AS orders
FROM main.silver.orders
GROUP BY to_date(order_ts), country;

The examples assume the named catalogs and schemas exist, the executing identity has the required privileges, and main.silver.orders contains the typed order_ts, country and amount columns from the cleaning lesson. In the following path, main is the catalog, landing the schema and orders an existing volume; use immutable JSON files with string fields order_id, amount, country and order_ts. Parse timestamps under an agreed session timezone (UTC here). Standalone SQL objects also require supported SQL warehouse/serverless features.

SQL
-- Ingestion with a streaming table
CREATE OR REFRESH STREAMING TABLE main.bronze.orders_raw
AS SELECT * FROM STREAM read_files('/Volumes/main/landing/orders/', format => 'json');

3. Lakeflow Spark Declarative Pipelines

Lakeflow Spark Declarative Pipelines let you describe what each dataset should contain and let Databricks manage how: dependency order, incremental processing, retries, infrastructure and monitoring. You define datasets (streaming tables, materialized views and views) and the pipeline builds the graph from the references between them.

Key ideas:

  • A pipeline is the unit of deployment. It has a target catalog and schema, a source code (notebooks or files in SQL or Python), compute settings (serverless is common) and a trigger mode (triggered or continuous).
  • Each dataset declaration is a query. You do not write the MERGE, checkpoint or retry code yourself.
  • Datasets read each other with STREAM(...) (streaming read of another streaming table) or by plain name (materialized view reads).
  • A pipeline update runs the graph; the UI shows the DAG, row counts and data quality metrics.
  • Pipelines use the medallion layering naturally: streaming tables for bronze and silver, materialized views for gold.

A small pipeline in SQL follows. Create it separately from the standalone examples and configure its target catalog/schema. Do not let a second pipeline own the same outputs. Tolerant casts turn malformed fields into nulls before expectations evaluate them; ordinary casts can raise an ANSI conversion error first. This simplified sales contract accepts positive amounts only; refunds would require a different rule.

SQL
CREATE OR REFRESH STREAMING TABLE bronze_orders
AS SELECT * FROM STREAM read_files('/Volumes/main/landing/orders/', format => 'json');

CREATE OR REFRESH STREAMING TABLE silver_orders (
  CONSTRAINT valid_order_id EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW,
  CONSTRAINT positive_amount EXPECT (amount IS NOT NULL AND amount > 0) ON VIOLATION DROP ROW,
  CONSTRAINT valid_order_ts EXPECT (order_ts IS NOT NULL) ON VIOLATION DROP ROW
)
AS SELECT try_cast(order_id AS BIGINT) AS order_id,
          try_cast(amount AS DECIMAL(12,2)) AS amount,
          country, try_cast(order_ts AS TIMESTAMP) AS order_ts
FROM STREAM(bronze_orders);

CREATE OR REFRESH MATERIALIZED VIEW gold_daily_revenue
AS SELECT to_date(order_ts) AS order_date, country, SUM(amount) AS revenue
FROM silver_orders
GROUP BY to_date(order_ts), country;

The equivalent silver definition in Python is an alternative to the SQL silver declaration, not a second definition to include alongside it:

Python
from pyspark import pipelines as dp

@dp.table(name="silver_orders")
@dp.expect_or_drop("positive_amount", "amount IS NOT NULL AND amount > 0")
@dp.expect_or_drop("valid_order_ts", "order_ts IS NOT NULL")
@dp.expect_or_drop("valid_order_id", "order_id IS NOT NULL")
def silver_orders():
    return (spark.readStream.table("bronze_orders")
            .selectExpr("try_cast(order_id AS BIGINT) AS order_id",
                        "try_cast(amount AS DECIMAL(12,2)) AS amount",
                        "country", "try_cast(order_ts AS TIMESTAMP) AS order_ts"))

Older notebooks use import dlt, @dlt.table and CREATE LIVE TABLE. Recognize the former product vocabulary, but migrate using the current API and publishing-mode documentation. Legacy LIVE name resolution and current catalog/schema behavior are not blindly interchangeable.

4. Expectations: warn, drop, fail

An expectation is a data quality rule attached to a dataset: a name, a Boolean SQL condition, and an action when a record violates it. Warn and drop actions record row-level quality counts. Fail actions stop the affected flow and do not record expectation metrics for the failing update.

Action SQL Python Effect on bad rows Effect on the update
Warn (default) EXPECT (cond) @dp.expect("name", "cond") Kept in the target Continues; violations are counted in metrics
Drop EXPECT (cond) ON VIOLATION DROP ROW @dp.expect_or_drop Removed from the target Continues; drops are counted
Fail EXPECT (cond) ON VIOLATION FAIL UPDATE @dp.expect_or_fail The affected table update is rejected The failing flow stops and its table transaction rolls back; other flows may already have committed

How to pick the action:

  • Warn when bad data should be visible but must not stop or filter anything (a soft rule such as "country is usually populated"; you want a metric and a decision later).
  • Drop when invalid rows would corrupt downstream results and can be discarded (null primary keys, negative quantities).
  • Fail when any violation must reject the affected dataset update, such as a missing required country in a regulated report. Expectations evaluate a Boolean condition per row. For aggregate checks such as reconciliation totals, first calculate a validation dataset and then apply a row-level rule to that result. Conditions cannot query external services or use subqueries that reference other tables.

To keep invalid data for investigation instead of losing it, route it to a quarantine dataset: read the same parsed, unfiltered upstream data into a separate table using a null-safe rejection predicate, such as NOT coalesce(order_id IS NOT NULL AND amount IS NOT NULL AND amount > 0, false). Keep valid rows in silver with drop expectations. Include timestamp validity in both routes if it is part of the contract. Never derive quarantine from silver after invalid records have already been dropped. You can group several rules with expect_all, expect_all_or_drop and expect_all_or_fail in Python.

Inspect warn/drop counts in the pipeline event log and supported data-quality UI. Standalone SQL streaming tables/materialized views do not expose that same data-quality tab; query the event log. For fail actions, inspect the failure details rather than expecting pass/fail counters. A triggered pipeline may continue independent parallel flows when one fails: the entire graph is not one atomic transaction. Gate a regulatory export on a successful validation/pipeline task in Lakeflow Jobs orchestration and verify the expected run id and freshness before publishing. Previously committed snapshots may remain queryable.

Expectations versus Delta constraints

Regular Delta tables also support constraints enforced on write. The following is a separate regular-table example, not an instruction to alter the pipeline-owned silver_orders output. Existing rows must satisfy a new enforced constraint, and the caller needs appropriate privileges:

SQL
ALTER TABLE main.silver.orders ADD CONSTRAINT positive_amount
CHECK (amount IS NOT NULL AND amount > 0);
ALTER TABLE main.silver.orders ALTER COLUMN order_id SET NOT NULL;
Feature Delta CHECK / NOT NULL Pipeline expectation
Where it applies Any Delta table written by any job or SQL Datasets defined in a declarative pipeline
On violation The whole write fails with an error Warn, drop or fail, your choice
Metrics None built in Warn/drop counts in the event log; fail uses error details
Typical use Hard invariants on a table Row-level quality rules in a pipeline, with flexible handling

A Delta constraint cannot drop a bad row and continue; it stops the transaction. If the requirement is "discard bad rows and keep loading", use a pipeline expectation with a drop action (or filter the rows yourself in a regular job).

5. Data quality checks for silver and gold

Think of quality rules in layers:

Layer Typical checks Typical action
Bronze Almost none: keep everything, record the rescued data Warn, monitor
Silver Required keys not null, valid ranges, valid enumerations, parseable timestamps, deduplication logic for business keys Drop (with quarantine) or fail for critical rules
Gold Totals reconcile with silver, business-approved revenue rules, expected grain, freshness Fail or alert; aggregates should never be silently wrong

Examples of well-formed conditions: order_id IS NOT NULL, amount BETWEEN 0 AND 100000, country IN ('FR','DE','ES'), order_ts <= current_timestamp(). Combine nullable range checks with IS NOT NULL when a field is required. Keep each rule small and named for diagnosis. A per-row expectation cannot itself establish whole-table uniqueness; use the deduplication logic and an aggregate validation dataset.

Worked example: choosing the objects and actions for a sales pipeline

Scenario. A retailer lands JSON order files daily. Requirements: (1) ingest new files incrementally without reprocessing; (2) invalid orders (null order_id or non-positive amount) must not appear in reports, but the team wants to count how many are rejected; (3) finance wants a daily revenue table that is fast for dashboards, refreshed every night; (4) a monthly regulatory export must stop if more than zero rows have a missing country, because filing wrong data is unacceptable.

Step 1: bronze ingestion. Files are append-only and incremental, so a streaming table over read_files or Auto Loader.

Step 2: silver rules. Requirement 2 says remove invalid rows and count them: EXPECT ... ON VIOLATION DROP ROW for order_id IS NOT NULL and amount IS NOT NULL AND amount > 0. The metrics in the event log give the rejection count.

Step 3: gold for dashboards. Requirement 3 is an expensive, shared aggregation with acceptable staleness: a materialized view over silver. A plain view does not persist the aggregate; caches can help but do not replace an explicit refresh and staleness contract.

Step 4: the regulatory export. Requirement 4 says violations must stop the process: a fail action: EXPECT (country IS NOT NULL) ON VIOLATION FAIL UPDATE on the export dataset. Make the actual export a separate dependent job task that requires success and the current validation run; a failed flow does not roll back every other table or revoke an older export.

Why the alternatives are worse. A warn action would let invalid orders reach reports. A Delta CHECK constraint for requirement 2 would fail the whole write instead of dropping the bad rows. A view does not persist the aggregate. A fail action for requirement 2 would reject the affected flow for a single bad row rather than continuing with valid orders.

Common pitfalls and exam tips

  • Default expectation behavior is warn. Without ON VIOLATION, invalid rows are kept and only counted.
  • Drop is silent in the data. Dropped rows disappear from the target; check the metrics or quarantine to see them.
  • Fail rolls back the affected table transaction. Independent flow commits are not rolled back, and fail expectation counts are not recorded.
  • Materialized view versus view. A view stores a query rather than its result; a materialized view stores a precomputed result.
  • Choose the flow semantics. Append ingestion does not automatically replay history; AUTO CDC applies source changes and full refresh can replay retained input.
  • Names. DLT is now Lakeflow Spark Declarative Pipelines; @dlt.table is the former API, @dp.table the current one.
  • Constraints are not expectations. A CHECK constraint fails the write; an expectation can warn or drop.
  • Gold from silver. Building gold from bronze duplicates cleaning logic.

Exam tip: Read the requirement for the verb. "Record but keep" means warn, "remove" means drop, "stop" means fail. "Fast dashboards on heavy aggregation" means materialized view. "Process new files incrementally" means streaming table.

Mini case study

Situation. A logistics firm ingests parcel scan events into a pipeline. Rules: parcel_id must not be null (an event without it is useless), scan_ts must not be in the future (clock errors are common but not critical; the team wants to see how often they occur), and the weekly customs report must never contain a parcel without a declared weight_kg greater than zero. The operations center needs a dashboard of scans per depot per hour, queried by 200 people; an hourly refresh with some staleness is acceptable.

Task. Pick object types and expectation actions.

Solution.

  1. Bronze scans_raw: a streaming table over the landing volume.
  2. Silver scans: a streaming table with parcel_id IS NOT NULL as drop (the row is useless), and scan_ts <= current_timestamp() as warn (keep and count the violations).
  3. Gold scans_per_depot_hour: a materialized view over silver; 200 users read it, and the aggregation is heavy, so precomputing is right.
  4. Customs dataset: weight_kg IS NOT NULL AND weight_kg > 0 with fail on the customs dataset, because a delayed report is preferable to an invalid one. Publish only after the current validation succeeds; this does not imply rollback of independent ingestion flows.

Key terms

  • Gold layer: business-level tables and views for analytics and ML.
  • Materialized view: precomputed, stored query result refreshed by a pipeline or refresh command.
  • Streaming table: pipeline-owned Delta table updated incrementally by flows, including append ingestion and change-data processing.
  • Lakeflow Spark Declarative Pipelines (formerly DLT): declarative framework that manages dataset dependencies, incremental processing and quality.
  • Expectation: named data quality rule with a warn, drop or fail action.
  • Quarantine table: dataset holding rows that failed validation.
  • Event log: pipeline table with run details and expectation metrics.
  • CHECK constraint: Delta table rule that fails any violating write.

Summary checklist

  • I can say what belongs in gold and why it is built from silver.
  • I can choose between table, view, materialized view and streaming table.
  • I can write a simple declarative pipeline in SQL or Python.
  • I know warn is the default, drop removes rows, and fail rejects the affected flow without pipeline-wide atomic rollback.
  • I can tell Delta constraints and pipeline expectations apart.
  • I can choose quality rules and actions for silver and gold datasets.

Official sources