Skip to content
FR

Gold Layer Objects and Data Quality with Lakeflow Spark Declarative Pipelines

Step 15 of 30

Gold Layer Objects and Data Quality with Lakeflow Spark Declarative Pipelines

Introduction

This lesson completes the Data Transformation and Modeling section of the official exam outline (about 22% of the scored questions). You have already built silver tables. 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. Build gold only from silver (or other gold), never directly from raw bronze, so that cleaning rules are applied once. Give columns clear business names and documented comments. Keep one table per business grain (per day and country, per customer). Register everything in Unity Catalog so that permissions, lineage and discovery work, for example main.gold.daily_revenue_by_country.

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) Every time it is queried 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 Incrementally processes new data from an append-only source; each record handled once 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 guarantees results consistent with the current state of its sources at refresh time and recomputes only what is needed when possible. A streaming table, in contrast, never reprocesses already-handled input, so changes to old source rows are not reflected. 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).

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

-- 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:

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 > 0)
)
AS SELECT CAST(order_id AS BIGINT) AS order_id,
          CAST(amount AS DECIMAL(12,2)) AS amount,
          country, 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 same silver table in Python:

from pyspark import pipelines as dp   # older code uses: import dlt
from pyspark.sql import functions as F

@dp.table(name="silver_orders")
@dp.expect("positive_amount", "amount > 0")
@dp.expect_or_drop("valid_order_id", "order_id IS NOT NULL")
def silver_orders():
    return (spark.readStream.table("bronze_orders")
            .select(F.col("order_id").cast("bigint").alias("order_id"),
                    F.col("amount").cast("decimal(12,2)").alias("amount"),
                    "country", "order_ts"))

Older notebooks use import dlt, @dlt.table and CREATE LIVE TABLE. The exam uses the new names, but the concepts are identical, so treat the old syntax as the same feature under its former name.

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. Databricks records how many rows passed and failed.

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 None written for the failing update The update fails and the transaction rolls back until the data is fixed

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 means something is badly wrong and downstream must not run (for example, row counts that make no sense, or a schema guarantee in a regulated report).

To keep invalid data for investigation instead of losing it, route it to a quarantine dataset: define an inverse condition (NOT (order_id IS NOT NULL AND amount > 0)) in a separate table, and keep the valid-only silver table with expect_or_drop. You can group several rules with expect_all, expect_all_or_drop and expect_all_or_fail in Python.

Expectation results appear in the pipeline UI's data quality view and in the pipeline event log, which you can query to build quality dashboards or alerts.

Expectations versus Delta constraints

Delta tables also support constraints enforced on write:

ALTER TABLE main.silver.orders ADD CONSTRAINT positive_amount CHECK (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 Counts per rule in the event log and UI
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, no duplicates on the key Drop (with quarantine) or fail for critical rules
Gold Totals reconcile with silver, no negative revenue, expected row 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(). Keep each rule small and named so that its metric tells you exactly what is wrong.

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 for 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 would recompute on every dashboard load.

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.

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 for the daily revenue would recompute every time. A fail action for requirement 2 would block the whole pipeline for a single bad row.

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. A failing update leaves the target unchanged until the data or rule is fixed.
  • Materialized view versus view. A view stores no data and recomputes on each query; a materialized view stores a precomputed result.
  • Streaming tables are append-only readers. They process each input record once and do not reflect updates to historical source rows.
  • 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.

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 > 0 with fail, because delivering a wrong customs report is worse than a delayed one.

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: Delta table that incrementally processes an append-only source.
  • 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 rolls back the update.
  • I can tell Delta constraints and pipeline expectations apart.
  • I can choose quality rules and actions for silver and gold datasets.

Official sources