[ADAP-383] [Feature] Support Dynamic Data Masking in CTAS Statements

[jdoldis]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-snowflake functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Currently you cannot specify column masking policies in Snowflake CTAS statements with dbt-snowflake. For example, CREATE TABLE <table_name>(<col_name> <col_type> WITH MASKING POLICY <policy_name>) AS SELECT <query>.

As a workaround masking policies can be applied to columns in a dbt post hook using an ALTER TABLE statement. The issue with doing this is that the CTAS and ALTER TABLE statements cannot be issued in the same transaction, as per the Snowflake documentation - "Each DDL statement executes as a separate transaction". As a result there is there is a small window of time between the CTAS and ALTER TABLE <table_name> MODIFY COLUMN <column_name> SET MASKING POLICY <policy_name> statements where the data is not masked, and if the ALTER TABLE statement fails it would remain that way.

Supporting masking policy specification in the CTAS statement would fix this. As per the Snowflake documentation "Executing a CREATE TABLE … AS SELECT (CTAS) statement applies any masking policies on columns included in the statement before the data is populated in the new table".

It also may not be difficult to support this given the recent work on model contracts which provides the CREATE TABLE <table_name>(<col_name> <col_type>) AS SELECT <query> syntax. All that would need to be added is the WITH MASKING POLICY <policy_name> part of the statement.

One way to provide the config would be something like:

columns:
      - name: id
        description: "Primary key for this model"
        data_type: integer
        masking_policy: <policy_name>
        contraints: <constraint_list>
        ...

The masking policy could then be applied in get_columns_spec_ddl.

Describe alternatives you've considered

Using an ALTER TABLE statement in a post hook to apply the masking policy. As described above due to Snowflake DDL statements always being executed in separate transactions this leaves the possibility of unmasked data.

Who will this benefit?

Anyone that wants to take advantage of dynamic data masking in Snowflake using dbt.

Are you interested in contributing this feature?

Yes

Anything else?

No response

[jdoldis]

This has also been brought up in the slack community a few times, adding links for reference:

• https://getdbt.slack.com/archives/CBSQTAPLG/p1656532962877469
• https://getdbt.slack.com/archives/CBSQTAPLG/p1656532962877469
• https://getdbt.slack.com/archives/CBSQTAPLG/p1679392349799339

[Fleid]

@jtcohen6 I'm sorry if we already discussed this... but is that something on your radar for contracts?

[Fleid]

Thanks @jdoldis for the write-up, you make a great case for it :)

[jdoldis]

Thanks @Fleid , I'm writing a custom materialization to support the syntax as described above. Let me know if you'd like those changes contributed here. Otherwise, I look forward to seeing it at some point down the road 🙂

[ingolevin]

I have the exact same requirement, but for row access policies ! I think this can be implemented in one go, as it share the same CTAS syntax

CTAS:

CREATE TABLE <table_name> ( <col1> <data_type> [ with ] masking policy <policy_name> [ , ... ] )
  ...
  [ WITH ] ROW ACCESS POLICY <policy_name> ON ( <col1> [ , ... ] )

Config with masking and row access policies could like this:

columns:
      - name: id
        description: "Primary key for this model"
        data_type: integer
        masking_policy: <policy_name>
        row_access_policy: <policy_name>
        contraints: <constraint_list>
        ...

Currently the only option is via post_hook ALTER table|view ADD ROW ACCESS POLICY ...

Since one has to check the information_schema first if that relation (view or table) already has said RAP applied, because otherwise the ALTER command fails, this whole process can take up to 10 seconds [Edit: that was mainly due to a cluster_by, but even in the best case there is a 1sec delay] in which the data in the relation is unprotected.

[ingolevin]

Thanks @Fleid , I'm writing a custom materialization to support the syntax as described above. Let me know if you'd like those changes contributed here. Otherwise, I look forward to seeing it at some point down the road slightly_smiling_face

@jdoldis Would you release your custom materialization as a package, until this gets implemented into dbt-snowflake? I'd be very interested as well!

[jdoldis]

Hey @ingolevin, the materialisation I wrote is essentially a copy paste of the standard v1.5 table materialisation. The difference is I have modified the table_columns_and_constraints macro to support masking policies. In this modified macro I build the ddl by looping through the model columns and outputting the name/datatype, and then adding WITH MASKING POLICY <policy_name> if a masking policy is defined for the column.

[jdoldis]

Since I raised this it seems the ddl logic has moved around a bit, the relevant code that could be modified in the Snowflake adapter to support masking policies would now be this function I think.

[jdoldis]

Regarding creating a package, it would be good to hear back from @Fleid first. Ideally we could implement here, but if that's not possible I would be open to it 🙂