Write a materialized view¶

Let’s produce a gold materialized view — a per-customer order summary — as a query-defined, engine-maintained table. And let’s do it without hand-writing the @dp.materialized_view decorator, the three-part table name, or the batch read that wires the query to the table.

The streaming tables you built in Get Started append rows as data arrives: LHP generates dp.create_streaming_table plus an @dp.append_flow that reads a stream. A materialized view is different in kind. Its contents are a query — you give Databricks a SELECT, and the engine keeps the table current by recomputing it from that query. Reach for a materialized view when the table is batch-computed analytics — a gold aggregation or dashboard source — rather than an incremental append. That “streaming table vs materialized view” decision has more to it; this page covers one task: writing the materialized view.

The change is one line of intent. Instead of write_target.type: streaming_table, you declare write_target.type: materialized_view and hand it the query. LHP writes the rest.

Before you start¶

You need a Lakehouse Plumber project (see the Get Started course for lhp init) and a source table for the view to read — here, a silver orders table your gold summary aggregates. The materialized view is defined entirely by its query, so this flowgroup needs no load and no upstream view.

Declare the materialized view¶

A materialized view is a single write action whose write_target is typed materialized_view and carries the defining query inline as sql. Create pipelines/gold_analytics.yaml:

pipelines/gold_analytics.yaml¶

# Write guide: define a gold materialized view from an inline SQL query.
# One action: a write whose target is a materialized_view. The view is the
# query — LHP needs no source view, transform, or append flow.
pipeline: gold_analytics
flowgroup: customer_summary

actions:
  # Materialized view: per-customer order summary, computed from the silver
  # orders table. Databricks recomputes it from the query; you declare the
  # target and the SELECT, nothing else.
  - name: write_customer_summary
    type: write
    description: "Per-customer order summary for gold dashboards"
    write_target:
      type: materialized_view
      catalog: "${catalog}"
      schema: "${gold_schema}"
      table: customer_summary
      comment: "Per-customer order summary for gold dashboards"
      sql: |
        SELECT
          customer_id,
          COUNT(*)      AS order_count,
          SUM(amount)   AS total_amount,
          MAX(order_ts) AS last_order_at
        FROM ${catalog}.${silver_schema}.orders
        GROUP BY customer_id

The sql block is the view: a GROUP BY over ${catalog}.${silver_schema}.orders that rolls each customer’s rows into an order count, a total, and a last-order timestamp. The ${...} tokens resolve per environment, so the same flowgroup targets dev, staging, and prod unchanged. Because the query defines the view, no action-level source is needed — the query is the source. The write action reference covers the other ways to supply the query (a source view or an external sql_path file) and the tuning options (refresh_policy, clustering, table_properties, Unity Catalog tags).

Generate the pipeline¶

Validate first, then generate:

$ lhp validate --env dev
✓ discover (0.01s)
✓ preflight (0.00s)
✓ gold_analytics  0 files
✓ validate (0.34s)
1 validated · 0.4s

$ lhp generate --env dev
✓ discover (0.01s)
✓ preflight (0.00s)
✓ gold_analytics  1 file
✓ generate (0.33s)
✓ format (0.05s)
✓ monitoring (0.00s)
1 pipeline generated · 1 file · 0.4s

Read what Lakehouse Plumber wrote¶

Open generated/dev/gold_analytics/customer_summary.py. This is the entire output — nothing is hidden behind a runtime:

generated/dev/gold_analytics/customer_summary.py¶

# Generated by LakehousePlumber
# Pipeline: gold_analytics
# FlowGroup: customer_summary

from pyspark.sql import DataFrame
from pyspark import pipelines as dp

# Pipeline Configuration
PIPELINE_ID = "gold_analytics"
FLOWGROUP_ID = "customer_summary"


# ============================================================================
# TARGET TABLES
# ============================================================================


@dp.materialized_view(
    name="dev_catalog.gold.customer_summary",
    comment="Per-customer order summary for gold dashboards",
    table_properties={},
)
def customer_summary():
    """Per-customer order summary for gold dashboards"""
    # Materialized views use batch processing
    df = spark.sql("""SELECT
  customer_id,
  COUNT(*)      AS order_count,
  SUM(amount)   AS total_amount,
  MAX(order_ts) AS last_order_at
FROM dev_catalog.silver.orders
GROUP BY customer_id""")

    return df

Compare it to a streaming table. There’s no dp.create_streaming_table and no @dp.append_flow — a materialized view needs neither. Instead LHP wrote one @dp.materialized_view decorator, resolved your catalog and schema tokens into the three-part name dev_catalog.gold.customer_summary, and generated a batch spark.sql(...) read of your query. You described the table’s definition; LHP wrote the declaration.

What you just did¶

Twenty lines of YAML — seven of them your SELECT — compiled to thirty-four lines of Lakeflow Python. Zero lines of the ``@dp.materialized_view`` wiring came from you: not the decorator, not the three-part name, not the batch read. Change the query and regenerate, and LHP rewrites the declaration to match — you never touch the Python.

Point the same write action at streaming_table instead and you get the append-flow shape from Get Started. That’s the payoff: the kind of table is a one-line declaration, and LHP owns the boilerplate for each kind.

What’s next¶

Tune the refresh and layout — set refresh_policy or automatic liquid clustering on the same write target. See the write action reference for the full option set.
Factor out the shared shape — when several gold views follow this same pattern, a template or preset stamps them out from short per-view configs.