1.8.0b1: Expanded Materialized View and Streaming Table support

[benc-db]

Today I'll be releasing 1.8.0b1 to get feedback on expanded support for materialized views and streaming tables. This post includes my preliminary docs, which I'll gather feedback on before publishing to the dbt doc site when we release 1.8.0 (most likely in April).

This update brings two heavily-requested features:

1. The set of configuration available for materialized views and streaming tables has expanded, and now includes the ability to specify a refresh schedule, as well as other features to bring these new materializations closer to parity with tables and views.
2. We now support on_configuration_change for materialized_view and streaming_table materializations.

Configuration Options

Support summarized as follows

Databricks Concept	Config Name	MV/ST support
PARTITIONED BY	partition_by	MV/ST
COMMENT	description	MV/ST
TBLPROPERTIES	tblproperties	MV/ST
SCHEDULE CRON	schedule: { 'cron': '<cron schedule>', 'time_zone_value': '<time zone value>' }	MV/ST
query	defined by your model sql	on_configuration_change for MV only

-- MV example

{{ config(
    materialized='materialized_view',
    description='this is a materialized view',
    partition_by='id',
    schedule = {
        'cron': '0 0 * * * ? *',
        'time_zone': 'Etc/UTC'
    },
    tblproperties={
        'key': 'value'
    },
) }}
select * from {{ ref('my_seed') }}

Config Details

partition_by

Works the same as for views and tables, i.e. can be a single column, or an array.

description

As with views and tables, adding a description to your schema.yml file will lead to a comment added to your MV or ST.

tblproperties

Works as with views and tables with an important exception: we maintain a list of keys that are set by Databricks when making an MV or ST that are ignored for the purpose of determining configuration changes. I know this approach is fragile and is likely to change in the future. Take a look at tblproperties.py for the current ignore list.

schedule

Use this to set the refresh schedule for the model. If you use the schedule key, a cron key is required in the associated dictionary, but time_zone_value is optional (see the example above). The cron value should be formatted as documented by Databricks. If an MV/ST has a schedule set, and your dbt project does not specify a schedule for it, the refresh schedule will be set to manual when you next run the project. Even if a schedule is set, dbt will ask for the materialization to refresh manually when run.

query

For materialized views, if the compiled query for the model differs from the query in the database, we will the take the configured on_configuration_change action. Changes to query are not currently detectable for streaming tables; see the next section for details.

What's left?

Currently we do not support specifying column level documentation / types for either materialization. Hopefully we can bring this support later this year.

on_configuration_change

on_configuration_change is supported for materialized views and streaming tables, though the two materializations handle it different ways.

Materialized Views

Currently, the only change that can be applied without recreating the materialized view in Databricks is to update the schedule. Some config is unlikely to ever be capable of changing without recreating the view, e.g. partitioned by; we can expand support to include updating the description and tblproperties without recreating the view if/when the SQL API for altering those becomes available.

Streaming Tables

