Below are pragmatic, production-grade data validation rules for column dwd_customer.email, focused on warehouse-friendly enforcement and pipeline checks. The rules aim to prevent malformed, duplicate, or low-quality values while remaining implementable in SQL and data quality frameworks.
Scope and assumptions
- Column type: string (VARCHAR).
- Email values are single addresses (no lists).
- Unicode domains may be present; normalization is required.
- Enforcement strategy may differ by platform; where constraints are not enforced, run validation queries in ETL/ELT or DQ jobs.
Validation rules
- Presence and nullability
- If the business requires an email for a customer, enforce NOT NULL; otherwise allow NULL.
- If NULLs are allowed, ensure empty strings are normalized to NULL.
- Rule: TRIM(email) IS NOT NULL AND TRIM(email) <> '' (if mandatory).
- Length constraints
- Total length ≤ 254 characters.
- Local part (before @) ≤ 64 characters.
- Rule:
- LENGTH(email) ≤ 254
- LENGTH(SPLIT_PART(email, '@', 1)) ≤ 64 (or engine-specific string split)
- Whitespace and control characters
- No leading/trailing whitespace; internal whitespace not allowed.
- No control characters (ASCII < 32), no tabs, newlines.
- Rule:
- email = TRIM(email)
- NOT REGEXP_LIKE(email, '[\s\p{Cntrl}]') (adjust for engine)
- Single address with single “@”
- Exactly one “@”.
- Rule:
- email LIKE '%@%' AND email NOT LIKE '%@%@%'
- Syntactic pattern (ASCII pragmatic; excludes quoted local parts)
- Local part: atoms separated by single dots; allowed chars A-Z a-z 0-9 and !#$%&'*+/=?^_`{|}~-
- No leading/trailing/consecutive dots in local part.
- Domain: labels 1–63 chars, alphanumeric plus hyphen (not starting/ending with hyphen), at least one dot.
- TLD length ≥ 2.
- Rule (regex, anchored):
- ^(?:[A-Za-z0-9!#$%&'+/=?^_
{|}~-]+(?:\\.[A-Za-z0-9!#$%&'*+/=?^_{|}~-]+))@(?:A-Za-z0-9?)(?:\.(?:A-Za-z0-9?))+$
- Additional TLD check (optional if engine cannot easily enforce in regex):
- LENGTH(REGEXP_SUBSTR(email, '\.([A-Za-z0-9-]{2,63})$')) > 0
- Unicode/IDN domain handling
- If Unicode domains are permitted, normalize domain to punycode before storage or create a derived field email_domain_ascii for matching/dedup.
- Rule:
- email_normalized = local part unchanged + '@' + lowercased punycode domain.
- If punycode conversion is unavailable in-warehouse, perform in the ingestion/transformation step.
- Reserved/test addresses
- Reject known placeholders:
- Local part in {'test', 'admin', 'null'} with example domains (example.com, example.org, example.net).
- Reject comma/semicolon-separated values.
- Rules:
- NOT REGEXP_LIKE(email, '[,;]')
- NOT (LOWER(email) LIKE 'test@%' OR LOWER(email) LIKE '%@example.%')
- Disposable/temporary domains
- Maintain a reference table ref_disposable_domains(domain_name).
- Rule:
- LOWER(SPLIT_PART(email, '@', 2)) NOT IN (SELECT domain_name FROM ref_disposable_domains)
- Uniqueness and normalization
- Define email_canonical for deduplication: lowercased full address after trimming; if IDN supported, domain punycoded then lowercased.
- Enforce uniqueness on email_canonical among active customers.
- Rules:
- email_canonical = LOWER(TRIM(email)) (plus domain punycode if available)
- UNIQUE(email_canonical) for active records (or pipeline check)
- Optional deliverability check (outside warehouse constraints)
- Periodic DNS MX (or fallback A) lookup for domains; store deliverability_status.
- Rule:
- domain has at least one MX record (DQ process), not a strict storage constraint.
Example implementations
A) Warehouse-side SQL checks (generic pattern; adapt functions to engine)
-
Invalid rows detection:
SELECT customer_id, email
FROM dwd_customer
WHERE
email IS NULL OR TRIM(email) = ''
OR LENGTH(email) > 254
OR email NOT LIKE '%@%' OR email LIKE '%@%@%'
OR REGEXP_LIKE(email, '[\s\p{Cntrl}]')
OR NOT REGEXP_LIKE(TRIM(email),
'^(?:[A-Za-z0-9!#$%&''+/=?^_{|}~-]+(?:\\.[A-Za-z0-9!#$%&''*+/=?^_{|}~-]+))@(?:A-Za-z0-9?)(?:\.(?:A-Za-z0-9?))+$')
OR REGEXP_LIKE(email, '[,;]')
OR LENGTH(SPLIT_PART(email, '@', 1)) > 64
OR LOWER(SPLIT_PART(email, '@', 2)) IN (SELECT domain_name FROM ref_disposable_domains);
-
Canonicalization (ETL step):
SELECT
customer_id,
email,
LOWER(TRIM(email)) AS email_canonical
FROM staging_customer;
B) dbt tests (schema.yml)
models:
- name: dwd_customer
columns:
- name: email
tests:
- not_null: {config: {where: "email_required = true"}}
- accepted_regex:
regex: "^(?:[A-Za-z0-9!#$%&'+/=?^_
{|}~-]+(?:\\.[A-Za-z0-9!#$%&'*+/=?^_{|}~-]+))@(?:A-Za-z0-9?)(?:\.(?:A-Za-z0-9?))+$"
- custom_sql:
sql: "LENGTH(email) <= 254 AND LENGTH(SPLIT_PART(email,'@',1)) <= 64"
- name: email_canonical
tests:
- unique: {config: {where: "is_active = true"}}
C) Great Expectations (expectation suite pseudocode)
- expect_column_values_to_not_be_null (conditional on email_required)
- expect_column_values_to_match_regex for the pattern above
- expect_column_values_to_be_unique (on email_canonical for active customers)
- expect_column_values_to_not_match_regex for '[,;]'
- expect_column_values_to_match_regex (negative) for '\s|\p{Cntrl}'
- Custom expectation: domain not in disposable list via query
D) Delta Lake / Spark SQL table constraint (if supported)
ALTER TABLE dwd_customer
ADD CONSTRAINT chk_email_format CHECK (
LENGTH(email) <= 254
AND email RLIKE '^(?:[A-Za-z0-9!#$%&''+/=?^_{|}~-]+(?:\\.[A-Za-z0-9!#$%&''*+/=?^_{|}~-]+))@(?:A-Za-z0-9?)(?:\.(?:A-Za-z0-9?))+$'
AND LENGTH(split(email, '@')[0]) <= 64
AND NOT (email RLIKE '[\s\p{Cntrl}]')
AND NOT (email RLIKE '[,;]')
);
Operational notes
- Constraint enforcement varies by platform; if constraints are informational, run validation queries in ETL/ELT or DQ jobs and quarantine failures.
- Store both raw email and normalized (email_canonical). Use email_canonical for uniqueness and joins, raw for auditing.
- Maintain and regularly update the disposable domain list.
- If the system must support quoted local parts or internationalized local parts, expand the parser accordingly; the provided regex intentionally excludes rare, complex RFC cases to remain practical for data quality.
