Data Transformation and Processing Logic Questions

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

An out-of-memory error during shuffle almost always traces to one of three causes: too much data moving through too few partitions (including a skewed key concentrating data on one partition), a join strategy that's shuffling both sides when one side should have been broadcast instead, or executor memory configured too low for the partition sizes actually being produced. Work through them in that order since each is progressively more specific to diagnose.

Structured elaboration

1. Check partition count and size first: too few partitions for the data volume means each one is huge; look at the actual shuffle partition sizes in the engine's UI/metrics before touching anything else.
2. Check for skew specifically: if one or two partitions are dramatically larger than the rest, this is a skewed key, not a general under-provisioning problem, and the fix is salting/broadcast/pre-aggregation (see the join-skew answer), not just adding memory.
3. Check whether a broadcast join should have been used: a dimension table that's small enough to broadcast, but that the optimizer decided to shuffle-join instead (often because of a stale size estimate), causes both sides of the join to shuffle unnecessarily; forcing a broadcast hint (after verifying the real size) removes the shuffle entirely for that join.
4. Check serialization and caching choices: an unnecessarily wide row format, or repeatedly recomputing (rather than persisting) an intermediate result that's reused multiple times downstream, both inflate memory pressure without adding real signal.
5. Only then look at raw executor configuration: if none of the above explains it, the executor's memory allocation relative to actual partition sizes may simply be too tight, but this is a last resort, not a first response, resizing without addressing an underlying skew or plan issue usually just moves the OOM to a larger dataset later.

Worked example

Given the join-skew answer's own verified example, where one key concentrated 1,000 of a 1,040-row dataset onto a single partition, the SAME symptom (an OOM during shuffle) would show up here first as one dramatically oversized partition when you inspect the per-partition row counts, which is the diagnostic signal that tells you to reach for the skew toolkit rather than simply increasing executor memory.

Trade-offs and pitfalls

• Increasing executor memory as the first response to an OOM without checking for skew is a common but often wasteful reaction, it can mask the symptom on today's data volume while leaving the same query fragile against tomorrow's larger or more skewed input.
• Adaptive query execution can auto-mitigate some of this at runtime, but it needs to be enabled and is not a substitute for understanding why the skew exists in the first place, especially if it recurs across many different queries against the same table.
• A join that appears to be the problem may actually be fine; the real cost can be an upstream wide SELECT * bringing far more columns into the shuffle than the query needs, which column pruning fixes independent of anything about the join itself.

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

1. 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.
2. 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.
3. 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.
4. 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

CANON_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.

Direct answer

Maintain a bounded-memory structure that only remembers "recently seen" ids, evicting anything older than a configured TTL (time-to-live), so memory never grows unbounded even on an infinite stream. Two implementation choices trade off differently: an exact structure (a dict/ordered-map of id to last-seen timestamp) gives zero false positives but costs memory proportional to the number of DISTINCT ids within the TTL window; a Bloom filter caps memory at a fixed size regardless of cardinality, at the cost of a tunable false-positive rate.

Structured elaboration

1. Exact, TTL-bounded (LRU-style, least-recently-used): keep an ordered map from id to last-seen time. On each new event, first evict everything older than the TTL from the front of the ordering, then check membership, then record the new id at the back.
2. Probabilistic (Bloom filter): for very high cardinality where even a TTL-bounded exact map is too large, trade exactness for a fixed memory footprint; a false positive means a genuinely-new event gets incorrectly treated as a duplicate and dropped, which is the failure mode to disclose to whoever consumes the output.
3. Correctness consequence of each: exact eviction can let a true duplicate through if it arrives just after its TTL window closed (a real trade-off, not a bug, since unbounded memory is not an option); the Bloom filter can drop a true new event as a false duplicate, silently under-counting.

Worked example

from collections import OrderedDict

