--- title: "Test compliance rules" description: "GDPR and other compliance as tests — masked values for flagged customers, the right to be forgotten across every table, retention, consent, no personal data where none belongs — with set is empty, contains and templates" url: "https://docs.justcat.it/how-to-guides/test-patterns/test-compliance-rules/" --- # Test compliance rules ## Masked values for flagged customers The anonymization job replaces personal columns with a marker — `--removed--`, a hash, `NULL` — for customers with a flag (erasure requested, contract ended, consent withdrawn). The test: a flagged customer whose column still holds anything else. **Properties** Name : Erased customers carry no personal data Suite : GDPR Description : Customers with ErasureRequested = 1 must have every personal column set to '--removed--' by the nightly anonymization job. Data source : CRM Query : ```sql SELECT CustomerId, Email, Phone, FirstName, LastName FROM dbo.Customer WHERE ErasureRequested = 1 AND (Email <> '--removed--' OR Phone <> '--removed--' OR FirstName <> '--removed--' OR LastName <> '--removed--' OR Email IS NULL OR Phone IS NULL) ``` Expectation : set is empty Maximum errors logged : 0 **YAML** ```yaml Tests: - Name: Erased customers carry no personal data Suite: GDPR Description: Customers with ErasureRequested = 1 must have every personal column set to '--removed--' by the nightly anonymization job. Data source: CRM Query: | SELECT CustomerId, Email, Phone, FirstName, LastName FROM dbo.Customer WHERE ErasureRequested = 1 AND (Email <> '--removed--' OR Phone <> '--removed--' OR FirstName <> '--removed--' OR LastName <> '--removed--' OR Email IS NULL OR Phone IS NULL) Expectation: set is empty Maximum errors logged: 0 ``` `Maximum errors logged: 0` because the offending row *is* personal data — the message says how many rows broke the rule, not what they contain; `Log number of errors: true` adds the total. When the marker is a hash or `NULL`, test for *that* shape; the pattern is the same. ## Forgotten everywhere An erased person must be gone — or masked — in every table that ever held them: orders, tickets, the warehouse, the lakehouse, the model. The list of IDs to forget lives in one place; every other system is checked against it. Two shapes, depending on where the list and the data are: * **One query, one system**, when the list and the data are reachable from one place: `SELECT … FROM dim.Customer WHERE SourceCustomerId IN (SELECT CustomerId FROM erasure list) AND Email <> '--removed--'` → `set is empty`. * **Two systems**: the erasure list must be *contained* in the set of customers the other system has already masked — `contains`, with the masked IDs as the first (super)set and the erasure list as the second, `Key` on the ID. A missing row in the message is a person that system forgot to forget. **Properties** Name : Every erasure request is applied in the warehouse Suite : GDPR First data source : DWH First query : SELECT DISTINCT SourceCustomerId FROM dim.Customer WHERE SourceSystem = 'CRM' AND Email = '--removed--' ORDER BY SourceCustomerId Second data source : CRM Second query : SELECT CustomerId FROM dbo.ErasureRequests WHERE CompletedOn IS NOT NULL ORDER BY CustomerId Expectation : contains Key : 1 Maximum errors logged : 0 **YAML** ```yaml - Name: Every erasure request is applied in the warehouse Suite: GDPR First data source: DWH First query: SELECT DISTINCT SourceCustomerId FROM dim.Customer WHERE SourceSystem = 'CRM' AND Email = '--removed--' ORDER BY SourceCustomerId Second data source: CRM Second query: SELECT CustomerId FROM dbo.ErasureRequests WHERE CompletedOn IS NOT NULL ORDER BY CustomerId Expectation: contains Key: 1 Maximum errors logged: 0 ``` One such test per system that holds the person; a [template](https://docs.justcat.it/reference/tests/templates/ "Templates") over the list of those systems writes them for you. ## Retention Nothing older than the retention period may exist — and the test names the table and the oldest row: **Properties** Name : No support ticket older than 7 years Suite : Retention Data source : Helpdesk Query : ```sql SELECT MIN(CreatedOn) AS OldestTicket, COUNT(*) AS Tickets FROM dbo.Ticket WHERE CreatedOn < DATEADD(YEAR, -7, SYSUTCDATETIME()) HAVING COUNT(*) > 0 ``` Expectation : set is empty **YAML** ```yaml - Name: No support ticket older than 7 years Suite: Retention Data source: Helpdesk Query: | SELECT MIN(CreatedOn) AS OldestTicket, COUNT(*) AS Tickets FROM dbo.Ticket WHERE CreatedOn < DATEADD(YEAR, -7, SYSUTCDATETIME()) HAVING COUNT(*) > 0 Expectation: set is empty ``` Per table with a date column it is a template over `INFORMATION_SCHEMA.COLUMNS` (tables that have a `CreatedOn`), one test each — see [Generate tests from metadata](https://docs.justcat.it/how-to-guides/organize-and-run-tests/generate-tests-from-metadata/ "Generate tests from metadata"). ## Consent No marketing contact for a customer without consent; no profiling row for a customer who opted out: **Properties** Name : No campaign contact without marketing consent Suite : GDPR Data source : CRM Query : ```sql SELECT c.ContactId, c.CustomerId, c.SentOn FROM dbo.CampaignContact AS c JOIN dbo.Customer AS cu ON cu.CustomerId = c.CustomerId WHERE cu.MarketingConsent = 0 AND c.SentOn > cu.ConsentChangedOn ``` Expectation : set is empty Maximum errors logged : 0 **YAML** ```yaml - Name: No campaign contact without marketing consent Suite: GDPR Data source: CRM Query: | SELECT c.ContactId, c.CustomerId, c.SentOn FROM dbo.CampaignContact AS c JOIN dbo.Customer AS cu ON cu.CustomerId = c.CustomerId WHERE cu.MarketingConsent = 0 AND c.SentOn > cu.ConsentChangedOn Expectation: set is empty Maximum errors logged: 0 ``` ## Personal data where none belongs Free-text and technical columns that must not carry emails, phone numbers, national IDs — a pattern per column, `set is empty`: **Properties** Name : No email address in ticket subjects Suite : GDPR Data source : Helpdesk Query : SELECT TicketId FROM dbo.Ticket WHERE Subject LIKE '%@%.%' Expectation : set is empty Maximum errors logged : 0 **YAML** ```yaml - Name: No email address in ticket subjects Suite: GDPR Data source: Helpdesk Query: SELECT TicketId FROM dbo.Ticket WHERE Subject LIKE '%@%.%' Expectation: set is empty Maximum errors logged: 0 ``` The same over the lakehouse's bronze layer, where raw files land, is where most leaks are found. ## Making the run the evidence * **`Description` is the rule in plain words** — it travels into the results, so the output reads as an audit record without the project file. * **Write the results to a [database output](https://docs.justcat.it/reference/outputs/database-outputs/ "Database outputs")** — one row per test per run with `Started on`, result, message — and keep it; that table *is* the log an auditor asks for. An Excel output for the person who signs. * **`Maximum errors logged: 0` on every test that would otherwise show personal data** — the evidence must not become a new copy of what it protects. * **Schedule it** — nightly after the anonymization job, not before a review; see [Pipelines and schedulers](https://docs.justcat.it/how-to-guides/pipelines-and-schedulers/ "Pipelines and schedulers"). ## Related * [Find problems with set is empty](https://docs.justcat.it/how-to-guides/test-patterns/find-problems-with-set-is-empty/ "Find problems with set is empty") · [Compare data across systems](https://docs.justcat.it/how-to-guides/test-patterns/compare-data-across-systems/ "Compare data across systems") * [Contains](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains") · [Failure message](https://docs.justcat.it/reference/tests/failure-message/ "Failure message") — what the message shows, and `Maximum errors logged: 0`.