Data Cleaning and Business Logic Edge Cases Questions

Covers handling data centric edge cases and complex business rule interactions in queries and data pipelines. Topics include cleaning and normalizing data, handling nulls and type mismatches, deduplication strategies, treating inconsistent or malformed records, validating results and detecting anomalies, using conditional logic for data transformation, understanding null semantics in SQL, and designing queries that correctly implement date boundaries and domain specific business rules. Emphasis is on producing robust results in the presence of imperfect data and complex requirements.

EasyTechnical

27 practiced

Design a deterministic transformation to canonicalize phone numbers into an E.164-like format for storage given inconsistent source formats: missing country code, leading zeros, extensions, spaces, and punctuation. Describe a Spark or Python implementation, how you would validate numbers, and how you'd handle ambiguous or invalid entries in ETL.

Sample Answer

**Approach**- Deterministic transform: strip punctuation/whitespace, parse country code (default to configured country), remove leading zeros, handle extensions, produce E.164-like +CCNNNN...

**Python/Spark sketch (uses phonenumbers lib)**

python

import phonenumbers

def canonical_e164(raw, default_region='US'):
    if not raw: return None
    raw = raw.strip()
    # remove common ext labels
    raw = re.sub(r"ext(?:ension)?[:.\s]*\d+$", '', raw, flags=re.I)
    try:
        p = phonenumbers.parse(raw, default_region)
        if not phonenumbers.is_possible_number(p):
            return None
        return phonenumbers.format_number(p, phonenumbers.PhoneNumberFormat.E164)
    except Exception:
        return None

**Validation & ETL handling**- Validate via library (is_possible, is_valid). Write invalid/ambiguous to a dead-letter table with reason and sample. For ambiguous (no country), attempt default region but mark as inferred.

**Notes**- Use phonenumbers (libphonenumber) for robust parsing; in Spark apply UDF and collect metrics on invalid rate. Store normalized E.164 and source_raw for traceability.

EasyTechnical

23 practiced

You ingest a 'full_name' free-text field from many sources. Describe and implement (SQL or Python pseudocode) a normalization pipeline to: trim leading/trailing whitespace, collapse multiple internal spaces, remove unexpected characters while preserving hyphens/apostrophes, normalize capitalization (respecting common prefixes like Mc/Mac and suffixes), and produce a canonical form for matching. Discuss trade-offs (over-normalization vs preserving legitimate variants) and mention libraries you might use.

Sample Answer

**Approach**- Normalize by trimming, collapsing spaces, removing unexpected chars (keep hyphen/apostrophe), title-casing with exceptions (Mc/Mac, O') and produce a canonical all-lower ASCII match key.

**Implementation (Python pseudocode)**

python

import re
import unicodedata

def normalize_name(s):
    if not s: return None
    s = unicodedata.normalize('NFKC', s)
    s = s.strip()
    s = re.sub(r"\s+", " ", s)
    s = re.sub(r"[^\w\-\' \u00C0-\u024F]", "", s)  # keep letters, digits, hyphen, apostrophe

    def cap_token(t):
        t = t.lower()
        if t.startswith(("mc","mac")) and len(t)>2:
            return t[:2].capitalize() + t[2:].capitalize()
        return t.capitalize()

    parts = [cap_token(p) for p in s.split(' ')]
    pretty = ' '.join(parts)
    canonical = re.sub(r"[^a-z0-9]", "", pretty.lower())
    return pretty, canonical

**Key points**- Preserve common punctuation, handle Unicode normalization.- Canonical form strips punctuation and lowercases for matching.

**Trade-offs**- Over-normalization can merge distinct names (e.g., "Anne-Marie" vs "Anne Marie"). Keep both pretty and canonical forms.

**Libraries**- python's unicodedata, regex; for heavy workloads, use curated name libraries (nameparser) and ICU via PyICU.

MediumTechnical

20 practiced

You need to pick a canonical customer record when duplicates differ across email, phone, source_priority, and last_updated. Write SQL to select one canonical record per customer_id prioritizing source_priority (higher wins) then most recent last_updated. Explain how you would persist provenance (which original record fields contributed) for auditing and later reconciliation.

Sample Answer

**Goal**: One canonical record per customer_id prioritizing higher source_priority, then latest last_updated; persist provenance.

**SQL (Postgres/BigQuery style)**:

SELECT DISTINCT ON (customer_id)
  customer_id,
  email, phone, source_priority, last_updated,
  source_id as winning_source_id
FROM customers
ORDER BY customer_id, source_priority DESC, last_updated DESC;

Alternative ANSI window function:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY source_priority DESC, last_updated DESC) as rn
  FROM customers
)
SELECT * EXCEPT (rn) FROM ranked WHERE rn = 1;

