ETL
35 bites tagged ETL — interview questions with model answers, and 60-second explainers.
What is the difference between ETL and ELT?
ETL transforms before loading, on a separate engine; ELT loads raw then transforms inside a scalable warehouse. Choose ELT with cloud warehouses and large raw or schema-on-read data. knowing where transformation runs in a pipeline.
ETL versus ELT in cloud data platforms
ETL transforms before loading into the target; ELT loads raw first then transforms in the warehouse, leveraging cheap storage and elastic compute. data pipeline pattern fluency.
Implement CDC from OLTP to warehouse
Contrast log-based CDC with query-based timestamp polling, cover deletes and load on source, then pick log-based for minimal impact. low-impact replication of inserts, updates, and deletes.
Architecting multi-touch attribution
Ingest touchpoints, resolve to one identity, order into paths, apply a model; last-touch is trivial, time-decay needs the full path. Identity stitching and credit assignment. Ignoring cross-device identity.
Designing a warehouse model for feature adoption
Star schema with a feature-usage fact table at a defined grain, surrounded by user, feature, date, and device dimensions. Dimensional modeling skill. One giant wide table or modeling without defining the grain.
ETL vs ELT: when to prefer each for ML?
Tests architecture tradeoffs. Contrast ETL's pre-load transformation with ELT's in-warehouse transformation, map structured legacy to ETL and raw lakes to ELT, and cite ML iteration needs. Red flag: calling one better ignoring compute placement or volume.
ETL: Extract, Transform, Load
ETL moves data through three phases from sources to containers. It handles one or more inputs and outputs via software that automates the process on recurring schedules or in batches. The footgun is defaulting to manual runs when automation is typical.
Design a clickstream pipeline from ingestion to data warehouse
Tests data pipeline design under load: buffering, idempotent transform, and warehouse modeling. A strong answer orders ingestion via Kinesis or MSK, Spark EMR sessionization, and Redshift star schemas. Red flag: no buffer and direct warehouse writes.
Describe the difference between ETL and ELT and when to choose each
Tests transform timing and compute location. ETL cleans data before loading via external engines; ELT loads raw data first, then transforms in the warehouse. Pick ETL when pre-load cleansing is needed, ELT when warehouse compute is cheaper.
Design an incremental load pipeline from a transactional DB to a warehouse
Contrast timestamp watermarking, CDC from transaction logs, and open-table incremental reads; cite merge logic and idempotency. OLTP-to-OLAP sync without full dumps.
ETL: The Three-Phase Data Pipeline
ETL is a three-phase pipeline: extract from sources, transform, and load into containers. It supports many sources and destinations and runs as automated software, manual jobs, or scheduled batches. The footgun is manual execution of recurring jobs.
Design a content gap tool: data sources and core logic
Tests system design for SEO pipelines. Strong answers cite APIs (Ahrefs, Semrush, GSC), explain normalization, and frame logic as a left-anti-join on keyword plus geo and device filtered by rank. Red flag: dismissing API cost, rate limits, and freshness.
Outline an automated pipeline to load a daily CSV into a database
Event trigger on drop, schema validation, idempotent load, and observability. designing a minimal production-ready ingestion pipeline, not just listing tools.
Differences between ETL and ELT, and when to choose each
ETL transforms before loading for structured data; ELT loads raw first and transforms in the warehouse for scale. Pipeline architecture tradeoffs. Calling one better without citing volume, structure, or compute.
Implement CDC from an OLTP database to a data warehouse
This tests your grasp of production system trade-offs. A good answer compares log-based and trigger-based CDC, focusing on source impact and data fidelity, then recommends log-based for its low overhead.
How do you create a 'golden record' from fragmented data?
Tests your ability to design a data reconciliation system. A great answer outlines a process: profiling sources, defining survivorship rules, implementing a matching engine, and creating a stewardship process. A red flag is suggesting a simple one-off script.
How would you handle 10% null values in a key column?
This tests your understanding of data cleaning trade-offs. First, investigate the cause of nulls. Then, discuss simple imputation (mean/median) vs. discarding rows, weighing pros and cons. A red flag is jumping to a solution without asking about the data.
ETL vs. ELT: Key Differences and When to Use Each
This tests your grasp of data pipeline trade-offs. Define ETL (transform first) vs. ELT (load first), contrasting transform location and data state. A red flag is ignoring how cloud warehouses make ELT the modern default for flexibility.
How would you implement Change Data Capture (CDC)?
Tests your grasp of data replication trade-offs. A great answer compares log-based CDC (low impact, complete) with query-based methods (higher impact, misses deletes), and recommends log-based CDC for its minimal production impact.
Build a pipeline to load CSVs into a database
Tests your grasp of event-driven architecture and basic ETL. A good answer outlines a trigger (storage event), a processing function (serverless), and a destination (database), mentioning error handling. A red flag is describing a manual or cron-based process.
ETL vs. ELT: Key differences and when to use each?
This tests your understanding of modern data architecture trade-offs. A good answer explains the T vs. L order, then links ETL to structured, legacy systems and ELT to flexible, cloud-native data lakes/warehouses.
ETL: The Assembly Line for Your Data
ETL (Extract, Transform, Load) is an assembly line for data, moving it from various sources into a single destination for analysis. It's used to populate data warehouses by combining data from databases, logs, and APIs into a unified view.
Data Lake vs. Data Warehouse: Raw vs. Refined Data
A data lake is a vast pool of raw data; a data warehouse is a library of refined data ready for analysis. Lakes store everything for future ML or data science; warehouses power BI reporting on clean metrics.
ETL vs. ELT: When to Transform Your Data
ETL (Extract, Transform, Load) preps data before storage, like a chef prepping ingredients. ELT loads raw data first, transforming it inside the warehouse. Use ETL for structured reporting; use ELT for flexibility with raw data.
Get ETL bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.