Data Engineering — Frequently Asked Questions
Practical answers on building data pipelines — ELT versus ETL, warehouse or lakehouse, batch versus streaming, idempotency, silent failure, data quality checks that matter, and who should own a pipeline.
Design choices#
ETL or ELT?#
ELT for most modern warehouse work: load the raw data first, transform inside the warehouse. Keeping the raw layer means a transformation bug is fixable by re-running rather than by asking the source system for history it may no longer have.
ETL still fits when data cannot land in its raw form for legal reasons, when volume makes loading everything wasteful, or when the transformation is what makes the data safe to store.
Do we need a warehouse, a lake, or a lakehouse?#
Start with a warehouse if your data is mostly structured and your questions are mostly analytical — it is the simplest thing that works, and simplicity is worth a lot in a data platform.
Add a lake when you have volumes of semi-structured or raw data that would be expensive to hold in a warehouse and that you want to keep anyway. "Lakehouse" describes the pattern of running warehouse-style tables over lake storage; it is a sensible destination and a poor starting point for a small team.
The failure to avoid is the lake with no catalogue and no ownership. Data nobody can find and nobody maintains is storage cost, not an asset.
Batch or streaming?#
Batch unless something genuinely reacts within seconds. Streaming multiplies the operational surface — ordering, late events, replay, state, exactly-once semantics that are rarely exactly what they sound like.
Ask what decision is made with the data and how fast. Most reporting is consumed daily by humans, and a well-run hourly batch beats a fragile stream that nobody can debug at 3am.
How often should a pipeline run?#
As often as the decision it feeds actually requires, and no more. Every run costs money and every schedule creates an expectation. A dashboard refreshed hourly that people look at on Monday mornings is six wasted runs a day and a service level you now have to meet.
Reliability#
What does idempotent mean here, and why does it matter so much?#
Running the same period twice produces the same result as running it once — usually by overwriting a partition or upserting on a key rather than appending blindly.
It matters because recovery always involves re-running. A pipeline that duplicates rows when re-run cannot be recovered under pressure; you end up hand-editing production data during an incident, which is how small problems become large ones.
Our pipeline "succeeded" but the data was empty. How do we prevent that?#
Check the values, not the exit code. A job that catches its own errors, writes an empty file and exits zero is reported as green by every scheduler.
The rule that prevents it: fail closed. Below a minimum row count, write nothing, keep the previous good output, and exit non-zero. Stale data that is labelled stale is recoverable; empty output propagates downstream in minutes, and the dashboards that read it will show zeroes rather than errors.
How would we know if a pipeline stopped running altogether?#
You would not, if your monitoring only watches runs — a pipeline that is never triggered produces no failures to alert on. Monitor freshness of the output: if the newest record is older than it should be, something is wrong regardless of what the scheduler believes.
What quality checks are actually worth running?#
The ones that catch silent wrongness, run on every load, and fail the run:
- Row count is not zero, and is within an expected range
- Primary key is unique
- Freshness: the newest timestamp is within the expected window
- Null rate on critical columns
- Values inside their allowed domain
- Totals reconcile against the source
Distribution shift is worth a warning rather than a failure — it catches real problems and produces false alarms on legitimate business change.
Change and ownership#
The source changed its schema and broke us. What is the fix?#
Two things. Technically: validate the incoming contract and fail loudly on an unexpected shape rather than silently mapping a renamed column to null.
Organisationally: make sure the source owner knows you depend on them. Most breaking changes are routine work by a team that had no idea anyone consumed their table. A short conversation buys more reliability than a clever schema-inference layer.
Who should own a pipeline?#
A named person, not a rota and not "the data team". The owner answers for freshness, cost and correctness, and receives the alert.
The strongest arrangement is that the team who owns the source data also owns its published output, with the platform team owning the tooling underneath. Ownership by whoever built it last degrades quickly.
How much documentation does a pipeline need?#
One page: what it feeds, where the data comes from, when it runs, whether it is safe to re-run, what breaks downstream if it is late or wrong, who to call. Anything longer will not be read during an incident, which is the only time documentation is truly load-bearing.
Business rules encoded in transformations deserve special attention — they are frequently the only written record of a rule the business actually operates by.
Cost and scale#
Why is the warehouse bill increasing?#
Usually queries rather than storage: dashboards refreshing on a schedule nobody reviews, full scans where a partition filter would do, and transformations that rebuild everything nightly when they could process an increment.
Look at cost by query and by user before adding capacity. The top few queries frequently account for most of the spend.
When should we introduce a data catalogue?#
When people start asking which of two similar tables is the right one — that question is the signal. Before that, a catalogue is overhead; after it, the absence of one costs analysts hours a week and produces contradictory numbers in meetings.
How do we handle personal data in a pipeline?#
Decide classification at the source, carry it through, and avoid copying personal data into places that do not need it — every copy is another thing to protect, to retain correctly and to delete on request. Pseudonymise where analysis does not require identity, restrict access by role, and make sure deletion requests reach the warehouse and the backups policy, not just the production database.