Data Scrubbing

Data Scrubbing

When to Use

• Profile a table or file and define data-quality rules before analysis or modeling
• Clean, standardize, dedupe, or link records in CSV, Parquet, SQL extracts, or notebook pipelines
• Treat missing values, duplicates, outliers, types, encodings, and column naming consistently
• Document a reproducible scrub pipeline with validation checks and sign-off criteria
• Scrub actuarial/insurance fields (policy keys, claims triangles, exposure bases) for downstream reserving or pricing prep
• Flag or redact PII at a technical level before sharing extracts (coordinate with compliance for legal requirements)

When NOT to Use

• Star/snowflake modeling, warehouse ETL/ELT, CDC, or platform ingestion design → data-warehouse-engineer
• Predictive modeling, A/B tests, causal inference, feature engineering for ML, or MLOps → data-scientist
• Loss development, IBNR, pricing models, or appointed-actuary sign-off → actuary
• Assumption sets, governance memos, or model assumption workshops → assumption-setting
• SOC 2 / ISO control mapping, audit evidence automation, or privacy legal program → compliance-engineer
• Cloud cost allocation, FinOps dashboards, or unit economics only → finops-analyst
• Spreadsheet formula integrity or cell-level model audit without a scrub pipeline → audit-xls (if available)

Related skills

Core Workflows

1. Intake and scope

1. Identify source(s), grain, primary keys, and downstream consumer (report, model, regulatory filing) 2. Record business definitions for critical fields and acceptable quality thresholds 3. Choose deliverables: scrubbed dataset, rule catalog, pipeline code, validation report, sign-off checklist 4. Confirm what must not change (audit trail, raw landing zone immutability)

See `references/data_scrubbing_scope_and_workflow.md`.

2. Profile and define quality rules

1. Run structural profile: row/column counts, types, null rates, cardinality, min/max, patterns 2. Classify columns: identifier, measure, dimension, date, free text, PII-sensitive 3. Draft rules: uniqueness, referential checks, range/domain, regex, cross-field logic, volume gates 4. Prioritize rules by severity (blocker vs warning) and tie each to a remediation action

See `references/profiling_and_quality_rules.md`.

3. Remediate missing values, duplicates, outliers

1. Apply documented strategies per column (impute, flag, drop, split, business rule) 2. Deduplicate at correct grain; preserve lineage for merged records 3. Treat outliers with explicit policy (cap, winsorize, exclude, investigate)—never silent deletion 4. Re-run profile deltas after each major remediation pass

See `references/missing_duplicates_and_outliers.md`.

4. Standardize and coerce types

1. Normalize names, units, currencies, time zones, and categorical vocabularies 2. Coerce types with explicit parse rules and quarantine rows that fail 3. Fix encoding (UTF-8), delimiters, locale-specific decimals, and boolean sentinels 4. Version mapping tables (code → label) alongside the pipeline

See `references/standardization_and_type_coercion.md`.

5. PII and governance (technical, not legal advice)

1. Inventory sensitive columns; classify using organizational taxonomy when provided 2. Apply minimization: drop, hash/tokenize, mask, or aggregate per approved pattern 3. Log scrub actions; restrict outputs; never commit secrets or production PII to public repos 4. Escalate legal basis, retention, and cross-border rules to compliance-engineer / counsel

See `references/pii_redaction_and_governance.md`.

6. Actuarial / insurance scrubbing

1. Validate policy/claim keys, effective/accident dates, and triangle orientation 2. Align exposure bases and earned premium logic with documented definitions 3. Scrub large losses, sublimits, and reinsurance fields without distorting triangle structure 4. Hand off reserving/pricing math to actuary after data is signed off for modeling

See `references/actuarial_insurance_data_scrubbing.md`.

7. Validate, document, sign off

1. Execute rule suite on scrubbed output; compare to thresholds and prior period if applicable 2. Produce validation report: pass/fail counts, quarantine volume, top failure reasons 3. Package reproducible pipeline (script/SQL/notebook), config, and rule catalog with version hash 4. Obtain owner sign-off before promoting to modeling or reporting consumers

