Contains Social Security Number

Use the Contains Social Security Number rule when a field is expected to contain text in a recognizable Social Security Number (SSN) pattern.

What Is Contains Social Security Number?

Think of Contains Social Security Number as a pattern check for sensitive identity fields.

It checks whether a non-null field value contains text that matches the rule's SSN criteria and surfaces non-matching values for review. It does not confirm that a number was issued to a person or establish regulatory compliance.

In simple terms: It evaluates whether the selected values have a recognizable SSN structure.

Add Contains Social Security Number Check

Use this check to evaluate a selected field and surface records whose non-null values do not contain a supported SSN pattern.

What Does This Check Do?

For records in the Scan scope, the rule evaluates a single field for:

• A supported SSN layout is present, such as XXX-XX-XXXX, XXX XX XXXX, or XXXXXXXXX
• The number groups do not use values excluded by the check
• Null values pass this rule. Add a Not Null check when a value must be present.

Records that do not meet the configured criteria can produce anomalies, subject to the check's filter and coverage settings.

How Does Contains Social Security Number Work?

Step 1: Select the Field

Choose the field that is expected to contain an SSN (for example: C_SSN).

Step 2: Validate the Content

Qualytics evaluates whether each non-null field value matches a supported SSN pattern.

Step 3: Review the Results

• Pass: The field is null or contains text that matches a supported SSN pattern.
• Fail: A non-null value does not contain a supported pattern or uses a number group excluded by the check.

Why Should You Use This Check?

1. Surface Policy Exceptions

Missing or malformed SSN values can create processing problems and may require review under your organization's policies.

2. Catch Issues Early

The check can surface pattern failures during a Scan so the responsible team can review them before later reporting or audit activities.

3. Protect Sensitive Workflows

Payroll, identity verification, and compliance processes may depend on SSN data. This rule provides a repeatable signal for reviewing whether the stored values match the expected pattern.

Real-Life Example: Customer Identity Data Validation

The Situation

BrightMart is a retail company that maintains a central customer database containing identity information required for compliance and verification.

Each customer record is expected to include a value in the organization's approved Social Security Number (SSN) pattern.

This data is used for:

• Customer identity verification
• Regulatory and compliance reporting
• Internal audits and analytics

The Problem They Had

As BrightMart’s customer base grew, the data team relied on manual reviews to verify SSN values.

During a routine compliance review, the team noticed that some customer records could not be validated, even though SSN data appeared to be present. This raised an important question:

Why are some records failing verification when they seem correct at first glance?

After investigating, the team discovered:

• Some SSN values were missing or empty
• Some values were incorrectly structured
• Manually reviewing thousands of records took significant time
• Human review made it easy to miss subtle issues

These problems had gone unnoticed for weeks and required review before the team could complete its reporting process.

The Solution: Contains Social Security Number

To apply the same initial criteria across its data, the team implemented the Contains Social Security Number rule type.

The rule evaluated the customer records included in the Scan and surfaced values that did not match the expected SSN pattern. The team still reviewed the findings to determine whether they were genuine issues or documented exceptions.

What the Check Reviewed

After running the check, Qualytics produced the following Source Records view. Records that did not meet the check criteria were highlighted for review.

What Contains Social Security Number Discovered

ALERT GENERATED:

Although the failure rate was small, each failed record required review against the team's identity-data policy.

🔍 Summary

• Most customer records matched the configured SSN pattern
• A small subset did not meet the check criteria
• The failed records gave the team a focused review queue

The Outcome

Immediate Benefits

• The same initial pattern criteria were applied across the Scan scope
• Records needing investigation were grouped for review
• The team documented accepted exceptions and confirmed issues

Long-Term Benefits

• Repeatable evaluation whenever the scheduled Scan runs
• Evidence to support audit and compliance review
• Shared context for identity-data owners and data stewards

Why This Matters

Missing or malformed SSN values can affect reporting or compliance processes, so each finding needs contextual review.

The Contains Social Security Number check consistently surfaces records in the Scan scope that do not match its criteria. It supports review of sensitive identity data, but it does not by itself establish that the data is complete, valid for every business purpose, or audit-ready.

Field Scope

Single: The rule evaluates a single specified field.

Accepted Types

General Properties

The filter allows you to define a subset of data upon which the rule will operate.

It requires a valid Spark SQL expression that determines the criteria rows in the DataFrame should meet. This means the expression specifies which rows the DataFrame should include based on those criteria. Since it's applied directly to the Spark DataFrame, traditional SQL constructs like WHERE clauses are not supported.

O_TOTALPRICE > 1000

C_MKTSEGMENT = 'BUILDING'

WHERE O_TOTALPRICE > 1000

WHERE C_MKTSEGMENT = 'BUILDING'

O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

(L_SHIPDATE = '1998-09-02' OR L_RECEIPTDATE = '1998-09-01') AND L_RETURNFLAG = 'R'

WHERE O_ORDERPRIORITY = '1-URGENT' AND O_ORDERSTATUS = 'O'

O_TOTALPRICE > 1000, O_ORDERSTATUS = 'O'

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - INSTR('-', O_ORDERPRIORITY)
) = 'URGENT'

LEVENSHTEIN(C_NAME, 'Supplier#000000001') < 7

RIGHT(
    O_ORDERPRIORITY,
    LENGTH(O_ORDERPRIORITY) - CHARINDEX('-', O_ORDERPRIORITY)
) = 'URGENT'

EDITDISTANCE(C_NAME, 'Supplier#000000001') < 7

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM {{_qualytics_self}}
    WHERE O_TOTALPRICE > 1000
)

O_ORDERSTATUS IN (
    SELECT DISTINCT O_ORDERSTATUS
    FROM ORDERS
    WHERE O_TOTALPRICE > 1000
)

Anomaly Types

Example

Objective: Check whether C_CONTACT_DETAILS entries contain a recognizable Social Security Number pattern.

Sample Data

{
    "description": "Check that C_CONTACT_DETAILS entries contain a recognizable Social Security Number pattern",
    "coverage": 1,
    "properties": {},
    "tags": [],
    "fields": ["C_CONTACT_DETAILS"],
    "additional_metadata": {"key 1": "value 1", "key 2": "value 2"},
    "rule": "containsSocialSecurityNumber",
    "container_id": {container_id},
    "template_id": {template_id},
    "filter": "1=1"
}

Anomaly Explanation

In the sample data above, the entry with C_CUSTKEY 2 does not satisfy the rule because its SSN starts with 666, a prefix excluded by the check. The value for C_CUSTKEY 1 passes because the check accepts nine digits without separators, as well as supported hyphenated or space-separated forms.

graph TD
A[Start] --> B[Retrieve C_CONTACT_DETAILS]
B --> C{Does C_CONTACT_DETAILS contain a recognizable SSN pattern?}
C -->|Yes| D[Move to Next Record/End]
C -->|No| E[Mark as Anomalous]
E --> D

-- An illustrative SQL query demonstrating the rule applied to example dataset(s).
select
    c_custkey,
    c_contact_details
from customer 
where
    not regexp_like(
        c_contact_details,
        '((?!219-09-9999|078-05-1120)(?!666|000|9[0-9]{2})[0-9]{3}-(?!00)[0-9]{2}-(?!0{4})[0-9]{4})|((?!219 09 9999|078 05 1120)(?!666|000|9[0-9]{2})[0-9]{3} (?!00)[0-9]{2} (?!0{4})[0-9]{4})|((?!219099999|078051120)(?!666|000|9[0-9]{2})[0-9]{3}(?!00)[0-9]{2}(?!0{4})[0-9]{4})'
    )

Potential Violation Messages