Snowflake stored procedure
GDPR Profiler
Automatically scan a Snowflake schema for personal data via configurable regex rules — without manually sifting through every table.
Goal
Knowing which columns contain personal data is the first step for GDPR compliance. The GDPR Profiler does this work automatically: it joins the system catalogue to a table of regex rules, samples column values and writes its findings — including a confidence score and recommended action — to a result set.
The detection rules are fully configurable via PII_REGEX_RULES. Adding new PII categories means inserting a row, not changing code.
Required tables
PII_REGEX_RULES
Configuration table with detection patterns. Add rows to support new PII categories without modifying the procedure.
PII_SCAN_RESULTS
Results table. Each scan run writes its findings here, tagged with scan_id and timestamp.
How it works
Identify candidates
The procedure cross-joins information_schema.columns with PII_REGEX_RULES and filters directly on data type. This way irrelevant columns are already filtered out in SQL.
Sample values
For each candidate column a sample is taken and values are checked against the value_regex pattern. The match ratio is compared with MATCH_THRESHOLD.
Store findings
Name and value confidence are added together (max. 1.0) and stored in PII_SCAN_RESULTS together with the detection reason and recommended action.
Parameters
| Parameter | Default | Description |
|---|---|---|
| DATABASE_NAME | — | Required. Database to be scanned. |
| SCHEMA_NAME | — | Required. Schema to be scanned. |
| SAMPLE_ROWS | — | Required. Number of rows sampled per column. |
| MATCH_THRESHOLD | 0.1 | Minimum ratio of matching values to flag a column (0–1). |
| UTILS_DATABASE | current database | Database where PII_REGEX_RULES and PII_SCAN_RESULTS are located. |
| UTILS_SCHEMA | current schema | Schema where the utility tables are located. |
Usage
1. Basic scan
Scan an entire schema with 1,000 sample rows per column:
CALL DATAMODDER_UTILS.PROFILE_PII(
'MY_DATABASE',
'MY_SCHEMA',
1000
);2. Adjust sensitivity
Lower the threshold to also detect weaker signals (e.g. 5% of values match):
CALL DATAMODDER_UTILS.PROFILE_PII(
'MY_DATABASE',
'MY_SCHEMA',
5000,
0.05
);3. Separate utility location
Are your utility tables in a different database or schema? Pass them explicitly:
CALL DATAMODDER_UTILS.PROFILE_PII(
'MY_DATABASE',
'MY_SCHEMA',
1000,
0.1,
'UTILS_DB',
'UTILS_SCHEMA'
);4. View results
SELECT * FROM DATAMODDER_UTILS.PII_SCAN_RESULTS ORDER BY scanned_at DESC, confidence DESC;