See `references/data_scrubbing_scope_and_workflow.md` (sign-off section).

When to load references

Actuarial and Insurance Data Scrubbing

Purpose

Prepare insurance and actuarial tabular inputs—policies, claims, premiums, exposures—for reserving, pricing, experience studies, and management reporting. Scrubbing ensures correct grain, keys, and field semantics; reserve math and assumption selection belong to actuary and assumption-setting.

Common datasets

Policy fields

Validate and standardize:

Document mid-term endorsements: grain may be policy segment not static policy row.

Claims fields

Subrogation and salvage: separate columns; do not net into incurred without specification.

Premium and earning

Scrub inputs to earned premium logic, not the actuarial formula itself:

• Identify cancellation timing and return premium rows
• Separate audit vs written premium when both exist
• Align policy term with earning pattern (pro-rata, short-rate) via metadata flags

Hand off earned calculations to actuary when actuarial standard required.

Exposure bases

Exposure must match rating variable definition:

Checks:

• Exposure period aligns with policy term
• Units match filing manual (no mixed units in one column)
• Zero exposure with positive premium flagged

Claims triangles

Before triangle construction:

1. Confirm orientation: rows = accident period, cols = development lag (or document transpose) 2. Consistent valuation date for all cells 3. Exclude incomplete latest diagonal only with documented rule 4. Handle negative development from corrections via adjustment rows, not silent cell edits 5. Reconcile triangle totals to claim register control

Route triangle methodology and tail selection to actuary.

Reinsurance

Catastrophe and large loss

• Tag CAT codes separately from attritional
• Do not cap CAT losses in scrub without explicit modeling policy
• Geographic coordinates: validate range; generalize if PII concern

Experience study prep

For ratemaking experience:

• Align policy year / accident year definitions
• Filter to target segment after documenting exclusion counts
• On-level factors: store indices in versioned tables, not ad hoc spreadsheet columns
• Credibility weights: compute in actuarial workflow, not hidden in scrub

Coordination with peers

Sign-off (actuarial data)

Additional gates before actuary modeling:

• [ ] Triangle/register reconciliation within tolerance
• [ ] LOB and state mapping signed by actuarial data owner
• [ ] Large loss and CAT flags reviewed
• [ ] Development lag completeness documented
• [ ] Known reformations (COVID, rate changes) flagged by period

Anti-patterns

• Mixing accident year and policy year in one column without label
• Dropping open claims to "clean" triangles
• Applying loss development factors during scrub (belongs in modeling)
• Changing historical earned premium without restatement flag
• Using scrubbed triangle for regulatory filing without actuary review

Data Scrubbing — Scope and Workflow

Purpose

Data scrubbing prepares analysis-ready tabular data: consistent types, trustworthy keys, documented transformations, and measurable quality. It sits after extraction and before modeling, BI, or regulatory submissions—not at warehouse platform design.

In scope

Out of scope (route to peers)

End-to-end workflow

Intake → Profile → Rule catalog → Remediate (iterative) → Validate → Sign-off → Handoff

Phase 1 — Intake

Capture in a one-page scrub charter:

• Sources: files, tables, APIs; refresh cadence
• Grain: one row per what (transaction, policy-month, claim)
• Keys: natural vs surrogate; composite keys
• Consumers: analyst, actuary, ML, regulator
• Constraints: immutability of raw layer, retention, PII policy
• Success metrics: max null rate, duplicate rate, rule pass rate

Phase 2 — Profile (baseline)

Minimum profile artifacts:

1. Row count and byte size 2. Per-column: type, null %, distinct count, top values (capped) 3. Numeric: min, max, mean, percentiles (as appropriate) 4. Date: min, max, future-dated count 5. Text: max length, encoding issues, leading/trailing whitespace rate

Store baseline profile with dataset version ID.

Phase 3 — Rule catalog

For each rule assign:

Phase 4 — Remediate (iterative)

Order of operations (default—adjust when domain requires):

