Data Engineer (AI-focused)

A contract only helps if a machine enforces it at the boundary, so I write it as something checkable rather than a wiki page. Schema is the obvious part: field names, types, nullability, and enum members, expressed in a format the producer's build can validate against — Protobuf or Avro for events, JSON Schema otherwise. Then semantics, where the real value sits: what a row means, the grain, the primary key, whether updates are emitted or only inserts, and what a null actually signifies. Then operational guarantees, meaning expected volume with an upper and lower bound, maximum lateness, delivery semantics, and how deletions are communicated. Then ownership: a named team, a support channel, and a deprecation policy with a notice period, usually a couple of release cycles. The mechanism I care about most is the compatibility check running inside the producer's pipeline that fails their build when they break my consumers, because a contract enforced only downstream is not a contract, it is a complaint. The residual weakness is semantic drift while the schema stays valid, so I pair it with distribution monitoring.

I start from the freshness the consumer genuinely needs rather than the freshness they ask for. Most requests for real-time turn out to mean 'not once a day', and a fifteen-minute micro-batch satisfies that at a fraction of the operational cost. Streaming earns its complexity when the decision the data feeds is itself real-time — fraud checks, session personalisation — or when the volume makes any full batch window impossible. The costs I make explicit up front: out-of-order and late events, watermarks, state stores that need sizing and recovery planning, and on-call for a system where a bad deploy loses data rather than merely delaying it. Batch fails loudly and you rerun it; streaming fails subtly and you find out from a downstream complaint. Reprocessing is the other axis, since if I expect transformation logic to change often, a batch path over immutable raw files is far easier to reason about. My usual answer is a hybrid: land raw events continuously in append-only storage for durability, serve most consumers from scheduled transformations, and stream only the specific paths that justify themselves.

Exact duplicates are cheap: a content hash taken after normalisation — trimmed whitespace, consistent Unicode form, boilerplate stripped — catches most of the volume, and I do it at ingestion so the same bytes are never stored twice. Near-duplicates need MinHash with locality-sensitive hashing. I shingle at five to seven words, use a few hundred permutations, band them for a Jaccard threshold around 0.8, and cluster the candidate pairs. On a corpus of hundreds of millions of documents that runs as a Spark job in hours, and the band-and-row configuration is the knob that actually matters, since it sets the precision and recall of candidate generation. Within each cluster I keep the longest or most recent document and record the discarded identifiers for lineage, because 'why is this document missing' is a question I will be asked. Two cautions: templated content such as invoices and legal boilerplate is near-identical by design, so blanket removal destroys a legitimate distribution and I threshold per source. And cross-source duplication is where the real evaluation leakage lives, so deduplication has to be global.

The immediate fix is that my pipeline should degrade rather than die, so readers need to be tolerant: ignore unknown fields, treat missing optional fields as null, and quarantine records that fail validation into a dead-letter location carrying the raw payload and the rejection reason, instead of aborting the run. That converts an outage into a backlog I can replay. Structurally the answer is a schema registry with compatibility enforced at write time. Backward compatibility means adding optional fields with defaults, never renaming, never narrowing a type, never removing an enum member, and a required field with no default is a breaking change the registry should reject before it ever reaches a topic. Where a genuine breaking change is unavoidable, the pattern is publishing a new version alongside the old, migrating consumers on their own schedule, and retiring the old one on a dated deprecation. I also keep a contract test in the producer's pipeline running my parser against their sample payloads. And I alert on quarantine volume, since a slow trickle of rejected records hides for weeks.

Freshness has to be defined against event time rather than the time my job finished, or I am measuring my own scheduler instead of the data. So I instrument two clocks: the maximum event timestamp present in the served dataset, and the lag between that and now. The commitment is then a statement about that lag at a percentile — for example, ninety-nine percent of the day the served table contains everything that happened more than twenty minutes ago. I publish it per dataset rather than per pipeline, because consumers care about the table they query, not my DAG. Monitoring is a small job reading the watermark and emitting a metric, alerting on a trend rather than a single breach, since one late upstream file is normal noise. The subtle part is partial freshness: a table can look fresh in aggregate while one contributing source is a day stale, so I track per-source watermarks and expose the minimum as the table's real figure. I also surface freshness in the catalogue beside the dataset, so an analyst sees staleness before building on it.

