Materialized View
- Latest Version:
- 1.0.0
- Supported Platform:
- Release Date:
- June 06, 2025
Overview
In Databricks, Materialized views cache the results and update them as the underlying source tables change—either on a schedule or automatically.
Installation
- In Coalesce, open the Workspace where you wish to install the package.
- Go to the Build Setting of the Workspace, tab Packages, and click the Install button on the top right of the page.
- Paste the Package ID, and proceed with the installation process.
Description
Databricks Materialized View
The Databricks Materialized View UDN is a versatile node that allows you to develop and deploy a Materialized View in Databricks.
A materialized view is a view where precomputed results are available for query and can be updated to reflect changes in the input. Each time a materialized view is refreshed, query results are recalculated to reflect changes in upstream datasets. All materialized views are backed by a DLT pipeline. You can refresh materialized views manually or on a schedule.
Node Configuration
The Databricks Materialized View Stage has Four configuration groups:
Node Properties
There are four configs within the Node Properties group.
| Property | Description |
|---|---|
| Storage Location | Storage Location where the Materialized View will be created |
| Node Type | Name of template used to create node objects |
| Deploy Enabled | If TRUE the node will be deployed / redeployed when changes are detected If FALSE the node will not be deployed or will be dropped during redeployment |
Key point
- Materialized View automatically infers the structure of the source table and defines the data type of columns.Hence we create materialized view without column definition in Infer stage(keeping infer toggle enabled)
- Re-Sync the columns and the mapping grid will be updated with the expected column structure
- Keep the infer toggle off,create the Materializsed View with the intended column or table-level constraints
General Options
| Option | Description |
|---|---|
| Infer MV structure | True / False toggle - True: Materialized View Options will be disabled - False: Materialized View Options will be visible |
| Schedule refresh | True / False toggle - True: Schedule Option will be visible - False: Schedule Option will be disabled |
Materialized View Options
Materialized View Options is available only when Infer MV Structure toggle is False.
| Option | Description |
|---|---|
| Table properties | |
| Partition by | True / False Toggle - True: Enables the Column Dropdown and Textbox to add columns for partitioning - False: Disables the Dropdown and Textbox to add columns |
| Table Constraints | True / False Toggle - True: Primary key and Foreign Key toggle will visible - False : Primary key and Foreign Key toggle will Disable |
| Primary key | Visible when Table Constraints is True options for Primary Key - Primary key Name : Primary Key name is added in short - Primary key columns : Column Dropdown and Textbox to add columns box appear to select primary key |
| Foreign Key | Visible when Table Constraints is True options for Foreign Key - Foreign key attributes : Text box appear where we can add Foreign Key Name,Parent table name,Foreign key columns |
| Other constraints | True / False Toggle Constraints : Text box appear where we can add constraints on selected column Options -ColumnName : From column dropdown we can add columns -Expected Expression : can give specific Expression -ON VIOLATION : Two options can be selected from dropdown - Fail Update, Drop Row |
Schedule Options
Schedule Options is available only when Schedule Refresh toggle is True
| Option | Description |
|---|---|
| Task Schedule | Options in Task Schedule - Periodic Schedule - CRON |
| Schedule refesh-time period | Available when Task Schedule is Set to Periodic Schedule Options in Schedule refesh-time period -Every Hours -Every Days -Every Weeks |
| Specific interval of periodic refresh(integer value) | Available when Task Schedule is Set to Periodic Schedule |
| CRON string | Available when Task Schedule is Set to CRON |
| CRON TIME ZONE | Available when Task Schedule is Set to CRON |
Deployment
Initial Deployment
When deployed for the first time into an environment Materialized View will execute three stages:
| Stage | Description |
|---|---|
| Create Materialized View | This stage will execute a CREATE OR REPLACE statement and create a Materialized View in the target environment |
Redeployment
After the Materialized View has deployed for the first time into a target environment, subsequent deployments may result in either altering the Materialized View or recreating the Materialized View.
If a Materialized View is to be altered this will run the following stage:
Recreating the Materialized View
If anything changes other than the configuration options specified above then the Materialized View will be recreated by running a CREATE OR REPLACE statement.
Undeployment
If a Materialized View is deleted from a Workspace, that Workspace is committed to Git and that commit deployed to a higher-level environment then the Materialized View in the target environment will be dropped.
This is executed as a single stage:
| Stage | Description |
|---|---|
| Drop Materialized View | Removes the materialized view |
Code
Versions
Available versions of the package.
| Version # | Release Date | Notes |
|---|---|---|
| 1.0.0 | June 06, 2025 | |