--- name: base-models-comment-piis description: Scan all base model SQL files, comment out inferred PII columns into a labelled section at the end of each SELECT, and update the corresponding source YAML with PII metadata. disable-model-invocation: true argument-hint: "[source_name] [table_name]" --- # Comment Out PII Fields in Base Models Scan base model SQL files, detect PII columns by naming convention, move them to a commented-out `-- PII` section at the bottom of each `SELECT`, and mark them in the YAML documentation. `$ARGUMENTS` is optional: - No arguments → process **all** `models/base/**/*.sql` files. - One token → process all SQL files under `models/base//`. - Two tokens → process only `models/base//base__.sql`. ## What this skill does 1. Finds base model SQL files matching the scope above. 2. For each file, parses the column list and detects PII fields by name. 3. Rewrites the SQL: non-PII columns stay in place; PII columns are moved to a `-- PII` comment block at the bottom of the `SELECT`. 4. Updates the corresponding `_.yml`: adds `pii: true` metadata and updates each PII column's description to note it is excluded. ## PII detection rules A column is flagged as PII if its name (case-insensitive, after stripping any table-alias prefix like `source.`) **matches any of the patterns below**. ### Exact-match suffixes (the column ends with one of these words, after splitting on `_`) Personal identity: - `name` — **only** when preceded by a personal-context word: `first`, `last`, `full`, `given`, `middle`, `maiden`, `family`, `display`, `preferred`, `legal`, `customer`, `user`, `contact`, `person`, `employee`, `buyer`, `seller`, `owner`, `recipient`, `subscriber`, `client` - Examples that **are** PII: `first_name`, `customer_name`, `user_display_name` - Examples that are **not** PII: `campaign_name`, `product_name`, `ad_group_name`, `category_name` - `email`, `email_address` - `phone`, `phone_number`, `mobile`, `mobile_number`, `telephone`, `tel` - `address`, `street`, `street_address`, `billing_address`, `shipping_address`, `mailing_address` - `city`, `postcode`, `postal_code`, `zip`, `zip_code` - `ssn`, `national_id`, `tax_id`, `vat_id`, `passport`, `driver_license`, `drivers_license` - `date_of_birth`, `dob`, `birth_date`, `birthdate` - `age` — **only** when preceded by `customer`, `user`, `person`, `contact`, `buyer` - `gender`, `sex`, `ethnicity`, `race`, `religion`, `nationality` - `ip_address`, `ip`, `device_id`, `mac_address`, `user_agent` - `latitude`, `longitude`, `lat`, `lon`, `location`, `geolocation`, `coordinates` - `credit_card`, `card_number`, `card_holder`, `iban`, `bic`, `bank_account`, `account_number` - `password`, `pin`, `secret`, `token` (auth tokens, not dlt tokens) - `photo`, `avatar`, `profile_picture`, `profile_image` - `salary`, `income`, `wage` ### Substring match anywhere in the column name - `_email_`, `_phone_`, `_dob_`, `_ssn_`, `_iban_` (with underscores on both sides to avoid false positives) **When in doubt, do NOT flag a field as PII.** It is safer to miss a field than to comment out a legitimate business field. If a field is borderline (e.g. `name` without a personal-context prefix), leave it in and add a `# TODO: verify if PII` SQL comment next to it. ## Procedure ### 1. Resolve scope Parse `$ARGUMENTS` (may be empty). Build the list of `.sql` files to process: - No args: `glob("models/base/**/*.sql")` - One arg ``: `glob("models/base//*.sql")` - Two args ` `: `["models/base//base__
.sql"]` If no files are found for the given scope, stop and tell the user. ### 2. Identify the source name and YAML path for each file For a file at `models/base//base__
.sql`: - `source_name` = `` - `yaml_path` = `models/base//_.yml` ### 3. Parse the SQL SELECT column list Read the SQL file. Locate the innermost `SELECT` that selects from the `source` CTE (typically in a `renamed` CTE). Extract the list of selected columns, including any aliases (`col AS alias`). Column names to evaluate for PII are the **output alias** (the name after `AS`), or the raw column name if no alias is present. Strip any table-alias prefix (e.g. `source.email` → `email`). ### 4. Classify columns Apply the PII detection rules above to each column. Produce two lists: - `non_pii_columns` — to stay in the visible `SELECT` - `pii_columns` — to be commented out If `pii_columns` is empty for a file, skip that file (no changes needed), and note it in the summary. ### 5. Rewrite the SQL Reconstruct the `SELECT` block with this structure: ```sql SELECT -- regular columns , , ... -- PII (commented out — excluded from base model output) -- , -- , -- , ... FROM source ``` Rules: - Preserve leading commas and 2-space indentation (project coding standard). - Keep the original column order within each group; PII columns go last, in their original relative order. - The `-- PII` comment header uses exactly that string. - Each commented-out PII column line is prefixed with `-- ` (two dashes, one space). - Preserve all other parts of the file (config block, import CTEs, `FROM` clause, etc.) exactly. If the file already has a `-- PII` section (this skill was already run), **replace** the existing section rather than appending. Do not ask the user — always recompute from scratch. ### 6. Write the updated SQL Write the rewritten SQL back to the same file path. Show the user a compact diff (old → new column list only, not the whole file). ### 7. Update the YAML Read `models/base//_.yml`. Find the `models:` entry for this table (match on `name: base__
`). For each PII column in `pii_columns`, find its entry under `columns:` and: 1. Add `meta: {pii: true}` at the column level. 2. Prepend `"[PII — excluded from model output] "` to the existing `description` string. If a column entry does not yet exist in the YAML (the model entry may be incomplete), add it with the PII metadata and a placeholder description. For non-PII columns, leave the YAML entry unchanged. Write the updated YAML back to the same file. ### 8. Validate After processing all files, compile the project: ```bash dbt compile ``` Fix any compilation errors before finishing. ### 9. Report Print a summary table: ``` File PII columns found ------------------------------------------- ----------------- models/base/foo/base_foo_orders.sql email_address, billing_address models/base/foo/base_foo_customers.sql first_name, last_name, phone models/base/bar/base_bar_users.sql 0 (skipped) ``` If no PII was found in any file, say so clearly and exit without modifying anything. ## Output - Modified SQL files (in place). - Modified YAML file(s) (in place). ## Notes - Do NOT remove columns from the SQL — they must remain as commented lines so developers can see what exists at the source. The goal is to make the base model output PII-free without losing the audit trail. - Do not flag dlt internal columns (`_dlt_id`, `_dlt_load_id`, etc.) as PII — they are infrastructure fields. - Do not modify `interim_` or `reporting_` models — only `base_` models are in scope. - If `$ARGUMENTS` is empty and there are many files, process them all and report at the end. Do not ask for confirmation before each file. - Always run `dbt compile` at the end, even when only one file was changed.