Top 30 ETL Interview Questions and Answers
30 multiple-choice questions on ETL, drawn from 30 bites out of the 35 tagged ETL on Tezvyn. Answer them here or read straight down. Every question carries the correct option, why it is correct, and a link to the bite it came from.
30 questions. Pick an answer, or open “Show the answer” to read it.
Answers are graded in your browser. Nothing is saved, and no XP or streak is earned here. The app keeps score.
Question 1 of 30
Why does ELT better support iterative ML experimentation than ETL?
Show the answer
Answer: d · It allows repeated transformations of raw data within the warehouse without re-extraction
ELT loads raw data into the target warehouse first, so data scientists can run and revise transformations repeatedly without rebuilding external pipelines or re-extracting source data. Option C describes ETL, which transforms data on a secondary server before loading and requires pipeline changes for new logic.
Question 2 of 30
Which scenario best highlights the primary benefit of using a data lake compared to a data warehouse?
Show the answer
Answer: b · Storing vast amounts of diverse, unprocessed data for future machine learning model training.
The card states that data lakes are ideal for storing "massive volumes of diverse, unstructured data cheaply, without having to define its structure or purpose upfront" and for "data scientists and machine learning engineers who need access to raw data for exploratory analysis and model training." Option B directly reflects this core advantage. Option C describes a use case better suited for a data warehouse, which is optimized for consistent, high-performance reporting.
Read the full bite: Data Lake vs. Data Warehouse: Raw vs. Refined Data
Question 3 of 30
For a new data ingestion pipeline into a cloud data warehouse, which scenario is the strongest argument for choosing an ELT approach?
Show the answer
Answer: a · Raw, high-volume data needs to be retained for future, unpredictable analysis.
ELT is ideal for loading large volumes of raw data directly into a scalable cloud data warehouse, enabling flexible in-place transformations for diverse and evolving analytical needs. Masking sensitive data or conforming to rigid schemas before loading are typical use cases for ETL, which transforms data on a separate server.
Read the full bite: ETL vs. ELT: Key differences and when to use each?
Question 4 of 30
When is ELT generally the preferred approach over ETL?
Show the answer
Answer: b · When semi-structured JSON clickstream data with evolving fields is loaded into a cloud warehouse for later transformation
ELT is ideal for semi-structured data like evolving JSON because the schema is applied after loading in the warehouse, whereas the compliance-focused option describes ETL, since sensitive data often requires cleansing and masking before it reaches the target system.
Read the full bite: Differences between ETL and ELT, and when to choose each
Question 5 of 30
Why has the ELT pattern become more prevalent than ETL in modern data architectures?
Show the answer
Answer: a · The rise of powerful cloud data warehouses allows for efficient in-database transformations.
ELT's popularity is driven by powerful cloud data warehouses that can efficiently handle transformations on raw data, offering greater flexibility. ETL, not ELT, is often chosen for strict compliance because it allows data to be cleaned or anonymized before loading.
Read the full bite: ETL vs. ELT: Key Differences and When to Use Each
Question 6 of 30
Which of the following describes a proper ETL implementation?
Show the answer
Answer: b · A scheduled process automatically extracts data from several sources, transforms it, and loads it into a central database.
ETL is intended as an automated, three-phase pipeline run on recurring schedules, which option B illustrates. Option A represents the manual-run footgun the card warns against, while B omits the required transformation phase and D describes a one-time task lacking recurring automation.
Question 7 of 30
For an automated pipeline loading daily CSVs from cloud storage to a database, which architecture is most efficient and scalable?
Show the answer
Answer: c · An S3 event triggers a Lambda function to stream the CSV, batch records, and execute a single batch INSERT into RDS.
The correct architecture leverages event-driven serverless functions to stream and batch data, avoiding memory limits and inefficient row-by-row database operations. Distractor D, while event-driven and serverless, fails by loading the entire file into memory and performing individual inserts, which are both anti-patterns for scalability.
Read the full bite: Build a pipeline to load CSVs into a database
Question 8 of 30
Which design best distinguishes a production-ready daily CSV pipeline from a simple demo script?
Show the answer
Answer: a · Using an event trigger, validating the file, loading into a staging table, merging idempotently, and alerting on failures
Option A captures the card's production requirements: event-driven triggers, pre-load validation, idempotent staging-to-final loading, and observability. Option C is the most tempting distractor because a cron job on a VM is a common beginner choice, but it introduces latency, unnecessary cost, and duplicate-row risks.
Read the full bite: Outline an automated pipeline to load a daily CSV into a database
Question 9 of 30
Which scenario best illustrates the primary benefit of a Schema-on-Read approach?
Show the answer
Answer: b · Ingesting raw log files from various microservices for exploratory analysis.
Schema-on-Read excels at handling varied, evolving data like raw log files for exploration, as it defers structure definition to query time, optimizing for ingestion speed and flexibility. Options A and B describe use cases where Schema-on-Write is superior due to its emphasis on upfront data consistency and query performance.
Read the full bite: Schema-on-Read vs. Write: Pay for Structure Now or Later?
Question 10 of 30
What is the primary objective of implementing an ETL process in an organization?
Show the answer
Answer: c · To integrate, clean, and structure data from diverse sources into a central repository for analysis.
The card emphasizes that ETL's core purpose is to consolidate and prepare data from various sources into a unified view for business analysis and reporting, typically in a data warehouse. Option C directly reflects this. Option A is incorrect because the card explicitly states ETL is not ideal for real-time needs, which require different processing frameworks.
Question 11 of 30
A key numerical column in your dataset has 10% null values. What is the best initial step to take?
Show the answer
Answer: b · Investigate the cause and distribution of the nulls to inform your handling strategy.
The correct first step is always to investigate why data is missing before applying a solution. Dropping 10% of rows is a significant data loss, and imputing with the mean or median without understanding the data's distribution is premature.
Read the full bite: How would you handle 10% null values in a key column?
Question 12 of 30
Which scenario best illustrates the primary function of a data warehouse?
Show the answer
Answer: b · A retail company analyzing quarterly sales trends across different product lines and regions.
A data warehouse is designed for complex analysis of historical data from various sources, as described in option B. Options A, B, and D describe real-time transactional operations, which are explicitly stated as scenarios where a data warehouse should not be used.
Read the full bite: Data Warehouse: The Single Source of Truth for Analytics
Question 13 of 30
When designing survivorship rules for creating a 'golden record' from multiple data sources, what is the most effective approach?
Show the answer
Answer: b · Work with business owners to define a hierarchy of trust that can be specific to each individual data attribute.
The correct approach involves defining attribute-specific rules with business stakeholders, as the 'source of truth' often varies by attribute. Designating a single system as the source for everything is a common but inflexible strategy that ignores this nuance.
Read the full bite: How do you create a 'golden record' from fragmented data?
Question 14 of 30
Which scenario best illustrates the primary purpose of Data Vault modeling?
Show the answer
Answer: d · Creating a central repository to trace all data changes from diverse systems for regulatory compliance.
Data Vault modeling is designed for "long-term, auditable historical storage, prioritizing data lineage and resilience to change" and is ideal when "regulatory compliance, data lineage, and full auditability are critical" for data from "multiple, disparate operational systems." Option A describes a use case for which Data Vault is explicitly not recommended due to complex and slow direct querying.
Read the full bite: Data Vault Modeling: An Audit-First Data Warehouse
Question 15 of 30
Which scenario best illustrates the primary application of Reverse ETL?
Show the answer
Answer: c · Delivering calculated customer lifetime value scores from a data warehouse to a CRM for sales teams.
Reverse ETL's core purpose is to operationalize curated, aggregated data from a data warehouse by pushing it into business tools, such as providing customer scores to a CRM. It is not designed for initial data ingestion into a warehouse or for real-time, event-driven data synchronization between transactional systems.
Read the full bite: Reverse ETL: From Warehouse Insights to Operational Action
Question 16 of 30
In an SEO content gap pipeline, which operation and key set correctly isolates competitor keywords your domain does not rank for?
Show the answer
Answer: b · Left anti-join on keyword, geography, and device, filtering for competitor rank in the top 20 and your domain absent or below position 100
A left anti-join on keyword, geography, and device with rank filters correctly finds competitor keywords you do not own. Option C is tempting because it compares rankings, but an inner join on keyword text alone ignores geo and device while returning shared terms rather than true gaps.
Read the full bite: Design a content gap tool: data sources and core logic
Question 17 of 30
Which pattern best synchronizes a large OLTP table to a warehouse while correctly handling hard deletes, out-of-order updates, and exactly-once recovery?
Show the answer
Answer: d · Capture database change events from the transaction log, stage them in an open-table format, and merge incremental files into the warehouse with checkpointed offsets
CDC from the transaction log captures every mutating event including deletes and preserves ordering, while staging in an open-table format enables incremental merge into the warehouse with checkpointed offsets for exactly-once recovery. Dual writes risk inconsistency across independent transactions, and simple timestamp watermarking misses hard deletes and struggles with out-of-order rows unless paired with costly full extracts.
Read the full bite: Design an incremental load pipeline from a transactional DB to a warehouse
Question 18 of 30
What is the primary reason data cleansing is a crucial first step in data pipelines?
Show the answer
Answer: a · To ensure that analytical results and applications are built on reliable and accurate information.
The card emphasizes that data cleansing prevents 'Garbage In, Garbage Out' by ensuring data is sound before analysis or application building. While cleansing can reduce dataset size or aid compatibility, its primary role is to ensure the reliability and accuracy of the data used for critical outcomes. Cleansing fixes existing data, it does not generate new data points.
Read the full bite: Data Cleansing: Fixing Your Data Before It Fails You
Question 19 of 30
In which situation would a formal ETL pipeline be considered unnecessary overhead?
Show the answer
Answer: b · When a system operator needs to perform a genuine one-time data transfer that requires no transformation
The card states that ETL adds unnecessary overhead for genuine one-time transfers that a system operator can handle manually without automation. Option D mirrors the canonical example where ETL is the ideal choice.
Question 20 of 30
Which factor most strongly justifies choosing ETL over ELT?
Show the answer
Answer: d · Regulations require that sensitive data be masked before entering storage
ETL is correct because it transforms data on external compute before loading, satisfying requirements to mask sensitive data before it reaches storage. The option citing cheaper warehouse compute is tempting but actually describes a primary reason to choose ELT instead.
Read the full bite: Describe the difference between ETL and ELT and when to choose each
Question 21 of 30
According to the mental model for handling duplicates, what is the primary goal?
Show the answer
Answer: c · To consolidate fragmented information into a single, complete, and accurate record.
The card states the mental model's goal is to "merge them into a single, complete, and accurate record," emphasizing intelligent reconciliation over simple deletion. Option B is a common misconception and explicitly warned against as a "footgun" because it can lead to loss of valuable information or valid transactional data.
Question 22 of 30
Which of the following tasks is explicitly identified as an inappropriate use case for regular expressions?
Show the answer
Answer: a · Analyzing the nested tag structure of an XML document
The card explicitly states, "Do not use regex to parse structured, nested formats like HTML or JSON. These formats are not 'regular' and require a proper parser." XML, being a structured, nested format, falls into this category, making regex an unsuitable tool for parsing its hierarchical structure. The other options represent appropriate and common uses for regular expressions.
Question 23 of 30
Which pipeline design best prevents backpressure during flash sales while enabling cost-efficient sessionization and warehouse loading?
Show the answer
Answer: b · ECS ingestion to Kinesis, S3 batches, EMR Serverless Spark sessionization, and Redshift COPY via Step Functions
The correct answer decouples producers from processors with a buffer and uses batch-friendly EMR Serverless for sessionization before bulk-loading to Redshift. Distractor D is tempting because MSK is a valid buffer, but streaming everything into Redshift ignores that clickstream analytics is typically cheaper and more efficient as batch or micro-batch.
Read the full bite: Design a clickstream pipeline from ingestion to data warehouse
Question 24 of 30
What is the defining architectural difference that makes ELT attractive with modern cloud data warehouses?
Show the answer
Answer: a · ELT loads raw data first and runs transformations using the warehouse's own scalable compute, keeping raw data for reprocessing
ELT loads raw data then transforms in place using the warehouse's elastic compute, preserving raw data for reprocessing. The other options either trivialize the difference, drop extraction, or describe ETL's pre-load transform.
Read the full bite: What is the difference between ETL and ELT?
Question 25 of 30
When modeling feature-adoption events in a warehouse star schema, which decision should you make first and why?
Show the answer
Answer: b · Declare the grain of the fact table so counts are unambiguous
Declaring grain is the foundational step; every measure and dimension depends on what one fact row represents. Key choice, schema shape, and tooling are secondary decisions made after the grain is fixed.
Read the full bite: Designing a warehouse model for feature adoption
Question 26 of 30
A data engineering team needs to build a new pipeline for rapidly evolving data sources, prioritizing the retention of all raw data for future, unpredictable machine learning applications. Which data integration approach is most appropriate?
Show the answer
Answer: b · ELT, as it loads raw data directly into a scalable data warehouse, allowing for flexible, on-demand transformations and preserving the original datasets.
ELT is specifically suited for retaining raw data for future use cases like machine learning and offers flexibility for evolving data sources by transforming data within the warehouse. ETL's rigid, predefined transformations and discarding of raw data make it less suitable for these needs.
Read the full bite: ETL vs. ELT: When to Transform Your Data
Question 27 of 30
What is the main reason ELT fits modern cloud data warehouses well?
Show the answer
Answer: d · It exploits cheap storage and the warehouse's elastic compute to transform in place
ELT loads raw data cheaply then transforms using the warehouse's scalable compute, keeping raw data for later reuse. It actually retains raw data and does not inherently mask sensitive fields before loading.
Question 28 of 30
Why is a time-decay attribution model substantially harder to implement than a last-touch model?
Show the answer
Answer: d · Time-decay needs the full ordered touchpoint path and robust identity stitching, not just the final touch
Time-decay weights every touch by recency, so it depends on a complete, correctly stitched and ordered path. Last-touch only needs the final touch, neither model strictly requires ML, and time-decay spreads credit rather than concentrating it.
Question 29 of 30
When replicating data from a high-throughput OLTP database, which approach is recommended to minimize performance impact on the source system?
Show the answer
Answer: a · Log-based CDC, because reading from the transaction log is an asynchronous process that adds negligible overhead to the database.
Log-based CDC is correct because it reads from the database's write-ahead log asynchronously, which does not interfere with or add latency to active write transactions. While trigger-based CDC is simpler, it adds overhead to every write operation, degrading performance on high-volume systems.
Read the full bite: Implement CDC from an OLTP database to a data warehouse
Question 30 of 30
When implementing Change Data Capture (CDC) from a production OLTP database, which method is most recommended for balancing minimal impact and data completeness?
Show the answer
Answer: a · Log-based CDC, as it reads the database's internal transaction log, minimizing impact on the production system and capturing all data changes, including deletes.
Log-based CDC is preferred because it reads the transaction log, which has minimal impact on the production database and captures all changes, including deletes. Query-based CDC, while simpler, puts significant load on the database and cannot capture deleted records effectively.
Read the full bite: How would you implement Change Data Capture (CDC)?
Could you explain these out loud?
That is what an interview actually tests. Tezvyn gives you questions like these with what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.