Data and AI engineering fundamentals

ETL transforms data in a separate processing tier before it lands, because storage was expensive and the warehouse could not be trusted with heavy computation. ELT lands the raw data first and transforms it inside the warehouse or lakehouse, usually in SQL. ELT won because object storage became almost free and warehouse compute became elastic and billed separately, so there is no longer a reason to discard the raw form. The real prize is replayability: when a transformation turns out to be wrong, you fix the logic and re-derive from the raw layer rather than asking the source system for last quarter again — which it frequently cannot give you. The costs are that raw storage grows forever unless somebody owns retention, and that transformation now competes with analyst queries for the same warehouse compute.

That running it again over the same logical window leaves the same state as running it once. The mechanism is almost always that a run owns a slice of the output and replaces that slice wholesale — delete-and-insert inside one transaction, an atomic partition overwrite, or a merge keyed on a business identifier. Appending is what breaks it, because an append has no way to know it already happened. Idempotency matters more here than almost anywhere else in engineering because every orchestrator retries, every operator eventually reruns a day by hand, and neither of those is a failure you get to prevent. A pipeline that is only correct when it runs exactly once is a pipeline that is occasionally wrong.

The job succeeded at loading and then failed afterwards, which is the ordinary case rather than the unlucky one.

Daily job for 2026-07-26, appends into fact_orders

  22:00  read source WHERE order_date = '2026-07-26'    -> 412,908 rows
  22:04  INSERT INTO fact_orders ...                       412,908 rows appended
  22:06  downstream aggregate step throws, task marked FAILED
  22:11  scheduler retries the task from the beginning
  22:15  INSERT INTO fact_orders ...                       412,908 rows appended

  fact_orders holds 825,816 rows for one day.
  Revenue for 26 July is exactly double. Nothing errored.

The insert was durable and the task was not, so the retry replayed a step that had already committed. Note that no alert can catch this: every task eventually reported success, row counts grew as expected for a busy day, and the only evidence is a revenue figure that looks like a good Sunday.

The fix is to make the run own the partition rather than add to it:

BEGIN;
DELETE FROM fact_orders WHERE order_date = DATE '2026-07-26';
INSERT INTO fact_orders (order_id, order_date, customer_key, amount_minor)
SELECT order_id, order_date, customer_key, amount_minor
  FROM stg_orders
 WHERE order_date = DATE '2026-07-26';
COMMIT;

Three details decide whether this actually works. The delete and the insert must be one transaction or an interrupted run leaves the day empty, which is a different wrong answer rather than a safe one. The partition predicate must be derived from the run's logical date, not from now(), or a rerun of an old day deletes today. And where the output is late-arriving by nature — a fact that can be corrected weeks later — the honest shape is a merge on the business key rather than a window overwrite, because the window you would have to replace grows without bound.

The tempting non-fix is "check whether rows exist before inserting". That is a read-modify-write across two systems with no shared transaction, so two concurrent runs both see nothing and both insert.

Running a pipeline over historical windows, either because the logic changed or because the data was wrong. It is dangerous for three reasons that are easy to discover the hard way. It multiplies load: six months of daily jobs launched at once is 180 times the normal concurrency against the same source and warehouse, which is how a backfill becomes an incident in the operational database. Downstream consumers see history change underneath them, so a report someone already sent now disagrees with itself. And a backfill that is not idempotent duplicates 180 days rather than one. The disciplined version runs windows in bounded batches, uses a separate resource pool, and is announced to whoever depends on the numbers.

A directed acyclic graph of tasks with declared dependencies, so the scheduler knows what may run in parallel and what must wait. What the orchestrator owns is narrow and worth stating precisely: it decides when to start a task, records whether the process exited zero, retries on failure, and refuses to start a task whose upstreams have not succeeded. That is all. It does not know whether the data was correct, whether the source had finished writing, or whether a task that exited zero produced any rows at all. Most data incidents live in that gap, which is why data-aware scheduling — triggering on the arrival or freshness of a dataset rather than the completion of a task — has become the direction of travel.

Every task succeeded, every dependency was respected, and the answer published to the business was zero.

02:00  extract_orders          SUCCESS    0 rows read
02:05  transform_orders        SUCCESS    0 rows written
02:10  publish_daily_revenue   SUCCESS    revenue for 26 July = 0.00
07:30  the upstream export finally lands in the bucket
09:00  finance asks why yesterday earned nothing

DAG status: green. Retries used: 0. Alerts fired: 0.

The scheduler enforced task ordering perfectly and had no opinion about data existing. extract_orders read an empty prefix, exited zero, and every downstream task did exactly what it was told with the nothing it was given.

The first fix is a sensor or freshness assertion at the boundary: the extract does not begin until the expected file or watermark is present, and it fails rather than succeeding when the wait times out. Failing is the important half — an extract that logs a warning and continues has converted a loud problem into a quiet one.

The second is to treat an implausible row count as a failure. Zero rows is almost never right for a daily fact load, and a bound against the trailing median catches both the empty case and the accidental full reload. This has to be an assertion that stops the run, not a dashboard tile, because the whole failure mode is that publication continued.

The third is to make publication conditional. Gold-layer outputs should swap atomically to a new snapshot only when the checks pass, so a bad run leaves yesterday's correct numbers in place. A consumer reading slightly stale data knows how to behave; a consumer reading confident zeroes does not.

A retry is the orchestrator re-executing a task it just saw fail, usually within minutes and usually for a transient reason. A rerun is a human re-executing a window that already completed, because the output was wrong or the code changed. A backfill is a rerun across many windows. They are worth distinguishing because they need different safety properties: a retry needs the task to be idempotent, a rerun needs the output partition to be replaceable, and a backfill needs concurrency limits and a downstream communication plan. Conflating them produces the common design where retries are safe and a manual rerun quietly corrupts a month.

The value of the layering is that each layer makes a promise the one before it does not, so you know which layer to distrust when a number is wrong.

flowchart LR
    S[Source systems] --> B[Bronze<br/>raw, append-only,<br/>exactly as received]
    B --> V[Silver<br/>typed, deduplicated,<br/>conformed, quality-checked]
    V --> G[Gold<br/>business aggregates<br/>and marts]
    G --> C[BI, ML features,<br/>reverse ETL]
    B -.replay when logic changes.-> V