1. Encoding and delimiter fixes 2. Type coercion and parsing quarantine 3. Standardization (codes, units, time zones) 4. Key construction and referential fixes 5. Duplicate resolution / linkage 6. Missing value treatment 7. Outlier treatment (last—after types stable)

Log every transformation in a change log table: step, rows_in, rows_out, rows_quarantined, timestamp.

Phase 5 — Validate

Re-run full rule suite on scrubbed output. Compare to baseline profile:

• Unexpected null spikes
• Cardinality collapse (over-deduping)
• Distribution shift beyond agreed tolerance

Phase 6 — Sign-off

Sign-off package (minimum):

1. Scrub charter (updated) 2. Rule catalog with final pass rates 3. Validation report (blockers = 0 unless waived) 4. Pipeline artifact hash / git commit 5. Sample row diff (before vs after) for critical fields 6. Named approver and date

Waivers: document rule ID, business justification, expiry, and compensating control.

Reproducibility requirements

• Pin random seeds where imputation or sampling is used
• Store mapping tables (e.g., state code → region) in version control
• Parameterize paths and dates; no hard-coded production secrets
• Separate raw, staging scrub, and published zones when possible

Handoff checklist

Before passing to data-scientist or actuary:

• [ ] Grain documented and verified
• [ ] Primary keys unique at stated grain
• [ ] Critical measures non-null above threshold
• [ ] Date fields parsed and time zone documented
• [ ] PII minimized per policy
• [ ] Validation report attached
• [ ] Known limitations listed (coverage gaps, manual fixes)

Anti-patterns

• Scrubbing in the only copy of production data without backup
• Dropping rows without quarantine or counts
• Changing keys without lineage to source records
• One-off Excel edits without reproducible pipeline
• Mixing scrub and model training in the same undocumented notebook cell block

Missing Values, Duplicates, and Outliers

Missing values

Classify missingness first

Document assumption in scrub charter; escalate structural MNAR to domain owner.

Treatment options (choose explicitly per column)

Default: prefer flag + impute over silent fill for fields used in modeling.

Missing value workflow

1. Report null rate by column and segment (product, region, year) 2. Cross-tab nulls across related fields (co-missing patterns) 3. Select strategy per column with written rationale 4. Apply in reproducible step; quarantine rows that fail derivation 5. Re-profile null rates; attach before/after table

Duplicates

Define grain before deduping

Example grains:

• Customer: one row per customer_id
• Policy-month: policy_id + calendar_month
• Claim: claim_id (not claim line unless line grain intended)

Deduping at wrong grain destroys information or leaves hidden duplicates.

Duplicate types

Survivorship rules (deterministic)

When multiple rows share a key, pick winner by ordered criteria:

1. Most recent updated_at 2. Source system precedence (e.g., core admin > portal) 3. Most complete record (fewest nulls on critical fields) 4. Manual override table

Document merged-field logic: non-key fields may use COALESCE priority list.

Record linkage (fuzzy)

High-level steps:

1. Standardize blocking keys (soundex, postal, phone digits) 2. Generate candidate pairs within blocks 3. Score pairs (Fellegi-Sunter, ML classifier) 4. Auto-merge above high threshold; queue medium for review 5. Never auto-merge below low threshold

Preserve source_ids[] and match_score on golden record.

Outliers

Distinguish outlier types

Detection methods (use multiple)

• IQR / z-score on log-transformed positives
• Percentile caps (p99.5) for reporting views only
• Business caps (max claim by line of business)
• Multivariate (isolation forest) for anomaly flags, not silent drops

Treatment policies (document one per measure)

Never remove outliers without counts, flags, and approver awareness for material fields.

Combined workflow diagram

Profile nulls → classify → impute/flag/quarantine
       ↓
Profile dupes at grain → survivorship / linkage → golden table
       ↓
Profile distributions → outlier flags → cap/investigate/keep
       ↓
Re-validate keys and rule suite

Metrics to report

Handoff notes for modeling

Tell data-scientist explicitly:

• Which fields were imputed and how
• Whether outliers were capped or excluded from training slice
• Dedup grain and survivorship order
• Any MNAR concerns on target variable