class StreamDeduper:
    def __init__(self, ttl_seconds):
        self.ttl = ttl_seconds
        self.seen = OrderedDict()  # id -> timestamp, insertion order approximates eviction order

    def offer(self, event_id, ts):
        self._evict(ts)
        if event_id in self.seen:
            return False
        self.seen[event_id] = ts
        return True

    def _evict(self, now):
        while self.seen:
            oldest_id, oldest_ts = next(iter(self.seen.items()))
            if now - oldest_ts > self.ttl:
                self.seen.pop(oldest_id)
            else:
                break

Run against the sequence ('a',0), ('b',1), ('a',2), ('a',150), ('b',151) with a 100-second TTL, this returns [True, True, False, True, True] (verified): the second 'a' at t=2 is correctly flagged as a duplicate of the one at t=0, but 'a' at t=150 is treated as new, because by then the original t=0 entry has aged out past the 100-second TTL and been evicted.

Trade-offs and pitfalls

• The TTL choice directly trades correctness against memory: too short and you miss real duplicates that arrive just late; too long and memory grows with the event rate times the window.
• A GC/cleanup pass for TTL-based state at real scale needs to run without blocking the offer/check path and without a race that lets a record get evicted and re-admitted as "new" mid-cleanup; a common pattern is a background sweep that only removes entries strictly older than the TTL, using the same clock the offer path uses.
• For a Bloom filter, false positives compound over time unless you periodically rebuild/rotate the filter (e.g. time-bucketed filters), or the effective false-positive rate creeps upward as more items are inserted than the filter was sized for.

Direct answer

When neither file fits in memory, sort each one independently into small on-disk runs, merge those runs into one fully sorted stream per file (external sort), then stream a merge-join over the two now-sorted files, advancing whichever side has the smaller current key, one pass, no random access, bounded memory throughout.

Structured elaboration

1. External sort, phase 1 (create runs): read the input in memory-sized chunks, sort each chunk, and write it back to disk as a "run." This bounds memory to one chunk at a time regardless of the file's total size.
2. External sort, phase 2 (merge runs): k-way merge the sorted runs using a min-heap keyed on the next unread value from each run, producing one fully sorted output stream without ever holding more than one record per run in memory at once.
3. Sort-merge join: once BOTH files are sorted by the join key, walk two pointers forward: if the current keys match, emit the joined row(s) and advance the side(s) that match; if one key is smaller, advance only that side. This never requires either file to be held in memory, only the current position in each.
4. Parallelization: the sort phase parallelizes trivially across files or chunks (each chunk sorts independently); the merge-join phase is harder to parallelize directly, but if the join key range can be pre-partitioned (e.g. by a hash or range of the key), independent sort-merge joins can run in parallel per partition.
5. Skew: a key with a huge number of matching rows on both sides still requires buffering all of them for that key at merge time; if that single key's matches don't fit in memory, this specific technique needs a secondary strategy (spill that key's group to disk, or salt it) layered on top.

Worked example

Two unsorted files A (keys 5,2,8,1,9,3) and B (keys 2,5,7,1), run-size 2 (illustrative, tiny scale so the process is checkable by hand and was verified by execution):

sorted A: 1,2,3,5,8,9
sorted B: 1,2,5,7
streaming merge-join finds matching keys: 1, 2, 5

The join correctly finds exactly the 3 shared keys (1, 2, 5) between the two files, verified end to end (create runs, k-way merge each file, then a two-pointer merge-join over the results) without ever loading either full file into memory.

Trade-offs and pitfalls

• Run size (how much fits in memory per sort chunk) directly determines the number of merge passes needed; too small a run size multiplies the number of k-way merge rounds and the total I/O.
• This technique assumes the join key distribution doesn't have a single value with more matches than fit in memory on either side; if it does, it needs the same skew-handling ideas (salting, partial materialization to disk per key) as a distributed hash/shuffle join would.
• A hash join (build an in-memory hash table on the smaller side) is simpler and often faster when one side genuinely fits in memory; reach for external sort-merge specifically when NEITHER side does.