Bronze guarantees fidelity and nothing else. It is the raw payload with ingestion metadata attached, deliberately including the malformed rows, because its job is to be the thing you can replay from. Nobody should build a report on it, and the reason is that it has no schema promise.

Silver guarantees shape and identity: columns are typed, duplicates are resolved, keys are conformed across sources so a customer means one thing, and the quality tests have passed. This is the layer engineers work from, and it is where most of the actual effort lives.

Gold guarantees business meaning. It holds the aggregates and dimensional models that answer questions, with metric definitions that are agreed rather than invented per query. Its consumers are people and dashboards, and its contract is semantic rather than structural.

The dotted replay arrow is the whole reason for the arrangement. Because bronze is immutable and complete, a bug in silver logic is a re-derivation rather than a data-loss event. Skip bronze — transform on the way in — and every transformation bug becomes a request to the source system for history it has already overwritten.

The failure mode to name is layers that exist as folder names without the promises: bronze that has been cleaned a bit, silver that consumers bypass, gold that three teams each compute differently. Then you have the storage cost of a medallion architecture and none of its benefit.

A central fact table holding measurements at a declared grain, surrounded by denormalised dimension tables holding the descriptive attributes you filter and group by. It survived forty years of alternatives because it matches the shape of analytical questions — one big table of events, joined to a handful of small lookups — and because query engines optimise for exactly that shape with star joins and bloom filters. It is also the model humans can navigate: an analyst can see which table has the money and which has the labels. The cost is that dimensions are deliberately denormalised, so attribute changes need an explicit policy, which is what slowly changing dimensions exist to provide.

One fact, three dimensions, and the surrogate keys that make the joins cheap.

fact_sales                          dim_date
  date_key        FK  ->              date_key      PK   20260726
  product_key     FK  ->              full_date          2026-07-26
  store_key       FK  ->              month              2026-07
  order_id            (degenerate)    quarter            2026-Q3
  quantity            additive        is_weekend         true
  net_amount_minor    additive
  discount_minor      additive      dim_product
  unit_cost_minor     additive        product_key   PK   4471
                                      sku                "ACM-1180"
grain: one row per product per        name               "Widget, blue"
       line on a customer order       category           "Hardware"
                                      supplier           "Acme Ltd"

dim_store
  store_key       PK  882
  store_name          "Leeds Central"
  region              "North"
  opened_on           2019-04-02

Three things in this sketch are the actual answer. The measures in the fact are all additive at the declared grain, which is what lets any dashboard sum them across any combination of dimensions without a special rule. A non-additive measure — a margin percentage, a running balance — does not belong as a stored fact column; store its numerator and denominator and let the tool divide the sums.

The keys are surrogates, not the source system's identifiers. That is not bureaucracy: it is what allows a dimension row to be versioned, a late-arriving dimension member to be handled, and two source systems to be merged without a key collision.

order_id sits in the fact with no dimension of its own, which is a degenerate dimension. It exists so you can count distinct orders and trace a row back to the operational system, and inventing a one-column dimension table for it buys nothing.

The dimensions are wide and repetitive on purpose. Normalising category and supplier out into their own tables gives you a snowflake, saves a trivial amount of storage on a small table, and adds a join to every query an analyst writes.

The grain is the precise meaning of one row — "one row per order line per shipment", not "sales data". It has to be declared before any column is chosen because every measure must be additive at that grain and every dimension must apply to it. Almost every broken warehouse table is a grain that was never stated and then drifted: a shipping cost that belongs per order stored on each line, so summing it multiplies by the line count. In an interview, answering "what is one row in this table?" before drawing anything is the single strongest signal of dimensional modelling experience, and the fastest way to spot a double-counting bug in someone else's schema.

One big table — a wide denormalised table with the dimension attributes flattened into each row — is genuinely faster on columnar engines, because there is no join and the query reads only the columns it names. It is a reasonable choice for a stable, single-purpose mart consumed by one dashboard. What it costs is maintainability: a dimension attribute correction now has to be rewritten across every historical row, there is no single place to define what a customer segment means, and the table cannot serve a question at a different grain. The usual answer is a star as the modelled layer with wide tables materialised from it for specific hot consumers, so the flattening is derived rather than authoritative.

The problem is that a descriptive attribute changes — a customer moves region, a product changes category — and you have to decide whether history should follow the change. Type 1 overwrites: the dimension always shows the current value, so last year's sales are suddenly attributed to the new region and any report run before the change no longer reproduces. Type 2 versions instead: the old row is closed off and a new row inserted with a new surrogate key, so each fact stays joined to the attributes as they were when it happened. Type 1 is right for corrections of wrong data; type 2 is right for genuine changes over time, which is most of them.

The customer moves from the North region to the South on 12 July, and the question is what June's sales are attributed to afterwards.

BEFORE  dim_customer
 customer_key | customer_id | name      | region | valid_from | valid_to   | current
 -------------+-------------+-----------+--------+------------+------------+--------
 5001         | C-3390      | Acme Ltd  | North  | 2019-04-02 | 9999-12-31 | true

AFTER   dim_customer
 customer_key | customer_id | name      | region | valid_from | valid_to   | current
 -------------+-------------+-----------+--------+------------+------------+--------
 5001         | C-3390      | Acme Ltd  | North  | 2019-04-02 | 2026-07-11 | false
 7742         | C-3390      | Acme Ltd  | South  | 2026-07-12 | 9999-12-31 | true

fact_sales
 order_id | order_date | customer_key
 ---------+------------+-------------
 A-118    | 2026-06-30 | 5001          <- still North, correctly
 A-904    | 2026-07-20 | 7742          <- South

The old row was not updated in place; it was closed. That is the entire mechanism, and it means the June fact still points at key 5001 and still reports as North, which is what "history is preserved" concretely amounts to.

Two columns carry the weight. customer_id is the durable natural key and is what you group by when you want the customer across all versions — customer_key counts as two customers, which is the classic type 2 reporting bug. And current is a convenience flag so the common "as it is now" query does not need a date predicate; it must be maintained in the same transaction as the valid_to update or the dimension will briefly have two current rows.

