Contains Social Security Number Check Examples

The situation: An HR dataset stores the employee tax identifier in tax_id. Rows where the column holds an internal badge number or a placeholder break payroll filings.

Check configuration

Payload

{
    "description": "Employee tax identifier must hold a social security number.",
    "rule": "containsSocialSecurityNumber",
    "fields": ["tax_id"],
    "container_id": 410,
    "coverage": 1,
    "filter": null,
    "properties": {},
    "tags": ["hr", "format"],
    "additional_metadata": {"jira": "DATA-7301"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 18
}

Sample Data

What gets flagged

Employee E-002 has an internal badge number where the tax identifier should be. E-004 is NULL and passes without firing an anomaly. Coverage is 100%, so the failing row is reported as a Record Anomaly.

Flowchart

graph TD
    A["No filter, evaluate all rows"] --> B["Read tax_id as text"]
    B --> C{"Is value NULL?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E{"Does it contain<br/>a social security number?"}
    E -->|Yes| D
    E -->|No| F["Flag row.<br/>Record Anomaly per failing row."]

Equivalent SQL

-- Rows the Contains Social Security Number check would flag.
SELECT e.*
FROM employees e
WHERE e.tax_id IS NOT NULL
  AND e.tax_id NOT RLIKE '<social security number pattern>';

The situation: The contractors table mixes domestic and international records. Only domestic contractors carry a social security number in national_id; international ones hold other national identifiers and are out of scope.

Check configuration

Payload

{
    "description": "Domestic contractors must carry a social security number.",
    "rule": "containsSocialSecurityNumber",
    "fields": ["national_id"],
    "container_id": 411,
    "coverage": 1,
    "filter": "country = 'US'",
    "properties": {},
    "tags": ["compliance", "format"],
    "additional_metadata": {"jira": "DATA-7344"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 18
}

Sample Data (filtered to country = 'US')

Why the filter matters

The filter runs before the evaluation, so international contractors are never tested against a domestic format. Only domestic rows are evaluated.

What gets flagged

K-02 holds a status note instead of an identifier, so it is the only row reported. Coverage is 100%, so the failure is reported as a Record Anomaly, and the message ends with the filter that scoped the evaluation.

Flowchart

graph TD
    A["Apply filter: country = 'US'"] --> B["Read national_id as text"]
    B --> C{"Does it contain<br/>a social security number?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E["Flag row.<br/>Anomaly message ends with<br/>[filter: country = 'US']"]

Equivalent SQL

-- Rows the check would flag among domestic contractors.
SELECT k.*
FROM contractors k
WHERE k.country = 'US'
  AND k.national_id IS NOT NULL
  AND k.national_id NOT RLIKE '<social security number pattern>';

The situation: A legacy person_ref column mixes identifiers with free-text notes. A cleanup job is rewriting the bad rows, and a small fraction is expected to remain broken until it finishes, so the check tolerates up to 0.5% failures.

Check configuration

Payload

{
    "description": "Legacy person reference must hold a social security number.",
    "rule": "containsSocialSecurityNumber",
    "fields": ["person_ref"],
    "container_id": 412,
    "coverage": 0.995,
    "filter": null,
    "properties": {},
    "tags": ["legacy", "format"],
    "additional_metadata": {"jira": "DATA-7388"},
    "anomaly_message_field": null,
    "template_id": null,
    "status": "Active",
    "owner_id": 7,
    "default_anomaly_assignee_id": 41
}

Sample Data

What gets flagged

Records R-02 and R-03 still hold free-text notes. R-04 is NULL, so it passes without firing an anomaly but still counts in the scanned total. Only 50% of the rows pass, which is below the 99.5% coverage threshold, so the check reports a single Shape Anomaly for the dataset with a sample of the offending rows. Coverage below 100% does not produce Record Anomalies.

Flowchart

graph TD
    A["No filter, evaluate all rows"] --> B["Read person_ref as text"]
    B --> C{"Is value NULL?"}
    C -->|Yes| D["Row passes"]
    C -->|No| E{"Does it contain<br/>a social security number?"}
    E -->|Yes| D
    E -->|No| F["Flag row.<br/>Passing rate falls below the 99.5%<br/>coverage, so one Shape Anomaly is reported."]

Equivalent SQL

-- Rows the check would flag.
SELECT r.*
FROM person_records r
WHERE r.person_ref IS NOT NULL
  AND r.person_ref NOT RLIKE '<social security number pattern>';