Tell actuary explicitly:

• Whether large losses were capped or segmented
• Triangle completeness after claim dedupe
• Exposure adjustments applied before scrub sign-off

PII Redaction and Governance (Technical)

Not legal advice. This reference covers technical patterns for minimizing sensitive data in scrub pipelines. Legal basis, retention, DPA terms, and regulatory filings require compliance-engineer and qualified counsel.

Goals

• Reduce exposure of personally identifiable information (PII) and sensitive categories in datasets used for analytics, modeling, or sharing
• Maintain usefulness where possible via aggregation, tokenization, or separation of keys
• Leave audit trail of what was redacted and how

PII inventory (minimum)

For each column, record:

Use organizational data catalog when available; do not invent legal categories.

Common PII categories (technical)

Treatment patterns

Removal

Drop column from derivative datasets when not needed. Simplest and lowest risk.

Masking

Partial display for operational support only:

• Email: j***@domain.com
• Phone: show last 4 digits

Not sufficient for open analytics datasets.

Hashing / tokenization

token = HMAC-SHA256(pepper, normalized_value)

Requirements:

• Use organization-approved pepper/secret store (env var, KMS)—never commit pepper
• Normalize before hash (lower email, strip phone formatting)
• Same input → same token enables joins within environment
• Salting per environment prevents cross-env linkage

Generalization

Aggregation

Publish cohort-level stats only; suppress small cells (n < k).

Pseudonymization vs anonymization

Do not label output "anonymized" without organizational sign-off.

Pipeline controls

1. Separate environments: scrub in non-prod; restrict prod exports 2. Least privilege: pipeline service account reads only required columns 3. Logging: log rule IDs and counts, not raw PII values 4. Output scanning: run pattern detectors on sample exports (email regex, SSN patterns) 5. Secure quarantine: encrypted store for rows held for investigation

Sharing externally

Before third-party transfer:

• Confirm contract and purpose limitation with legal/compliance
• Strip or tokenize per data sharing agreement
• Document schema and transformation version sent

Route SOC 2 / ISO evidence of controls to compliance-engineer.

Coordination table

Detection helpers (non-exhaustive)

Pattern-based scans (tune per locale):

• Email: RFC-like regex on sample
• Phone: E.164 length checks
• SSN (US): ###-##-#### with invalid area filters
• Credit card: Luhn check on digit runs

Flag columns for review; do not auto-delete without rule approval.

Documentation for audit

Include in scrub package:

• PII column list and treatment per column
• Hash algorithm and key management reference (not the secret)
• Sample of masked output
• Count of rows in quarantine for failed redaction

Anti-patterns

• Committing production extracts to git or public buckets
• Logging full row on parse errors
• Using reversible encoding (base64) and calling it encrypted
• Sharing dedupe keys across partners without agreement
• Assuming k-anonymity without formal analysis

Profiling and Quality Rules

Profiling goals

Profiling answers: what is in the dataset, where it breaks expectations, and what must be fixed before use. Treat profiling as a versioned artifact, not a one-time print statement.

Structural profile

Statistical profile (tabular)

For numeric columns:

• Central tendency and spread (mean, median, std, IQR)
• Zero-inflation rate
• Negative values where only positive expected
• Spike detection vs prior period (%, not only absolute)

For categorical columns:

• Cardinality vs expectation
• Rare level concentration (long tail)
• Unknown / OTHER bucket size

For text columns:

• Length distribution
• Regex pattern match rate (email, phone, postal)
• Non-printable character rate

Column classification

Rule severity model

Rule pattern catalog

Uniqueness

COUNT(*) = COUNT(DISTINCT primary_key)  -- at grain G

Document composite keys explicitly (e.g., policy_id + effective_date).

Completeness

null_rate(column) <= threshold

Different thresholds for optional vs required fields.

Domain / validity

• Enumerated lists (status in {A,B,C})
• Range checks (0 <= loss_ratio <= 5 with business cap)
• Regex (^[A-Z]{2}[0-9]{9}$ for internal IDs)