The load logic is a merge, not an insert: compare the incoming row against the current version on the natural key, and if any tracked attribute differs, close the old row and insert a new one. Which attributes are tracked is a modelling decision — a corrected spelling of the name should overwrite, type 1 style, and a real region change should version. Type 2 on every column produces a dimension that grows a row every time somebody fixes a typo.

The costs to name unprompted: the dimension grows without bound for volatile attributes, every fact load must resolve the key using the fact's own event date rather than today, and getting that lookup wrong attributes history to the new value while looking entirely correct.

A warehouse stores modelled, typed, governed tables in a proprietary format with a query engine that owns them, so it gives strong SQL performance and transactions and makes you load data before you can use it. A lake is files in object storage in open formats, so it is cheap and accepts anything, and gives no transactions, no schema enforcement and — historically — no reliable way to update a row. A lakehouse adds a metadata layer over those files, such as Iceberg or Delta, which brings ACID commits, schema evolution and time travel to open storage. The decision that matters is not the label but who enforces the schema and whether you can safely rewrite a partition while somebody is reading it.

Because it is columnar, so a query naming three columns of forty reads three columns of forty rather than every row in full. Storing a column together also makes compression far more effective, since adjacent values share a type and often a range — dictionary encoding a low-cardinality string column can be an order of magnitude. On top of that it carries per-row-group statistics, so an engine can skip whole chunks whose min and max cannot satisfy the predicate, which is where most of the apparent magic comes from. The trade is that it is poor for row-level access and for writes: appending one record means writing a file, which is exactly how the small-files problem begins.

By the predicate that appears in nearly every query, which in practice is almost always a date. The purpose is partition pruning — letting the engine skip directories entirely — so a partition column the queries do not filter on buys nothing and costs planning time. The two failure modes are opposite and equally common: too coarse, so every query scans a year, and too fine, so a day becomes ten thousand tiny files and the metadata operation costs more than the read. Partitioning on a high-cardinality column such as customer identifier is the classic mistake, because it produces a directory per customer and a listing operation that dominates every query.

A streaming job writing to a lakehouse table every thirty seconds, and the arithmetic of what that produces after a month.

Write interval        30 seconds
Writers               4 partitions in parallel
Files per day         2,880 intervals x 4                    = 11,520
Files after 30 days                                          = 345,600
Average file size     18 GB of data / 345,600                ~ 54 KB

A full scan of the month:
  345,600 object listings and opens, each ~15 ms of overhead   ~ 86 minutes
  18 GB of actual bytes read at 400 MB/s                       ~ 45 seconds

After compaction to 256 MB files:
  Files                18 GB / 256 MB                        ~ 72
  Listing overhead     72 x 15 ms                            ~ 1 second
  Same 45 seconds of reading. Query goes from 86 minutes to under a minute.

Nothing about the data changed. All of the cost was per-file overhead — an object store request, a Parquet footer read, a task scheduled — and at 54 KB per file that overhead is several hundred times the work of reading the file.

The second cost is invisible until it bites: row-group statistics only help if row groups are big enough to skip meaningfully, and a 54 KB file has one tiny row group, so predicate pushdown stops eliminating anything. The table has lost the property that made the format fast.

The fix is compaction, run as a scheduled maintenance job that rewrites small files into large ones — and the target is unglamorous but specific: files in the 128 MB to 1 GB range, aligned to the engine's read granularity. Table formats like Iceberg and Delta make this safe to run concurrently with readers, which is the reason the problem is now maintenance rather than an outage.

The related skew worth naming is the opposite shape: one partition holding ninety per cent of the rows because the partition key is a status or a country. Then a single task reads a 40 GB file while thirty others finish instantly, and the job's runtime is one task's runtime. Even file sizes matter as much as few files.

When the value of an answer decays in seconds to minutes, and somebody or something acts on it automatically. Fraud declines, ad bidding, operational alerting, live inventory on a checkout page — these genuinely cannot wait for a nightly job. What is usually not streaming, despite being asked for, is a dashboard a human looks at twice a day, or a feature where "within fifteen minutes" would be entirely acceptable and a micro-batch is far cheaper to operate. The reason to push back is that streaming multiplies the operational surface: state to manage, watermarks to tune, backfills that now require replaying a log, and a class of correctness bug that only exists once time is a variable.

A mobile client goes through a tunnel, and the count for the 10:00 minute is computed before its event exists.

Rows are in arrival order. Watermark = (max event time seen) - 2 minutes,
so it is advanced by data, never by the wall clock.

event_time  processing_time  watermark   what the pipeline sees
                             afterwards
----------  ---------------  ----------  -----------------------------------
10:00:12    10:00:13         09:58:12    window 10:00, now 1 event, open
10:00:41    10:00:42         09:58:41    window 10:00, now 2 events, open
10:01:03    10:01:04         09:59:03    window 10:01 opens. 10:00 still
                                         open: watermark is short of 10:01:00
10:02:20    10:02:21         10:00:20    window 10:02. 10:00 still open
10:03:05    10:03:06         10:01:05    watermark passes 10:01:00, so
                                         window 10:00 CLOSES and emits 2
10:04:10    10:04:11         10:02:10    window 10:04
10:00:55    10:07:30         10:02:10    LATE. The tunnel event arrives
                             unchanged  6.5 min after its own event time.
                                        Window 10:00 closed 4 min ago, and
                                        this event cannot raise the maximum,
                                        so the watermark does not move

Windowing by processing time instead:
  window 10:00 = 2 events      window 10:07 = 1 event   <- wrong minute

Processing-time windows are always internally consistent and always wrong about the world: the tunnel event is attributed to 10:07, so a traffic graph shows a dip at 10:00 and a spike seven minutes later that no user caused. Event-time windows are right about the world and force you to decide when to stop waiting.

The watermark is that decision, expressed as a claim: "I believe I have seen everything up to time T." Read the watermark column carefully, because this is where the two clocks get conflated even by people who can define both. The watermark here is derived from the maximum event time seen minus two minutes, so it advances only when a newer event arrives — not when two minutes of wall clock pass. The 10:00 window does not close two minutes after 10:00:41; it closes when an event stamped 10:03:05 or later turns up, because only then does the watermark reach 10:01:00.

