Machine Learning Engineer Interview Questions — SQL

Start with the query that breaks:

SELECT patient_id,
       DATEDIFF(discharge_date, admit_date) AS stay_days
FROM admissions
WHERE stay_days > 7;

Unknown column stay_days. The column is right there on line two, and the database still cannot see it. The reason is ordering.

The logical sequence is:

1. FROM / JOIN — assemble the working row set
2. WHERE — filter individual rows
3. GROUP BY — collapse rows into groups
4. HAVING — filter groups
5. SELECT — evaluate expressions and assign aliases
6. DISTINCT
7. ORDER BY — sort
8. LIMIT / OFFSET / TOP / FETCH

Aliases come into existence at step 5. WHERE ran back at step 2, three steps too early. ORDER BY sits at step 7, which is why ORDER BY stay_days works perfectly in the same query that rejects it in WHERE.

Two ways out: repeat the expression in WHERE, or push the calculation into a derived table or CTE.

SELECT patient_id, stay_days
FROM (
  SELECT patient_id,
         DATEDIFF(discharge_date, admit_date) AS stay_days
  FROM admissions
) a
WHERE stay_days > 7;

Vendors diverge on the middle of that list, and it catches people who switch databases. MySQL permits aliases in GROUP BY and HAVING. PostgreSQL permits them in GROUP BY and ORDER BY, but not in HAVING. SQL Server permits them only in ORDER BY. None of them permit an alias in WHERE. A query written and tested on MySQL can fail on its first run against SQL Server for exactly this reason.

The word “logical” is doing real work here. This is the order the language guarantees results are equivalent to, not a description of the execution plan. An optimiser is free to push a filter below a join, evaluate a scalar subquery once instead of per row, or skip a sort it can satisfy from an index. Interviewers ask this because the sequence quietly explains WHERE versus HAVING, alias scope, and why HAVING can reference aggregates.

NULL is not a value sitting in the cell. It is a marker saying the value is unknown. So settled_on = NULL is asking whether one unknown quantity equals another unknown quantity, and the honest answer is: unknown. WHERE keeps a row only when the condition is true, and unknown is not true.

SELECT claim_id FROM claims WHERE settled_on = NULL;   -- always empty
SELECT claim_id FROM claims WHERE settled_on IS NULL;  -- pending claims

The same logic makes NULL <> NULL empty too, so you cannot escape by flipping the operator.

Where this genuinely costs people money is NOT IN. Suppose you want policies that have never been claimed against:

SELECT policy_id
FROM policies
WHERE policy_id NOT IN (SELECT policy_id FROM claims);

If a single row in claims has a NULL policy_id, this returns nothing. The reason: x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL, and that last term is unknown, so the whole expression can never be true. Nothing errors out. You just get an empty result and assume every policy has a claim. NOT EXISTS, or a LEFT JOIN with IS NULL, behaves correctly here.

One inconsistency to keep straight: NULL is not equal to itself for comparison, but GROUP BY and DISTINCT treat all NULLs as a single group, and ORDER BY sorts them together. Most engines also allow multiple NULLs in a UNIQUE column for the same reason.

When you do need NULL-safe equality, PostgreSQL and the standard offer IS DISTINCT FROM, and MySQL has the <=> operator.

The real payoff is conditional aggregation. Here are recharge volumes bucketed by ticket size, per telecom circle, in a single pass:

SELECT circle,
       COUNT(*) AS recharges,
       SUM(CASE WHEN amount < 100 THEN 1 ELSE 0 END)               AS under_100,
       SUM(CASE WHEN amount >= 100 AND amount < 500 THEN 1 ELSE 0 END) AS mid,
       SUM(CASE WHEN amount >= 500 THEN 1 ELSE 0 END)              AS premium,
       SUM(CASE WHEN validity_days >= 84 THEN amount ELSE 0 END)   AS long_pack_revenue
FROM recharges
WHERE recharged_on >= '2026-07-01'
GROUP BY circle;

Three separate queries collapse into one, and the table is scanned once.

Now the trap, and it is a common one:

COUNT(CASE WHEN amount < 100 THEN 1 ELSE 0 END)   -- counts every row

COUNT ignores NULL, not zero. The ELSE branch hands it a 0, which is a perfectly good value, so every row gets counted and all three buckets come back identical to the row total. Either drop the ELSE so non-matching rows produce NULL, or keep the ELSE and use SUM. Pick one style and stay with it.

Second thing to watch: CASE stops at the first branch that evaluates to true. Write overlapping ranges in a careless order and rows land in the wrong bucket without any warning — a WHEN amount > 100 sitting above WHEN amount > 500 sends every premium recharge into the mid bucket.

Also worth knowing that simple CASE (CASE status WHEN 'ACTIVE' THEN ...) uses equality internally, so it can never match a NULL status. Searched CASE with an explicit WHEN status IS NULL branch is the way to handle those.

Pick the join by asking which rows you cannot afford to lose. Take flights (every scheduled departure for a day) and bookings.

• INNER JOIN keeps only flights that have at least one booking. Empty flights disappear from the result. Fine for revenue analysis, quietly wrong for a load-factor report, because the worst-performing flights are exactly the ones that vanish.
• LEFT JOIN keeps every flight, filling booking columns with NULL where there is no match. This is the one you want for any report where the left table defines the universe of things being measured.
• RIGHT JOIN is the same operation with the tables reversed. It is legal and it works, but most teams standardise on LEFT and reorder the tables instead, because a query mixing both directions is genuinely hard to read.
• FULL OUTER JOIN keeps unmatched rows from both sides at once. Its natural home is reconciliation: in one pass you see flights with no bookings and bookings pointing at a flight ID that no longer exists in the schedule. Data quality checks between two systems are where this earns its keep.

MySQL has no FULL OUTER JOIN. You emulate it:

SELECT f.flight_id, b.booking_id FROM flights f
LEFT JOIN bookings b ON b.flight_id = f.flight_id
UNION
SELECT f.flight_id, b.booking_id FROM flights f
RIGHT JOIN bookings b ON b.flight_id = f.flight_id;

UNION rather than UNION ALL, since the matched rows appear in both halves.

The related pattern interviewers usually probe next is the anti-join: a LEFT JOIN with WHERE b.booking_id IS NULL gives you flights nobody booked.

Here is the query, joining e-commerce orders to a sparse returns table:

SELECT o.order_id, r.return_reason
FROM orders o
LEFT JOIN returns r ON r.order_id = o.order_id
WHERE r.return_reason = 'DAMAGED';

The join does its job. Every order survives it, and orders that were never returned come out with return_reason set to NULL. Then WHERE runs, and NULL = 'DAMAGED' evaluates to unknown, so every one of those padded rows is discarded. What remains is precisely the inner join. You have written LEFT JOIN and received INNER JOIN semantics, with no warning of any kind.

The fix depends on what you meant.

If you wanted all orders, with the reason shown only when it was a damaged return, the condition restricts matching, so it belongs in ON:

SELECT o.order_id, r.return_reason
FROM orders o
LEFT JOIN returns r
  ON r.order_id = o.order_id
 AND r.return_reason = 'DAMAGED';

Every order stays. Orders with a non-damaged return simply fail the match and get NULL, same as orders with no return at all.

If you genuinely wanted only damaged returns, use an INNER JOIN and say so, so the next person reading the query is not misled.

There is one right-side condition that is safe in WHERE, and it is the exception that proves the rule: WHERE r.order_id IS NULL. That is true only for padded rows, so it deliberately keeps the non-matches and throws away the matches. It is the standard anti-join.

The reason this matters beyond the interview is how quietly it fails. A monthly report joining orders to a partially populated feedback table with WHERE f.rating >= 4 does not error. It returns a smaller, entirely plausible order count, and it can sit in a dashboard for two quarters before anyone reconciles it against the source system.

Working rule: conditions on the preserved table go in WHERE, conditions on the optional table go in ON. For an INNER JOIN the distinction makes no difference to the result, which is where the habit of putting everything in WHERE comes from.

A gym chain has members, plus payments and class_bookings, both with many rows per member. This query looks reasonable:

