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.

HardTechnical

34 practiced

During feature or dataset preparation for a supervised model, a join can accidentally introduce label leakage: for example, joining in a table that contains information only available after the outcome is known, or a join that looks ahead in time. Describe common sources of this kind of leakage, how you would detect it automatically, and how you would design a rollback or re-computation process once leakage is found.

Sample Answer

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

HardTechnical

33 practiced

Some aggregations (sum, count) are associative and trivially parallelizable across a distributed dataset with a guaranteed deterministic result. Others, like median or percentile, are not. Discuss how you would compute an approximate percentile at scale with a mergeable, deterministic algorithm (for example a t-digest or histogram sketch), and separately, how you would implement a scalable approximate 'distinct count' (for example unique users in the last 30 days) using a structure like HyperLogLog, including the accuracy/memory trade-off of each.

Sample Answer

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

58 practiced

Implement a reusable data-cleaning function that imputes missing values in a DataFrame: numeric columns filled with the column median, categorical columns filled with the mode (or a placeholder), with a configurable strategy per column type. The function should preserve original dtypes, not mutate the input in place, handle an empty DataFrame and a column that is entirely missing without raising an exception, and optionally add a boolean ' <column>_imputed' flag so downstream consumers can tell which values were real versus imputed. Discuss how you would adapt this to a dataset too large to fit in memory.

Sample Answer

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 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. 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). 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. 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. 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 pythonimport 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.

MediumTechnical

28 practiced

Given a table of per-entity transactions (an entity id, an amount, and a timestamp), write a SQL query using window functions that computes a rolling N-day average or sum per entity per day, including days where the entity had no activity (rather than silently skipping them). Explain how you generate the calendar of dates needed for this and discuss the RANGE versus ROWS framing for the window.

Sample Answer

Unlock Full Question Bank

Get access to all 34 Data Transformation and Processing Logic interview questions and detailed answers.

Join thousands of developers preparing for their dream job.