Data Transformation and Processing Logic Questions

Direct answer

A Bloom filter's size is governed by two closed-form formulas once you fix the expected number of unique keys $n$ and the target false-positive rate $p$: the number of bits $m$ and the number of hash functions $k$. Getting the sizing right matters because a false positive here means a genuinely new record is treated as an already-seen duplicate and silently dropped, not merged, which is a different (and often worse) failure than a duplicate slipping through.

Structured elaboration

1. Bit-array size: $m = -\frac{n \ln p}{(\ln 2)^2}$. This grows linearly with $n$ for a fixed target error rate.
2. Number of hash functions: $k = \frac{m}{n} \ln 2$, rounded to the nearest integer.
3. What a false positive actually costs you here: unlike a hash-collision in a deterministic dedup scheme (which you can and should verify against the full key on a probable collision, since you still have the true key available), a Bloom filter that reports "seen" gives you no way to recover the false positive after the fact; the record is simply not re-processed. That asymmetry is the reason to size deliberately rather than guess.
4. Deterministic-hash collisions as a contrasting technique: a scheme that stores a fixed-width hash (say 128 bits) instead of the raw key avoids the Bloom filter's tunable-error trade-off, at the cost of needing enough bits that a collision is astronomically unlikely (128 bits generally is), plus an operational habit of logging any suspected collision so it can be manually verified rather than silently trusted.

Worked example

For $n = 1{,}000{,}000$ unique keys and a target false-positive rate $p = 0.01$ (1%):
$m = -\frac{1{,}000{,}000 \times \ln(0.01)}{(\ln 2)^2} \approx 9{,}585{,}059 \text{ bits} \approx 1.20 \text{ MB}$
$k = \frac{9{,}585{,}059}{1{,}000{,}000} \times \ln 2 \approx 7$
Both figures were computed and checked programmatically (not eyeballed): roughly 9.6 million bits (about 1.20 MB using decimal megabytes, 1 MB = 1,000,000 bytes) and 7 hash functions, for one million keys at a 1% false-positive target.

Trade-offs and pitfalls

• Doubling $n$ without resizing $m$ pushes the actual false-positive rate well above the target you designed for; a Bloom filter sized for today's cardinality silently degrades as the true key count grows past what it was built for.
• $k$ is a trade-off knob too: more hash functions lower the false-positive rate for a given $m$ up to a point, but increase the per-lookup CPU cost; the formula above gives the value that minimizes the false-positive rate for a fixed $m$ and $n$, not a free lunch.
• If the application cannot tolerate ANY false positive (dropping a genuinely new event silently), a Bloom filter is the wrong tool regardless of sizing; it belongs where an occasional false positive is an acceptable cost for a large, fixed memory footprint.

Direct answer

A defensive casting utility should never raise on a bad value and never silently produce a wrong result; it should return both the successfully cast values and a clear record of exactly which inputs failed, so the caller can decide what to do next rather than discovering a corrupted value downstream.

Structured elaboration

1. Never raise, never guess: a naive .astype(float) on a column with even one unparseable string raises for the WHOLE column in many libraries; a defensive version processes value-by-value (or vectorized with a coercion mode) and reports failures without stopping the whole operation.
2. Report what failed, specifically: returning the indices (or a boolean mask) of failed casts lets the caller inspect exactly which rows need attention, rather than only knowing "something in this column didn't cast."
3. Preserve NULLs as NULLs: a NULL input should stay NULL in the output, not become a cast failure; NULL means "no value," not "invalid value," and conflating the two loses information.
4. Common real-world casting pitfalls to name: locale-dependent number formats (thousands separators, decimal commas), dates that are ambiguous by format alone (02/03/2024), and numeric-looking strings with leading zeros that should be preserved as strings (a zip code, not a number) despite "looking like" they could cast.
5. Empty input, and an input where everything fails: both need to return a well-formed (if empty or all-failed) result rather than raising, a function that works on the happy path but throws on an all-empty or all-invalid input is only half-tested.

Worked example

import pandas as pd

def safe_cast(series, dtype):
    out = pd.Series(index=series.index, dtype='object')
    failed = []
    for idx, v in series.items():
        if pd.isna(v):
            out.loc[idx] = None
            continue
        try:
            out.loc[idx] = dtype(v)
        except (ValueError, TypeError):
            failed.append(idx)
            out.loc[idx] = None
    return out, failed

Against ['25','NA','','unknown', None, '30'] cast to float, this correctly casts index 0 to 25.0 and index 5 to 30.0, correctly leaves the None at index 4 as None (not a failure), and correctly reports indices 1, 2, and 3 ('NA', '', 'unknown') as failed casts, verified end to end, including on an empty Series, which returns an empty result and an empty failure list rather than raising.

Trade-offs and pitfalls

