Building Gold Objects with CREATE VIEW, CREATE MATERIALIZED VIEW and CREATE STREAMING TABLE

1.Creating a view in Unity Catalog

A view is the lightest gold object. It stores nothing, so building one means writing a query and registering it under a three-level name: catalog, schema, view. The syntax is CREATE VIEW <catalog-name>.<schema-name>.<view-name> AS SELECT <query>;. The documented example exposes a subset of columns from a raw sales table.

CREATE VIEW sales_metastore.sales.sales_redacted AS
SELECT
  user_id,
  email,
  country,
  product,
  total
FROM sales_metastore.sales.sales_raw;

To create the view you need USE CATALOG on the parent catalog plus USE SCHEMA and CREATE TABLE on the parent schema. There is no separate view-creation privilege in this list. You also need to be able to read everything the view references. Changing a view's definition or description later is reserved for its owner, and MANAGE is not enough. Databricks recommends building views only on Unity Catalog tables or views. If a view references workspace-local Hive metastore tables, you can access it only from that workspace.

Sources1

2.Creating a materialized view

The gold example in the medallion docs is a weekly aggregate. The defining query is an ordinary GROUP BY. What makes it a materialized view is the CREATE OR REPLACE MATERIALIZED VIEW header.

CREATE OR REPLACE MATERIALIZED VIEW main.example_output.weekly_bookings AS
SELECT date_trunc('week', check_in) AS week,
       property_id,
       status,
       count(*) AS total_bookings,
       sum(total_amount) AS total_revenue
FROM samples.wanderbricks.bookings
GROUP BY week, property_id, status

Materialized views can be created only on a Pro or Serverless SQL warehouse, or inside a pipeline. A view created outside a pipeline is a *standalone* materialized view. Creating it is synchronous: the command blocks until the view exists and the initial load finishes. Databricks automatically creates a serverless pipeline for each one, and that pipeline handles creation and every later refresh, so the work does not consume SQL warehouse compute. Under Jobs & Pipelines, standalone ones have the pipeline type MV/ST, while views defined in a pipeline show ETL. The user who creates the view becomes its owner. Only the one pipeline that defines a materialized view can update it.

Inside a Lakeflow pipeline, the same object can be declared in Python with a decorator on a function that returns a DataFrame. The docs also suggest CLUSTER BY instead of PARTITIONED BY for materialized views. CLUSTER BY AUTO lets Databricks choose the clustering keys.

from pyspark import pipelines as dp

@dp. ? 
def regional_sales():
  partners_df = spark.read.table("partners")
  sales_df = spark.read.table("sales")

  return (
    partners_df.join(sales_df, on="partner_id", how="inner")
  )

Sources23

3.Keeping a materialized view fresh: SCHEDULE and TRIGGER ON UPDATE

A materialized view is only as current as its last refresh. You can refresh manually, or set a refresh policy in the definition with one of two clauses.

SCHEDULE refreshes on a clock, either EVERY n HOURS|DAYS|WEEKS or a six-field Quartz CRON expression with an optional AT TIME ZONE. If AT TIME ZONE is missing, the session time zone is used. If no session time zone is set either, the statement errors.

TRIGGER ON UPDATE refreshes when an upstream source changes, at most once per minute. AT MOST EVERY sets a longer minimum gap between refreshes. The docs recommend it for production workloads, especially when upstream dependencies don't run on predictable schedules. Its upstream sources must be Delta tables (including other materialized views or streaming tables) or supported managed views. It is also limited to 10 upstream sources per materialized view.

CREATE OR REPLACE MATERIALIZED VIEW mv_trigger
  TRIGGER ON UPDATE
AS SELECT
  date,
  sum(sales) AS sum_of_sales
FROM
  base_table1
GROUP BY
  date;

CREATE OR REPLACE MATERIALIZED VIEW daily_revenue_by_region
  SCHEDULE CRON '0 30 3 * * ?' AT TIME ZONE 'UTC'
AS SELECT
  date_trunc('day', order_time) AS sales_date,
  region,
  sum(revenue) AS total_revenue,
  count(*) AS order_count
FROM
  orders
GROUP BY sales_date, region;

Sources3

4.Creating a streaming table, and when it misleads gold consumers

A streaming table is declared with CREATE OR REFRESH STREAMING TABLE and reads its input with STREAM(...). Like a materialized view, it can be standalone (pipeline type MV/ST) or defined in a pipeline. Each streaming table is owned and updated by a single pipeline, although several flows can append to it.

CREATE OR REFRESH STREAMING TABLE customers_with_id (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    name string,
    region string
  )
  AS SELECT name, region FROM STREAM(customers_bronze);

Processing each row once is what makes streaming tables fast. It is also why they are risky for gold. A row that has already been appended is never re-queried. If you change the query, for example from LOWER(name) to UPPER(name), only new rows reflect the change until you run a full refresh. Joins against dimensions also do not recompute. The docs call this suitable for "fast-but-wrong" scenarios and point to materialized views when the result must always be correct. Streaming tables also need streams that are naturally bounded or bounded with a watermark, since an unbounded stream without one can make a pipeline fail from memory pressure. So use a streaming table in gold only when consumers truly need low latency. Use a materialized view when BI users need a correct, current aggregate.

Sources4

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Creating a view requires a dedicated view-creation privilege on the schema, and MANAGE lets you change any view's definition.Why is that wrong?

   Creating a view needs USE CATALOG, USE SCHEMA and CREATE TABLE. Only the view's owner can update its definition or description; MANAGE is insufficient.

   Covered in Creating a view in Unity Catalog

2. 2.A streaming table that joins facts to a dimension will update earlier rows when the dimension changes.Why is that wrong?

   Streaming tables handle each row once and do not recompute joins. Use a materialized view when the gold result must stay correct as dimensions change.

   Covered in Creating a streaming table, and when it misleads gold consumers

3. 3.Adding EXPECT constraints to a materialized view has no effect on how it refreshes.Why is that wrong?

   Expectations in a materialized view's definition force a full refresh on every update and disable incremental refresh.

   Covered in Keeping a materialized view fresh: SCHEDULE and TRIGGER ON UPDATE

Practise it for real

Build an event-driven gold materialized view from a Databricks SQL warehouse and confirm the pipeline that maintains it

1. 1.Open the SQL editor attached to a Pro or Serverless SQL warehouse.

   Why: Materialized views can be created only on a Pro or Serverless SQL warehouse, or inside a pipeline.

   You should see: The editor is connected to an eligible warehouse.

2. 2.Run a CREATE OR REPLACE MATERIALIZED VIEW ... TRIGGER ON UPDATE statement that aggregates a Delta table you can read, modeled on the mv_trigger example.

   Why: TRIGGER ON UPDATE is the documented approach for production workloads whose upstream sources don't run on predictable schedules.

   You should see: The statement blocks until the view is created and the initial data load finishes.

3. 3.Open Jobs & Pipelines and add the Pipeline type column.

   Why: Every standalone materialized view gets its own serverless pipeline.

   You should see: A pipeline for your view appears with type MV/ST.

4. 4.Insert rows into the source table, then check the refresh status in Catalog Explorer.

   Why: With TRIGGER ON UPDATE, an update to the upstream source starts a refresh, at most once per minute by default.

   You should see: A new refresh runs and the aggregate reflects the inserted rows.