What is the difference between ETL and ELT?
ETL transforms data on a dedicated processing engine before loading it, so only cleaned, conformed data lands in the destination; ELT loads raw data into the destination first and transforms it in place using the destination's own compute. ETL made sense when storage and compute were expensive and coupled, whereas ELT exploits cheap cloud object storage and elastic warehouse engines, which is why tools like dbt run transformations inside the warehouse. ELT also preserves the raw data, so transformations can be re-run or corrected without re-extracting from the source.
When should I use a data lake, a warehouse, or a lakehouse?
Use a data warehouse when your data is mostly structured and your workloads are BI and SQL analytics that need fast aggregate queries and strong governance. Use a data lake when you need to land large volumes of raw, semi-structured, or unstructured data cheaply and defer schema decisions, accepting that you must add cataloguing and quality controls to avoid a data swamp. A lakehouse aims to give you both: low-cost open storage with warehouse features like ACID transactions, schema enforcement, and time travel via open table formats, serving BI and machine learning from one copy of the data.
Why does idempotency matter so much in data pipelines?
Pipelines fail and tasks retry, so any step that is run more than once must produce the same result as running it once, or you will get duplicated or corrupted data. Idempotency is what makes retries, catchups, and backfills safe, and it is usually achieved by overwriting a deterministic partition or by merging on a stable business key rather than blindly appending. Designing for idempotency from the start is far cheaper than cleaning up duplicates after a partial failure.
How do I avoid duplicate records when ingesting streaming data?
Most messaging systems deliver at least once, so consumers must tolerate seeing the same message twice. The standard approach is to make the consumer idempotent: deduplicate on a stable message key, or make the downstream write itself idempotent with an upsert (MERGE) on a primary key. For stronger guarantees, combine idempotent sinks with transactional offset commits so an input affects the result exactly once even across failures.
What is a slowly changing dimension and when do I need Type 2?
A slowly changing dimension is a dimension table whose attributes change occasionally, and the SCD type defines how you record those changes. Type 1 simply overwrites the old value and loses history, while Type 2 preserves full history by inserting a new row with validity dates (and often a current-row flag) each time an attribute changes. Use Type 2 when analysts must reproduce what an entity looked like at the time of a past fact, for example reporting sales by the customer's region as it was on the order date.
How should I partition my tables?
Partition on a column that your queries frequently filter on, most often a date, so the engine can prune partitions and scan far less data. Aim for partitions large enough to be efficient but not so coarse that queries still scan irrelevant data; avoid over-partitioning, which creates many tiny files and heavy metadata overhead. Partitioning also enables idempotent partition-level overwrites, which is a key building block for reliable batch and backfill jobs.
What is the small files problem and how do I fix it?
The small files problem occurs when a data lake accumulates many tiny files, which inflates metadata, listing, and task-scheduling overhead and slows down reads. It typically comes from high-frequency streaming writes or over-partitioning. The fix is compaction, periodically rewriting many small files into fewer larger ones, and using an open table format that manages file sizes and lets you run optimise/compaction operations safely.
When should I choose streaming over batch processing?
Default to batch unless you have a concrete latency requirement, because batch is simpler to build, test, reason about, and recover. Choose streaming when the business genuinely needs low end-to-end latency, for example fraud detection, real-time personalisation, or operational alerting, and you can invest in handling event time, out-of-order data, windowing, and exactly-once concerns. Many teams adopt a hybrid: streaming ingestion for freshness with periodic batch jobs for heavy, correctness-critical aggregations.
How do I handle late-arriving data in a stream?
Process by event time rather than processing time, and use a watermark to declare how long you will wait for stragglers before closing a window and emitting results. The watermark is a trade-off: waiting longer captures more late events but increases latency, while emitting sooner is faster but may drop or require reprocessing late records. For events arriving after the watermark, you typically either drop them, route them to a side output, or allow bounded window updates depending on your engine and correctness needs.
What is change data capture and why use it?
Change Data Capture streams row-level inserts, updates, and deletes out of a source database, usually by reading its transaction log rather than polling tables. It gives you near-real-time, incremental replication without running heavy queries against the operational source or scanning full tables. It is the standard way to keep a warehouse, search index, or cache in sync with a transactional system, with Debezium being a common open-source implementation on Kafka.
How do I keep data quality from degrading over time?
Treat data quality as code: add automated tests and constraints that check accuracy, completeness, uniqueness, and validity, and run them in the pipeline so bad data fails or is quarantined before it reaches consumers. Add data observability to monitor freshness, volume, schema, and distribution so anomalies are caught automatically rather than via downstream complaints. Push quality "left" with data contracts, so producers commit to a versioned schema and guarantees and cannot make silent breaking changes.
What is a data contract and what problem does it solve?
A data contract is an explicit, versioned agreement between a data producer and its consumers that specifies the schema, semantics, quality expectations, and SLAs of a dataset. It solves the recurring problem of upstream teams changing a column or its meaning without warning and silently breaking every downstream pipeline and dashboard. By making the interface a first-class, testable artefact, contracts shift quality enforcement to the point of production and allow breaking changes to be versioned and negotiated.
What is the medallion architecture?
The medallion architecture organises a lakehouse into progressive quality layers: bronze holds raw ingested data exactly as received, silver holds cleaned, conformed, and deduplicated data, and gold holds curated, business-level aggregates ready for consumption. Each layer adds structure and trust while isolating raw landing from the tables that analysts query. It makes pipelines easier to reason about, reprocess, and debug, because you can always rebuild a downstream layer from the one above it.
Should I normalise or denormalise my analytical tables?
For analytical, read-heavy workloads on columnar engines you generally denormalise, using star schemas or wide tables to reduce joins and speed up queries, accepting some redundancy and more complex writes. Normalisation belongs to transactional (OLTP) systems where write integrity and avoiding update anomalies matter more than read speed. The decision follows the access pattern: model for how the data will be queried, not for an abstract ideal of purity.
How do I make backfills safe and cheap?
Make every task idempotent and partition-scoped so rerunning a date range overwrites exactly that range rather than appending duplicates. Run the backfill partition by partition with bounded concurrency so you control cost and load on shared infrastructure, and validate a sample before processing the full history. An orchestrator that supports catchup and partitioned reruns, plus a table format that supports atomic partition overwrites, makes large backfills routine instead of risky.
What causes data skew and how do I mitigate it?
Data skew is the uneven distribution of data across keys or partitions, so a few tasks handle far more work and become stragglers that dominate job runtime, most often on joins or aggregations over hot keys. Mitigate it by salting the skewed key to spread it across partitions, repartitioning to balance load, or using a broadcast join when one side is small enough to replicate. Modern engines also offer adaptive query execution that can detect and split skewed partitions automatically.
What is data lineage and why should I care?
Data lineage is the traceable record of where data came from and how it was transformed across systems to reach its current state, captured at the table or even column level. It lets you do impact analysis before a change, debug where a wrong number originated, prove compliance, and build trust by showing consumers a dataset's provenance. Without lineage, every upstream change is a guessing game and every data incident is a manual archaeology exercise.