Data Analytics ETL and Data Warehousing Interview Questions and Answers

Every dashboard an analyst builds sits on top of a data warehouse, and interviewers want to know you understand how data gets there and why it is modeled the way it is. ETL and warehousing questions are less about writing pipelines and more about grasping the shape of analytics-ready data: fact tables, dimensions, star schemas and the load process that fills them. This page covers the questions that recur in analyst interviews, answered with the practical clarity that shows you know your way around a warehouse.

How to answer ETL and warehousing questions

Anchor answers in why analytics data is modeled differently from application data. A transactional database is normalized for fast, safe writes; a warehouse is denormalized for fast reads and aggregation. Most warehousing questions are variations on that single trade-off, and naming it explicitly frames every answer well.

Q1. What is the difference between ETL and ELT?

ETL extracts data, transforms it in a separate processing layer, then loads the clean result into the warehouse. ELT extracts and loads raw data first, then transforms it inside the warehouse using its own compute. Cloud warehouses have made ELT popular because their compute is cheap and scalable.

The judgement point is when each fits. ELT keeps raw data available for reprocessing and leverages warehouse power, which suits modern cloud stacks. ETL still makes sense when you must transform or mask sensitive data before it lands, or when the source is heavy and you want to reduce what you load.

Interview note: Follow-up: "why did ELT rise?" Separation of cheap storage and elastic compute in cloud warehouses (and tools that transform in-warehouse) made loading raw and transforming later both cheaper and more flexible.

Q2. What is a data warehouse, and how is it different from a database?

A data warehouse is a central repository optimized for analytical queries and reporting, integrating data from many sources into a consistent, historical, read-optimized model. A transactional database (OLTP) is optimized for many small concurrent writes and is highly normalized.

The contrast to draw is workload-driven: warehouses answer "how did sales trend across regions over three years" by scanning and aggregating huge volumes, so they are denormalized and columnar. An OLTP database answers "insert this order, update this balance" quickly and safely, so it is normalized and row-oriented.

Interview note: Trap: "just query the production database for analytics?" Heavy analytical scans compete with transactional workload and slow the app; separating them into a warehouse protects both.

Q3. Explain OLTP versus OLAP.

OLTP (Online Transaction Processing) handles frequent, small read-write transactions with normalized schemas — think order entry. OLAP (Online Analytical Processing) handles complex read-heavy aggregations over large datasets with denormalized schemas — think dashboards. Warehouses are OLAP systems.

A crisp way to remember: OLTP is many users doing tiny writes; OLAP is few analysts doing huge reads. The schema design follows directly — normalize to avoid update anomalies in OLTP, denormalize to avoid expensive joins in OLAP.

Interview note: Follow-up: "can one system do both?" Hybrid (HTAP) systems exist, but the classic architecture separates them precisely because their optimizations conflict.

Q4. What is a star schema, and how does it differ from a snowflake schema?

A star schema has one central fact table (measures plus foreign keys) surrounded by denormalized dimension tables. A snowflake schema normalizes those dimensions into sub-tables, creating more joins. Star is simpler and faster for queries; snowflake saves storage and reduces redundancy.

-- star schema query: fact joined directly to dimensions
SELECT d.region, p.category, SUM(f.sales_amount) AS total
FROM fact_sales f
JOIN dim_store d   ON f.store_key   = d.store_key
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY d.region, p.category;

The star schema's simple single-level joins are why it dominates analytics — queries are easy to write and fast to run. Snowflaking a dimension (splitting product into product and category tables) reduces redundancy but adds joins, so most warehouses favor star unless storage or maintenance forces normalization.

Interview note: Trap: "snowflake is always better because it is normalized?" In analytics the extra joins usually cost more than the storage saved. Denormalized star is deliberately chosen for read speed.

Q5. What are fact tables and dimension tables?

A fact table stores the measurable, quantitative events of a business process — sales amount, quantity, at a defined grain — along with foreign keys to dimensions. Dimension tables store the descriptive context — who, what, where, when — that you slice and filter by.

The concept that must be crisp is grain: the level of detail one fact row represents (one row per order line, per day, per store). Getting the grain right is the foundation of a correct model, and interviewers often ask you to state the grain of a fact table before anything else.

Interview note: Follow-up: "additive, semi-additive, non-additive facts?" Sales are additive across all dimensions; an account balance is semi-additive (not additive over time); a ratio is non-additive. Knowing this prevents summing things that should not be summed.

Q6. What are slowly changing dimensions, and how do you handle Type 2?

Slowly changing dimensions (SCD) describe how to handle changes to dimension attributes over time. Type 1 overwrites the old value (no history). Type 2 inserts a new row with effective dates and a current flag, preserving full history. Type 3 keeps previous and current values in separate columns.

Type 2 is the one interviewers press on because it preserves history correctly. If a customer moves from Hyderabad to Bengaluru, Type 2 closes the old row (end date, current flag false) and opens a new one, so a sale made last year still attributes to Hyderabad. A surrogate key per version is what makes this work.

Interview note: Trap: "just update the customer's city?" That is Type 1 and it rewrites history — last year's sales would wrongly show the new city. Type 2 is needed when historical accuracy matters.

Q7. What is staging, and why use it in an ETL pipeline?

A staging area is an intermediate landing zone where raw extracted data sits before transformation and loading. It decouples extraction from transformation, allows validation and reprocessing without re-hitting the source, and provides a recovery point if a load fails.

The reliability argument wins: pulling from source once into staging means you can rerun transformations after a bug without re-extracting or overloading the source system. Staging also lets you compare incoming data against the target to detect changes for incremental loads.

Interview note: Follow-up: "full load vs incremental load?" Full reloads everything (simple but expensive); incremental loads only new or changed rows since the last run, using timestamps or change-data-capture, which is essential at scale.

Q8. How do you ensure data quality and handle failures in ETL?

Validate at each stage (schema, ranges, referential integrity), make loads idempotent so a rerun does not duplicate data, log row counts and reconcile against the source, and design for restartability with clear checkpoints. Alert on anomalies like a sudden drop in row volume.

Idempotency is the detail that signals experience: a well-built load can be safely rerun after a partial failure without double-counting, typically via upserts keyed on a natural or surrogate key. Reconciling counts against the source turns "the job succeeded" into "the job loaded the right data".

Interview note: Trap: "if the job ran without error, the data is correct?" No error does not mean correct data — a silent source change or partial extract can load wrong or incomplete data that only reconciliation catches.

What interviewers really test

ETL and warehousing rounds check whether you understand why analytics data is shaped the way it is — denormalized stars, fact grains, historized dimensions and reliable loads — so that you write correct, fast queries and trust your numbers. Pair this page with the data modeling questions, which go deeper on schema design, and the SQL for analysts set, since every warehouse concept surfaces in your queries. A structured Data Analytics path and a mock interview that walks a real schema will make these concepts concrete.