Stored Procedure

Anonymizer

Anonymise personal data in Snowflake for safe use in test and development environments.

Goal

Test environments ideally run with production-quality data, but must not contain real personal data. The Anonymizer solves this: it processes a copy of your production table so that data distribution and statistics are preserved, but values can no longer be traced back to individuals.

Per column you choose the anonymisation method that best fits the data type and purpose of the test environment.

What to expect

The stored procedure offers six anonymisation methods:

shuffle

Redistributes existing column values randomly across rows. The data remains realistic but is no longer traceable to an individual.

shuffle_name

Intelligently mixes first and last names by shuffling them separately, including infixes. The name structure remains intact.

shuffle_phone

Redistributes phone prefixes and suffixes independently per length group, so numbers remain realistic.

shift_housenumber

Changes house numbers by ±2 or ±4, so addresses are plausible but not correct.

random_string

Generates random strings based on a pattern. Use 'a' for a letter and '#' for a digit, e.g. 'aa##aa' for a postcode.

random_lookup

Fills rows with random values from another table column. Useful for realistic test data from a reference list.

Usage

1. One-time setup

First run the global setup.sql as accountadmin. Then create the stored procedure:

-- Adjust variables at the top of the script:
-- MY_DATABASE, MY_WAREHOUSE

-- Then run:
execute immediate $$ ... $$;

2. Call the procedure

Call the procedure with the target table and a column configuration. Per column you specify the method:

CALL datamodder.anonymize(
  'MY_DB.MY_SCHEMA.CUSTOMERS',
  ARRAY_CONSTRUCT(
    OBJECT_CONSTRUCT('column', 'NAME',    'method', 'shuffle_name'),
    OBJECT_CONSTRUCT('column', 'EMAIL',   'method', 'random_string',
                     'pattern', 'aaaa####@test.com'),
    OBJECT_CONSTRUCT('column', 'PHONE',   'method', 'shuffle_phone'),
    OBJECT_CONSTRUCT('column', 'ADDRESS', 'method', 'shift_housenumber')
  )
);

3. Verify with test scripts

The included test_anonymize_sp.sql creates sample data, runs the procedure and shows a before-and-after comparison.

View on GitHub All utils