Data Transformation and Processing Logic Questions

Direct answer

NULL means "unknown," not "empty" and not "zero," and it does not behave like an ordinary value in comparisons: any direct comparison involving NULL (NULL = NULL, x = NULL) evaluates to NULL (neither true nor false), which is why WHERE x = NULL never matches anything, even rows where x actually is NULL.

Structured elaboration

1. NULL vs empty string vs zero, by type: in a string column, '' is a real, present value (zero-length text) while NULL means no value was recorded; in a numeric column, 0 is a real, present value while NULL again means unknown; in a date column there is no equivalent "empty" value at all, only NULL, which is a common source of confusion when developers try to use a sentinel date instead.
2. Pitfall 1, filtering: WHERE x = NULL silently returns zero rows; the correct form is WHERE x IS NULL.
3. Pitfall 2, aggregation: COUNT(column) skips NULLs while COUNT(*) counts every row regardless; SUM/AVG over a column with NULLs ignore the NULLs rather than treating them as zero, which changes an average's denominator in a way that is easy to miss.
4. Pitfall 3, joins: a LEFT JOIN with no match produces NULLs for every column from the right-hand table, not zeros or empty strings; code that assumes a numeric join result defaults to 0 will get NULL instead unless it explicitly wraps the column in COALESCE.
5. Handling strategies: COALESCE(x, default) to substitute a value for display or arithmetic; explicit IS NULL / IS NOT NULL checks in filters; and, where the schema allows it, deciding at write time whether "unknown" should even be representable, versus requiring a value.

Worked example

SELECT id FROM users2
WHERE email IS NULL OR TRIM(email) = '';

Against rows (1, NULL), (2, ''), (3, ' '), (4, 'a@x.com'), this correctly returns ids 1, 2, and 3 (verified): NULL, an empty string, and a whitespace-only string are three distinct representations of "no real email," and all three need to be checked explicitly, none of them is caught by checking for only one of the three.

Trade-offs and pitfalls

• COALESCE(amount, 0) is the right fix when "unknown" should be treated as zero for a sum, but the wrong fix when "unknown" should exclude the row from an average entirely (it will pull the average toward zero).
• Comparing a nullable column to another nullable column with plain = silently drops rows where either side is NULL from an equi-join; some engines offer a NULL-safe equality operator (IS NOT DISTINCT FROM in Postgres, <=> in MySQL) for the cases where NULL should be treated as matching NULL.
• Casting a NULL numeric to a string for display purposes can turn it into the literal text "None" or "null" in some client libraries if not explicitly handled, which then fails an IS NULL check silently downstream.

Direct answer

Without a shared unique identifier, entity resolution comes down to three ordered steps: normalize the fields you'll compare, use blocking to cut down the astronomically large number of candidate pairs to a tractable set, then score each remaining candidate pair with a similarity metric and a chosen confidence threshold that decides auto-merge versus human review.

Structured elaboration

1. Normalization first: lowercase, strip punctuation and whitespace, expand common abbreviations, so that "Jon Smith" and "JON SMITH" compare as identical inputs to the similarity step, not as a similarity problem in themselves.
2. Blocking: comparing every pair of records is $O(n^2)$, infeasible past a few hundred thousand records; blocking groups records into buckets that are cheap to compute (first letter of last name, postal code, a phonetic key like Soundex) so only records in the SAME bucket are ever compared to each other. This sacrifices some recall (a true match split across two blocks is missed) for tractability.
3. Similarity scoring: within a block, score each pair with a string-similarity metric (Levenshtein edit distance, Jaro-Winkler for names, or token-set methods for addresses); at truly large scale, MinHash/LSH approximates "which pairs are similar" without materializing all pairwise comparisons, by hashing records into buckets where similar records collide with high probability.
4. Confidence threshold and workflow: pairs scoring above a high threshold auto-merge; pairs in an ambiguous middle band route to a human reviewer with the evidence shown; pairs below a low threshold are left as distinct. Where you set these two thresholds is a precision/recall trade-off made explicit, not hidden inside a single "is duplicate" boolean.
5. At true scale (hundreds of millions of records), an exact pairwise approach is replaced by LSH/MinHash for candidate generation, with the same blocking-then-scoring logic applied within the much smaller candidate set it produces; a hybrid architecture (fast approximate first pass, then exact reconciliation on the surviving candidates) is common precisely because the two techniques catch different failure modes.

Worked example

Records: "Jon Smith", "John Smith", "Jonathan Smith", "Amy Lee", "Amy Le". Using first-letter-of-name blocking and a sequence-similarity ratio (verified computationally):

'Jon Smith' vs 'John Smith'      similarity = 0.95
'Jon Smith' vs 'Jonathan Smith'  similarity = 0.78
'John Smith' vs 'Jonathan Smith' similarity = 0.83
'Amy Lee' vs 'Amy Le'            similarity = 0.92

