Data Cleaning and Business Logic Edge Cases Questions

Covers handling data centric edge cases and complex business rule interactions in queries and data pipelines. Topics include cleaning and normalizing data, handling nulls and type mismatches, deduplication strategies, treating inconsistent or malformed records, validating results and detecting anomalies, using conditional logic for data transformation, understanding null semantics in SQL, and designing queries that correctly implement date boundaries and domain specific business rules. Emphasis is on producing robust results in the presence of imperfect data and complex requirements.

MediumTechnical

12 practiced

In SQL, you have orders(order_id, customer_id, created_at, completed_at, canceled_at, amount) and refunds(order_id, refunded_at, refund_amount). Write a query that returns net revenue by calendar month, counting an order only if it completed, excluding canceled orders, and subtracting any refunds in the month they were issued. How would you make sure month-end boundary cases and null timestamps do not produce incorrect totals?

Sample Answer

I would build this in two steps: first assign each order to the month it completed, then subtract refunds in the month they were issued. A transaction should count only if completed_at is not null and canceled_at is null.

sql

WITH completed_orders AS (
  SELECT
    DATE_TRUNC('month', completed_at) AS month,
    amount
  FROM orders
  WHERE completed_at IS NOT NULL
    AND canceled_at IS NULL
),
monthly_refunds AS (
  SELECT
    DATE_TRUNC('month', refunded_at) AS month,
    SUM(refund_amount) AS refund_total
  FROM refunds
  WHERE refunded_at IS NOT NULL
  GROUP BY 1
),
monthly_orders AS (
  SELECT month, SUM(amount) AS gross_revenue
  FROM completed_orders
  GROUP BY 1
)
SELECT
  COALESCE(o.month, r.month) AS month,
  COALESCE(o.gross_revenue, 0) - COALESCE(r.refund_total, 0) AS net_revenue
FROM monthly_orders o
FULL OUTER JOIN monthly_refunds r
  ON o.month = r.month
ORDER BY 1;

I would also watch month boundaries by using month truncation on the timestamp column itself, not string logic. Null timestamps are excluded in the WHERE clause, so they do not leak into totals.

A useful test is an order completed on Jan 31 and refunded on Feb 1. It should count in January for revenue and in February for the refund, which matches the business rule.

MediumTechnical

10 practiced

You are building training features from event logs where some events arrive late, some are replayed by the pipeline, and some users have actions recorded after the label window should have closed. How would you make sure the feature set matches the state that was actually known at prediction time, and how would you detect leakage introduced by cleaning logic?

HardTechnical

12 practiced

An upstream source sends a daily snapshot of customer records, and the same row may be rewritten for days before it stabilizes. Your pipeline is rerun frequently, and finance depends on the aggregates being identical after every rerun. How would you design the cleaning and deduplication logic so the pipeline is idempotent, preserves history where needed, and does not change prior totals when a snapshot is reprocessed?

MediumTechnical

11 practiced

A dashboard count drops after you join a fact table to a dimension table. You discover some foreign keys are null, and some dimension rows are missing entirely. How would you determine whether the drop comes from the join itself, a misplaced filter, or genuine source-data loss, and what checks would you add before shipping the metric?

MediumTechnical

13 practiced

Two systems feed the same customer dimension, and the same person may appear multiple times with slight differences in name, email, phone, and signup date. If downstream revenue and churn metrics must count each real customer once, how would you build a deduplication rule that is stable over time and avoids merging distinct people by mistake?

Unlock Full Question Bank

Get access to hundreds of Data Cleaning and Business Logic Edge Cases interview questions and detailed answers.

Join thousands of developers preparing for their dream job.