For streaming tables, only changes to the partitioning currently requires a drop and create. For any other config changes, we will use create or refresh (+ an alter statement for changes to the schedule) to make the changes. There is currently no mechanism for the dbt adapter to detect if the streaming table query has changed, so in this case, regardless of the behavior requested by on_configuration_change, we will use a create or refresh statement (assuming partitioned by hasn't changed), which will cause the query applied to future rows to change, without rerunning on any previously processed rows. If your source data is still available, running with '--full-refresh' should reprocess the available data with the current query.

[thijs-nijhuis]

@benc-db , thanks for releasing this beta. We are experimenting with integrating MV/ST into our DBT project at the moment so the timing is perfect for us. I installed 1.8.0b1 and found a couple of things I wanted to share with you:

• When I add an MV to my models, it is created properly. But when I execute dbt run --select model_name again, I get an error "Unhandled error while executing" right after the "1 of 2 Start sql materialzied view ..." output . Nothing useful in the logs. It seems to happen during compilation as I don't get anything in my target\run folder if it fails. When I install 1.7.9 the command succeeds and starts to refresh the MV.
• If I understand your statement above correctly, some tableproperties should be filtered out when creating an MV. But that doesn't seem to happen. I have the code below in my dbt_project.yml and all of these properties are added to the materialized view when using 1.8.0b1. When i remove the code they are not set of course and when I use 1.7.9 they are ignored as well.

    +tblproperties:
          'delta.minReaderVersion': 2
          'delta.minWriterVersion': 5
          'delta.columnMapping.mode': 'name'
          'delta.enableChangeDataFeed': true

• Related to the issue above, I think you are right that you don't need to be able to set the enableChangeDataFeed property on an MV either. I once noticed a difference if you have an MV that reads from another, "source" MV. If that "source" MV would not have CDF enables, all refresh of the other MV where 'COMPLETE_RECOMPUTE's. That why I created this issue: Creating materialized views do not set table properties #577 . Just tested it again but I see no difference in refresh behavior anymore if the "source" does not have CDF enabled. So either I was wrong earlier or something changed. Will close that issue.

[benc-db]

When I add an MV to my models, it is created properly. But when I execute dbt run --select model_name again, I get an error "Unhandled error while executing" right after the "1 of 2 Start sql materialzied view ..." output . Nothing useful in the logs. It seems to happen during compilation as I don't get anything in my target\run folder if it fails. When I install 1.7.9 the command succeeds and starts to refresh the MV.

This is disconcerting. What are the relevant lines in your dbt.log? We have a test expressly for this, so I'm a little surprised.

If I understand your statement above correctly, some tableproperties should be filtered out when creating an MV. But that doesn't seem to happen. I have the code below in my dbt_project.yml and all of these properties are added to the materialized view when using 1.8.0b1. When i remove the code they are not set of course and when I use 1.7.9 they are ignored as well.

Those properties are added by Databricks, so even if you have them in your project, you will see them show up in your MV. The intent is that if you change those in your project, that we ignore them rather than recreate your MV on the second run, since from the perspective of the adapter, the outcome is unknowable if you change those values (i.e. it might break the pipeline in obscure ways).

[thijs-nijhuis]

Here is my terminal output and the logs from that same run (slightly redacted).
logs.txt
terminal_output.txt

I think the issue is on line 109. If I run "select * from my_catalog_name.INFORMATION_SCHEMA.views" directly on our catalog I don't get any rows returned. When I run "select * from system.INFORMATION_SCHEMA.views " and than filter on the 'table_catalog' column with the name of our catalog I do get all the views in the catalog including the materialized view. I have run into this issue before in a different context. I think in "older" Databricks documentation I saw the use this syntax of querying the information_schema schema within your catalog as well but they now all state you should refer to the "system" catalog it seems.

I tested it with both serverless and SQL Classic.

[benc-db]

If I run "select * from my_catalog_name.INFORMATION_SCHEMA.views" directly on our catalog I don't get any rows returned

Is this consistent? This sounds like a bug in Databricks.

[benc-db]

Can you try the further_mv_st branch and see if that fixes the problem?

[thijs-nijhuis]

I am new to installing from a branch so let me first explain what I did so you can spot any mistakes i might have made. Tot test, I:

• uninstalled my dbt-databricks
• installed with this command pip install git+https://github.com/databricks/dbt-databricks.git@further_mv_st

When I run the MV model it gets refreshed properly. In the logs I also see you're selecting from the system catalog now.
After that I dropped the MV, and ran the model again. The MV is created and I see the table properties I have set on a project level so I know I am not running with 1.7.9 or earlier. If I run it again it gets refreshed as well

If I execute pip uninstall dbt-databricks and then pip install dbt-databricks==1.8.0b1, the model run fails again. So I think this branch fixed the issue in our setup.

[thijs-nijhuis]

If I run "select * from my_catalog_name.INFORMATION_SCHEMA.views" directly on our catalog I don't get any rows returned

Is this consistent? This sounds like a bug in Databricks.

I know it is consistent in our workspace and catalog. Like I said, I am 99% sure that at some point the select * from catalog_name.information_schema.views syntax was the suggested way in the databricks documentation because we ran into this issue (not getting any rows returned) about 2 months ago. But when I look at the docs now, I only see the select * from system.information_schema.views way. So my guess is that something has changed.

[benc-db]

The information I gathered internally says that this can happen if the catalog has been renamed at some point, because the catalog information schema views are hardcoded on creation. In general, both should work, so I'm hoping they fix that.

I am new to installing from a branch so let me first explain what I did so you can spot any mistakes i might have made. Tot test, I:

Perfect!

[thijs-nijhuis]

Those properties are added by Databricks, so even if you have them in your project, you will see them show up in your MV. The intent is that if you change those in your project, that we ignore them rather than recreate your MV on the second run, since from the perspective of the adapter, the outcome is unknowable if you change those values (i.e. it might break the pipeline in obscure ways).

O, ok. So the filter list is meant for change detection, not as a filter upon MV creation. In that case it works as expected.