PII reach, test theatre, and a breaking column change
An EU fintech runs 240 dbt models on Snowflake across three domain teams. Columns customer_email, iban, and birth_date appear in 38 models. Ninety models have descriptions; 15 exposures exist for roughly 70 dashboards. The suite has 1,100 tests, of which 900 are unique and not_null on surrogate keys. A "certified" badge is maintained in a spreadsheet that is two months out of date. Internal audit found that mart_marketing_events joined raw email addresses to ad-click data and was readable by the entire company through a single ANALYST role. Separately, the customer domain wants to change dim_customer.country from ISO-3 to ISO-2 codes; the column feeds 40 downstream models and 18 dashboards, and the team has never used dbt model versions.
1. Audit found a PII mart readable by everyone. Where in the stack do you enforce protection, and why is tagging columns in dbt documentation insufficient?
Read the worked answer
Documentation is metadata; enforcement has to live where the query executes. A tag in a YAML file does not stop a SELECT *.
Layer 1 - account-level masking. Tag PII columns at the source and in staging with a Snowflake tag (pii = 'high'), then attach masking policies to the tag itself (ALTER TAG pii SET MASKING POLICY ...). Tag-based masking applies to every column carrying that tag, including columns added next quarter by someone who never read the policy. Per-column policy attachment does not scale to 38 models and gets missed.
Layer 2 - role boundaries at the schema level. dbt grants on marts should name the consuming role, never PUBLIC. Split ANALYST into domain roles and a PII_READ role; mart_marketing_events gets a role that a handful of people hold. This is the control that would have stopped the audit finding directly - masking protects values, roles protect the join itself, and joining email to ad-click data is sensitive even when the email renders as ***.
Layer 3 - dbt model access. Set access: private on intermediate models so domain internals cannot be referenced across groups, and protected only on models intended as interfaces.
Layer 4 - CI check on lineage. Fail the build when a pii-tagged column appears in a model whose declared grant includes a broad role or whose exposure owner is outside the data domain. This is what catches the next mart_marketing_events before merge rather than at audit.
Layer 5 - contracts on shared marts so a new column cannot appear silently. Documentation then describes the system instead of pretending to be the control.
2. With 900 of 1,100 tests being unique/not_null on surrogate keys, how do you reallocate testing effort? Give a prioritization rule and concrete tests to add.
Read the worked answer
Surrogate-key tests on models you generated yourself are close to tautological: the key is unique because you built it with row_number(). They are cheap and not worthless, but 900 of them are crowding out the tests that would catch real incidents.
Prioritization rule: test at boundaries, weighted by blast radius. Rank models by (number of downstream models) × (number of exposures) and prioritize tests where data crosses a trust boundary - source to staging, staging to mart, mart to consumer. A model with eight exposures and no reconciliation test is a bigger risk than a leaf model with six not_null tests.
Tests to add, in order:
Source freshness on every raw source, with thresholds tied to the upstream SLA. This catches the most common incident class - stale data - which no schema test detects.
relationshipstests on every foreign key at mart grain. Orphaned facts silently drop from every join downstream.accepted_valueson low-cardinality status and type columns, with severitywarnwhere new values are expected anderrorwhere they are not.Reconciliation singular tests: row counts and monetary sums between the mart and its source for yesterday, with a tolerance. These catch filtering and join bugs that schema tests never see.
not_nullon business keys and join columns - keep these, but as a small set.
Tag them test_level: critical and run critical tests on every PR, the full suite nightly. Retire surrogate-key tests on models where the key is provably generated. Track test failures that preceded an incident; that history argues for budget better than a coverage percentage.
3. The customer domain wants to change `dim_customer.country` from ISO-3 to ISO-2. What is your migration path, and when would you use a dbt model version versus an additive column?
Read the worked answer
This is a breaking semantic change to a widely consumed column, so the question is how to decouple the schema change from the consumer migration.
Default path: additive column plus deprecation. Add country_code_iso2, backfill it, and keep country as ISO-3 with a documented deprecation date. Notify the 18 dashboard owners through exposures and lineage, not email. This is the right choice when the downstream models read the column but do not join on it or compare it to external ISO-2 data - most of the 40 models probably just pass it through, and the migration is a rename in a SELECT.
When to use a model version. Use dim_customer v2 with latest_version: 2 and deprecation_date when the change alters the meaning of the model in a way consumers must reason about - for example, if joins to another system's ISO-2 table exist, or if country feeds a partitioning or clustering key. A version gives v1 consumers a stable relation while v2 consumers opt in, and it makes the v1 sunset date machine-readable so CI can fail when it arrives.
Guardrails either way. Put a dbt contract on dim_customer so the column type and presence are enforced at build time. Add an accepted_values test with the ISO-2 list, and a reconciliation test that every v1 ISO-3 code maps to exactly one v2 code - a mapping gap is the failure mode that produces null countries in 18 dashboards. Sequence the switchover so no deploy changes the column and the contract in the same run.
Do not rename in place with a coordinated weekend cutover; with 40 downstream models and no version history, you have no way to prove every consumer migrated.