Materialized Views

Applies To:

Pipeline Bundle

Configuration Scope:

Data Flow Spec

Databricks Docs:

Overview

Materialized Views are the pre-computed results of a query stored in a Table. Please refer to the above documentation for full details on Materialized Views and how they work.

Key Features:

  • Automatic updates based on pipeline schedule/triggers

  • Guaranteed consistency with source data. All required data is processed, even if it arrives late or out of order.

  • Incremental refresh optimization. Databricks will try to choose the appropriate strategy that minimizes the cost of updating a materialized view.

  • Ideal for transformations and aggregations

  • Pre-computation of slow queries

  • Optimization for frequently used computations

Important

To support Incremental refresh, some keywords and clauses require row-tracking to be enabled on the queried data sources. Refer to the the following links for details on:

Sample Bundle

A sample is available in:

  • the feature-samples bundle in the src/dataflows/feature_samples/dataflowspec folder in the materialized_views_main.json file

  • the pattern-samples bundle in the src/dataflows/base_gold/dataflowspec folder in the gold_materialized_views_main.json file

Data Flow Spec Configuration

Materialized Views are must be configured in the Materialized Views Data Flow Spec Type. This Data Flow Specification is defined in the Creating a Materialized View Data Flow Spec Reference documentation.

Data Flow Spec Configuration Schema

The following schema details the configuration for a Materialized View Data Flow Spec:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
{
    "dataFlowId": "feature_materialized_views",
    "dataFlowGroup": "feature_samples",
    "dataFlowType": "materialized_view",
    "materializedViews": {
        "mv_name": {
            "sourceView": {
                "sourceViewName": "",
                "sourceType": "[delta|python|sql]",
                "sourceDetails": {}
            },
            "sqlPath": "",
            "sqlStatement": "",
            "tableDetails": {
                "database": "",
                "schemaPath": "",
                "tableProperties": {},
                "path": "",
                "partitionColumns": [],
                "clusterByColumns": []
            },
            "dataQualityExpectationsEnabled": false,
            "dataQualityExpectationsPath": "",
            "quarantineMode": "off",
            "quarantineTargetDetails": {},
            "refreshPolicy": "auto"
        }
    }
}

Source Type Details

Materialized Views can be configured in your Data Flow Spec in three ways:

  1. Using a Source View - Define a source view that the materialized view will be based on - Supports Delta, Python, and SQL source types - Example configuration:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    {
    "sourceView": {
        "sourceViewName": "v_customer",
        "sourceType": "delta",
        "sourceDetails": {
            "database": "{staging_schema}",
            "table": "customer",
            "cdfEnabled": true
        }
    }
}

  2. Using SQL Path - Reference a SQL file containing the query for the materialized view - Example configuration:

    1
2
3
    {
    "sqlPath": "./customer_mv.sql"
}

  3. Using SQL Statement - Directly specify the SQL query for the materialized view - Example configuration:

    1
2
3
    {
    "sqlStatement": "SELECT * FROM {staging_schema}.customer"
}

Additional Configuration Options

Materialized Views support several additional configuration options:

  • Table Details: Configure the target table properties - Database - Schema path - Table properties - Path - Partition columns - Cluster by columns

  • Data Quality Expectations - Enable data quality checks - Specify expectations path - Configure quarantine mode (off, flag, table)

  • Quarantine Configuration - Set quarantine mode - Configure quarantine target details

  • Refresh Policy (Beta — requires DBR 17.3+) - Control how the MV is refreshed: auto, incremental, incremental_strict, or full - When set, the framework uses @dp.materialized_view() with the refresh_policy parameter - incremental_strict disallows non-deterministic functions (e.g. current_timestamp()); use disableOperationalMetadata in configFlags when needed

Example Configuration

A complete example of a materialized view configuration:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
{
    "dataFlowId": "feature_materialized_views",
    "dataFlowGroup": "feature_samples",
    "dataFlowType": "materialized_view",
    "materializedViews": {
        "mv_from_source_view": {
            "sourceView": {
                "sourceViewName": "v_mv_source_view",
                "sourceType": "delta",
                "sourceDetails": {
                    "database": "{staging_schema}",
                    "table": "customer",
                    "cdfEnabled": true
                }
            }
        },
        "mv_from_sql_path": {
            "sqlPath": "./mv_from_sql_path.sql"
        },
        "mv_from_sql_statement": {
            "sqlStatement": "SELECT * FROM {staging_schema}.customer"
        },
        "mv_with_quarantine": {
            "sqlStatement": "SELECT * FROM {staging_schema}.customer_address",
            "dataQualityExpectationsEnabled": true,
            "dataQualityExpectationsPath": "./customer_address_dqe.json",
            "quarantineMode": "table",
            "quarantineTargetDetails": {
                "targetFormat": "delta"
            }
        },
        "mv_with_refresh_policy": {
            "sqlStatement": "SELECT * FROM {staging_schema}.customer",
            "refreshPolicy": "incremental_strict",
            "tableDetails": {
                "configFlags": ["disableOperationalMetadata"]
            }
        }
    }
}

For more detailed information about configuration options, refer to the Data Flow Spec Reference documentation.