MediumTechnical

29 practiced

Multiple data sources represent the same categorical attribute with different spellings or abbreviations (for example 'NY', 'New York', 'new_york', 'NYC' all meaning the same state or city). Describe a practical pipeline to detect, normalize, and maintain a mapping for these variants before merging datasets, including fuzzy matching, a maintained ruleset, and a human-in-the-loop step for ambiguous cases.

Sample Answer

Direct answer Maintain an explicit, versioned mapping from every known variant spelling to one canonical category value, apply it during ingestion or transformation, and route anything that doesn't match a known variant to human review rather than guessing a mapping automatically, since a wrong automatic guess here silently corrupts every downstream aggregation grouped by that category. Structured elaboration Normalize before matching: lowercase, strip punctuation, and collapse separators (underscores, hyphens) to a consistent form before comparing against the known-variants map, so trivial formatting differences don't require their own separate map entries. Explicit mapping, not fuzzy matching, as the default: for a bounded, known set of categories (states, a fixed product taxonomy), an explicit dictionary of known variants to canonical values is more predictable and auditable than a similarity-based fuzzy match, reserve fuzzy matching for genuinely open-ended text. Unrecognized values return None/unmapped, not a guess: a value that doesn't match anything in the known-variants map should be surfaced for human review and addition to the map, not silently passed through unmapped or silently mapped to the closest-looking known value. Maintaining the mapping over time: this map needs to be a living, versioned artifact (not a one-time script), since new source systems will periodically introduce new spelling variants that need to be added. Worked example pythonCANON_MAP = {'ny':'New York', 'new york':'New York', 'new_york':'New York', 'nyc':'New York'} def normalize_category(raw): key = raw.strip().lower().replace('_',' ').replace('-',' ') return CANON_MAP.get(key.replace(' ',''), CANON_MAP.get(key, None)) Verified: 'NY', 'new_york', and 'NYC' all correctly map to 'New York'; 'Chicago', which has no entry in the known-variants map, correctly returns None rather than being silently mis-mapped, signaling that it needs either its own mapping entry or human review to confirm it's genuinely a different category. Trade-offs and pitfalls A mapping table that's built once and never revisited will slowly accumulate "unmapped" values as new sources are onboarded; treat unmapped-value counts as an operational metric to monitor, not a one-time cleanup task. Being too aggressive with normalization (stripping too much punctuation, treating too many things as equivalent) can accidentally merge two genuinely distinct categories that happen to normalize to the same key; validate the mapping against a domain expert, not just against the raw data's own patterns. This differs from the earlier fuzzy-entity-resolution competency in an important way: that one handles genuinely open-ended, unbounded text (people's names) where similarity scoring is the only practical tool; this handles a small, bounded, enumerable set of known categories, where an explicit map is both more accurate and more auditable than a similarity score would be.

MediumTechnical

30 practiced

Given a free-text address field with inconsistent layouts across countries, describe a strategy to parse and canonicalize it into structured components (street, city, region, postal code, country) suitable for matching and analytics, including how you would handle a case your approach can't confidently resolve. As a smaller related exercise, implement a general text-normalization function that trims whitespace, collapses repeated internal whitespace, strips control characters, and preserves Unicode letters and emoji.

Sample Answer

Direct answer Parse a free-text address into structured components using a mix of regex heuristics and a purpose-built parsing library, attach a confidence score to uncertain matches rather than a binary success/fail, and escalate low-confidence cases to an external geocoding service rather than silently accepting a bad parse; a smaller, related need is a general text-normalization utility for cleaning up whitespace and control characters before any of this structured parsing happens. Structured elaboration Normalize before parsing: trim, collapse repeated whitespace, and strip control characters from the raw text first, since inconsistent whitespace alone can break a naive tokenizer before it even gets to the address-specific logic. Tokenize and match against known patterns: a parsing library (built specifically for postal address formats, which vary significantly by country) handles the bulk of well-formed cases; expect it to still fail or guess on truly irregular inputs. Confidence scoring, not a binary outcome: rather than "parsed" or "failed," attach a confidence score so a low-confidence parse can be routed differently (to a cheaper fallback or a human reviewer) than a high-confidence one, which can be trusted directly. Escalation to an external geocoding service: for addresses the local parser can't confidently resolve, a paid geocoding API can often disambiguate using real-world postal data, at a per-call cost that needs to be weighed against how often escalation is actually needed. General text normalization, as the smaller companion utility: trimming whitespace, collapsing repeated internal whitespace, and stripping control characters while explicitly preserving Unicode letters and emoji is a narrower, reusable piece of the same "clean free text before matching" competency, useful well beyond just addresses. Worked example pythondef normalize_text(s): s = s.strip() s = re.sub(r'[\x00-\x08\x0b\x0c\x0e-\x1f\x7f]', '', s) # control chars s = re.sub(r'\s+', ' ', s) return s Verified: " hello world\t\n " correctly normalizes to "hello world" (trimmed and collapsed), while "café \U0001F600" (an accented letter plus an emoji) is correctly left untouched, control-character stripping and whitespace collapsing do not damage legitimate Unicode content. This normalization step is what you'd run on a raw address field BEFORE handing it to an address-parsing library or a confidence-scored matcher, since inconsistent whitespace and stray control characters are a common, avoidable source of otherwise-good addresses failing to parse. Trade-offs and pitfalls A confidence threshold for auto-accepting a parsed address is a precision/recall trade-off exactly like the one in fuzzy entity resolution: too permissive and bad parses get treated as good; too strict and too many legitimate addresses get escalated unnecessarily, driving up geocoding API cost. Escalating every low-confidence address to a paid geocoding service without a volume cap can turn an occasional fallback into a significant, unplanned recurring cost; rate-limit or sample escalations if volume is unpredictable. Aggressively "cleaning" free text can occasionally strip something meaningful if the regex is too broad (a legitimate but unusual Unicode character mistaken for a control character); test the normalization function against a representative sample of real international addresses, not just ASCII-only examples.

MediumTechnical

35 practiced

Given a users table and a transactions table, write a SQL query using window functions to build a per-user feature/summary table in one pass: last transaction date, a rolling count of transactions in the past 30 days, a rolling average amount over the past 90 days, and days since signup. Explain how you handle a user who has never transacted.

Sample Answer

MediumTechnical

40 practiced

You receive phone numbers from several partners in inconsistent formats (with or without country codes, extensions, punctuation, or leading zeros). Design a normalization and validation workflow that produces a single canonical format suitable for matching and analytics, including how you would flag numbers you cannot confidently normalize and any privacy considerations for storing them.

Sample Answer

Direct answer Normalize confidently-parseable phone numbers into a single canonical format (E.164-style, a leading + and country code), extract any extension separately rather than losing it or corrupting the main number, and explicitly return "cannot confidently normalize" for genuinely ambiguous inputs instead of guessing a country code. Structured elaboration Strip formatting, isolate digits: remove punctuation, spaces, and parentheses to get the raw digit sequence, then separately detect and extract an extension (x123, ext. 123) if present, before trying to interpret the remaining digits as a phone number. Confident cases: a number that already starts with + is assumed to include an explicit country code; a 10-digit number (in a context where you know the default country, e.g. US) can be confidently prefixed with that default country code; an 11-digit number starting with a 1 similarly maps cleanly to a US/Canada E.164 number. Ambiguous cases, fail closed: a number like 001-44-20-1234 mixes an exit code convention with a country code in a way that isn't safely disambiguated without knowing the sender's own dialing convention; rather than guess, return "cannot confidently normalize" and route it to a review queue. Privacy consideration: phone numbers are personal data; whatever normalization and storage approach you use should follow the same access-control and retention discipline as other PII, not be treated as a harmless free-text field just because it's short. Worked example pythondef canonicalize_phone(raw, default_country='1'): digits = re.sub(r'\D', '', raw) ext = None m = re.search(r'(?:x|ext\.?)\s*(\d+)$', raw, re.IGNORECASE) if m: ext = m.group(1) digits = re.sub(r'\D', '', raw[:m.start()]) if raw.strip().startswith('+'): return '+' + digits, ext if len(digits) == 10: return '+' + default_country + digits, ext if len(digits) == 11 and digits.startswith('1'): return '+' + digits, ext return None, ext # cannot confidently normalize Verified: '+1 (555) 123-4567' and a bare '5551234567' (with a known US default) both correctly normalize to +15551234567; '5551234 x123' correctly extracts extension 123 while returning None for the main number (a 7-digit number is too ambiguous to confidently assign a country code to); '001-44-20-1234' correctly returns None rather than a confidently-wrong guess. Trade-offs and pitfalls Assuming a default country for any bare 10-digit number is only safe when you actually know the data source's country context; applying a US default to a dataset that actually mixes multiple countries' numbers will silently mis-normalize a real fraction of them. A library purpose-built for phone number parsing (handling the full space of international dialing conventions correctly) is generally a better production choice than a hand-rolled regex approach like the one above, which is presented here as an illustration of the fail-closed PRINCIPLE, not a claim that this exact regex handles every real-world case. Storing a normalized phone number without also retaining (or being able to reconstruct) the original raw input can make it hard to audit or re-normalize later if the canonicalization rule itself needs to change.

MediumTechnical

28 practiced

Design and implement a streaming deduplication component that consumes a stream of (id, timestamp) events and reports whether each event is new or a duplicate within a bounded time window, using bounded memory (an LRU cache or a Bloom filter, your choice). Discuss the correctness trade-off of the approach you chose: can it produce false positives or false negatives, and what does that mean for events that get silently dropped or double-counted?

Data Transformation and Processing Logic Questions

Sample Answer

Direct answer

Structured elaboration

Worked example

Trade-offs and pitfalls

Sample Answer

Direct answer

Structured elaboration

Worked example

Trade-offs and pitfalls

Sample Answer

Direct answer

Structured elaboration

Worked example

Trade-offs and pitfalls

Sample Answer

Direct answer

Structured elaboration

Worked example

Trade-offs and pitfalls

Sample Answer

Direct answer

Structured elaboration

Worked example

Trade-offs and pitfalls

Unlock Full Question Bank