• A per-value Python loop, as above, is clear and safe but slow at large scale; a vectorized pd.to_numeric(series, errors='coerce') achieves the same "don't raise, produce NaN for failures" behavior far faster, at the cost of not directly returning which specific values failed (you'd derive that separately by comparing which entries became NaN that weren't NaN originally).
• Silently converting a failed cast to NaN (rather than surfacing it) is fine for some pipelines and dangerous for others (a NaN feeding a downstream SUM behaves very differently from a value that should have raised an alert); the caller's context determines whether "coerce and report" or "coerce and alert" is correct.
• A value that "looks numeric" but represents an identifier (a zip code, a phone number) is a modeling mistake waiting to happen if cast to a number; casting should be driven by what the column MEANS, not by whether the string happens to parse.

Direct answer

An unexpected row-count increase after a join almost always means the join key is not unique on at least one side, so one row on the "one" side is fanning out against multiple matching rows on the other side. The fix is to find which side has the duplicate key values, decide whether that duplication is real data or a bug, and pre-aggregate before joining if it's real.

Structured elaboration

1. Check first: count rows in each input table, then check whether the join key is actually unique on each side (SELECT key, COUNT(*) FROM t GROUP BY key HAVING COUNT(*) > 1). This single query almost always finds the cause.
2. Sample the fan-out: pull a few of the order_ids that have more than one matching refund row and look at them directly. Is it two legitimate partial refunds on the same order, or a genuine duplicate/bug in the refunds table?
3. Decide the fix: if the duplication is legitimate (partial refunds are a real, expected pattern), pre-aggregate the many-side table (SUM(refund_amount) GROUP BY order_id) before joining, so the join is one-to-one; if it's a bug (accidental double-insert), fix it upstream instead of working around it downstream.
4. Explaining it to a non-technical stakeholder: "each order can have more than one refund, and when we combine the two tables, each of those extra refunds adds an extra row for that order, that's why the count went up even though nothing was duplicated in the original orders."
5. A related but distinct hygiene issue: two tables can share a column name (both have an id or created_at) that means something different in each source; joining them naively can silently produce a nonsensical combined column, or a name collision that overwrites one column with the other depending on the SQL dialect's join-column handling. The fix here is renaming or namespacing columns before the join, and preserving provenance (which source each value came from) rather than letting the join silently pick one.

Worked example

Orders (10,000 rows) LEFT JOIN refunds (200 rows) on order_id, where a few orders have more than one refund row:

SELECT COUNT(*) FROM orders o LEFT JOIN refunds r ON o.order_id = r.order_id;

With orders (1,100),(2,50),(3,75) and refunds (1,20),(1,30),(2,10) (order 1 has two refund rows), the join returns 4 rows, not 3, verified by execution: order 1 fans out into 2 result rows, order 2 into 1, order 3 into 1 (with NULL refund columns) - 2+1+1 = 4. The "missing" row isn't missing at all, it's the extra refund row for order 1 that explains the count increase.

Trade-offs and pitfalls

