Path based access not supported for tables with row filters?

[Nes_Hdr]

Hello,  

I have encountered an issue recently and was not able to find a solution yet. 

I have a job on databricks that creates a table using dbt (dbt-databricks>=1.0.0,<2.0.0). I am setting the location_root configuration so that this table is external and not managed. (further dbt config: +materialized: table)

Now I want to apply a row filter on this table, I created the function, I run the 

ALTER TABLE table_name SET ROW FILTER column ON (col); command. The row filter is successful and works as predicted. 

However, now that I have applied this row filter on my table, I get the following error message when I run my job again (see picture) : 

Database Error in model ... (src/dbt_layer/models/......sql) [RequestId=fb4bd9..... ErrorClass=INVALID_PARAMETER_VALUE.PATH_BASED_ACCESS_NOT_SUPPORTED_FOR_TABLES_WITH_ROW_COLUMN_ACCESS_POLICIES] Path-based access to table .... with row filter or column mask not supported.

Maybe also good to know:

My row filter function contains the case: 

WHEN IS_ACCOUNT_GROUP_MEMBER("admin_group_name") THEN TRUE -- return everything if admin

And my user has admin privileges (meaning no row restrictions apply to my user.) 

I know that one solution to avoid this is to switch from external table to managed, but I do not want to do that. 

I am looking for an explanation why this happens especially no error was thrown when I set the row filter to the table, but only when dbt tries to "recreate" the table.

And I would appreciate receiving feedback / alternative solutions to this issue! 

Thanks so much for the help! 

[Nes_Hdr]

To recreate the issue:

PS. Good to know: using dbt to create materialized tables is equivalent to running "create or replace table table_name"

• The following code will create an external table with row security:

create or replace table table_name using delta location 'path/to/table' with row filter func_name on (column)

• Rerunning the previous code will generate the error described above.
• Since  the table is now created, if you run the following code, no error will occur (removing the location): 

create or replace table table_name using delta with row filter func_name on (column)​

But the table storage location and table type (external) will remain the same.... 

This still doesn't make sense to me, but I found a work around for this: 

Since I am using dbt, I simply added a project variable row_filter, defaulting to false. 

I set this variable to true, in the Databricks asset bundle task : dbt run ..... --vars '{row_filter: true, ...}' this way the variable can be set for each table seperately

Then I add the following jinja code at the beginning of my dbt models/xxx.sql model:

{% if var('row_filter', false) | string | lower == 'false' %}
{{ config(
location_root=set_location_root(parameter1, parameter2...) 
) }}
{% endif %}

* set_location_root() is a self developed macro

The work around basically sets the location root if there is no row filtering happening, otherwise it will skip it. 

This will not impact the tables as they were already created within the right location previously to applying the row filter. 

I would still appreciate receiving feedback and a better solution to this issue 🙂 

Thanks!