That distinction has a consequence worth stating out loud: on a stream that goes quiet, windows do not close at all. If the last event in this table had been the one at 10:01:03, the watermark would have stalled at 09:59:03 and the 10:00 window would still be open tonight, holding its state and emitting nothing. Production pipelines deal with this by emitting an idle-source watermark after a period of silence, which is a deliberate decision to trust the wall clock in the absence of data, and it is the one place where the two clocks are allowed to meet.

What happens to the late event is the part candidates skip, and there are only three honest options. Drop it and record the count of drops as a metric, which is fine for approximate telemetry and unacceptable for billing. Emit a correction — keep window state around for an allowed lateness and re-emit an updated count, which requires every downstream consumer to handle a retraction. Or route it to a side output for a batch process to reconcile later, which is the pragmatic answer when the streaming path feeds a dashboard and a batch path owns the ledger.

The tuning tension is direct: a longer watermark delay catches more late data and delays every result, and it holds window state in memory for longer. There is no setting that is both complete and immediate, and saying so is the answer.

A watermark is the stream processor's assertion about how far event time has advanced — effectively "no further events older than T are expected" — and it is what allows a window to close and emit a result. It is a heuristic, usually derived from the maximum event time seen minus a fixed delay, and it can be wrong. Data arriving behind it is late, and the framework does one of three things depending on configuration: drops it, holds window state open for an allowed-lateness period and emits a corrected result, or diverts it to a side output. The consequential point is that the choice is a business decision, since silently dropping late events is invisible until someone reconciles totals.

Not exactly-once delivery, which no network provides, but exactly-once effect on state — the result is as if each record were processed once. Frameworks achieve it by checkpointing operator state together with the input offsets and committing both atomically, so a restart resumes from a consistent point rather than reprocessing into already-updated state. The guarantee ends at the framework's boundary. Writing to an external system makes it exactly-once only if that write is idempotent or transactional, which is why a sink keyed on an event identifier, or a two-phase commit sink, is the part that has to be designed. "The platform gives us exactly-once" is true and stops being true at the first HTTP call.

A tumbling window is fixed-length and non-overlapping, so every event belongs to exactly one — right for periodic reporting like counts per minute. A sliding window is fixed-length and advances by a smaller step, so windows overlap and an event contributes to several, which suits moving averages and rate detection at the cost of multiplying the state held per key. A session window has no fixed length: it groups events for a key until a gap of inactivity closes it, which is the natural model for user sessions and the most expensive, because state must be kept per key for the whole gap duration and a key that never goes idle never releases.

An explicit agreement between a data producer and its consumers, versioned and enforceable, rather than a wiki page describing what a table happens to contain today. A usable one specifies the schema with types and nullability, the semantic meaning and unit of each field, the grain of a row, the primary key, the freshness and delivery commitment, the allowed range or enumeration for constrained fields, and the process for changing any of it. Its value is that it moves breakage from the consumer's dashboard to the producer's build: a change violating the contract fails a check in the producer's pipeline. Without one, every schema change is discovered downstream, days later, by an analyst.

Adding an optional column is safe, because a consumer selecting named columns ignores it — and unsafe for anyone who wrote SELECT * into a fixed-shape target. Widening a type, int to bigint, is usually safe; narrowing is not. Renaming, dropping, changing a type incompatibly, changing units, and changing the meaning of an existing value are all breaking, and the last is the dangerous one because no automated check sees it: repurposing a status code from "pending" to "pending payment" passes every schema test and corrupts every report. Breaking changes need the same expand-and-contract discipline as a database migration — publish the new field alongside the old, migrate consumers, then remove.

Tests placed at the wrong layer either catch nothing or block everything, so the useful question is what each layer can assert and what it should do on failure.

layer     assertion                                on failure
--------  ---------------------------------------  --------------------------
ingest    batch row count within 40-160% of the    quarantine the batch,
          trailing median; schema matches the      page the on-call, do not
          contract version                         advance the watermark
bronze    no duplicate on the source key plus      fail the run; the source
          source timestamp; ingest metadata        is repeating itself
          present on every row
silver    referential: every order.customer_id     fail the run and emit the
          resolves to a dim_customer row;          orphan keys, they are the
          amount_minor >= 0; currency in ISO set   diagnosis
gold      revenue in gold equals revenue in        block the publish, leave
          silver to within 0.01; row count per     yesterday's snapshot in
          day matches the fact grain               place
serving   freshness: max loaded_at < 90 minutes    alert and show a staleness
                                                   banner, do not fail

The pattern is that assertions get more semantic as you move right, and the response gets less aggressive. At ingest you can only check shape and volume, and you should stop hard, because letting a bad batch through contaminates everything derived from it. At the serving edge you cannot stop anything — consumers are already reading — so the correct response is to tell them the data is stale rather than to hide it.

The single most valuable test in this list is the gold-versus-silver reconciliation, because it is the only one that catches a logic error. Every other check verifies that the data looks like data; a reconciliation verifies that the transformation preserved a quantity it was supposed to preserve, and that is where double-counted joins are found.

The rule about volume bounds is worth stating too: use a trailing median rather than a fixed number, or the threshold is wrong within a quarter and someone disables the check. And bound it on both sides — the accidental full reload looks like success to a minimum-only test.

A test runs as part of the pipeline and can prevent bad data from being published, so it belongs on invariants you are willing to fail a run for: uniqueness, referential integrity, non-negative amounts. A monitor observes data already in place and alerts on drift — a distribution shifting, a null rate creeping up, freshness slipping — where the right response is investigation rather than a blocked build. Confusing them produces either a pipeline that fails nightly on a soft signal until everyone ignores it, or a serious constraint implemented as a dashboard nobody reads. The question to ask of any check is what should happen when it fires, and if the answer is "nothing immediately", it is a monitor.