At an auto-merge threshold of 0.85, "Jon Smith"/"John Smith" and "Amy Lee"/"Amy Le" merge automatically as true near-duplicates, while both pairs involving "Jonathan Smith" (0.78 and 0.83) fall below the bar and are routed to human review rather than silently merged or silently kept apart. This concretely shows why the threshold is a business decision: a lower threshold would auto-merge "Jonathan Smith" into "Jon Smith" too, which may or may not be correct.

Trade-offs and pitfalls

• Blocking is itself a recall risk: if the blocking key is wrong for one of a true pair (a typo in the very field used for blocking), that pair is never even compared. Multiple, differently-keyed blocking passes mitigate this at added cost.
• A pure precision-maximizing threshold (very high) leaves many true duplicates unmerged, which quietly inflates downstream counts (more "unique customers" than really exist); a pure recall-maximizing threshold (very low) risks merging two different real people, which is often the more damaging error since it can misattribute one person's history to another.
• A debugging variant of this problem: two reporting systems disagreeing on a metric by a visible percentage is frequently traced to exactly this kind of inconsistency, one system's fuzzy-matching rules differing from the other's, which is worth naming as a root cause an interviewer may be probing for.

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

1. 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.
2. Resolve the thousands-vs-decimal ambiguity: 1,234.56 (US-style, comma is thousands, period is decimal) and 1.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 bare 1,234 with no decimal at all) may need an explicit locale hint from the source rather than a guess.
3. 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.
4. NULL and invalid markers: an explicit NULL string, 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.
5. 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.

Direct answer

CSV is the lowest common denominator (universally readable, no schema enforcement, row-oriented, poor compression); JSON adds nested structure at the cost of size and columnar-query performance; Parquet and Avro are both binary and schema-aware, but Parquet is columnar (fast for analytical scans and aggregations) while Avro is row-oriented (better for write-heavy streaming and full-row reads), which is the main axis to reason from when picking one.

Structured elaboration

1. CSV: human-readable, universally supported, but no native types (everything is a string until parsed), no compression, and a classic "small files" problem at scale; fine for interchange with external, less-technical parties, poor as an internal storage format at any real volume.
2. JSON: supports nested structure natively, which CSV cannot express at all, but is verbose (repeats field names in every record) and, like CSV, is row-oriented text, so it doesn't get the query-time benefits of a columnar format.
3. Parquet: columnar, so an analytical query that only touches 3 of 50 columns only reads those 3 columns' data off disk; strong compression from storing like-typed values together; supports schema evolution (adding/removing columns) reasonably well; the natural default for anything that will be queried by column-selective analytical workloads.
4. Avro: row-oriented but still binary and schema-aware (with a schema embedded or in a registry); better suited to streaming/write-heavy pipelines where you're appending whole records rather than scanning by column, and where schema evolution rules (backward/forward compatibility) are a first-class concern.
5. Cross-file type mismatches: when reading many partitioned Parquet/ORC files where the SAME column has drifted type across files (int in older files, string in newer), detect this at read time (most engines surface a clear error or a silent up-cast depending on configuration) and prevent it going forward with an enforced, versioned schema at write time rather than letting each writer infer its own types independently.

Worked example

A pipeline ingesting streaming clickstream events (write-heavy, whole-record reads by downstream consumers) writing to Kafka is well-served by Avro with a schema registry; the SAME data, once landed in a data lake and queried by analysts who mostly aggregate a handful of columns across billions of rows, is far better served by Parquet, which is why a "raw ingestion in Avro, analytical storage in Parquet" pattern is common: each format is doing the job it's actually good at, rather than one format being used everywhere out of inertia.

Trade-offs and pitfalls

• Storing frequently-updated, narrow (few-column) records in Parquet, optimized for wide analytical scans, is a mismatch; a row-oriented format or a proper OLTP store fits that access pattern better.
• CSV's total lack of type enforcement means a schema/type bug (a date column suddenly containing a non-date string) isn't caught until something downstream tries to parse it, often much later and far from the actual source of the bug.
• Reconciling a cross-file type mismatch after the fact (rather than preventing it at write time) usually means an explicit CAST or schema-normalization pass before the data can be safely unioned across the affected files; silently letting the query engine auto-coerce types can produce a subtly wrong result (e.g. a numeric column partially read as its string representation) rather than a loud error.

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

1. One partition, several window functions: MAX(...) OVER (PARTITION BY user_id) for the most recent date, COUNT(...) FILTER (WHERE ...) for a windowed count, and AVG(...) FILTER (WHERE ...) for a windowed average can all be computed together against the same joined result, each with its own time-window filter condition.
2. 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.
3. Tenure calculation: days_since_signup is 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 supports FILTER; the equivalent SUM(CASE WHEN ... THEN ... ELSE 0 END) / AVG(CASE WHEN ... THEN ... END) pattern works everywhere FILTER doesn'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.