Row Level Security while streaming data with Materialized views

[Splush]

Hey,

I have the following problem when trying to add row level security to some of our Materialized views. According to the documentation this feature is still in preview - nevertheless, I try to understand why this doesnt work and how it would be supposed to to work once the feature is live.

So there is a lot of differences when comparing the SQL statements in Notebooks, SQL Editor and DLT. If I run the following query in notebooks it will fail:

CREATE materialized view test_rowfilter
WITH ROW FILTER gold.cost_center_filter on (CostCenter)
AS
select
    *
from bronze.some_table

The same query will work for some random reason in SQL editor though.

If I change the first line to 

CREATE OR REFRESH MATERIALIZED VIEW 

the SQL Editor will fail, but the query will work in DLT assuming I removed the Row Filter clause. According to the docs, everything should just work but it doesnt.

You can try and reproduce this by just playing around with the "OR REFRESH" and "WITH ROW FILTER" statements. You will get a large variety of different error messages depending on the location of where you test it. I wonder why such a feature is even in preview state.

Am I misunderstanding something here maybe? 

Our goal would be to have the Query as CREATE OR REFRESH and WITH ROW FILTER running inside of a DLT pipeline.

[raphaelblg · Accepted solution]

Hello @Splush, 

There are 2 ways to create materialized views at the current moment:

1. Through Databricks SQL: Use Materialized Views in Databricks SQL. These are the limitations.

2. Through DLT: Materialized View (DLT). All DLT tables are subject to these limitations.

The reason why the statements fail on interactive or job clusters is because materialized view declaration is only supported on DBSQL or DLT, there's no random reason. 

Now regarding RLS (Row Level Security), these are the docs: Filter sensitive table data using row filters and column masks. And these are the Limitations.

Regarding your question:

Am I misunderstanding something here maybe? 

Our goal would be to have the Query as CREATE OR REFRESH and WITH ROW FILTER running inside of a DLT pipeline.

At the current stage of the preview (2024-07-24), one of the limitations is: "Materialized views and streaming tables declared in Delta Live Tables don’t support row filters or column masks.". I believe this statement should answer your question.

Databricks is working on improving the DLT compatibility with overall UC features and you can expect to see this limitation being removed on future DLT releases.