SELECT m.member_id, SUM(p.amount) AS total_paid
FROM members m
JOIN payments p        ON p.member_id = m.member_id
JOIN class_bookings cb ON cb.member_id = m.member_id
GROUP BY m.member_id;

A member with 6 payments and 20 class bookings produces 6 × 20 = 120 rows, and SUM(p.amount) comes out 20 times too high. The nasty part is that the inflation factor differs per member, so total revenue is not off by a clean multiple you might notice. It is just wrong, in a way that looks like it could be right.

A join is not a lookup. It pairs every left row with every right row that satisfies the condition. One join to a many-side is often exactly what you want: joining orders to order_items should repeat the order row once per item. The damage starts when you hang two independent one-to-many branches off the same key, because they form a Cartesian product with each other inside every member.

Diagnose before you assume. Compare the row count to the base table, or check the join key directly:

SELECT member_id, COUNT(*) FROM payments GROUP BY member_id HAVING COUNT(*) > 1;

The reliable fix is to collapse each branch to one row per key before joining:

SELECT m.member_id, p.total_paid, cb.classes_booked
FROM members m
LEFT JOIN (SELECT member_id, SUM(amount) AS total_paid
           FROM payments GROUP BY member_id) p  ON p.member_id = m.member_id
LEFT JOIN (SELECT member_id, COUNT(*) AS classes_booked
           FROM class_bookings GROUP BY member_id) cb ON cb.member_id = m.member_id;

Resist the shortcut of SUM(DISTINCT p.amount). Two genuine payments of ₹2,499 in different months are indistinguishable after DISTINCT, so one of them silently disappears. It patches the number without fixing the cardinality, which is worse than leaving it visibly broken.

Worth doing in the interview itself: before writing anything, ask whether the relationship is one-to-one or one-to-many. Candidates who ask that get read as people who have debugged a wrong revenue figure at least once.

One query on a music streaming log makes all three visible:

SELECT COUNT(*)                 AS play_events,     -- 4,812,000
       COUNT(user_id)           AS identified_plays,-- 4,105,000
       COUNT(DISTINCT user_id)  AS listeners,       -- 318,400
       COUNT(DISTINCT track_id) AS tracks_played    -- 27,900
FROM plays
WHERE played_on >= '2026-08-01';

Four different numbers, four different questions. The gap between the first two is anonymous playback with no logged-in user. The gap between the second and third is simply that people listen more than once.

Where this turns into a bug is after a LEFT JOIN. Count the wrong side and you count the padding:

SELECT a.artist_id, COUNT(*) AS plays
FROM artists a
LEFT JOIN plays p ON p.artist_id = a.artist_id
GROUP BY a.artist_id;

An artist nobody streamed still contributes one NULL-padded row, so COUNT(*) reports 1 play instead of 0. COUNT(p.play_id) reports 0, because the padded row has NULL there. Any time a count follows an outer join, count a column from the optional table, never the star.

On performance, the folklore that COUNT(*) is slower than COUNT(id) is worth dropping. In PostgreSQL and MySQL/InnoDB, COUNT(*) is treated as a row count and the planner picks the cheapest index it can scan. COUNT(id) may be no faster and cannot use certain shortcuts if the column is nullable. What genuinely costs is COUNT(DISTINCT ...), which has to deduplicate through a hash or a sort, and gets expensive on high-cardinality columns. Approximate functions exist for that, such as APPROX_COUNT_DISTINCT in SQL Server and BigQuery, when an exact figure is not needed.

Multi-column distinct is a portability trap. MySQL accepts COUNT(DISTINCT user_id, track_id). PostgreSQL requires COUNT(DISTINCT (user_id, track_id)) with a row constructor, and SQL Server supports neither, so you concatenate or use a subquery.

There is no hierarchy in a GROUP BY list. The database builds a key out of every grouped column, joined together, and every distinct key becomes one output row. GROUP BY market, crop and GROUP BY crop, market produce the same set of rows with the same aggregates. Only the column order in a SELECT or an ORDER BY changes what you see.

For daily mandi arrivals:

SELECT market_name,
       crop,
       SUM(quantity_quintal)   AS arrivals,
       AVG(modal_price_per_qtl) AS avg_price
FROM mandi_arrivals
WHERE trade_date = '2026-08-19'
GROUP BY market_name, crop;

If Bengaluru’s Yeshwanthpur mandi traded onion, tomato and potato that day, that mandi contributes three rows. A mandi that traded only onion contributes one. There is no fixed rectangle, only the combinations that actually occur in the filtered data.

That last point is the one that trips reports. GROUP BY cannot invent a group for a pair with no rows behind it. A crop that arrived at no mandi on that date is absent from the output, not present with a zero. If the consumer of your report needs a fixed set of rows, you build the grid separately and join to it.

The counting rule is worth internalising because it explains a class of “my numbers changed” tickets. Adding a column to GROUP BY can only split existing groups, never merge them. Someone adds grade to the list for a bit more detail, the row count goes from 900 to 2,600, and the totals per market are now spread across three grade rows each. Nothing is wrong. The report just answers a finer question than the one the dashboard tile was built for.

Every non-aggregated column in the SELECT must appear in the GROUP BY. PostgreSQL and SQL Server enforce this. MySQL enforces it too under ONLY_FULL_GROUP_BY, which is on by default from 5.7 onwards, but a legacy server with that mode disabled will accept a stray column and hand back a value from an arbitrary row in the group. The one relaxation everyone allows: columns functionally dependent on the grouped primary key can be omitted in PostgreSQL and modern MySQL.

Both of these find pharmacy SKUs whose current stock is below the average stock level for their category. The nested version:

SELECT s.sku_id, s.category, s.on_hand
FROM stock s
WHERE s.on_hand < (SELECT AVG(on_hand) FROM stock x WHERE x.category = s.category);

The CTE version:

WITH category_avg AS (
  SELECT category, AVG(on_hand) AS avg_on_hand
  FROM stock
  GROUP BY category
)
SELECT s.sku_id, s.category, s.on_hand
FROM stock s
JOIN category_avg c ON c.category = s.category
WHERE s.on_hand < c.avg_on_hand;

The second one reads in the order you would explain it aloud. Compute the category averages, then compare each SKU against them. That matters more than it sounds, because the version you can debug in six months is the version that stays correct. You can also comment out the final SELECT and run the CTE alone, which you cannot do with a deeply nested inline subquery.

Reuse is where a CTE stops being a style preference. Reference category_avg twice, once for understocked SKUs and once for overstocked, and the subquery version means writing the same aggregation twice and hoping both copies get edited together.

On plans, be careful about the advice you repeat. PostgreSQL before version 12 always materialised CTEs, which made WITH an optimisation fence: predicates could not be pushed down into it, and a CTE inside a big query was sometimes a real performance problem. From 12 onwards a CTE used once and not recursive is inlined by default, and you can force either behaviour with MATERIALIZED or NOT MATERIALIZED. MySQL 8.0 also merges CTEs into the outer query where it can. So on current versions the two forms above typically produce the same plan. The correct answer in the room is that it depends on the engine and version, and that you would check EXPLAIN rather than assert it.

One capability a subquery simply does not have: recursion. Walking an unbounded hierarchy or generating a series needs WITH RECURSIVE, which is CTE-only.

For the positive check, pick whichever reads better:

-- accounts that received a transaction flagged for review
SELECT a.account_no FROM accounts a
WHERE a.account_no IN (SELECT account_no FROM flagged_txns);

SELECT a.account_no FROM accounts a
WHERE EXISTS (SELECT 1 FROM flagged_txns f WHERE f.account_no = a.account_no);

Same result, and on PostgreSQL and MySQL 8.0 usually the same plan, because both get rewritten into a semi-join. The advice that “EXISTS is faster than IN” comes from older optimisers, particularly MySQL 5.x, where a correlated IN subquery could be re-executed per outer row. Repeating it as a general truth about current engines is how candidates get caught out.

Negate the check and the two stop being interchangeable:

SELECT a.account_no FROM accounts a
WHERE a.account_no NOT IN (SELECT account_no FROM flagged_txns);

