Test compliance rules

Compliance rules are data rules with a legal deadline: this column must be masked for these customers, this person must be gone from every table, nothing older than seven years may exist. They test like any other rule — as a query that finds the violations — and the run is your evidence.

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
• 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
• 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 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
• 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.

Consent #

No marketing contact for a customer without consent; no profiling row for a customer who opted out:

• Properties
• 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
• 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 — 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.

Related #

• Find problems with set is empty · Compare data across systems
• Contains · Failure message — what the message shows, and Maximum errors logged: 0.