On this page

• Materialized view
  • Minimal example
  • Options
  • Expectations
  • Shared table and view fields
  • Action fields
  • Related guides

Materialized view

write_target.type: materialized_view. Emits a @dp.materialized_view(...) decorated function. Target-specific fields are listed below.

Minimal example

Add this entry to the actions list in your flowgroup.

- name: write_customer_summary
  type: write
  write_target:
    type: materialized_view
    catalog: "${catalog}"
    schema: "${gold_schema}"
    table: customer_summary
    sql: "SELECT customer_id, COUNT(*) AS orders FROM v_orders GROUP BY customer_id"

Options

Key	Type	Default	Notes
sql	string	—	Inline query; one of sql / sql_path / action source.
sql_path	string	—	External .sql query file.
refresh_schedule	string	—	Cron/schedule; emitted as refresh_schedule=.
refresh_policy	string	—	One of auto, incremental, incremental_strict, full.
expectations_file	string	—	Path to a data-quality expectations file, relative to the project root. Each rule becomes a @dp.expect_all, @dp.expect_all_or_drop or @dp.expect_all_or_fail decorator on the view. See Expectation formats for the file formats and Expectations below. Valid only on a materialized_view write target.

Define the view by exactly one of: an action-level source view, inline sql, or sql_path. When sql/sql_path is provided, no action-level source is needed.

Substitution tokens (${token}, ${secret:scope/key}) resolve in both inline SQL and the sql_path file. A plain ${name} that is still in the sql_path file after substitution fails lhp validate and lhp generate with LHP-CFG-010. Dotted or prefixed Spark variables such as ${mypipeline.start} or ${hiveconf:x} are left as written. To read a pipeline configuration: parameter at runtime, give it a dotted key (for example mypipeline.start_date) and reference it as ${mypipeline.start_date}; a plain ${start_date} fails even when the configuration defines it.