If flagged_txns.account_no is nullable and even one row is NULL, this returns zero accounts. NOT IN expands into a chain of inequality comparisons joined by AND, and a comparison against NULL is never true, so the chain can never succeed. The query does not fail. It reports that every account has a flagged transaction, which in a compliance report is a conclusion someone might act on.

NOT EXISTS has no such behaviour. It asks whether a matching row exists, gets a straight yes or no, and is the form to write by default:

SELECT a.account_no FROM accounts a
WHERE NOT EXISTS (SELECT 1 FROM flagged_txns f WHERE f.account_no = a.account_no);

Two smaller points worth having ready. IN with a literal list is a different thing from IN with a subquery, and very long literal lists, in the tens of thousands, degrade badly and are better handled by a temporary table or a join. And the SELECT 1 inside EXISTS is convention rather than optimisation. The engine never evaluates the select list, so SELECT * performs identically, but SELECT 1 signals to the reader that no column is being returned.

GROUP BY answers “what is the total per category” and hands back one row per category. The moment you also want the individual rows, you are asking a different question and GROUP BY is the wrong tool.

For a supermarket’s daily till log:

SELECT bill_id,
       aisle,
       line_total,
       SUM(line_total) OVER (PARTITION BY aisle)                       AS aisle_total,
       ROUND(100.0 * line_total / SUM(line_total) OVER (PARTITION BY aisle), 2) AS pct_of_aisle
FROM bill_lines
WHERE bill_date = '2026-08-19';

Every line item survives, and each one now carries its aisle total and its share of that total. Doing this with GROUP BY needs an aggregate in a subquery joined back to the detail, which is three times the text for the same result.

The clause-order rule catches nearly everyone once. Window functions are evaluated after WHERE, GROUP BY and HAVING, and before ORDER BY. Two consequences follow. First, the window only ever sees rows that survived WHERE, so filtering to one aisle makes the “total” the total of that aisle alone. Second, you cannot filter on a window result in WHERE:

WHERE SUM(line_total) OVER (PARTITION BY aisle) > 50000   -- error, every engine

The value does not exist yet at that point. Wrap the query in a CTE or derived table and filter on the outer level. That single restriction is behind most “top N per group” solutions you will see.

Windows and GROUP BY are not mutually exclusive. A window function can operate on the output of a GROUP BY, since it runs later:

SELECT aisle, SUM(line_total) AS aisle_total,
       SUM(SUM(line_total)) OVER () AS store_total
FROM bill_lines GROUP BY aisle;

The nested SUM looks wrong on first reading and is correct. The inner one aggregates the group, the outer one is a window over the grouped rows.

OVER () with nothing inside treats the entire result set as one partition, which is the neat way to put a grand total on every row for percentage calculations.

Take five cricket batters ordered by runs scored in a tournament, with two of them level on 412:

SELECT player, runs,
       ROW_NUMBER() OVER (ORDER BY runs DESC) AS rn,
       RANK()       OVER (ORDER BY runs DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY runs DESC) AS dense
FROM tournament_stats;

Read the Bose row. RANK says 4th because two players occupy second place and third is consumed. DENSE_RANK says 3rd because it counts distinct scores, not positions. Neither is more correct; they answer different questions. A leaderboard shown to the public almost always wants RANK, because “joint second, then fourth” is how sport reports it. DENSE_RANK is what you want for “the third highest distinct score”, which is a different claim entirely.

The trap with ROW_NUMBER is that it fabricates an order where none exists. Naik and Rathore are genuinely level, and ROW_NUMBER hands one of them 2 and the other 3. Which one is not defined, and it can change between runs, after an index rebuild, or when the query goes parallel. If you use ROW_NUMBER to pick “the top scorer” and there is a tie, you have written a query that returns different answers on different days.

The fix is to make the ordering deterministic by adding a tiebreaker that is unique:

ROW_NUMBER() OVER (ORDER BY runs DESC, player_id)

Now it is arbitrary but stable, which is what you want for deduplication and pagination.

ROW_NUMBER is still the right choice for exactly those jobs, where you need one row per group and do not care which. Deduplication, “latest record per key”, and stable paging all want distinct numbers and no gaps.

ORDER BY inside OVER is required for RANK and DENSE_RANK, since there is nothing to rank without it. ROW_NUMBER technically permits its absence in some engines, but the result is then non-deterministic and there is no good reason to write it.

Before writing anything, ask the interviewer one question: if two events are priced identically at the top, is the next price down the second highest or the third? People who start typing immediately give the wrong answer half the time, and the asking is a large part of what is being marked.

Prices for concert tickets: 4500, 4500, 3200, 2800.

-- third highest DISTINCT price -> 2800
SELECT price FROM (
  SELECT price, DENSE_RANK() OVER (ORDER BY price DESC) AS dr
  FROM ticket_prices
) t WHERE dr = 3;

-- third row by price -> 3200
SELECT price FROM ticket_prices ORDER BY price DESC LIMIT 1 OFFSET 2;

Two defensible answers, four hundred rupees apart. “Third highest price” in ordinary speech nearly always means the third distinct price, so DENSE_RANK is the safer default, but say why you chose it.

The window function has to be filtered in an outer query. WHERE DENSE_RANK() OVER (...) = 3 is rejected everywhere, because windows are evaluated after WHERE.

Two edge cases worth raising unprompted. If the table holds only two distinct prices, both queries return zero rows rather than NULL, so calling code that expects a scalar needs to handle an empty result. And NULL prices sort first under ORDER BY price DESC in PostgreSQL and last in MySQL, which shifts every position by one on a nullable column. WHERE price IS NOT NULL removes the ambiguity and costs nothing.

The pre-window classic still comes up on older MySQL:

SELECT MAX(price) FROM ticket_prices
WHERE price < (SELECT MAX(price) FROM ticket_prices
               WHERE price < (SELECT MAX(price) FROM ticket_prices));

It works, it naturally handles ties as one position, and it does not generalise past about N=3 without becoming unreadable. Worth knowing it exists; not worth writing when window functions are available.

LIMIT ... OFFSET is MySQL and PostgreSQL syntax. SQL Server needs OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY, and it requires an ORDER BY.

Run both over a set of sensor readings from a manufacturing line and count the output.

-- 12 machines in, 12 rows out
SELECT machine_id, AVG(vibration_mm_s) AS avg_vibration
FROM sensor_readings GROUP BY machine_id;

-- 4.6 million readings in, 4.6 million rows out
SELECT reading_id, machine_id, vibration_mm_s,
       AVG(vibration_mm_s) OVER (PARTITION BY machine_id) AS machine_avg
FROM sensor_readings;

Identical arithmetic. The first throws away every reading once it has the average; the second keeps all of them and hangs the average off each one, which is what you need to flag readings running above their own machine’s baseline.

Three practical differences follow from that.

Filtering works in opposite directions. HAVING filters GROUP BY output in the same query. A partitioned result cannot be filtered in place at all, because window functions are computed after WHERE and HAVING, so anything comparing a row to its window average needs a CTE and an outer WHERE.

The SELECT list has no restrictions under PARTITION BY. With GROUP BY, every non-aggregated column must be grouped or aggregated. With a window, the row is still there, so you can select any column you like alongside the windowed value.

Ordering is available inside a window and not in a group. PARTITION BY machine_id ORDER BY recorded_at gives each partition an internal sequence, which is what makes LAG, running totals and ranking possible. GROUP BY has no concept of order within a group.

They compose, and the combination is genuinely useful:

SELECT shift_id, machine_id,
       AVG(vibration_mm_s) AS shift_avg,
       AVG(AVG(vibration_mm_s)) OVER (PARTITION BY machine_id) AS machine_avg_of_shifts
FROM sensor_readings
GROUP BY shift_id, machine_id;

GROUP BY runs first and produces one row per shift per machine. The window then runs over those grouped rows. The nested aggregate reads oddly and is correct for exactly that reason.

On cost, PARTITION BY is not free. The engine sorts or hashes by the partition key, so on a large table it can be heavier than the equivalent GROUP BY, which at least gets to shrink its output. An index matching the partition and order keys is what keeps it cheap.