Three concrete jobs, none of which is drawing a nice graph. Impact analysis: before changing a column, knowing which twelve downstream models and four dashboards consume it, so the change is announced rather than discovered. Debugging: when a gold number is wrong, walking upstream to find which transformation introduced it, instead of reading every model. And compliance: proving where a personal data field ended up, which is the only practical way to answer an erasure request across a warehouse. Column-level lineage is substantially more useful than table-level for the first two, because most tables have one broken column rather than being broken outright.

Not collecting the field is the strongest form, and it is the one skipped because collection feels free. After that: tokenise or hash identifiers at ingest so downstream layers join on a surrogate and never hold the raw value; keep the mapping in one restricted store; separate the small number of tables that need identity from the many that need behaviour. Aggregate early where the use case is analytical, because a table of counts per segment has no erasure obligation. And set retention per field rather than per table, since an email address and an event timestamp rarely deserve the same lifetime. The design goal is that most of your platform is out of scope, rather than all of it being in scope with access controls.

A comparison of an independently computed total against the pipeline's own output — transaction count and sum in the source system against the warehouse fact for the same day, or gold revenue against silver. It matters because it is the only class of check that catches transformation logic errors: a join that fanned out and doubled amounts passes every schema, null and uniqueness test while being completely wrong. It should run per window and per partition rather than in total, because a full-table match can hide two days that are wrong in opposite directions. The tolerance needs to be explicit and tiny, since a tolerance chosen to make the check pass is not a check.

The training set fits the parameters. The validation set chooses between models and settings — hyperparameters, features, thresholds — and is therefore consumed by every decision you make with it. The test set estimates performance on unseen data and is only honest if it was used once, at the end, because each look at it leaks information into your choices and turns it into another validation set. The discipline people skip is that the split must respect the structure of the data: random splitting is wrong when rows are correlated by time, by user or by group, because a near-duplicate row in training makes the test score meaningless. For anything predicting the future, split by time.

Two lines of feature engineering, written before the split, and a model that scores 0.97 in validation and 0.71 in production.

# Wrong: both of these see the entire dataset, including the test rows.
df["amount_z"] = (df.amount - df.amount.mean()) / df.amount.std()
df["merchant_fraud_rate"] = df.groupby("merchant").is_fraud.transform("mean")

X_train, X_test, y_train, y_test = train_test_split(
    df.drop(columns="is_fraud"), df.is_fraud, test_size=0.2, random_state=42)

# Reported:  validation ROC-AUC 0.971
# Production after two weeks:   ROC-AUC 0.712

The standardisation is the mild leak: the mean and standard deviation encode information about the test rows, which flatters the score slightly and would not be available at scoring time, where you must use the training statistics.

The target encoding is the severe one. merchant_fraud_rate is computed from is_fraud across all rows, so every test row's feature value was partly derived from its own label. The model learns to read the answer out of the feature, and in production the feature must be computed from history alone, so the signal it depended on is not there.

# Right: fit every transformation inside the split, on training data only.
pipe = Pipeline([
    ("scale", StandardScaler()),               # fitted on train folds
    ("model", GradientBoostingClassifier()),
])
# And compute the merchant rate from data strictly before each row's timestamp.

Two further rules follow. Any transformation with fitted state — scalers, imputers, encoders, feature selection, over-sampling — belongs inside the cross-validation loop, refitted per fold, which is precisely what a pipeline object exists to enforce. And for temporal data the split must be by time, with features computed from a window that ends before the prediction point, because a random split lets the model see the future of the same merchant.

The diagnostic worth naming: a validation score that is surprisingly high is evidence of leakage rather than of success. The correct first response to ROC-AUC 0.99 on a hard problem is to look for the feature that contains the label.

Overfitting is a model learning noise specific to the training sample, so training error keeps falling while held-out error rises. It is a capacity problem relative to the amount of signal available: too many parameters, too many features, too few rows, or too much training. Regularisation constrains the solution so the model cannot express arbitrarily complex fits — an L2 penalty shrinks coefficients towards zero, L1 drives some to exactly zero and thereby selects features, dropout removes units at random so no unit can be relied on, and early stopping halts training when validation error turns. All of them trade a little training accuracy for generalisation, which is the trade you always want until the model starts underfitting.

The model is a single line and it beats most first attempts on the metric that was reported.

100,000 transactions, of which 1,000 are fraudulent   -> 1.0% positive class

Model:  return "not fraud"   (no features, no training, no parameters)

  correct predictions   99,000
  accuracy              99.0%
  fraud detected        0 of 1,000
  recall                0.000
  precision             undefined - it never predicts positive
  business value        zero, minus the cost of building it

Accuracy is a weighted average dominated by the majority class, so on a 1% positive class it is essentially a measure of how often the model says "no". A genuinely good fraud model catching 700 of 1,000 frauds while wrongly flagging 2,000 legitimate transactions has an accuracy of 97.7% — worse on the reported metric than the model that does nothing.

That inversion is the whole point. Once the classes are imbalanced, accuracy ranks the useless model above the useful one, so any decision made by comparing accuracies is being made backwards.

The metrics that stay informative are the ones computed on the positive class alone: recall, precision, F-measures, and the precision-recall curve. Balanced accuracy and Cohen's kappa also correct for the base rate if a single number is demanded.

The stronger answer goes past metrics to a baseline. Always report the majority-class score alongside the model's, because "99.2% accuracy" means nothing until the reader knows the trivial model scores 99.0%. A candidate who volunteers the baseline unprompted is telling you they have been burned by this.

The same fraud model at one particular threshold, with the arithmetic written out and each error type given its cost.

                     predicted fraud   predicted legitimate
actual fraud                620 TP                 380 FN
actual legitimate         2,480 FP              96,520 TN

precision = TP / (TP + FP) = 620 / 3,100   = 0.200
recall    = TP / (TP + FN) = 620 / 1,000   = 0.620
F1        = 2 x 0.200 x 0.620 / 0.820      = 0.302
accuracy  = (620 + 96,520) / 100,000       = 0.971

Cost of the errors:
  380 FN  x  £180 average loss per missed fraud    = £68,400
  2,480 FP x  £4 manual review + churn risk        = £9,920 plus goodwill