Columnar for anything scanned analytically — Parquet with sensible row-group sizes, compressed with Snappy or Zstd depending on whether I am optimising read throughput or storage cost — and behind a table format such as Iceberg or Delta so I get atomic commits, schema evolution, and time travel instead of hand-managed directories. Partitioning by ingestion date plus source is usually right; partitioning on anything high-cardinality produces the small-files problem, which is the failure I see most often, where millions of tiny objects make listing slower than reading. I target files in the low hundreds of megabytes and run compaction on a schedule. For the training path, sequential throughput dominates, so I also materialise shuffled sharded copies in a format the loader streams well rather than making every run re-shuffle a Parquet lake. Raw payloads stay immutable in their own bucket with a lifecycle rule moving them to colder storage after ninety days. The tradeoff I name openly is duplication: the same content lives in two or three shapes, it costs money, and I defend it because recomputing is slower and riskier.

I model it as a chain of idempotent stages, each writing to durable storage keyed by content hash, so any stage can be rerun without duplicating work. Parse and normalise, chunk, embed, upsert. Embedding is the expensive stage, so it reads a work queue of chunk hashes that have no vector for the current model version — that single decision makes incremental updates, retries, and backfills the same code path rather than three. I batch aggressively for accelerator utilisation, sixty-four to two hundred and fifty-six chunks depending on length, and sort by length within a batch to cut padding waste, which is worth a real fraction of throughput. If the embedder is a hosted API, concurrency gets capped and the queue absorbs bursts instead of dropping them. Every vector row carries model name, model version, chunk hash, source document id, and ingestion timestamp; without those columns the store becomes unauditable inside a quarter. I monitor queue depth, cost per thousand chunks, and the count of documents whose vectors are older than their source, which is the real correctness signal.

The binding constraint is that old and new vectors are not comparable, so a partial migration silently corrupts retrieval rather than degrading it visibly. I build the new index alongside the old one — a separate index, or a version column every query filters on — and cut over atomically once it is complete and evaluated. Cost and duration come first: I measure throughput on a one percent sample, price it, and decide whether to run as a low-priority background job across a week or buy capacity and finish in a day. Ordering matters, so I embed by recency or traffic weight, meaning that if the migration is halted the most-queried content is already done. Before cutover I run a retrieval evaluation on a fixed query set against both indexes, because a newer model is not automatically better on my corpus and I have watched upgrades lose recall on domain jargon. Throughout the window, newly ingested documents write to both indexes. Afterwards the old index survives a rollback period and is then deleted, since storage at that scale is not free.

I put the control at ingestion, before anything persists in a form that is hard to unwind, because once text has been embedded and copied into three systems, retracting it becomes an archaeology project. Detection is deliberately layered: deterministic validators for structured identifiers carrying checksums, like card numbers, national ids, and IBANs, which are high precision and nearly free; a trained span model for names, addresses, and free-text mentions; and pattern rules for the client-specific formats generic detectors always miss, such as an internal employee reference. I tune for recall and accept false positives, because a redacted invoice number is an annoyance while a leaked identifier is an incident. Redaction should be reversible where the business genuinely needs it, which means tokenising into a vault-backed surrogate rather than replacing with an opaque block, and irreversible where it does not. Then verification: sample the output, measure leakage against a labelled set, and scan the embedding store separately, because chunk overlap can reintroduce a span that was caught in one copy. Every row records which detector version processed it.

The thing teams are unprepared for is that most approximate nearest neighbour indexes are not built for churn. With HNSW, deletes are typically tombstones — the vector stays in the graph and gets filtered at query time — so a corpus with heavy turnover slowly degrades in both recall and latency until the segment is rebuilt. So I track deleted fraction per segment and trigger compaction above a threshold, usually ten to twenty percent, scheduled away from peak traffic. Memory is the other operational reality, since HNSW graphs live in RAM: capacity planning is vector count times dimensions times four bytes plus graph overhead, and quantisation is the lever once that stops fitting, at a recall cost I want measured rather than assumed. I also insist on the ability to rebuild the whole index from the source of truth, because the vector store is a derived artifact and must never be the only copy. The metrics I watch are p99 query latency, recall against a brute-force baseline on a sampled query set, build duration, and the drift between document count in the lake and vector count in the store.