**Provenance**:- Persist canonical table plus a provenance table: canonical_provenance(customer_id, canonical_id, contributing_record_id, field_name, original_value, contribution_type, created_at).- For each canonicalized field, write an entry recording which source record provided it and why (priority/timestamp).- Optionally store a JSON blob of merged_sources and the resolution rule version.

**Audit & reconciliation**:- Keep immutable audit log of runs with code/schema/version, input snapshot hash, and canonicalization job run_id so you can re-materialize or revert.

This guarantees deterministic selection and clear field-level provenance for later auditing.

HardTechnical

23 practiced

During an ID migration old_user_id -> new_user_id mappings are incomplete. Propose a safe approach to reconcile historical events (stored with old_user_id) with modern analytics keyed by new_user_id. Include design for a mapping table, backfill strategies, handling of unmapped events, avoiding double-counting, and performance considerations to avoid full rewrites of large datasets.

MediumTechnical

23 practiced

Describe an end-to-end strategy to deduplicate noisy customer records at scale using Apache Spark. Include: blocking strategies to reduce candidate pairs, choice and implementation of similarity metrics (Jaro-Winkler, Levenshtein, TF-IDF), approximate methods like LSH or MinHash, evaluation metrics (precision/recall), and a pseudocode sketch of a Spark workflow. Also explain threshold tuning and how to produce deterministic outputs for downstream consumers.

Sample Answer

**Framework**- Blocking -> Candidate generation -> Scoring (similarity) -> Clustering -> Review

**Blocking strategies**- Exact or n-gram on normalized fields (phone, email local-part, hashed name + zip) to reduce comparisons.- Use multiple blocks and union results for recall.

**Similarity metrics**- Jaro-Winkler for short string names; Levenshtein for edit distance; TF-IDF cosine or n-gram for addresses; MinHash/LSH for long text.

**Approximate methods**- Use LSH or MinHash on shingles of name+address to find similar candidates at scale.

**Evaluation**- Precision/recall on labeled sample. Use F1 and precision at K. Monitor cluster purity and merge errors.

**Spark pseudocode**

python

# pseudocode
raw = spark.read.parquet('customers')
norm = normalize(raw)  # lower, strip, standardize
blocks = norm.withColumn('block_key', block_fn(norm))
candidates = blocks.repartition('block_key').groupBy('block_key').apply(pairwise_pairs)
scored = candidates.withColumn('score', similarity_udf(col('left'), col('right')))
matches = scored.filter('score > threshold')
clusters = transitive_closure(matches)
representatives = clusters.map(select_canonical_record)

**Threshold tuning & determinism**- Tune thresholds on labeled set to meet target precision/recall; prefer higher precision for auto-merge.- Deterministic outputs: stable canonical selection rules (highest data completeness, most recent, lowest surrogate_id), sort ties by deterministic id.

This balances recall with performance and provides deterministic canonicalization for downstream consumers.

Unlock Full Question Bank

Get access to hundreds of Data Cleaning and Business Logic Edge Cases interview questions and detailed answers.

Join thousands of developers preparing for their dream job.