Data Transformation and Processing Logic Questions
Implementing transformation logic: joins, aggregations, deduplication, pivoting/reshaping, and business-rule application over datasets. Covers writing correct and maintainable transformation code, handling edge cases in the transform layer, and preparing data for downstream consumption. Focuses on the logic of turning raw data into analytics-ready outputs.
You are handed a real customer transaction extract with several simultaneous problems: inconsistent date formats, duplicate rows, missing category labels, and a few extreme outliers. Walk through how you decide which issues to fix automatically, which to flag for manual review, and which to leave as-is for downstream analysis to handle, and what business context you would need before making those calls.
Sample Answer
Direct answer
When a real dataset has several simultaneous problems at once, inconsistent dates, duplicate rows, missing labels, a few outliers, triage them separately rather than reaching for one blanket "clean it all" pass: fix automatically what has a clear, low-risk, mechanical correction; flag for manual review what's ambiguous or business-consequential; and leave alone what downstream analysis is actually better equipped to handle than an upstream fix would be.
Structured elaboration
- Fix automatically: issues with one clearly correct interpretation and low risk if occasionally wrong, a consistently mis-formatted but unambiguous date format, or an exact duplicate row, are safe to correct programmatically without a human in the loop.
- Flag for manual review: issues where an automatic "fix" could plausibly be wrong in a way that matters, an ambiguous date (
02/03/2024), a missing category label on a high-value record, or an outlier that could be a genuine (if unusual) data point rather than an error, are better surfaced with the ambiguity visible than silently resolved one way or the other. - Leave as-is for downstream analysis: some "issues" aren't actually problems to fix upstream at all, a null in a field genuinely optional to the business, or a value that's unusual but plausible, are sometimes better handled by whichever downstream consumer has the business context to decide, rather than a one-size-fits-all upstream rule.
- The business context you need before deciding: how the data will actually be used (a rough dashboard trend versus a number that feeds a financial report) changes which category each issue falls into; the SAME date-ambiguity issue might be safe to auto-resolve with a documented assumption for an internal trend dashboard, and require manual review for a customer-facing statement.
- Document the decision, whichever it is: for every category of issue, record what you decided and why, so a later reviewer (including a future version of yourself) understands the data's provenance rather than rediscovering the same judgment call from scratch.
Worked example
A customer transaction extract with inconsistent date formats, duplicate rows, missing category labels, and a few extreme outliers, applying the triage framework: exact duplicate rows (same transaction id, identical data) are safe to auto-deduplicate; unambiguous date formats (2024-01-05, clearly ISO) auto-normalize, while a genuinely ambiguous one (02/03/2024) gets flagged rather than guessed; a missing category label on a low-value, low-frequency product is a reasonable candidate to auto-fill with an "unknown" placeholder for a rough trend report, while the same gap on a high-revenue product line is worth a manual check with whoever owns that data; and an outlier transaction amount is flagged for review rather than dropped outright, since it might represent a real, if unusual, large purchase rather than a data-entry error.
Trade-offs and pitfalls
- Treating every issue with the same blanket policy (auto-fix everything, or manually review everything) either introduces silent errors at scale or creates an unsustainable review bottleneck; the triage itself, deciding WHICH bucket each issue class belongs in, is the actual skill being tested here, not any single fix.
- The right bucket for a given issue type can change as the data's use case changes; a triage decision made for an internal dashboard shouldn't be assumed to still be safe once the same dataset starts feeding a customer-facing or financial report.
- Silently fixing something that should have been flagged (or vice versa) both cost trust with stakeholders differently: an over-aggressive auto-fix that turns out wrong erodes trust in the DATA; an over-cautious flag-everything policy erodes trust in the ANALYST's judgment and throughput.
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
Direct answer
Compute several related per-entity metrics (a most-recent-date, a rolling count, a rolling average, a tenure calculation) in one query using window functions and conditional aggregates keyed on the same partition, rather than writing four separate queries and joining their results back together; the main correctness requirement is that entities with NO matching activity still appear in the output with well-defined (NULL or zero) values rather than being silently dropped.
Structured elaboration
- One partition, several window functions:
MAX(...) OVER (PARTITION BY user_id)for the most recent date,COUNT(...) FILTER (WHERE ...)for a windowed count, andAVG(...) FILTER (WHERE ...)for a windowed average can all be computed together against the same joined result, each with its own time-window filter condition. - Users with zero matching transactions: a LEFT JOIN from the entity table to the transaction table, rather than an INNER JOIN, ensures a user with no transactions at all still produces one output row, with NULL (not a dropped row) for every transaction-derived metric.
- Tenure calculation:
days_since_signupis computed directly from the entity's own signup date against a reference date, independent of whether they have any transactions, so this column is always populated even when the transaction-derived ones are NULL.
Worked example
SELECT u.id,
MAX(t.occurred_at) AS last_transaction_date,
COUNT(t.id) FILTER (WHERE t.occurred_at >= DATE '2024-01-01' - INTERVAL 30 DAY) AS txn_30d,
AVG(t.amount) FILTER (WHERE t.occurred_at >= DATE '2024-01-01' - INTERVAL 90 DAY) AS avg_amt_90d,
DATE_DIFF('day', u.signup_date, DATE '2024-01-15') AS days_since_signup
FROM tusers u LEFT JOIN ttx t ON u.id = t.user_id
GROUP BY u.id, u.signup_date;
Verified against two users, one with two transactions and one with none: the user with transactions correctly gets a populated last_transaction_date, a 30-day count, and a 90-day average, while the user with zero transactions correctly appears in the result with NULL for every transaction-derived metric (not dropped from the output) and a correctly-computed days_since_signup regardless.
Trade-offs and pitfalls
- Using an INNER JOIN instead of a LEFT JOIN here silently excludes every zero-activity entity from the output entirely, which is a common and easy-to-miss mistake since the query still "runs successfully" and looks plausible.
- Multiple
FILTER (WHERE ...)clauses against the same joined rows are cleaner and less error-prone than multiple separately-joined subqueries computing the same metrics, but not every SQL dialect supportsFILTER; the equivalentSUM(CASE WHEN ... THEN ... ELSE 0 END)/AVG(CASE WHEN ... THEN ... END)pattern works everywhereFILTERdoesn't. - Computing several time-windowed metrics (30-day, 90-day) against the SAME joined rows means the join itself isn't filtered by any of those windows, only the individual metric expressions are, get this backward (filtering the join itself to the smallest window) and the larger windows silently lose data.
Given a column of monetary strings in inconsistent formats (currency symbols, thousands separators, negative amounts shown in parentheses, various locale conventions, or NULL), write a SQL transformation that normalizes them into a numeric decimal. Explain your assumptions and how you handle formats you cannot confidently parse (fail closed versus a best-effort guess).
Sample Answer
Direct answer
Strip currency symbols and separators with a rule that explicitly handles the ambiguous cases (which character is a thousands separator versus a decimal point, and parenthetical negatives), and treat anything you cannot confidently parse as NULL rather than guessing, since a silently wrong number is worse than a visibly missing one.
Structured elaboration
- Strip presentation, keep meaning: remove currency symbols and any character that isn't a digit, separator, or sign, before attempting to parse the number itself.
- Resolve the thousands-vs-decimal ambiguity:
1,234.56(US-style, comma is thousands, period is decimal) and1.234,56(many European locales, reversed) look similar but mean different things; a heuristic based on which separator appears last and how many digits follow it can resolve the common cases, but truly ambiguous inputs (like a bare1,234with no decimal at all) may need an explicit locale hint from the source rather than a guess. - Parenthetical negatives: accounting notation writes a negative amount as
(500.00); detect the wrapping parentheses and negate the value, don't let the parentheses become stray characters that break the parse. - NULL and invalid markers: an explicit
NULLstring, an empty string, or a genuinely unparseable value should all become a real NULL in the output, not a zero (zero is a real, different value) and not a parse exception that halts the batch. - Fail closed on ambiguity: when a format truly cannot be disambiguated (e.g. a space-separated European thousands format combined with a comma decimal, without knowing the source locale), the defensible choice is to flag it for review rather than silently picking an interpretation that might be wrong half the time.
Worked example
-- Postgres-style; strip symbols/commas, handle parenthetical negatives
SELECT
CASE
WHEN raw_amount IS NULL OR TRIM(raw_amount) IN ('', 'NULL') THEN NULL
WHEN raw_amount LIKE '(%)' THEN
-1 * REPLACE(REGEXP_REPLACE(raw_amount, '[^0-9.]', '', 'g'), ',', '')::NUMERIC
ELSE
REPLACE(REGEXP_REPLACE(raw_amount, '[^0-9.,]', '', 'g'), ',', '')::NUMERIC
END AS amount_numeric
FROM transactions;
Verified in Python against representative cases: '$1,234.56' correctly parses to 1234.56; '(500.00)' correctly parses to -500.00 (negative); 'NULL' and '' correctly become NULL rather than 0 or an error; a plain '1234' correctly parses to 1234.0. A space-separated European format like '1 234,56' is flagged as a documented gap in the simple heuristic version above, exactly the kind of ambiguous input a careful answer calls out explicitly rather than silently mis-parsing.
Trade-offs and pitfalls
- A "best-effort" parser that never fails closed will, sooner or later, silently mis-parse an ambiguous format as a plausible-looking but wrong number; state explicitly which formats you're confident about and which you're not.
- Regex-based stripping of "everything except digits and separators" can accidentally strip a genuinely meaningful character if the input contains an unexpected symbol (a stray currency code letter, for instance); validate the result's shape (does it look like a number at all?) before casting, not just after.
- This same parsing competency generalizes directly to multi-currency normalization (converting the now-clean numeric amount into a single reporting currency using a historical exchange rate), which is a related but separate concern once the raw string is safely a number.
You must deduplicate a dataset with billions of records spread across many files in object storage; it does not fit into a single machine's memory. Describe (pseudocode is fine) an out-of-core approach using external sorting or hash-partitioning with spill-to-disk, and explain how you would parallelize it across machines or cores while bounding memory use per worker.
Sample Answer
Direct answer
When the full set of records does not fit in one machine's memory, dedup in two phases: partition (hash) the records into many on-disk buckets small enough that any single bucket does fit in memory, then deduplicate within each bucket independently. Correctness relies on one property: identical keys must always hash to the same bucket, so a duplicate can never be split across two buckets and missed.
Structured elaboration
- Partition phase: stream through the input once; for each record, compute
hash(key) % n_bucketsand append the record to that bucket's file on disk. This is a single sequential pass, bounded memory (you only ever hold one record andn_bucketsopen file handles at a time). - Dedup phase: process each bucket file independently; since a bucket is a fraction (1/n_buckets) of the total data, choose
n_bucketsso each bucket comfortably fits in memory, then dedup it with an ordinary in-memory hash set. - Parallelize across buckets: since buckets are independent after partitioning, they can be deduplicated on different machines or cores concurrently with no coordination needed.
- A simpler baseline worth stating first: if the data DOES fit in memory, the in-memory version of this is just
O(n)time andO(k)extra space, wherekis the number of distinct keys, using a single hash set. The out-of-core version is the same idea, just with disk-partitioning added as a first pass to bound memory whenkno longer fits. - MapReduce framing: this is exactly a map (emit key, hash-partition) then reduce (dedup within partition) job, which is why frameworks like Spark or Hadoop implement this pattern natively via a shuffle-by-key.
Worked example
import tempfile, os
def external_dedup(records, key_fn, n_buckets=4, bucket_dir=None):
bucket_dir = bucket_dir or tempfile.mkdtemp()
paths = [os.path.join(bucket_dir, f'b{i}.txt') for i in range(n_buckets)]
files = [open(p, 'w') for p in paths]
for r in records:
k = key_fn(r)
files[hash(k) % n_buckets].write(k + '\n')
for f in files:
f.close()
seen_total = unique_total = 0
for p in paths:
with open(p) as f:
keys = [line.strip() for line in f]
seen_total += len(keys)
unique_total += len(set(keys)) # each bucket fits in memory
return seen_total, unique_total
Run against 1,000 synthetic records containing only 30 distinct ids (heavy duplication), this correctly returns (1000, 30), verified by executing it end to end: the partition-then-dedup-per-bucket approach recovers the true distinct count without ever holding all 1,000 records in one in-memory set.
Trade-offs and pitfalls
- If
n_bucketsis too small, a single bucket can itself overflow memory (especially with a skewed key distribution where one key's variants dominate); this is the same skew problem that shows up in distributed joins. - A hash collision across DIFFERENT keys landing in the same bucket is fine (the in-memory set still separates them correctly by full key value); the only failure mode to avoid is the SAME key landing in different buckets, which cannot happen with a deterministic hash function.
- At true petabyte scale with fingerprints that must survive retries under different generated ids, add a stable content fingerprint (hash of the record's semantic fields) as the dedup key rather than relying on a producer-assigned id that may differ across retries.
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
def 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.
Unlock Full Question Bank
Get access to all 42 Data Transformation and Processing Logic interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.