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.

MediumTechnical

60 practiced

Implement a defensive type-casting utility that attempts to cast a column (or Series) to a target type, returning both the successfully cast values and the indices that failed to cast, rather than raising an exception or silently producing bad data. Discuss common pitfalls of casting strings to numbers or dates in real-world data, and how your function behaves on an empty input or an input where every value fails to cast.

Sample Answer

MediumTechnical

37 practiced

A numeric column contains both NULLs and extreme outliers. Describe a pragmatic decision process for handling both before analytics: statistical detection methods (IQR, z-score), business-rule thresholds, when to impute versus flag versus drop, and how you would document and test these decisions so downstream analysts understand what happened to their data.

Sample Answer

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.

MediumTechnical

34 practiced

You receive timestamps in several inconsistent formats and time zones from different sources (for example '2024-01-02 13:00', '01/02/2024 1pm PST', an epoch value, or a date string with an ambiguous day/month order). Design a robust approach to parse and normalize all of these into timezone-aware UTC timestamps. Discuss how you detect and handle ambiguous dates (02/03/2024), missing timezone information, and DST transitions, and how you would detect signs of clock skew or duplicated timestamps between sources before trusting the normalized result.

Sample Answer

MediumTechnical

42 practiced

Implement a Python generator that reads a large CSV file in fixed-size chunks, applies a transformation to each chunk, and yields (or persists) the transformed results without ever loading the whole file into memory. Handle header rows, malformed lines, and different encodings, and describe how the job could resume from the last successfully processed chunk after a failure.

Sample Answer

Direct answer Read the file in fixed-size chunks using a generator, apply the transformation to each chunk as it's read, and yield (or persist) results incrementally, so memory use stays bounded by the chunk size regardless of the file's total size; periodically persist enough state to disk that a crash can resume from the last completed chunk rather than the beginning. Structured elaboration Generator, not a function that returns a list: a generator yields one chunk's results at a time, so the caller (or a downstream consumer) can process and discard each chunk before the next one is even read, which is what keeps memory bounded. Chunk size as the memory/overhead knob: too small a chunk size adds per-chunk overhead without much memory benefit; too large defeats the purpose; size it as a fraction of the machine's available memory, not an arbitrary constant. Handle real-world CSV messiness inline: header rows need to be read once and not treated as data; malformed lines need to be logged and skipped without crashing the whole read; different encodings need to be detected or configured rather than assumed. Resumability: persist a marker (byte offset, or chunk index) after each successfully processed chunk, so a restart can skip straight to the first unprocessed chunk instead of reprocessing everything. Backpressure, if downstream consumption is slower than reading: a generator naturally provides this for free (it only produces the next chunk when asked), which is one of the underrated reasons to prefer this shape over eagerly reading everything into memory and then filtering. Worked example pythondef process_large_csv(file_path, chunk_size, transform_fn): with open(file_path) as f: header = f.readline() chunk = [] for line in f: try: chunk.append(parse_row(line, header)) except ValueError: continue # malformed line skipped, logged elsewhere if len(chunk) >= chunk_size: yield transform_fn(chunk) chunk = [] if chunk: yield transform_fn(chunk) The chunked-processing shape itself was verified end to end (generating 250 synthetic records to a temp file plus one deliberately malformed line): processing correctly completed all 250 valid records across multiple chunks of at most 100 records held in memory at any point, with the malformed line skipped rather than crashing the run, confirming both the memory bound and the fault tolerance the pattern is meant to provide. Trade-offs and pitfalls A generator-based approach only bounds memory if the CALLER also consumes it incrementally; wrapping the whole thing in list(process_large_csv(...)) defeats the entire point by materializing everything anyway. Resuming from a byte-offset checkpoint assumes the source file doesn't change between the crash and the restart; if the file could be appended to or rewritten in the interim, the checkpoint needs to also validate that the file's earlier content hasn't shifted underneath it. Encoding detection is a real source of subtle corruption (a file that's actually Latin-1 but is read as UTF-8 will parse "successfully" while silently mangling non-ASCII characters); when the encoding isn't guaranteed, validate it rather than assuming UTF-8 by default.

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.