Two percent with no alert means the loss sits inside a path that treats it as normal, so I instrument the boundaries before theorising. I add a count at every hop — source emitted, broker received, consumer read, transformed, written — over the same window, keyed on event time rather than processing time, so late data does not masquerade as loss. The hop where the numbers diverge tells you almost everything. The usual candidates are records failing deserialisation and being skipped by a permissive parser, which is the classic silent drop; a windowed aggregation whose watermark closes before late events arrive, discarding rather than dead-lettering them; consumer group rebalances with auto-commit running ahead of processing; or a join quietly dropping rows with a null key. I also verify the source count is what I think it is, because soft-deleted rows and a mismatched timezone in the comparison query explain a shocking proportion of these. The durable fix is that discarded records must always land somewhere countable, and reconciliation counts should run daily as a test rather than as an investigation.

By treating the dataset as an immutable addressable artifact rather than a query somebody ran once. Every training set gets a manifest: the list of source record identifiers with content hashes, the filter and dedup logic pinned to a commit, the pipeline version, and the timestamp of the snapshot it was cut from. That manifest is stored beside the model, and it is what makes the question answerable at all, because regenerating from the same query a year later returns something different once upstream tables have mutated. Underneath, the raw layer must be append-only with soft deletes and effective-dated records so point-in-time reconstruction is even possible; a pipeline that overwrites is one whose history is gone. I capture column-level lineage automatically wherever the tooling allows rather than relying on documentation, since hand-maintained lineage is wrong within a month. The use cases that justify the cost are not academic — a licensing dispute, a deletion request, or an evaluation revealing the model learned from a source later found corrupted, where you need to know precisely which models are affected.

I split assertions into three tiers, because they fail differently and deserve different responses. Structural checks are cheap and blocking: primary key uniqueness, referential integrity against the dimension being joined, non-null on fields the contract declares required, enum membership. These run before a table is published and a failure stops the publish. Volume and distribution checks are statistical: row count inside a band derived from the trailing fourteen days for that weekday, null rate per column within tolerance, category proportions not shifting past a threshold. Those alert rather than block, because a genuine business change will trip them and blocking trains people to ignore them. Business-logic checks catch the real bugs: totals reconciling against an independent source, no future-dated events, no negative amounts where impossible. Where they run matters as much as what they assert, so I use write-audit-publish — stage into a temporary table, run the suite, swap atomically — which means bad data is never visible even briefly. And I manage alert fatigue explicitly: any check firing more than about twice a month without action gets fixed or deleted.

Backfills are dangerous mainly because they compete with production for the same resources and the same tables, so the first rule is writing to a separate location rather than in place. I run the corrected logic into a shadow table partition by partition, then compare it against the existing one — row counts, key overlap, distribution of the fields that changed — so I know exactly what the correction did before anyone sees it. The work gets chunked by partition with checkpointing so a failure resumes instead of restarting, and concurrency capped so the cluster does not starve the nightly jobs; usually I run at lower priority across several nights rather than saturating for one. The swap should be atomic at the table format level so readers never observe a half-rewritten history. Then the part people forget: everything derived from that table — aggregates, features, embeddings — is now stale and needs its own backfill in dependency order, and consumers caching results need telling. I keep the pre-backfill snapshot for a rollback window and warn stakeholders that numbers in old reports will move.

I get attribution before cutting anything, because the intuitive answer is usually wrong. Break the bill down by bucket, prefix, storage class, and object age, then look separately at request and egress charges, which on some workloads exceed storage outright — a job listing a million small objects on every run can cost more in API calls than the bytes cost to keep. Typical findings are uncompacted small files, intermediate outputs nobody deletes, a table format accumulating orphaned snapshots because expiry was never configured, three copies of the same corpus in different shapes, and logs retained at full fidelity for two years. My cuts go in order: lifecycle rules moving cold partitions to infrequent access and then archive, snapshot expiry and orphan cleanup, compaction, and dropping derived datasets recomputable in under an hour. What I will not cut is the immutable raw layer, because losing the ability to reprocess costs far more than the storage does. Then I make it stick by tagging every dataset with an owner and showing each team its own line item, since unattributed cost regrows within two quarters.