Read the two ratios as questions about different denominators. Precision answers "when it says fraud, how often is it right" — 20% here, so four out of five flagged customers are innocent. Recall answers "of the fraud that happened, how much did we catch" — 62%, so £68,400 walked out.

The costs are not symmetric and they are not even the same kind of thing. A false negative is a direct, measurable loss. A false positive is a small operational cost plus an unquantified reputational one, since the customer whose legitimate card is declined at a checkout may not come back. That asymmetry, not the F1 score, is what should set the threshold.

Which is the point about F1: it weights precision and recall equally, and almost no business problem does. If a missed fraud costs forty-five times a review, recall matters far more, and F2 or an explicit expected-cost calculation over candidate thresholds is the honest metric. Report the cost curve and let the business choose the operating point.

One structural note: the threshold is not part of the model. The classifier produces a score, and moving the threshold moves every number in this table. Training a new model when the real problem is a badly chosen threshold is a common and expensive mistake.

By the cost of each error in the specific application, which means asking what happens to a person when the model is wrong in each direction. Screening for a treatable disease wants recall, because a missed case is severe and a false positive costs a follow-up test. Automatically blocking a payment or suspending an account wants precision, because the false positive lands on a real customer with no recourse. Content moderation that routes to a human review queue can favour recall, since the human supplies the precision. The framing to offer is that you are choosing an operating point on a curve rather than a better model, and the curve should be shown to whoever owns the cost.

Because ROC-AUC's x-axis is the false positive rate, whose denominator is the enormous negative class, so thousands of false positives barely move it. A model can look excellent at 0.95 ROC-AUC and still flag twenty innocent cases per true one, because that ratio is invisible to the metric. A precision-recall curve puts precision on one axis, whose denominator is the model's own positive predictions, so it responds directly to the thing you care about. PR-AUC also has a base rate as its floor — a random model scores about the positive prevalence, 0.01 rather than 0.5 — so improvements are legible. Use ROC-AUC when classes are roughly balanced and both errors matter comparably.

It gives a less noisy performance estimate by training and evaluating k times on different partitions, which matters most when the dataset is small enough that a single held-out split is luck. It also exposes variance across folds, and a wide spread is itself the finding — it means your estimate is unstable and the single number is not trustworthy. It misleads whenever the folds are not independent of each other: time series, where a random fold trains on the future; grouped data, where the same patient or customer appears in several folds; and any pipeline where a transformation was fitted before the split. The fixes are respectively time-series splits, group-aware folds, and fitting inside the loop.

First by asking whether it needs handling at all, since many algorithms handle it adequately once you stop using accuracy and tune the decision threshold — which is the cheapest intervention and the one people skip. Beyond that: class weights, which tell the loss function that a minority error costs more and require no data manipulation; under-sampling the majority, which is fast and throws away information; and synthetic over-sampling such as SMOTE, which fabricates minority examples and must happen inside the cross-validation folds or it leaks. Resampling changes the predicted probabilities, so if you need calibrated probabilities rather than a ranking you have to recalibrate afterwards.

Two, and they are usually conflated. The offline problem is reuse and correctness of historical features: a shared, versioned definition with point-in-time correct joins, so a feature for a row dated June uses only data available in June. The online problem is low-latency lookup at serving time, where the model needs a customer's thirty-day average in single-digit milliseconds and cannot run a warehouse query. The reason it exists as a product category rather than a table is the guarantee that both paths compute the feature from one definition, which is the only durable fix for training/serving skew. Its cost is real operational weight, so a small team with two models rarely needs one.

The same named feature, defined once in SQL for training and once in the scoring service, and the two definitions disagree in three ways.

feature: customer_avg_amount_30

TRAINING   SQL over the warehouse
  window   previous 30 completed transactions, current row excluded
  missing  fewer than 5 prior transactions -> NULL, row dropped from training
  currency amounts converted to GBP at the transaction date's rate

SERVING    Java in the scoring service
  window   transactions in the last 30 days, current one included
  missing  no history -> 0.0
  currency raw amount, no conversion

Same customer, same instant, current transaction 900.00 EUR:
  training-style value    142.60
  serving-style value     168.20     window and inclusion differ
  new customer, training  row never existed
  new customer, serving   0.0        -> scored as the lowest-spending decile

The model was fitted on one distribution and is being asked questions from another, so its coefficients are being applied to numbers that do not mean what they meant during training. Offline metrics stay excellent, because the offline evaluation uses the training definition, and online performance is quietly worse with nothing to point at.

The new-customer case is the sharpest. Training dropped those rows entirely, so the model never learned what a thin file looks like, and serving hands it 0.0 — a value that in training data meant "customer who genuinely spends nothing". Every new customer is scored as the model's cheapest segment.

The structural fix is one definition with one implementation, either by generating both paths from a shared spec or by having training read the same feature service the model will read at serving time. Where that is impossible, the fallback is a skew monitor: log the feature vector actually used at scoring time, and compare its distribution against the training set's on a schedule.

The habit worth stating is that a feature is not a column, it is a definition including its window, its exclusions, its null policy and its units. Two implementations of the same name is the default state of any team that has not deliberately prevented it.

Two distinct things. Data drift is the input distribution moving — a new customer segment, a changed upstream field, a new merchant category — and it is detectable immediately by comparing feature distributions against the training reference. Concept drift is the relationship between features and target changing, so the model is wrong even on familiar inputs, and it is only detectable once labels arrive, which may be weeks later. So monitoring has two tiers: feature-level statistical tests and prediction-distribution shift as early warning, and true performance metrics against delayed ground truth as confirmation. The gap between them is why a model with no label feedback loop is running unmonitored regardless of how many dashboards it has.

Batch when the prediction depends only on data that changes slowly and the consumer can read a precomputed table — churn scores, next-best-offer, credit segments. It is dramatically cheaper and simpler: no latency budget, no serving infrastructure, failures are retryable, and the model runs in the same environment it was trained in. Real-time is required when the prediction depends on the request itself — the contents of this basket, this transaction's amount, this session's clicks — or when the input space is too large to precompute. The middle option worth naming is precomputing the expensive parts and combining them with request features at serving time, which gets most of the freshness for much less of the operational cost.

Both run two models on the same traffic; what differs is whether the new one's output reaches anyone.

