Snowflake connections

DataMasque masks Snowflake tables in place. It reads rows from the table, masks them in memory, and loads the masked rows back into the table through a temporary internal stage that Snowflake manages. Masked data is not written to any storage outside Snowflake.

Masking process
- How masked data is written
- Changes to staging in 3.26.18
Required privileges
Connection parameters
- Snowflake specific parameters
- Snowflake Advanced Settings
Encrypted connections
Limitations
- Snowflake Time Travel Security
Supported data types
Unsupported key columns

Masking process

The minimum specs for masking Snowflake databases are 8 vCPUs and at least 16GiB RAM.

Snowflake databases are masked by querying the data from the table, applying the masking transformations in memory, and then loading the masked data back into the table through a temporary internal stage.

The following diagram shows the high level data masking workflow for Snowflake:

DataMasque Snowflake masking

How masked data is written

Each worker that masks a table:

Creates a temporary internal stage and a temporary table in the target schema, with Snowflake server-side encryption (CREATE TEMPORARY STAGE ... ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')).
For each batch of masked rows, serialises the rows to gzipped NDJSON in memory and uploads them to the stage with PUT, over HTTPS. No file is written to disk.
When every batch it was assigned is staged, loads the staged rows into the temporary table with one COPY INTO, and applies the masked values to the target table with one MERGE on the primary key, in one transaction.

If any step fails, that worker's transaction is rolled back and its rows are left unchanged. With more than one worker on a table, rows that other workers had already committed stay masked, so a failed run can leave the table partly masked. DataMasque drops the temporary stage and temporary table when the worker has merged its rows, and when a run fails or is cancelled. Snowflake removes them when the session ends.

Snowflake warehouses can be set to automatically suspend after a specified period of time. When the next queries are run the warehouse will resume. This usually only takes 1–2 seconds. In rare cases, this may take longer when Snowflake is provisioning the resources.

DataMasque's timeout for testing a connection is 10 seconds.
During a masking run the connection timeout is 120 seconds.
By default auto-suspend, and auto-resume are enabled and the inactivity timeout is 10 minutes.
The values for auto-suspend and auto-resume can be set when creating warehouses or updated later using the ALTER WAREHOUSE statement.
If you're having trouble with timeouts, you could "pre-warm" the warehouse by testing the connection, or executing a simple query.

For more information, please refer to the official documentation:
Working with Warehouses
Warehouse considerations

Note: DataMasque supports masking Snowflake databases regardless of the selected Snowflake host cloud platform (AWS, Azure, or Google Cloud Platform). The internal stage is created in the same Snowflake account as the target table, so no cross-cloud data transfer takes place.

Changes to staging in 3.26.18

From DataMasque 3.26.18, Snowflake masking stages data only through the temporary internal stage. An S3 bucket, Azure Blob Storage container or storage integration is no longer required. Connections created in an earlier version may still hold these values, or an IAM Role ARN. DataMasque ignores them. The storage integration, bucket or container can be removed once nothing else depends on them.

The DataMasque Snowflake Marketplace App is unaffected: it has always used an internal stage.

Required privileges

DataMasque masks Snowflake tables with a role that you grant to the connection user. The role needs a small base set of privileges for every run. Some masks and table shapes make DataMasque run DDL on the target tables. Those need extra privileges, listed under Conditional privileges.

Base privileges

Every masking run needs:

USAGE on the warehouse, the target database and the target schema(s).
SELECT and UPDATE on each target table. DataMasque reads rows with SELECT and applies masked rows with a MERGE that only updates matched rows. It never inserts or deletes rows in the target table.

No CREATE privilege is needed for the write path. The internal stage and the load table are TEMPORARY objects. Snowflake does not require CREATE STAGE or CREATE TABLE on the schema to create temporary objects.

CREATE ROLE <masking-role>;

GRANT USAGE ON WAREHOUSE <warehouse> TO ROLE <masking-role>;
GRANT USAGE ON DATABASE <target-database> TO ROLE <masking-role>;
GRANT USAGE ON SCHEMA <target-database>.<target-schema> TO ROLE <masking-role>;

GRANT SELECT, UPDATE ON ALL TABLES IN SCHEMA <target-database>.<target-schema> TO ROLE <masking-role>;
-- Or, per table:
GRANT SELECT, UPDATE ON TABLE <target-database>.<target-schema>.<target-table> TO ROLE <masking-role>;

GRANT ROLE <masking-role> TO USER <connection-user>;

Conditional privileges

Grant these only when the ruleset or the table shape needs them.

When	What DataMasque runs	Privilege
A ruleset uses `secure_shuffle` or `from_blob`	Creates and drops a permanent helper table in the target schema	`CREATE TABLE` on the target schema
A ruleset has a `build_temp_table` task	Creates a table in the target schema	`CREATE TABLE` on the target schema
A ruleset has a `truncate_table` task	`TRUNCATE TABLE`	`TRUNCATE` on the table
A unique-preserving mask (for example `hash_columns` or `from_unique`) covers a primary key or unique column, or a foreign key points at a masked column	Drops and re-adds the `PRIMARY KEY`, `UNIQUE` and `FOREIGN KEY` constraints	`OWNERSHIP` of the target table and of every table whose foreign key references it
A unique-preserving mask covers the table's key column	Adds a temporary surrogate key column and a sequence, then drops them	`OWNERSHIP` of the table and `CREATE SEQUENCE` on the target schema
A masked column has a `DEFAULT <sequence>.NEXTVAL`	Re-creates the sequence above the highest masked value and re-points every column default that uses it	`OWNERSHIP` of the sequence, `CREATE SEQUENCE` on its schema, and `OWNERSHIP` of every table that has a column default on that sequence
The target is a hybrid table with a non-unique index on a masked column	Drops and re-creates the index	`OWNERSHIP` of the table

-- Only when needed; see the table above.
GRANT CREATE TABLE ON SCHEMA <target-database>.<target-schema> TO ROLE <masking-role>;
GRANT CREATE SEQUENCE ON SCHEMA <target-database>.<target-schema> TO ROLE <masking-role>;
GRANT TRUNCATE ON TABLE <target-database>.<target-schema>.<target-table> TO ROLE <masking-role>;
GRANT OWNERSHIP ON TABLE <target-database>.<target-schema>.<target-table> TO ROLE <masking-role> COPY CURRENT GRANTS;
GRANT OWNERSHIP ON SEQUENCE <target-database>.<target-schema>.<sequence> TO ROLE <masking-role> COPY CURRENT GRANTS;

Note: OWNERSHIP can be held by one role at a time. Granting it to the masking role removes it from the previous owner. Use COPY CURRENT GRANTS so other roles keep their existing privileges on the object. When DataMasque re-creates a sequence, the new sequence is owned by the masking role.

Connection test

The connection test authenticates, selects the database, schema (when set) and warehouse, and runs a query. It does not create any object. A masking run creates a temporary stage and a temporary table in the schema of each target table; the role needs USAGE on that schema and SELECT/UPDATE on the table.

Connection parameters

Snowflake specific parameters

For Snowflake databases, the following additional fields apply:


*Snowflake Account ID*	Snowflake account identifier. Does not include the `snowflakecomputing.com`. This is in the format `<organisation-name>-<account-name>`, e.g. `ABCDEFGH-XYZ12345`.
*Snowflake Warehouse*	Default Snowflake warehouse to use. Optional when DataMasque is deployed as a Snowflake Native App: if left blank, DataMasque uses its own warehouse created when the application was installed. Specify a warehouse to override this, for example to use a larger warehouse for large masking runs.
*Private Key*	Name of an available Snowflake Private key. These can be uploaded via the Files page.
*Private Key Passphrase*	Passphrase for the private key (if needed).

Snowflake Authentication

Snowflake has two types of authentication:

Keypair (recommended)
Password

To set up keypair authentication for Snowflake please refer to the documentation.

This is selected via radio buttons on the UI, shown in the screenshots below.

When the Password radio button is selected the Password field will be visible:

Snowflake Authentication Password

When Keypair is selected the Private Key dropdown box and Private Key Passphrase field will be visible:

Snowflake Authentication Keypair

The dropdown box will display all Private Key files available to be used. These can be uploaded via the Files page. If the selected key also requires a passphrase, enter it into the Private Key Passphrase field. The passphrase can be reset by clicking the trash icon or selecting a different Private Key. If the selected key does not require a passphrase, leave the field blank.

Snowflake Advanced Settings

For Snowflake databases, the following fields are optional:


*Schema*	The name of the schema that DataMasque will operate on. If not specified, the database user's default schema will be used.
*Role*	Name of the default role to use.

Encrypted connections

All DataMasque connections to Snowflake take place over HTTPS. This includes the PUT that uploads masked data to the internal stage.

Limitations

Only standard and hybrid tables are supported, not dynamic or Iceberg.
run_sql tasks can only execute one SQL statement per task. Tasks containing more than one SQL statement may cause an error.
mask_unique_key tasks are not supported.
DataMasque does not allow masking of tables with Time Travel enabled. See Snowflake Time Travel Security below for details and mitigation steps.

Snowflake Time Travel Security

Snowflake's Time Travel feature allows querying historical data for up to 90 days (Enterprise) or 1 day (Standard). When Time Travel is enabled, original unmasked data remains accessible via historical queries even after masking, creating a significant security risk.

Users can recover sensitive data using queries like:

SELECT * FROM table_name AT (TIMESTAMP => '2024-01-01'::timestamp)

DataMasque enforces Time Travel checking by default and will block masking of tables with Time Travel enabled. This behaviour can be downgraded to a warning with the allow_masking_tables_with_time_travel option.

How to Disable Time Travel

Set the DATA_RETENTION_TIME_IN_DAYS parameter to 0 for objects containing sensitive data:

For individual tables:

ALTER TABLE schema_name.table_name SET DATA_RETENTION_TIME_IN_DAYS = 0;

For entire schemas:

ALTER SCHEMA schema_name SET DATA_RETENTION_TIME_IN_DAYS = 0;

For entire databases:

ALTER DATABASE database_name SET DATA_RETENTION_TIME_IN_DAYS = 0;

Warning! The MIN_DATA_RETENTION_TIME_IN_DAYS account parameter can override attempts to disable Time Travel. If set at the account level by an ACCOUNTADMIN, it enforces a minimum retention period even when DATA_RETENTION_TIME_IN_DAYS = 0.

Check account-level settings:

SHOW PARAMETERS LIKE 'MIN_DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT;

If this returns a value > 0, contact your ACCOUNTADMIN to set MIN_DATA_RETENTION_TIME_IN_DAYS = 0 at account level.

Verification

Before running DataMasque, verify Time Travel is disabled (expected result is 0):

SHOW TABLES LIKE 'your_table_name' ->> SELECT "data_retention_time_in_days" FROM $1;

For more information, refer to the Snowflake Time Travel documentation.

Supported data types

The following Snowflake datatypes are supported. Note that only base datatypes are listed, not their aliases (e.g., number is listed but not decimal, which is an alias for number). integer is included despite being an alias because it has specific constraints on scale:

Data Type	Description
number	Numbers up to 38 digits, with an optional precision and scale.
integer	Synonymous with `NUMBER`, except that precision and scale can’t be specified (that is, it always defaults to `NUMBER(38, 0)`).
float	Double-precision (64 bit) IEEE 754 floating-point numbers.
varchar(n)	Unicode UTF8 strings up to 16,777,216 bytes in length. Some characters may require multiple bytes to store.
binary	Binary data up to 8MB (8,388,608 bytes) in length.
boolean	`TRUE` or `FALSE`.
date	Date value with no time.
time	Time value with no date.
timestamp_ltz	`TIMESTAMP` with local time zone.
timestamp_ntz	`TIMESTAMP` with no time zone.
timestamp_tz	`TIMESTAMP` with time zone.

Refer to the Snowflake data types documentation for information about the aliases for these data types.

Unsupported key columns

Data Type	Unsupported Format
float	All
binary	All
time	Precision >= 7
timestamp_ltz	Precision >= 7
timestamp_ntz, datetime	Precision >= 7
timestamp_tz	Precision >= 7
variant	All
object	All
map	All
geography	All
geometry	All
vector	All

Notes:

datetime is an alias for timestamp_ntz.

Likewise, timestamp is an alias for one of the timestamp_tz, timestamp_ntz, or timestamp_ltz formats, depending on session parameters.

The default precision (decimal places of seconds) for time and the various timestamp_* formats is 9 digits, but in order to support use as a key column, the precision must be at most 6 digits. Thus, to use a time or timestamp_* column as a key column, be sure to explicitly specify a precision of at most 6 when declaring the column, for example timestamp_ntz(6).