First I get a specific example, because 'unusable' spans everything from a genuine correctness bug to a schema they did not expect. It is usually one of three things. The grain differs from what they assumed — I delivered one row per event, they wanted one per session — which is a specification miss on my side for never writing the grain down. Or there is leakage: a column populated after the outcome they are predicting, which is my bug and a serious one, and the fix is point-in-time correctness, joining features as of the prediction timestamp instead of as of now. Or the distribution surprises them, it is entirely real, and what they actually need is documentation of why. The process change I push after a second occurrence is a written dataset request stating grain, label definition, time window, and point-in-time semantics, plus a sample delivered within a day so the mismatch surfaces before I build the full pipeline. I also hand over freshness and quality metrics alongside the table, rather than the table on its own.

The goal is that adding a source is configuration rather than a bespoke pipeline, so I invest in a small number of connector shapes — polled API, database replication, file drop, webhook stream — and make everything else declarative: source config, credential reference, schedule, schema, contract, target. Each connector lands raw payloads unmodified into an immutable zone with ingestion metadata attached, and only afterwards does source-specific transformation run. Keeping raw untouched is what makes reprocessing possible when a parser turns out to be wrong, and eventually one will be. The platform supplies the cross-cutting concerns once: retries with backoff, secret management, schema registration, dead-lettering, lineage capture, freshness metrics, cost attribution per source. Self-service matters at this count, so a team should open a pull request with a config file and get a reviewed pipeline, which means spending real effort on validation at review time instead of making a central team the bottleneck. What breaks first is operational noise: three hundred sources each failing monthly is ten pages a day, so alerting must be tiered by consumer criticality from the beginning.

Log-based capture gives low latency, records deletes and intermediate states, and loads the primary far less than a query-based snapshot scanning large tables nightly. The costs are real though: the connector becomes a stateful service whose replication slot will fill the primary's disk if a consumer stalls, schema changes arrive as events you must handle, and the initial snapshot plus streaming handover is genuinely tricky engineering. Snapshots are dumb and reliable, and for a slowly changing dimension of a few million rows I take the snapshot and spend my complexity budget elsewhere. On exactly-once I would be precise, because the term oversells itself: what these frameworks provide is effectively-once processing inside one system's boundary, using offsets plus transactional or idempotent writes. It does not extend across an arbitrary external sink, and anyone who believes it does will eventually be surprised. So the property I actually engineer for is idempotency — deterministic keys, upserts rather than appends, and a reconciliation job comparing source and target counts daily. That is what makes replays safe, and replays are inevitable.

The first three are engineering problems and the fourth is a policy problem, and I separate them explicitly when answering. Deleting from the lake requires finding the records, which means an identity index mapping subject to record locations maintained at ingestion time; retrofitting it means scanning petabytes, so it is a design decision made early or paid for painfully later. In an immutable columnar lake, deletion means rewriting affected files, so I batch requests into a scheduled compaction that satisfies the regulatory window rather than rewriting per request. The warehouse follows by re-derivation. The vector index needs both the vector removed and the source chunk purged, and I verify by querying for the content rather than trusting the delete call returned success. For the model, weights cannot be surgically edited in any manner I would defend to a regulator, so the honest position is a documented retraining cadence with output filtering in the interim, and counsel deciding whether that discharges the obligation. Deletion must also be auditable end to end, because being unable to prove it is equivalent to not doing it.

Centralising quality inside the platform team is the tempting answer and it reliably fails, because that team does not know what the numbers mean; they can tell you a column went null, not that a null is wrong there. What works is producer ownership backed by platform-provided mechanism. The producing team owns the contract and the tests for their data, while my team supplies the framework, the registry, the monitoring, and the catalogue so that owning it costs them an afternoon instead of a project. Then make ownership visible: every dataset in the catalogue carries a named owner, a tier, and a published freshness and quality history, and untiered datasets get no support commitment at all. The lever that genuinely changes behaviour is incident review — when a wrong number reaches a leadership dashboard it gets a postmortem with the producing team present, exactly like a service outage. I would also resist certifying everything; three tiers with perhaps fifty datasets in the top one is far more credible than a thousand nominally covered. The failure mode is a catalogue full of stale owner fields, so I tie ownership to existing on-call rotations.