WITH monthly AS (
  SELECT DATE_TRUNC('month', enrolled_on) AS month,
         SUM(premium) AS premium
  FROM mutual_fund_sips
  GROUP BY 1
)
SELECT month,
       premium,
       LAG(premium) OVER (ORDER BY month) AS prev_month,
       ROUND(100.0 * (premium - LAG(premium) OVER (ORDER BY month))
             / NULLIF(LAG(premium) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly;

The ORDER BY inside OVER is what defines “previous”. It has nothing to do with the ORDER BY of the outer query, and getting them out of sync is a common source of results that look shuffled.

Three things to have ready when this is asked.

The first row returns NULL, always, because there is no earlier row to reach. That is usually correct and should be presented as “no comparison available” rather than patched to zero, which would render as a 100% jump on the chart. If a default genuinely makes sense, LAG takes a third argument: LAG(premium, 1, 0).

NULLIF on the denominator is not decoration. One month with zero collection and the percentage calculation aborts the whole query in PostgreSQL. NULLIF turns the zero into NULL and the expression yields NULL for that row while everything else still computes.

The failure that actually reaches production is the missing month. LAG steps back one row, not one month. If April had no SIP enrolments, April has no row, and May’s LAG reaches all the way back to March while the column header still says “previous month”. The comparison is silently wrong and nothing errors. The fix is a date spine: generate every month, LEFT JOIN the actuals onto it, and let LAG operate over a complete series.

Partitioning applies the same way. PARTITION BY scheme_code ORDER BY month restarts the comparison for each fund, and the first month of every scheme correctly returns NULL rather than borrowing the last month of the previous one.

For a year-on-year comparison, LAG with an offset of 12 works only when every month is present, which is the same reason the spine matters. A self join on a date expression is more robust when the series is sparse.

Both stack one result set on top of another. Only one of them does extra work.

SELECT reg_no, marks FROM exam_results_2025
UNION ALL
SELECT reg_no, marks FROM exam_results_2026;

UNION ALL passes rows straight through. Swap in UNION and the engine must compare every row against every other row to eliminate exact duplicates, which means sorting or hashing the whole combined set. On two ten-million-row result sets that is a substantial amount of work, sometimes spilling to disk, to remove duplicates that in this case cannot exist — a 2025 result and a 2026 result differ in at least the year.

So the rule: use UNION ALL unless you specifically need deduplication and cannot get it more cheaply upstream.

Beyond speed, UNION can also quietly delete correct data. Combine two fee payment feeds where a student legitimately paid ₹5,000 twice on the same date, and if every selected column matches, UNION collapses those into one row. Your total is now ₹5,000 short, with no error and no obvious symptom. UNION ALL keeps both.

The mechanical requirements apply to both. Each branch needs the same number of columns, in the same order, with compatible types. Column names come from the first branch and the rest are ignored, so aliasing the second branch does nothing. A type mismatch is where behaviour diverges by engine: PostgreSQL is strict and will refuse to union text with integer, while MySQL applies its usual coercion and may return something surprising rather than an error.

ORDER BY belongs at the end, applied once to the entire combined result, not to the individual branches. Some engines reject an ORDER BY inside a branch outright; others accept it and ignore it, which is worse because it looks like it worked.

One case where UNION is the right call: merging customer contact lists from two acquired businesses, where the same person genuinely appears in both and you want a single row per person. Deduplication is the actual requirement there, not an accident.

Three timestamps define the extraction, and naming them before writing SQL is most of the work. The observation date is the moment the model would make a prediction. Everything before it is feature territory. The label window is a fixed span after it, and the label is whatever happened inside that span.

For a fitness-app subscription churn model with a 30-day horizon:

WITH cfg AS (SELECT DATE '2026-05-01' AS as_of, 30 AS horizon_days),
population AS (
  SELECT s.subscriber_id
  FROM app_subscriptions s, cfg
  WHERE s.started_on <= cfg.as_of - INTERVAL '90 days'
    AND (s.cancelled_on IS NULL OR s.cancelled_on > cfg.as_of)
),
labels AS (
  SELECT p.subscriber_id,
         CASE WHEN s.cancelled_on IS NOT NULL
               AND s.cancelled_on >  cfg.as_of
               AND s.cancelled_on <= cfg.as_of + (cfg.horizon_days || ' days')::INTERVAL
              THEN 1 ELSE 0 END AS churned_30d
  FROM population p
  JOIN app_subscriptions s ON s.subscriber_id = p.subscriber_id
  CROSS JOIN cfg
)
SELECT * FROM labels;

Four decisions are embedded there.

The population is subscribers active as at the observation date. Someone who had already cancelled cannot churn again, and including them adds a block of guaranteed negatives that shifts the class balance.

The 90-day tenure requirement guarantees every row has enough history for the features to mean something. Without it, a subscriber who joined yesterday has zero for every activity feature, and the model learns that zero activity predicts nothing rather than that the data is thin.

The label window is strictly after the observation date. A cancellation on the observation date itself is not a prediction, it is the present.

The maturity rule is the one people skip. Running this with as_of set to a date less than 30 days ago means the label window has not finished, and every unresolved subscriber is labelled 0. Your training data then says churn fell sharply in recent weeks, and the model inherits that. Only extract observation dates that are at least horizon_days in the past.

Two things to say unprompted. Multiple observation dates give more rows, and the same subscriber then appears several times, so any train/test split must be by time or by subscriber rather than random, or the same person sits on both sides. And write the config values into the output table as columns, so the exact boundaries travel with the dataset.

SELECT l.merchant_id, l.label_date, l.defaulted_on_settlement,
       m.account_status, m.risk_tier
FROM settlement_labels l
JOIN merchant_master m ON m.merchant_id = l.merchant_id;

merchant_master holds current state. For a label dated nine months ago, account_status is what the status is today, and a merchant who defaulted was moved to SUSPENDED shortly afterwards. The model learns that suspended merchants defaulted, which is true, useless, and unavailable at prediction time. AUC of 0.97 on a risk problem is not a triumph; it is a symptom.

Three shapes this takes, and all three come from the same root.

Current-state dimension joined to a historical row, as above. The fix is a point-in-time join against a history table carrying validity dates.

A field populated by the process that resolves the outcome. settlement_hold_reason, recovery_agent_assigned, days_past_due_final. These are filled in after the fact, and they will not exist at scoring time.

A timestamp that encodes the outcome. last_updated_at on the merchant record often sits within hours of the default, so even without reading the status the model can infer it from a date column that looks harmless.

The audit that catches these systematically is not statistical, it is procedural. For each feature, ask one question: at the moment the prediction would be made, was this value already written and would it have had this value? Anything you cannot answer with a confident yes goes on a list. Then check the columns your gradient-boosted model ranks highest, since leakage nearly always dominates importance.

An automated check worth building: for each candidate feature, compute the correlation between the feature and the label, and separately check whether the feature’s source row was written after the label date. A field whose write timestamp postdates the label is leaked by construction.

If dropping the leaked column takes AUC from 0.97 to 0.61, that is your real model. Painful and correct. A 0.61 model deployed beats a 0.97 model that collapses on the first day in production and destroys the team’s credibility with the risk function.

The obvious leak is a frame that extends forward:

AVG(fare_paid) OVER (PARTITION BY rider_id
                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

That averages the rider’s entire history, including trips taken after the observation date. Nobody writes it deliberately. It appears when someone adds an unbounded frame to make LAST_VALUE behave, then reuses the same OVER clause elsewhere by copy-paste.

The subtle leak is the one that survives review:

SUM(complaint_count) OVER (PARTITION BY rider_id ORDER BY event_date
                           ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS complaints_30d

Nothing here reads the future. But if the label is “did this rider file a complaint on this date”, the current row is the label event, and the feature has just counted it. The model discovers a feature that is perfectly predictive and worthless. Ending the frame one row early fixes it:

ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING

A third trap sits in the default. Writing an ORDER BY with no frame applies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE works on values rather than positions. Every row sharing the current timestamp is treated as part of the current row, so a batch of trips loaded with the same truncated timestamp all see each other. On event data with second-level or day-level granularity that is a real leak, and it is invisible in the query text because the frame is not written down. Always write the frame.

The audit habit worth carrying: for every window function in a feature query, read the frame out loud and ask what the latest row it can touch is, then compare that against the observation timestamp. If the answer is “the current row” and the label derives from the current row, it leaks.

A self-join version has exactly the same exposure. The join predicate is the frame, and e.event_date <= f.observation_date includes the observation day itself. < f.observation_date does not. The mechanism differs; the discipline is identical.

Two analysts pull the average credit score of loan applicants and get different numbers. One wrote AVG(credit_score). The other wrote SUM(credit_score) / COUNT(*). Both queries are valid, and one of them is answering a different question than the analyst thinks.

SELECT COUNT(*)            AS applications,   -- 10000
       COUNT(credit_score) AS scored,         -- 8200
       SUM(credit_score)   AS total_score,
       AVG(credit_score)   AS mean_score
FROM loan_applications;

So AVG(credit_score) divides by 8,200. The manual version divides by 10,000 and produces a mean roughly 18% lower, because 1,800 thin-file applicants with no bureau history were silently treated as zeros.

Which is correct depends on what NULL means in that column. For a mean credit score, an unknown score should be excluded, and AVG is right. For “average disbursed amount per application” where a NULL means nothing was disbursed, zero is the true value and AVG(COALESCE(disbursed_amount, 0)) is what you want. Decide that before you write the query, not after someone questions the number.

The failure that reaches production most often is the empty set. Filter to a branch that made no disbursals last month and COUNT(*) returns 0, but SUM(disbursed_amount) and AVG(disbursed_amount) both return NULL, not ₹0. The dashboard tile renders blank, and any downstream arithmetic on that NULL propagates NULL through the rest of the calculation. COALESCE(SUM(disbursed_amount), 0) is cheap insurance on every reporting query.

COUNT(DISTINCT credit_score) also skips NULL. And COUNT(1) is identical to COUNT(*), not a NULL question at all, though interviewers sometimes hide it inside this one.

A public library wants members who have never borrowed a book. Three ways to ask for it:

-- 1. LEFT JOIN + IS NULL
SELECT m.member_id
FROM members m
LEFT JOIN loans l ON l.member_id = m.member_id
WHERE l.member_id IS NULL;

-- 2. NOT EXISTS
SELECT m.member_id
FROM members m
WHERE NOT EXISTS (SELECT 1 FROM loans l WHERE l.member_id = m.member_id);

-- 3. NOT IN
SELECT m.member_id
FROM members m
WHERE m.member_id NOT IN (SELECT member_id FROM loans);

The first two always agree. The third agrees only while loans.member_id contains no NULLs. Add one row with a NULL member ID, from a bulk import or a nullable legacy column, and version 3 returns an empty set. Not an error, not a warning, just zero members. Because of how NULL comparisons resolve, the expression can never be true once a NULL is in the list.

Ship NOT EXISTS. It is immune to that, it stops scanning the moment it finds the first match, and it states the intent in the query text rather than encoding it as a side effect of a NULL check.

The LEFT JOIN form has a detail that catches people in review: the IS NULL test must be on a column that is never NULL in a matched row. Test the join key or the right table’s primary key. If you write WHERE l.returned_on IS NULL instead, you have quietly changed the question to “members with an outstanding loan”, which is a different report entirely.

On scale, the LEFT JOIN form materialises every matching pair before the filter throws them away. A member with 400 loans generates 400 rows so that 399 of them can be discarded. NOT EXISTS never builds those rows. On small tables the optimiser often converts one form into the other and the plans converge, but on a wide loans table the difference is measurable, and it goes the same way on both MySQL and PostgreSQL.

The dangerous version and the safe version differ by one character:

# unsafe
cur.execute(f"SELECT * FROM tuition_leads WHERE city = '{city}'")

# safe
cur.execute("SELECT * FROM tuition_leads WHERE city = %s", (city,))

The second form sends the statement and the value over separate channels. The database compiles the query first, then binds the value, so nothing inside city can ever become executable SQL.

Injection is the headline risk and not the only one. A perfectly innocent name containing an apostrophe breaks the interpolated query with a syntax error, so the same bug that lets an attacker in also makes your pipeline fail on real data. Parameter binding also lets the database reuse a prepared plan across calls, which matters in a scoring loop.

Placeholder style varies by driver: %s for psycopg2 and mysql-connector, ? for sqlite3, and named :param styles in SQLAlchemy. The placeholder is never quoted, even for strings. Writing '%s' reintroduces the problem.

Two cases parameters cannot cover.

Identifiers. Table and column names are not values, so binding does not work. Validate against an allowlist you control, or use the driver’s identifier-quoting helper, such as psycopg2.sql.Identifier. Never format a raw string into that position.

Variable-length IN lists. Generate the right number of placeholders and pass a matching tuple, or on PostgreSQL use WHERE id = ANY(%s) with a Python list, which handles any length with one parameter.

An ORM protects the parameterised paths and does not protect raw fragments. session.execute(text(f"... {user_input}")) is exactly as exposed as the psycopg2 version. The safety comes from binding, not from the library.

One practical habit for ML pipelines: keep the SQL in a file or a constant with named parameters, and pass the run date, model version and thresholds as parameters. Interpolating a date into the query text is the same anti-pattern and it also destroys reproducibility, since the query text and the data it produced no longer travel together.

Start with evidence rather than intuition. BigQuery exposes INFORMATION_SCHEMA.JOBS_BY_PROJECT with bytes billed per query; Snowflake has QUERY_HISTORY and WAREHOUSE_METERING_HISTORY with credits consumed.

SELECT user_email,
       ROUND(SUM(total_bytes_billed) / POWER(1024, 4), 2) AS tib_billed,
       COUNT(*) AS queries
FROM `region-asia-south1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY user_email
ORDER BY tib_billed DESC
LIMIT 20;

Cost is almost always concentrated. A handful of queries, or one scheduled job running far more often than anyone realised, usually accounts for most of the increase.

The two levers differ by platform. BigQuery bills on bytes scanned, so the fixes are structural: partition on the date column and always filter on it, cluster on the columns you filter by, and stop selecting columns you do not use. On a columnar store SELECT * reads every column file, and an ML feature query touching 8 of 90 columns pays for 90 unless you name them.

Snowflake bills on warehouse time, so the fixes are operational: right-size the warehouse, set auto-suspend to a minute or two, and separate workloads so a heavy training extract does not force the dashboard warehouse to a larger size. Result caching is free on both platforms and is defeated by CURRENT_TIMESTAMP() in the query text, which is a common accidental cost.

Two patterns specific to ML work. A feature query rerun for every experiment should be materialised once into a table and read many times. And exploratory work on a full table should sample first, since a hash-based subset gives the same intuition at a fraction of the scan.

Prevention is the part interviewers listen for. Set per-user and per-project quotas, enable a maximum bytes-billed setting on ad-hoc queries so a mistake fails instead of running, and put a weekly cost-by-user query on a dashboard. Discovering a runaway query on the invoice is discovering it three weeks late.

You write only the SELECT:

-- models/marts/fct_orphanage_donations.sql
{{ config(materialized='incremental', unique_key='donation_id') }}

SELECT d.donation_id,
       d.donor_id,
       d.received_on,
       d.amount,
       c.campaign_name
FROM {{ ref('stg_donations') }} d
LEFT JOIN {{ ref('dim_campaigns') }} c ON c.campaign_id = d.campaign_id

{% if is_incremental() %}
WHERE d.received_on > (SELECT MAX(received_on) FROM {{ this }})
{% endif %}

dbt compiles this into real SQL, substituting each ref() with the fully qualified table name for the target environment, and wraps it in whatever DDL the materialisation requires. Nobody writes CREATE TABLE.

ref() is doing two jobs, and the second is the one that matters. It resolves the name, and it declares an edge in the dependency graph. dbt derives run order from those edges, so stg_donations builds before this model without anyone maintaining a schedule. Hardcode the table name instead and the model still compiles, still runs, and will run in the wrong order the first time the upstream changes. Use source() for raw tables outside dbt’s control, for the same reason.

Four materialisations cover most work. view creates a view, cheap to build and paying full cost on every read. table rebuilds fully each run, simple and correct and expensive at scale. incremental inserts or merges only new rows, which is what a large event fact needs. ephemeral inlines the SQL as a CTE into whatever refs it, producing no database object at all.

Incremental is where care is required. The is_incremental() block is skipped on first build and on --full-refresh, so the model must be correct both ways. A watermark of MAX(received_on) misses late-arriving rows, so overlap the window and let unique_key deduplicate through a merge. Get this wrong and the table quietly loses rows for months.

For ML work the payoff is that feature definitions live in version control, with tests attached and lineage visible, so training and downstream consumers read the same table rather than two divergent copies of one definition.

Cumulative donations for a crowdfunding campaign:

SELECT donation_date,
       amount,
       SUM(amount) OVER (ORDER BY donation_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM donations
WHERE campaign_id = 88;

Drop the ROWS clause and the query still runs. That is the part worth understanding, because the default is not what most people picture.

Adding ORDER BY to a window without specifying a frame applies RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE works on values, not positions, so every row sharing the current row’s ORDER BY value is treated as part of the current row. Three donations on 12 August all get the same running total, the total including all three. It looks like the running total froze for three rows and then jumped.

ROWS counts physical rows instead, so each of those three donations accumulates one at a time.

Neither is wrong. A daily cumulative chart genuinely wants the RANGE behaviour. A donor-by-donor ledger wants ROWS. Choose deliberately and write the frame down, so the next reader does not have to know the default to understand the query.

Two more points. If you aggregate to one row per date first, ties disappear and the distinction stops mattering, which is the cleanest way to sidestep it for reporting.

And an ORDER BY that is not unique makes ROWS non-deterministic between rows with the same key, just as it does for ROW_NUMBER. Add the donation ID as a tiebreaker if reproducibility matters.

The frame is also how you get moving windows. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives a trailing seven-row sum, and with a complete date spine that is a trailing seven-day sum. PARTITION BY campaign_id restarts the accumulation for each campaign.

Window functions including frames arrived in MySQL 8.0. On 5.7 a running total needs a correlated subquery or a session variable, and neither is pleasant.

Implicit feedback gives you one class. A jewellery marketplace logs which designs a shopper opened and nothing about the thousands they did not, so the model has nothing to contrast against.

The uniform-random approach:

WITH positives AS (
  SELECT DISTINCT shopper_id, design_id FROM design_views
  WHERE viewed_on >= DATE '2026-06-01' AND viewed_on < DATE '2026-07-01'
),
candidates AS (
  SELECT p.shopper_id, d.design_id
  FROM (SELECT DISTINCT shopper_id FROM positives) p
  CROSS JOIN LATERAL (
    SELECT design_id FROM active_designs
    ORDER BY RANDOM() LIMIT 20
  ) d
)
SELECT c.shopper_id, c.design_id, 0 AS label
FROM candidates c
WHERE NOT EXISTS (SELECT 1 FROM positives p
                  WHERE p.shopper_id = c.shopper_id AND p.design_id = c.design_id);

The LATERAL with a LIMIT samples per shopper instead of building the full cross product, which for 4 lakh shoppers and 60,000 designs would be 24 billion rows. Never materialise the cross join and then sample; sample inside it.

The NOT EXISTS is the important half. Without it, some generated negatives are items the shopper actually viewed, and you are training the model that a genuine interest is a non-interest. Check against all observed interactions, not only the ones in your training window, and include purchases and wishlist adds rather than views alone.

Three design choices worth raising.

Random versus exposed negatives. A uniformly random design is one the shopper never saw, so the model partly learns what gets displayed rather than what gets clicked. Negatives sampled from items that were actually shown and not clicked are much harder and much more informative, and they need impression logs. If those logs exist, say you would use them.

Popularity. Uniform sampling draws mostly from the long tail, so popular items rarely appear as negatives and the model over-predicts them. Sampling proportional to popularity, or a blend, is the usual correction.

Ratio. Four to ten negatives per positive is a common starting range. The ratio changes the model’s output scale, so predicted probabilities are no longer calibrated to real click rates and need adjusting if anything downstream consumes them as probabilities.

One thing an unclicked item is not: a confirmed negative. The shopper may simply never have scrolled that far.

Rerunning the training query six months later is not reproduction. Between then and now, late-arriving telemetry landed, a dimension row was overwritten, and someone corrected a batch of mislabelled records. The query is unchanged and the dataset is not.

Three levels of rigour, and knowing which one you have is the honest answer.

Persisted snapshot. Write the extracted training set to an immutable table or a Parquet file at a versioned path, and never modify it. Compute a hash over the sorted contents and store it in the model registry alongside the query text, the parameter values, the run timestamp, the row count and the label distribution.

CREATE TABLE training_sets.crop_yield_v7 AS
SELECT ... ;

INSERT INTO model_registry (model_id, dataset_table, row_count, positive_rate,
                            extracted_at, query_git_sha)
SELECT 'yield-forecast-v7', 'training_sets.crop_yield_v7',
       COUNT(*), AVG(label::numeric), NOW(), 'a91f3c2'
FROM training_sets.crop_yield_v7;

Time-travel. Warehouses with snapshot isolation over versioned storage, such as Delta, Iceberg or Snowflake, let you query a table as at a past timestamp or version. That reproduces the inputs rather than the output, which is nearly as good and much cheaper in storage.

Nothing. The source is a mutable table with no history and no snapshot was taken. Then the honest answer is that the dataset cannot be recovered, and you say so rather than rerunning the query and presenting the result as the original. Producing a lookalike dataset and calling it the training data is worse than admitting the gap, because every subsequent debugging conclusion rests on it.

Two things the registry entry buys you. When production performance degrades, comparing the current feature distribution against the stored training distribution tells you within minutes whether the input data drifted or the model is genuinely stale. And when a regulator or a customer asks how a decision was made, a hash linking a specific dataset to a specific model version is the difference between a documented answer and a reconstruction.

Store the query in version control and record the commit SHA. The table alone does not tell you how it was built.

The training query aggregates two years of warehouse data in SQL. The serving path recomputes the same feature in application code against the transactional database, under a latency budget. Both were written from the same description and by different people.

They will disagree. The training version uses a 30-day window ending the day before; the serving version uses the last 30 days including today. One excludes reversed transactions, the other does not. One treats a missing value as zero, the other as null. None of these is a bug that any test catches, because each implementation is internally consistent.

The result is a model trained on one distribution and scored on another. Offline metrics stay excellent, production performance is worse than expected, and there is nothing in the logs pointing at the cause.

The fix is single definition, dual materialisation. The feature is computed once, in SQL, and written to two stores fed from the same computation:

-- one definition, run in batch
CREATE OR REPLACE TABLE feature_store.dealer_features AS
SELECT dealer_id,
       DATE '2026-08-19' AS feature_date,
       COUNT(*) FILTER (WHERE order_date >= DATE '2026-08-19' - 30
                          AND order_date <  DATE '2026-08-19') AS orders_30d,
       SUM(order_value) FILTER (WHERE order_date >= DATE '2026-08-19' - 90
                                  AND order_date <  DATE '2026-08-19') AS value_90d
FROM tractor_dealer_orders
GROUP BY dealer_id;

The offline store keeps every historical feature_date for point-in-time training joins. The online store keeps only the latest row per dealer, in a key-value system that answers in single-digit milliseconds. The values are identical because they came from one query.

Two consequences to be able to discuss. The online store is as fresh as the last batch, so a feature that must reflect the last five minutes cannot be served this way and needs a streaming computation, with the same single-definition discipline applied to the streaming job.

And you should monitor agreement rather than assume it. Sample entities daily, compute the feature both ways, and alert on divergence. A mismatch on 2% of dealers usually traces to a boundary condition such as new dealers with no history, and finding it through monitoring beats finding it through a quarter of poor predictions.

The tempting one-liner is the wrong one:

UPDATE training_rows t
SET repeat_visit_ratio = (SELECT ... FROM clinic_visits v WHERE v.patient_id = t.patient_id);

That computes one value from all available history and writes it onto every historical row, including rows observed in 2023. Every one of those now carries information from 2024 and 2025. The feature is leaked for old rows and correct for new ones, which is worse than uniformly wrong, because the model learns a relationship that only exists in the training set.

The backfill has to respect each row’s own observation date:

UPDATE training_rows t
SET repeat_visit_ratio = f.ratio
FROM (
  SELECT t2.row_id,
         COUNT(*) FILTER (WHERE v.visit_date >= t2.as_of - INTERVAL '180 days')::numeric
         / NULLIF(COUNT(*), 0) AS ratio
  FROM training_rows t2
  JOIN clinic_visits v
    ON v.patient_id = t2.patient_id
   AND v.visit_date < t2.as_of
  GROUP BY t2.row_id
) f
WHERE f.row_id = t.row_id;

Every aggregation is bounded by that row’s as_of. Slower, and the only version that is correct.

Three checks before trusting the result.

Source availability. If the visits table only starts in 2025, rows observed in 2023 cannot have this feature. Filling them with zero tells the model that patients had no prior visits, which is false. Leave them null and let the model handle missingness, or restrict training to the period where the feature exists, and state which you chose.

Definitional consistency. The backfilled values must match what the daily job will produce tomorrow. Recompute one recent day both ways and compare row by row. A backfill that quietly uses a different window length than the forward job creates a discontinuity at the changeover date, and the model sees a feature that shifts distribution partway through the training period.

Distribution over time. Plot the mean of the new feature by observation month. A step change usually means an upstream data collection change rather than a real trend, and training across that boundary teaches the model two different things.

Retrain only after those checks pass. A backfilled feature that fails any of them will look strong in validation for exactly the wrong reason.

Do not update the entity table. An in-place update on eight crore rows is expensive, it destroys the previous scores, and a half-completed run leaves a table where some accounts carry today’s model and some carry last week’s, with nothing to distinguish them.

Write to a scores table instead:

CREATE TABLE model_scores (
  account_id   BIGINT NOT NULL,
  scored_on    DATE   NOT NULL,
  model_version TEXT  NOT NULL,
  run_id       TEXT   NOT NULL,
  score        REAL   NOT NULL,
  PRIMARY KEY (account_id, scored_on, model_version)
) PARTITION BY RANGE (scored_on);

Partitioning by date makes each run a partition write, which is fast, and makes retention a partition drop rather than a delete over crores of rows. The run identifier lets you isolate and remove one bad run without touching anything else.

Loading follows the delete-then-insert pattern on the partition, inside a transaction, so a retry produces the same table rather than doubling it. Bulk loading beats row-by-row inserts by orders of magnitude here: stage the scores as a file, copy them in, then index.

Reading the current score is a join to the latest scored date:

SELECT a.account_id, a.segment, s.score
FROM credit_accounts a
LEFT JOIN model_scores s
       ON s.account_id = a.account_id
      AND s.scored_on = DATE '2026-08-19'
      AND s.model_version = 'v4';

LEFT JOIN, deliberately. An account the run missed comes back with a null score, which is visible and correct. An inner join drops it, and the consumer silently receives a smaller population than the one it asked about. Count the nulls as a run health check before publishing.

Two operational points worth raising. Serving from a warehouse table is fine for another batch process and hopeless for a real-time API, so push the latest scores into a key-value store if anything needs a fast lookup. And keeping history rather than overwriting is what makes score drift analysis possible: comparing this week’s distribution to last week’s is the first thing you will want when someone says the model is behaving strangely.

The query looks harmless:

SELECT COUNT(*)  FILTER (WHERE played_at >= NOW() - INTERVAL '7 days')  AS games_7d,
       AVG(duration_sec) FILTER (WHERE played_at >= NOW() - INTERVAL '30 days') AS avg_duration_30d
FROM esports_matches
WHERE player_id = 88214;

One row out, and it still aggregates every match that player has ever recorded. On a columnar warehouse this is worse, not better: those systems are optimised for scanning millions of rows in parallel and carry per-query overhead measured in hundreds of milliseconds, so a query returning one row costs nearly as much as one returning a lakh.

Four things add up to the 860 milliseconds. Query planning and coordination overhead. Scanning the player’s history. Connection acquisition, which on a warehouse can dominate everything else. And any queueing behind other work on a shared cluster, which is why the p99 is far worse than the p50.

The fix is to move the computation out of the request path entirely. A batch or streaming job computes the features on a schedule and writes one row per player to a key-value store; serving does a keyed get in one or two milliseconds and computes nothing.

Staleness is the trade-off you accept and must state. Features refreshed hourly mean the model scores on hour-old activity, which is fine for a matchmaking quality model and unacceptable for detecting an ongoing cheating session. For genuinely real-time signals, maintain a counter updated by the event stream rather than querying history.

Two further points worth making. If a SQL lookup is unavoidable, it must be an indexed point lookup against a transactional database with connection pooling, not an aggregate against a warehouse, and the feature must already be materialised as a column.

And NOW() inside a serving query is its own problem beyond latency: the training version used a fixed observation date and the serving version uses wall-clock time, so the two windows differ. Latency work often surfaces a skew bug that was there all along.

CREATE EXTENSION vector;

CREATE TABLE recipe_embeddings (
  recipe_id  BIGINT PRIMARY KEY,
  model_ver  TEXT NOT NULL,
  embedding  vector(768)
);

CREATE INDEX ON recipe_embeddings
  USING hnsw (embedding vector_cosine_ops);

SELECT recipe_id, 1 - (embedding <=> :query_vec) AS cosine_similarity
FROM recipe_embeddings
WHERE model_ver = 'text-v3'
ORDER BY embedding <=> :query_vec
LIMIT 10;

<=> is cosine distance in pgvector, <-> is L2 and <#> is negative inner product. The operator in the ORDER BY must match the operator class the index was built with, or the index is ignored silently and you get a sequential scan that still returns correct results, just slowly. That mismatch is the most common reason someone reports pgvector being unusably slow.

Without an index the query is exact and reads every row. At a few lakh vectors that is seconds, not milliseconds. HNSW gives better recall and query speed than IVFFlat at the cost of slower build and more memory; IVFFlat needs the table populated before you build it, since it clusters on existing data.

Approximate means approximate. The index may miss some true neighbours, controlled by ef_search on HNSW or probes on IVFFlat, and there is a real recall-latency curve you should measure on your own data rather than accept the defaults.

The operational issue that catches teams is the filter. WHERE model_ver = 'text-v3' combined with an ANN scan can return fewer than the requested rows, because the index finds candidates first and the filter removes some afterwards. Partial indexes per model version, or a separate table, avoid the surprise.

Re-embedding with a new model invalidates everything. Old and new vectors are not comparable, so distances between them are meaningless. Keep the model version as a column, write the new vectors alongside, and cut over once the backfill is complete rather than mixing them.

The honest boundary: at tens of crores of vectors with heavy query volume, a dedicated vector store usually wins. Keeping vectors in the same database as your relational data is worth a lot when you need to filter and join, and that convenience is the main reason to choose it.

Prediction drift is the cheapest signal and needs no labels:

SELECT scored_on,
       AVG(score)                                                    AS mean_score,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY score)            AS p50,
       PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY score)           AS p95,
       AVG(CASE WHEN score > 0.5 THEN 1.0 ELSE 0 END)                AS positive_rate
FROM warranty_claim_scores
WHERE scored_on >= CURRENT_DATE - 60
GROUP BY scored_on ORDER BY scored_on;

A positive rate that moves from 4% to 11% over a fortnight is worth investigating regardless of whether accuracy has changed yet.

Input drift needs a stored baseline. Compare bucketed shares rather than means, because a bimodal shift can leave the mean untouched:

WITH live AS (
  SELECT WIDTH_BUCKET(claim_amount, 0, 200000, 20) AS bucket,
         COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () AS live_share
  FROM scoring_inputs WHERE scored_on = CURRENT_DATE - 1
  GROUP BY 1
)
SELECT l.bucket, b.baseline_share, l.live_share,
       (l.live_share - b.baseline_share)
       * LN(NULLIF(l.live_share, 0) / NULLIF(b.baseline_share, 0)) AS psi_term
FROM live l JOIN training_baseline b ON b.bucket = l.bucket;

Summing psi_term gives the population stability index. The commonly used rule of thumb treats values under 0.1 as stable and above 0.25 as a material shift, though those thresholds are convention rather than statistics, so calibrate them against your own history of false alarms before wiring them to a pager.

Outcome quality comes last because labels lag. Warranty claims resolve over weeks, so accuracy on a given day’s predictions is not computable for weeks, and any monitoring built only on accuracy is blind precisely when you need it.

Two things worth saying unprompted. Null rate per feature belongs in the same dashboard, because a feature that silently goes 80% null is a broken pipeline masquerading as drift, and the two need different responses. And segment the drift, since an overall stable distribution can hide one region shifting hard while others compensate.

Distributed joins shuffle rows so that matching keys meet on the same worker. If 40% of your rows carry one key, 40% of the work goes to one machine, and the job finishes when that machine finishes.

Confirm before treating it. Look at the key distribution rather than guessing:

SELECT device_key, COUNT(*) AS rows
FROM ad_impression_log
GROUP BY device_key
ORDER BY rows DESC
LIMIT 20;

The classic culprit is a placeholder. A device key of 'unknown' or -1 assigned to every unidentified impression is not a real entity, and it can carry more rows than every genuine key combined. That case has the easiest fix: filter those rows out of the join, handle them separately, and union the results back.

When the hot key is legitimate, salting spreads it:

WITH salted_left AS (
  SELECT *, CONCAT(device_key, '-', CAST(FLOOR(RANDOM() * 20) AS TEXT)) AS salted_key
  FROM ad_impression_log
),
exploded_right AS (
  SELECT d.*, CONCAT(d.device_key, '-', CAST(s.n AS TEXT)) AS salted_key
  FROM device_dim d
  CROSS JOIN UNNEST(GENERATE_ARRAY(0, 19)) AS s(n)
)
SELECT ...
FROM salted_left l JOIN exploded_right r ON r.salted_key = l.salted_key;

The large side gets a random salt appended to the key; the small side is replicated once per salt value so every fragment still finds its match. The hot key now spreads across twenty workers. The cost is twenty times the rows on the dimension side, which is acceptable only because that side is small.

Broadcasting is simpler where it applies. If one side fits in worker memory, send a full copy to every worker and skip the shuffle entirely. Most engines do this automatically below a size threshold, and you can hint it. Skew disappears because nothing is partitioned by the key.

Two things worth naming. Skew also arises from partitioning and window functions, not only joins: a PARTITION BY on a skewed key has the same problem with the same fixes. And aggregating before joining often removes the issue entirely, since collapsing a hot key to one row per key means there is nothing left to concentrate.

Assertion tests are the cheap layer and catch most regressions. Each is a query returning rows only when something is wrong.

-- grain is one row per bus per service date
SELECT bus_id, service_date, COUNT(*)
FROM fct_bus_utilisation
GROUP BY bus_id, service_date
HAVING COUNT(*) > 1;

-- occupancy must be a valid proportion
SELECT trip_id, occupancy_ratio FROM fct_bus_utilisation
WHERE occupancy_ratio < 0 OR occupancy_ratio > 1;

dbt expresses these declaratively as unique, not_null, accepted_values and relationships tests in a YAML file, plus custom tests as SQL. Empty result means pass.

What assertions cannot do is prove the logic is correct. A transformation can produce unique, non-null, in-range values that are wrong. For that you need fixtures: a handful of input rows written by hand, run through the transformation, compared against an expected output you also wrote by hand.

Fixtures are where you cover the cases production has not produced yet. A null in the join key. A bus with two service records on the same date. A trip spanning midnight. An occupancy of exactly zero. Those are the situations that break a query eighteen months later, and no amount of testing against today’s data will surface them.

Structure a fixture test as input CTEs, the transformation under test, and a comparison:

WITH expected AS (SELECT ... UNION ALL SELECT ...),
actual AS (SELECT * FROM {{ ref('fct_bus_utilisation') }})
SELECT * FROM (
  SELECT * FROM expected EXCEPT SELECT * FROM actual
  UNION ALL
  SELECT * FROM actual EXCEPT SELECT * FROM expected
) diff;

The two-way EXCEPT catches both missing and extra rows, where a one-way comparison misses half the failures.

Two habits that make this stick. Every bug you fix gets a fixture reproducing it, so it cannot return. And keep tests fast enough to run on every change, since a suite taking twenty minutes gets skipped exactly when someone is in a hurry, which is when it was most needed.

Airflow gives every run a logical date representing the interval being processed. That is not the same as the moment the task executes, and the gap is exactly where the bug lives.

-- templated
DELETE FROM fct_lab_test_results WHERE report_date = '{{ ds }}';

INSERT INTO fct_lab_test_results (report_date, lab_id, tests, avg_turnaround_hours)
SELECT DATE '{{ ds }}', lab_id, COUNT(*), AVG(turnaround_hours)
FROM stg_lab_reports
WHERE report_date = DATE '{{ ds }}'
GROUP BY lab_id;

Write CURRENT_DATE instead and three things break. A task that fails on Tuesday and is cleared on Thursday processes Thursday, leaving Tuesday permanently missing. A ninety-day backfill runs ninety times and writes the same day ninety times. And the query is no longer reproducible, since rerunning it later gives a different answer with no change to the code.

The {{ ds }} value is the logical date, and understanding which day that refers to matters. In Airflow’s data-interval model, a daily DAG scheduled at 02:00 with a logical date of the 3rd runs after the 3rd’s interval has closed, so it processes the 3rd while the wall clock reads the 4th. Newer versions expose data_interval_start and data_interval_end explicitly, which are clearer than reasoning about ds and its offsets.

Pair templating with an idempotent write, as above: delete the partition then insert it, in one transaction. Templating alone gives you the right date; idempotency is what makes reruns and backfills safe. Without both, a backfill either duplicates rows or leaves gaps.

Two practical points. Keep the SQL in a .sql file referenced by the operator rather than inline in the DAG. It stays readable, it can be linted and diffed properly, and it can be run manually against the warehouse for debugging with the parameters substituted by hand.

And be careful about concurrency during backfills. Ninety tasks writing ninety different partitions is fine; ninety tasks with overlapping windows is not, and the delete-then-insert pattern will interleave badly. Bound the parallelism or make the partition boundaries strictly disjoint.

The failure that sends most ML pipelines to raw SQL is the N+1 pattern. Loading five lakh sensor readings as objects and iterating over a related attribute issues one query per object, so a job that should be one aggregate scan becomes five lakh round trips. It works in development against a thousand rows and collapses in production.

Reach for raw SQL when:

• The work is set-based. Window functions, multi-CTE transformations, conditional aggregation. Most ORMs can express these awkwardly and the resulting code is harder to read than the SQL would have been.
• You need to see and tune the exact query. Optimising something you cannot read is guesswork, and ORM-generated SQL is frequently not what you expect.
• Volume is large. Materialising rows as objects costs memory and time you do not need to spend when the result is an aggregate.
• The SQL needs to be reviewed by a data engineer or an analyst who does not read your ORM.

Keep the ORM when:

• You are reading or writing individual records with validation and relationships.
• Schema migrations are managed through it, which is a genuine benefit worth preserving.
• Portability across engines actually matters, though it matters less often than people claim.

A hybrid is normal and not a compromise. SQLAlchemy Core, or session.execute(text(...)) with bound parameters, gives you the connection pooling, transaction handling and parameter binding while you write the SQL yourself. Dropping to raw SQL never means dropping parameter binding; string interpolation is a separate and worse decision.

For an ML feature pipeline the usual split is: raw SQL for the extraction and aggregation that produces the training table, ORM for the application that consumes the scores. The two live in different code paths with different demands.

Keep the raw SQL in .sql files rather than embedded in Python strings. It stays syntax-highlighted, greppable and diffable, and an analyst can run it directly against the warehouse when a number looks wrong.