Consistency (cross-field)

• effective_date <= expiration_date
• paid_amount <= incurred_amount (unless documented exception)
• country_code aligns with currency when both present

Referential integrity

child.foreign_key IN parent.primary_key

Allow orphan rate threshold with quarantine export.

Volume and freshness

• Row count within % of trailing average
• max(event_timestamp) within SLA of clock

Distribution drift (monitoring)

Compare scrubbed output to prior period:

• PSI or simple bucket shift for key measures
• New categorical levels above rate threshold

Rule implementation options

Pick one primary framework per pipeline; avoid duplicate conflicting rules.

Rule documentation template

rule_id: CLAIM_AMOUNT_NON_NEGATIVE
severity: blocker
grain: claim_id
description: Incurred amount must be >= 0 unless adjustment_type = 'reversal'
expression: |
  incurred_amount >= 0 OR adjustment_type = 'reversal'
remediation: quarantine to exceptions.claim_amount
owner: claims_data_steward

Profiling outputs

Deliver at minimum:

1. Profile summary (HTML/Markdown or notebook export) 2. Column-level stats (CSV/Parquet for tooling) 3. Failed rule summary with top offending values 4. Quarantine row extract (sample + full if small)

Prioritization

When time-boxed, fix in order:

1. Key integrity and grain errors 2. Blocker domain violations on measures used in models 3. Referential breaks affecting joins 4. Warnings on optional attributes 5. Cosmetic standardization

Integration with scrub pipeline

raw → profile_raw → apply_rules(staging) → quarantine → profile_scrubbed → validate → publish

Never publish when blocker count > 0 unless an approved waiver exists on file.

Standardization and Type Coercion

Objectives

Standardization makes columns comparable, joinable, and machine-parseable without changing business meaning. Coercion applies explicit parse rules; failures go to quarantine—not silent coercion to null.

Naming conventions

Maintain a data dictionary synced with scrub pipeline version.

Categorical standardization

1. Trim whitespace; collapse internal double spaces 2. Upper or lower case per org standard (often upper for codes) 3. Map synonyms via versioned lookup:

4. Route unmapped values to UNKNOWN quarantine bucket with count threshold alerts

Numeric standardization

• Remove thousands separators and currency symbols before parse
• Store amounts as decimal type with explicit scale
• Separate amount and currency; convert FX in dedicated step with rate table version
• Document unit multipliers (thousands vs units)

Date and time

Validate: birth_date <= transaction_date where applicable.

Boolean coercion

Normalize sentinels to true/false/null:

Type coercion order

1. Read as string (preserve leading zeros on IDs) 2. Apply ID rules: strip only whitespace; no numeric cast for policy numbers with leading zeros 3. Parse dates with explicit formats 4. Parse decimals with locale rules 5. Cast integers only when domain confirms integral 6. Quarantine rows failing parse with parse_error reason

Encoding and file hygiene

• Enforce UTF-8 on export; detect BOM
• Replace curly quotes and non-breaking spaces in text fields
• Normalize line endings in CSV ingestion
• Escape delimiters consistently in outputs

Geographic and industry codes

Use standard code lists where applicable:

• ISO country / currency
• NAIC / LOB codes (insurance) per org reference
• Postal formats validated by country-specific regex

Keep code tables under version control with effective dates.

Record linkage prep (standardization for matching)

Do not over-normalize away discriminative tokens needed for match quality.

Derived fields

Document formulas in scrub spec:

earned_premium = written_premium * earned_ratio

Validate derived fields against source components on sample.

Versioning mapping tables

Each lookup CSV/SQL table includes:

• mapping_version
• effective_from / effective_to
• approved_by

Pipeline records which mapping version ran.

Validation after coercion

Tools (non-prescriptive)

Promote interactive fixes to scripted steps before production sign-off.

Anti-patterns

• Casting SSN/policy numbers to float (precision loss)
• Implicit locale in to_datetime without format
• Mixing NULL and empty string without policy
• Applying global title-case to codes
• Hard-deleting rows that fail parse without quarantine