In a template action, a sql_path file that contains {{, {% or {# is rendered with the template parameters before substitution, like the template’s inline SQL. See Parameters in SQL files.

Expectations

A materialized view takes data-quality expectations in two ways. Both put the decorators on the view itself, which is what lets Databricks keep refreshing the view incrementally. Expectations on a view upstream of a materialized view force it to recompute in full on every refresh.

Shape 1: ``write_target.expectations_file``. Name an expectations file on the write target. This works for every way of defining the view (sql, sql_path or a source view). The file uses the same formats as a data_quality transform (Expectation formats).

- name: write_mv_expect_sql_gate
  type: write
  write_target:
    type: materialized_view
    catalog: "${catalog}"
    schema: "${gold_schema}"
    table: "mv_expect_sql_gate"
    sql_path: "sql/gold/mv_expect_sql_gate.sql"
    expectations_file: "expectations/mv_write_expectations.json"

Shape 2: a fused ``data_quality`` action. When a data_quality action feeds a pass-through materialized view, code generation moves its expectations onto the view. The data_quality action is written as usual:

- name: check_mv_expect_orders
  type: transform
  transform_type: data_quality
  source: v_mv_expect_orders
  target: v_mv_expect_orders_checked
  expectations_file: "expectations/mv_write_expectations_dq.json"

- name: write_mv_expect_fused
  type: write
  source: v_mv_expect_orders_checked
  write_target:
    type: materialized_view
    catalog: "${catalog}"
    schema: "${gold_schema}"
    table: "mv_expect_fused"

The materialized view then carries the action’s expectations and its operational-metadata columns, and reads the action’s source view (v_mv_expect_orders). The data_quality view is still emitted, but nothing reads it, so it never runs. The data_quality action fuses when all of these hold:

• it is in dqe mode (quarantine mode never fuses);

• it reads in batch. An unset readMode inherits its producer’s kind (see Row expectations (data_quality));

• exactly one action in the flowgroup reads its view;

• that action is a materialized_view write whose source is the data_quality view alone (a string, or a list with that one view), with no sql or sql_path and no expectations_file of its own, and that is the only write to its table.

A test action that reads the data_quality view does not count as a reader, so the same flowgroup fuses with and without --include-tests. The test keeps working, because the data_quality view is still emitted.

The two shapes do not combine on one view. A materialized view that has its own expectations_file is not a fusion target: it keeps reading the data_quality view, and its rules then see only the rows that survive the data_quality action’s drop rules. To keep such a view incremental, put all the rules in the view’s own expectations_file.

Expectation names must be unique on the view. A repeat inside the view’s expectations_file fails with LHP-VAL-071, and so does a repeat inside the rules of a fused data_quality action.

Note

A materialized view whose own sql or sql_path reads a data_quality view is not fused. It works, but Databricks recomputes it in full on every refresh. To keep it incremental, put the rules on the view with Shape 1, or make the view a pass-through (source only).

Note

Incremental refresh needs a deterministic query. A non-deterministic expression in the view’s chain makes Databricks recompute it in full whatever the expectations. That includes operational-metadata columns such as F.current_timestamp(), which fusion copies onto the view (Databricks allows non-deterministic functions only in WHERE clauses).

Setting expectations_file on the action rather than in write_target raises LHP-CFG-073. Setting it in the write_target of a streaming table or sink raises LHP-CFG-001.

Shared table and view fields

These write_target fields apply to streaming_table and materialized_view. Sinks use their own field set.

Key	Type	Default	Notes
type	string	required	streaming_table, materialized_view, or sink.
catalog	string	—	UC catalog.
schema	string	—	UC namespace schema (not DDL — use table_schema for DDL).
table	string	—	Target table/view name.
create_table	bool	true	Streaming table only; snapshot_cdc and replace force true, and replace_where rejects false.
temporary	bool	false	Emitted as temporary=. temporary: true is rejected by replace_where.
private	bool	false	Emitted as private=. Creates the table for the pipeline’s lifetime without publishing it to the metastore; the dataset is visible only inside the pipeline.
comment	string	derived	Defaults to a description derived from the target table name.
table_properties	dict	—	Delta table properties.
tags	dict	—	UC tags {key: value}; applied by a generated tagging hook (REST API), not table DDL. See Unity Catalog tagging.
tags_file	string	—	Path to a unified schema/tags file (convention schemas/<table>.yaml) whose table-level tags and per-column tags supply UC tags. May be the same file as table_schema. Mutually exclusive with an inline tags mapping. See Schema and tag files.
partition_columns	list	—	Emitted as partition_cols=. Mutually exclusive with cluster_columns and cluster_by_auto: Databricks does not allow partitioning together with liquid clustering, so lhp validate and lhp generate reject the combination, even on an action with create_table: false. An empty list counts as unset.
cluster_columns	list	—	Liquid clustering; emitted as cluster_by=. With cluster_by_auto, these become the initial clustering keys. Mutually exclusive with partition_columns.
cluster_by_auto	bool	—	Auto liquid clustering. Can be combined with cluster_columns, which become the initial clustering keys; Databricks may later change the keys based on the workload. true is mutually exclusive with partition_columns.
spark_conf	dict	—	Spark configuration.
table_schema	string	—	Inline DDL, or a .ddl/.sql/.yaml/.json file path (auto-detected). A .yaml/.json file is the unified schema/tags file; its per-column type/nullable/comment are read here. Any UC tags it carries are ignored unless the same file is also set as tags_file — otherwise lhp generate warns LHP-CFG-069.
row_filter	string	—	Row-filter clause, emitted as row_filter=.
path	string	—	Storage location, emitted as path=.
database	string	—	Deprecated (removed at 1.0.0); use catalog + schema.

Added in version 0.9.2: The private field, for SDP private datasets that persist for the pipeline’s lifetime without being published to the metastore.

Action fields

These fields sit on the action, alongside write_target.

Field	Type	Default	Requirement and behaviour
name	string	required	Action name, unique within the flowgroup.
type	string	required	Always write.
source	string / list	conditional	Input view(s). Standard streaming writes and most sinks accept a list. CDC, replace, replace_where and foreachbatch use one view. Snapshot CDC takes its source inside snapshot_cdc_config. Materialized views require one of action source, target sql or target sql_path.
write_target	mapping	required	Target settings; type selects streaming_table, materialized_view or sink.
description	string	derived	Comment/docstring for the generated flow or view.
readMode	string	stream	stream or batch for standard streaming-table append flows. Replace requires streaming; replace_where reads in batch and rejects stream. Materialized views use batch queries; sinks use streaming reads. The read must match the kind of the view it reads, or lhp validate fails with LHP-VAL-070; a batch read in a standard streaming-table flow also needs once: true.
once	bool	false	Emits once=True for standard append and CDC flows. Not emitted for snapshot CDC, replace, replace_where, materialized views or sinks.
operational_metadata	bool / list	—	Accepted on the model; writes do not add metadata columns. Select columns on load or supported transform actions instead.
depends_on	list[string]	—	Extra upstream table/view references. See Dependency analysis reference.

Related guides

• Write a materialized view

• All write actions