ETL and Data Warehousing Interview Questions and Answers
Last updated 2026-11-19
ETL and warehousing interviews are about modelling and loading data so that analysts get correct, consistent numbers. The classic topics are star schemas, fact table grain, slowly changing dimensions, incremental loading and data quality. They are asked in data engineer, BI developer and analytics engineer interviews, often with a design question such as "model an e-commerce warehouse".
This page covers the modelling and loading side. For tools and streaming see data engineer interview questions, and for Spark-based transformation see PySpark interview questions. SQL skills underpin all of it: how to crack the SQL interview.
| Typical rounds | SQL test, schema design question, pipeline discussion |
|---|---|
| Core skills | Dimensional modelling, SCD, incremental loads, SQL, testing |
| Questions here | 14 worked questions |
The modelling vocabulary
- Fact tables hold measurable events; dimension tables hold the descriptive context.
- Grain is what one row of a fact table represents, and it is decided first.
- Surrogate keys are warehouse-generated integers that insulate the model from source key changes.
- Conformed dimensions are shared across fact tables so that numbers can be compared.
- Slowly changing dimensions define how attribute changes are stored over time.
A design answer for any warehouse question
Choose the business process, declare the grain in one sentence, list the dimensions that exist at that grain, then list the facts that are additive at that grain. Say how you would load it (full or incremental), how history is kept, and how you would test it. Following the order of process, grain, dimensions, facts is the heart of dimensional modelling.
ETL and Data Warehousing Interview Questions and Answers: questions with worked answers
1 Compare star schema and snowflake schema.
easyA star schema has a central fact table joined directly to denormalised dimension tables, so most queries need one join per dimension and are simple and fast. A snowflake schema normalises dimensions into sub-tables, for example product to category to department, which saves a little storage and reduces redundancy but adds joins and makes queries harder to read. Modern columnar warehouses make storage savings small, so stars are the default and snowflaking is used for very large or shared hierarchies.
2 What is the grain of a fact table, and why decide it first?
easyThe grain states exactly what one row represents, for example one order line item per product per order, or one account per day. Every dimension and measure must be true at that grain, and mixing grains, such as putting an order-level shipping fee on every order line, causes double counting when summed. Declaring the grain in one sentence before designing keeps the model consistent and tells analysts what COUNT(*) means.
3 Describe the three types of fact tables.
mediumA transaction fact table records one row per event at the lowest grain, such as a sale line. A periodic snapshot records the state at regular intervals, one row per account per day, which suits balances and inventory levels that are not events. An accumulating snapshot has one row per process instance that is updated as it moves through milestones, such as order placed, shipped and delivered dates, and it suits pipeline and lead-time analysis. A factless fact table records events or coverage without numeric measures, such as student attendance.
4 Explain slowly changing dimension types 1, 2 and 3.
mediumType 1 overwrites the old value, so history is lost, which suits corrections. Type 2 adds a new row with a new surrogate key and effective date range each time the attribute changes, so facts remain linked to the version that was true then. Type 3 adds a column for the previous value, which keeps limited history. Type 2 is the most common for attributes whose history matters, such as customer city or sales territory. It needs start date, end date and current flag columns.
5 Implement an SCD type 2 load in SQL.
hardCompare the staged rows with the current dimension rows. First close the current row of any customer whose tracked attribute changed, by setting its end date and current flag. Then insert a new current row for every customer who has no current row, which covers both changed and brand-new customers. Run the two steps in one transaction, and compare attributes null-safely in a real job. The syntax shown is PostgreSQL style.
sqlUPDATE dim_customer d SET end_date = CURRENT_DATE - 1, is_current = FALSE FROM stg_customer s WHERE d.customer_id = s.customer_id AND d.is_current AND d.city IS DISTINCT FROM s.city; INSERT INTO dim_customer (customer_id, city, start_date, end_date, is_current) SELECT s.customer_id, s.city, CURRENT_DATE, DATE '9999-12-31', TRUE FROM stg_customer s LEFT JOIN dim_customer d ON d.customer_id = s.customer_id AND d.is_current WHERE d.customer_id IS NULL;6 Why use surrogate keys?
easyA surrogate key is a meaningless integer generated by the warehouse. It lets one natural key have several dimension rows over time (needed for type 2 history), protects the model from changes or reuse of source keys, makes joins fast and compact, and lets you handle unknown or late-arriving members with reserved keys such as -1. The natural key is kept as an attribute for traceability.
7 Design a star schema for an e-commerce order system.
mediumBusiness process: order fulfilment. Grain: one row per order line. Dimensions at that grain are date (order date, plus ship date as a second role), customer, product, geography and promotion. Measures are quantity, gross amount, discount amount and net amount, all additive across every dimension. The order number is kept as a degenerate dimension in the fact table. Order-level facts such as shipping fee are allocated to lines or held in a separate order-grain fact table. Returns form a second fact table that shares the conformed dimensions.
8 Full load versus incremental load: how do you load only changed data?
mediumA full load truncates and reloads everything, which is simple and self-correcting but slow at scale. An incremental load extracts only new or changed rows. The common method is a high-water mark: store the largest updated_at value loaded so far and next time extract rows above it, minus a small safety overlap for late commits, then upsert on the key. Where there is no reliable timestamp or deletes must be captured, use change data capture. Always make the load idempotent so a rerun does not create duplicates.
sql-- extract the delta SELECT * FROM src.orders WHERE updated_at > (SELECT last_loaded_at - INTERVAL '10 minutes' FROM etl_watermark WHERE table_name = 'orders');9 Explain change data capture and its approaches.
mediumCDC detects inserts, updates and deletes at the source and passes them on. Log-based CDC reads the database transaction log (as tools like Debezium do), has low impact on the source and captures deletes and every intermediate change. Trigger-based CDC writes changes to an audit table through database triggers, which adds write overhead. Timestamp or version-column comparison is the simplest but misses hard deletes and intermediate changes between runs. Choose log-based for high-volume operational systems.
10 Compare the Kimball and Inmon approaches, and mention Data Vault.
mediumKimball builds the warehouse bottom-up from dimensional data marts that share conformed dimensions, delivering business value quickly. Inmon builds a normalised enterprise warehouse first and derives marts from it, giving a single integrated source at the cost of longer delivery. Data Vault models hubs (business keys), links (relationships) and satellites (descriptive history) and is designed for auditability, parallel loading and source changes, usually with a presentation layer on top. Many organisations use a layered approach regardless of the label.
11 What are bronze, silver and gold layers in a lakehouse?
easyThey describe progressive refinement. Bronze holds raw data exactly as ingested, with load metadata, so you can always reprocess. Silver is cleaned and conformed: duplicates removed, types fixed, keys standardised and sources integrated. Gold is business-ready: aggregated tables and dimensional models for reporting. Keeping bronze unchanged means a bug in a transformation can be fixed by rebuilding the later layers.
12 How do you handle late-arriving facts and dimensions?
hardFor a late-arriving fact whose dimension member does not exist yet, insert an inferred member into the dimension with the natural key and default attributes, link the fact to its surrogate key, and update the row when the real dimension record arrives (and, for type 2, follow your rules for history). For facts that arrive late for a past date, load them to the correct date key and reprocess any snapshots or aggregates built on that period. Monitor how late data usually is so you know how far back to reprocess.
13 How do you test an ETL pipeline?
mediumTest at several levels. Reconcile counts and totals between source and target, per load and per key dimension; check uniqueness of keys, not-null on mandatory columns and referential integrity from facts to dimensions; compare checksums on a sample; validate business rules, for example order total equals the sum of its lines; and run regression tests on transformation logic with small known inputs and expected outputs. Automate these as part of the pipeline so failures stop the load.
sql-- facts without a matching dimension row (should return 0) SELECT COUNT(*) FROM fact_sales f LEFT JOIN dim_product p ON p.product_key = f.product_key WHERE p.product_key IS NULL; -- source vs target totals SELECT (SELECT SUM(amount) FROM src.orders WHERE order_date = '2026-11-18') AS src_total, (SELECT SUM(net_amount) FROM fact_sales f JOIN dim_date d USING (date_key) WHERE d.full_date = '2026-11-18') AS dw_total;
Frequently asked questions
Is dimensional modelling still relevant with cloud warehouses and lakehouses?
Yes. Storage is cheap, but analysts still need clear grain, consistent dimensions and history handling, so star schemas remain the common presentation layer.
What SQL should I practise for ETL roles?
Joins, window functions, MERGE or upsert, deduplication and date logic. The SQL guide and window functions page cover these.
How do I answer a warehouse design question?
State the business process, declare the grain, list dimensions and facts, then say how you load, keep history and test.
Do ETL interviews include tools?
Usually some: an orchestrator, a transformation tool such as dbt or Spark, and a warehouse. Describe concepts first and mention the tools you have used.
Related guides
More in Role-wise Interview Questions by Job and Tech Stack
- Excel Interview Questions and Answers
- Express.js Interview Questions and Answers (Express 5)
- FastAPI Interview Questions and Answers
- Flask Interview Questions and Answers
- Flutter Interview Questions and Answers (Flutter 3, Dart 3)
- Frontend Developer Interview Questions and Answers
- Full Stack Developer Interview Questions and Answers
- Generative AI and LLM Interview Questions and Answers
- Google Cloud (GCP) Interview Questions and Answers
- GraphQL Interview Questions and Answers
- Hibernate and JPA Interview Questions and Answers
- HTML and CSS Interview Questions and Answers