flowchart LR
    R[Incoming request] --> C[Champion model]
    R --> S[Challenger model<br/>shadow copy]
    C --> U[Response served<br/>to the user]
    S --> L[Prediction logged<br/>never served]
    U --> O[Outcome recorded<br/>days later]
    L --> E[Compare both on<br/>identical traffic]
    O --> E

A shadow deployment answers questions about the system before it answers questions about the model. Does the challenger meet the latency budget under real traffic, does it error on inputs the test set never contained, does it agree with the champion on the cases you expect. All of that is available with zero user risk, because the challenger's output is discarded.

Champion/challenger is the evaluation that follows. Both models score the same requests, the actual outcomes are joined back when they arrive, and the challenger is promoted only if it beats the champion on the agreed metric over an agreed volume. Comparing on identical traffic is what makes it trustworthy — comparing this month's challenger against last month's champion confounds the model with the season.

The limit of shadowing is worth volunteering: it cannot measure anything involving the model's effect on behaviour. If the model's prediction changes what is shown to the user, and therefore what the user does, only a live split — an A/B test or a gradual rollout — can measure it. Shadow mode measures fit and safety; an experiment measures value.

The operational rules that make either work: the champion must remain deployed and instantly reselectable, promotion must be a configuration change rather than a release, and the comparison window must be fixed in advance so nobody promotes on the first good day.

More than the code. You need the exact training data — a versioned snapshot or a query pinned to a point in time, not "the customers table" — the feature definitions, the library versions, the random seeds, the hyperparameters, and the evaluation split. Missing any one of them means a rerun produces a different model and you cannot tell whether a change helped. That is what a model registry and experiment tracker are for: they record the artefact together with the lineage that produced it, so a model in production can be traced to a dataset and a commit. The practical test is whether you could rebuild last quarter's model today and get the same predictions.

A learned mapping from a piece of content to a fixed-length vector, arranged so that geometric closeness corresponds to whatever similarity the model was trained on. That last clause is the part that matters: closeness means "similar in the sense this model learned", which is usually topical or semantic similarity and is not the same as relevance to a question. Consequences follow directly. Vectors from two different models are not comparable, so changing the embedding model means re-embedding the entire corpus. Similarity is symmetric while question and answer are not, which is why a question rarely sits near its answer without help. And embeddings encode no notion of recency, authority or access rights, which must be handled as metadata.

You embed the query and find the nearest stored vectors by cosine or dot-product similarity. Exact search compares against every vector, which is linear in corpus size and fine up to perhaps a hundred thousand documents. Beyond that, approximate nearest neighbour indexes — HNSW graphs or inverted-file clustering — trade recall for speed by only exploring part of the space, so a query is sub-millisecond and may miss a genuinely nearest neighbour. That miss rate is a tunable parameter and it is the cost people forget to state: your retrieval evaluation should measure recall against exact search, because a silent 5% recall loss looks exactly like a model that does not know the answer.

Six stages, and the two that most implementations omit are the ones that decide answer quality.

flowchart LR
    Q[User question] --> E[Embed the question<br/>and expand it]
    E --> V[Vector plus keyword search<br/>over chunks]
    V --> R[Rerank top 50<br/>down to top 5]
    R --> P[Assemble prompt with<br/>chunks and citations]
    P --> M[Model generates<br/>a grounded answer]
    M --> G[Verify each claim maps<br/>to a retrieved chunk]

The retrieval stage is where accuracy is won or lost, and it is the stage that receives the least attention. If the right chunk is not in the candidate set, no amount of prompt engineering recovers it, which is why the first metric to instrument is retrieval recall at k measured against a labelled question set — before touching the generation prompt.

Reranking exists because embedding similarity and answer relevance are different things. Retrieving fifty candidates cheaply and reordering them with a cross-encoder that reads the question and chunk together is consistently the largest single quality improvement available, and it costs one extra model call.

The prompt assembly step carries a rule: the model must be instructed to answer only from the supplied context and to say when the context is insufficient. Its prior knowledge is stale and unattributable, so an answer blending both is untraceable even when correct.

The verification stage is the one most systems skip. Requiring every factual claim to cite a chunk identifier, and checking programmatically that the cited identifiers were actually retrieved, catches the failure where the model fabricates a plausible citation. Displaying the sources to the user does the rest of the work, because a wrong answer with a visible source is falsifiable and a wrong answer without one is not.

The same corpus split three ways, and what each split does to retrieval.

corpus: 4,000 support articles, average 1,800 tokens each = 7.2M tokens
a chunk never spans two articles, so the count is (chunks per article)
x 4,000 - not simply 7.2M divided by the chunk size

chunk 2,000 tokens, no overlap        -> 4,000 chunks
  1 per article, because the average article is shorter than the chunk.
  This is not really chunking at all; it is one embedding per document
  the right document is nearly always retrieved
  a single article covers three unrelated topics, so its one embedding
  is an average of all three and matches nothing strongly
  the model is handed the whole article and quotes the wrong paragraph

chunk 800 tokens, 100 overlap         -> ~12,000 chunks
  stride is 700, so 1,800 tokens takes 3 windows per article
  embeddings are topically focused, retrieval is usually right
  a configuration table gets split across two chunks; retrieval returns
  the half without the header

chunk 200 tokens                      -> 36,000 chunks
  9 per article, and here per-article and 7.2M/200 happen to agree
  because 1,800 divides exactly by 200
  precise matches on specific phrases
  an answer needs four adjacent chunks; top-k of 5 returns two of them
  the answer is fluent, cited, and missing half the steps

The tension is that an embedding is one vector per chunk regardless of length, so a long chunk dilutes its own meaning while a short chunk loses the context that made it interpretable. There is no size that is both.

What resolves it is decoupling the unit you retrieve from the unit you send. Embed small, focused chunks for matching, then expand each hit to its surrounding section before building the prompt — retrieval precision from the small unit, sufficiency from the large one.

Two further mitigations are cheap. Chunk on document structure — headings, sections, table boundaries — rather than a fixed token count, so a chunk is a coherent unit and tables stay intact. And prepend the document title and heading path to each chunk's text before embedding, so a chunk reading "set the value to 30 seconds" carries the context that says which product and which setting.