• Pre-aggregating the many-side table before joining fixes the row-count symptom but can hide a genuine data-quality issue (duplicate refund rows that shouldn't exist); confirm which case you're in before "fixing" it.
• A COUNT(DISTINCT o.order_id) after the join will look correct even when the join is silently fanning out revenue or other summed metrics; row-count sanity checks alone don't catch every symptom of a bad join, check the aggregated business metric too.
• Renaming colliding columns (e.g. o.created_at AS order_created_at, r.created_at AS refund_created_at) before the join is cheap and prevents an entire class of silent-overwrite bugs; do it by convention, not only when a collision is noticed.

Direct answer

Build one reusable, configurable imputation function rather than ad-hoc per-column code: numeric columns get a chosen strategy (commonly the median, which is robust to outliers), categorical columns get their own strategy (commonly the mode or an explicit placeholder), the function must not mutate its input, must preserve dtypes, and should optionally flag which values were imputed so downstream consumers can tell real data from filled-in data.

Structured elaboration

1. Per-column-type strategy, configurable: accept a strategy name per column type (numeric_strategy='median', categorical_strategy='mode') rather than hardcoding one approach, since different columns can legitimately need different handling.
2. Preserve dtype and avoid in-place mutation: work on a copy of the input, and cast the filled column back to its original dtype if the fill operation changed it (a common gotcha: filling an integer column's NaNs with a float median silently upcasts the whole column to float).
3. The <column>_imputed flag: adding a boolean companion column for each imputed column lets a downstream model or analyst distinguish "this was really 42" from "this was imputed to 42," which matters for both model training (it can be a useful feature itself) and for auditability.
4. Edge cases: an entirely-missing column has no median/mode to compute, decide explicitly what happens (skip it, fill with a fixed placeholder, or raise a clear error rather than a cryptic one); an empty input DataFrame should return an empty result without raising.
5. Scaling beyond memory: for a dataset too large to fit in memory, the median specifically can't be computed with one pass (unlike mean, which is just sum/count), it needs either a full-dataset pass to sort or estimate quantiles, or a chunked/approximate quantile estimate; this is worth calling out since "just apply the same function per chunk" doesn't work for median the way it does for mean.

Worked example

import pandas as pd

def impute_dataframe(df, numeric_strategy='median', categorical_strategy='mode',
                      text_fill='unknown', add_flags=True):
    out = df.copy()
    meta = {}
    for col in out.columns:
        if out[col].isna().sum() == 0:
            continue
        if add_flags:
            out[f'{col}_imputed'] = out[col].isna()
        if pd.api.types.is_numeric_dtype(out[col]):
            fill = out[col].median()
        elif pd.api.types.is_object_dtype(out[col]) or isinstance(out[col].dtype, pd.CategoricalDtype):
            mode = out[col].mode()
            fill = mode.iloc[0] if not mode.empty else text_fill
        else:
            fill = None
        out[col] = out[col].fillna(fill)
        meta[col] = {'strategy': numeric_strategy if pd.api.types.is_numeric_dtype(df[col]) else categorical_strategy, 'fill_value': fill}
    return out, meta

Handles an empty DataFrame (no columns have any NaNs to process, returns unchanged) and a column that is entirely NaN (its own median/mode over all-NaN input is itself NaN or empty; the fallback text_fill/placeholder path is what keeps this from raising, which is exactly the edge case a real interview probes).

Trade-offs and pitfalls

• Median is more robust than mean to the very outliers that made you decide to impute in the first place; using mean here quietly lets a few extreme values distort every imputed value.
• Adding a _imputed flag column doubles the column count for every imputed field; for very wide tables this is a real storage/complexity cost, worth it specifically when the imputed-vs-real distinction matters downstream (it usually does for modeling, less often for a quick ad-hoc report).
• Fitting the imputation strategy (the median/mode value itself) needs to happen on TRAINING data only if this feeds a model, computing it fresh on validation/test data (or on live production data) leaks information and produces an inconsistent, non-reproducible pipeline.

Direct answer

Join-induced label leakage happens when a feature you join into a training dataset was only available AFTER the outcome you're predicting was already known, so the model "cheats" by learning a feature that is really just a proxy for the label. The fix is systematic: for every joined feature, check whether its timestamp could postdate the label event, not just whether the join key is correct.

Structured elaboration

1. Common sources: joining in a "resolution" or "outcome" table (chargeback filed, ticket closed, refund issued) that is only populated after the event you're predicting has already happened; joining a snapshot of a dimension table AS OF today rather than as of the training example's own event time, which lets future state leak backward; and joins that bring in aggregates computed over a window that includes the label event itself.
2. Detection: for every feature, ask "could this column's value have existed at prediction time, for this specific row?" A concrete automatable check is comparing the feature's own timestamp (or the timestamp of the table it came from, at ingestion) against the label event's timestamp, and flagging any feature that is not strictly BEFORE it.
3. A telling symptom: a feature that correlates almost perfectly with the label is a strong practical signal to inspect first, real predictive features are rarely that clean.
4. Remediation once found: remove the offending join or rebuild it to only pull the AS-OF-prediction-time value (a point-in-time correct join, not a "latest" join); re-train and re-validate that accuracy drops to something more realistic, which paradoxically is the correct outcome of the fix.
5. Process control: automate the timestamp check as a standing test in the training pipeline (not a one-time manual audit), since leakage tends to reappear whenever a new feature or a new upstream table is added by someone unaware of this specific pipeline's history.

Worked example

import pandas as pd

labels = pd.DataFrame({'txn_id':[1,2,3], 'is_fraud':[0,1,0]})
resolutions = pd.DataFrame({
    'txn_id':[1,2,3],
    'chargeback_filed':[False, True, False],   # only known AFTER investigation concludes
    'resolved_at':['2024-02-01', None, '2024-02-01'],
})
merged = labels.merge(resolutions, on='txn_id')

Here chargeback_filed is perfectly correlated with is_fraud (verified: every row where is_fraud=1 has chargeback_filed=True and vice versa) precisely because it is set by the same investigation process that determined the label; a model trained with this feature would show excellent offline accuracy and then fail in production, where chargeback_filed doesn't exist yet at prediction time.

Trade-offs and pitfalls

• A feature does not need to be a literal copy of the label to leak; a near-perfect correlation is the warning sign, but a subtler leak (an aggregate that includes the label event in its own window) can hide behind a merely "very good" correlation instead of a perfect one.
• Removing a leaky feature and watching accuracy drop is not a failure, it is the fix working; be prepared to explain that to a stakeholder who saw the earlier (wrong) number and now sees a lower one.
• Automated point-in-time joins are more expensive to build and maintain than a simple "latest value" join; the cost is worth it specifically for any feature that could plausibly reflect a post-label-event fact.