The number to measure rather than guess: recall at k on a set of real questions with known answers. Chunk size is an empirical parameter, and the arguments about it are only settled by that measurement on your corpus.

Because retrieval separates knowledge from the model. Adding a document to an index is an ingestion job that takes seconds and is immediately effective; teaching the same fact by fine-tuning is a training run, and removing a fact you have trained in is not a supported operation at all. Retrieval also gives you citations, per-user access control on the retrieval step, and an audit trail — none of which a fine-tune can provide, since weights cannot be filtered by permission. Fine-tuning remains the right tool for a different problem: teaching form rather than fact, such as a consistent output structure, a domain's tone, or a classification task with abundant examples. Freshness is retrieval's job and behaviour is fine-tuning's.

The model is trained to produce likely continuations, not to withhold output, so when the context lacks the answer the most likely continuation is a fluent, plausible, wrong one. Nothing in the objective rewards saying "I do not know". Grounding attacks this by changing what "likely" means: supply the relevant source text in the context, instruct the model to use only that, require citations, and give it an explicit permitted refusal. That reduces the rate substantially and does not eliminate it, because the model can still misread a source or blend it with its priors. Which is why the durable answers are showing the sources so a user can check, and verifying citations programmatically.

The structure matters more than the tooling, because the failure is almost always that there is no fixed question set to regress against.

1  FIXED SET        150-300 questions from real usage, each with:
                      the question, the expected source documents,
                      a reference answer, a category, a difficulty
                    plus 30 adversarial cases: unanswerable, ambiguous,
                    out of scope, prompt-injection attempts

2  RETRIEVAL        recall@5, recall@20, mean reciprocal rank
   METRICS          measured against the labelled source documents,
                    reported separately from generation

3  GENERATION       groundedness  every claim traceable to a chunk
   GRADERS          relevance     answers the question asked
                    completeness  covers the reference answer's points
                    refusal       declines correctly on unanswerable cases
                    graded by a model, calibrated against ~50 human labels

4  GATES            block release if: groundedness < 0.95,
                    retrieval recall@5 drops more than 2 points,
                    any adversarial case leaks the system prompt,
                    p95 latency > 3s, cost per query > budget

5  RUN ON           every prompt change, model version, chunking change,
                    index rebuild - and weekly against production samples

The separation in stages 2 and 3 is the load-bearing decision. A single end-to-end score tells you the answer was bad and not whether retrieval missed the document or generation mishandled it, and those have entirely different fixes. Measure them apart and every regression points at its own cause.

The adversarial subset is what stops the harness from being a demo. Unanswerable questions are the only way to measure whether the system refuses rather than invents, and refusal rate is the metric most closely tied to whether users come to trust it.

Model-graded evaluation is unavoidable at this scale and needs calibrating. Label fifty examples by hand, check the grader's agreement, and re-check it whenever the grader model changes — an uncalibrated grader is a number generator.

The gates are the part that makes it an engineering artefact rather than a report. Without thresholds that fail a build, an evaluation suite becomes a dashboard someone looks at after a complaint.

Because the query is a mechanical consequence of decisions nobody wrote down. What is an active user — signed in, or performed an action, and within how many days, in which timezone? Does revenue include tax, refunds, cancelled orders recognised then reversed, and at which exchange rate? Each answer is defensible and different, so two teams both computing "monthly revenue" correctly get different numbers and spend a week discovering why. The engineering response is a single semantic layer where each metric is defined once and everything consumes that definition, and the cultural response is that the definition has a named owner. Reconciling dashboards is a symptom; undefined metrics is the disease.

Because the dashboard is generally the last honest component. The number is wrong for one of four reasons upstream: the data is stale, so the dashboard is correctly showing an old world; the transformation is wrong, most often a join that fanned out and multiplied a measure; the metric is defined differently from the one the person has in their head; or the source system itself changed a field or a code's meaning. Diagnosing in that order — freshness, then reconciliation against the layer below, then definition, then upstream schema — resolves it faster than reading the chart's SQL. The instinct that separates candidates is checking freshness first, because it is the cheapest check and a common cause.

Because the data has been copied by design. A single identifier can be in the bronze layer's immutable files, several silver tables, aggregates in gold, Parquet snapshots in a lake, a search index, a feature store, model training sets, warehouse backups, and a downstream SaaS tool fed by reverse ETL. Immutable storage is exactly what makes point deletion expensive, since a lakehouse must rewrite whole files to remove a row. The designs that make it tractable are crypto-shredding — store personal fields encrypted per subject and destroy the key — plus tokenising identity at ingest so most tables hold only a surrogate, and column-level lineage so you can prove which places you have covered.

Because the hard parts are upstream and downstream of the modelling. The label is the usual killer: there is no ground truth, or it arrives months late, or it is defined inconsistently by the humans who produced it, so the model is learning a noisy proxy for something nobody agreed on. Then the data is not available at prediction time even though it was available in training, which invalidates the whole feature set. Then there is no decision the prediction changes, so a good model has no route to value. A strong candidate asks about the label, the prediction-time data availability and the downstream action before discussing algorithms at all.

Reaching for streaming, a lakehouse, a feature store and an orchestrator with a hundred DAGs for a problem that is a nightly job over a few hundred million rows. Volume is the question that settles it, and it is usually asked too late: a warehouse and scheduled SQL comfortably handle most analytical workloads, and every additional system is more operational surface, another place data can be stale, and another thing to explain. The senior move is to state the volume and latency requirement, propose the simplest thing that meets it, and name the specific threshold at which you would add the next component. Restraint here is scored, not penalised.

"How would you know this number was wrong?" It cannot be answered generically, because it depends on the pipeline just described, and it requires having been on the receiving end of a silently wrong figure. A strong answer names a specific check — a reconciliation between two layers with a stated tolerance, a freshness assertion with a threshold, a volume bound against a trailing median — says where it runs, what it blocks when it fails, and who is paged. It also admits which errors the checks would not catch, because every data platform has a class of wrongness it can only find by someone noticing. A weak answer says data quality monitoring, which is a product category rather than an answer.