Whether you want to be an ETL tester, data analyst, Power BI developer or data engineer, data warehouse concepts come up in every interview. This page explains each concept in plain English with a small example, in the order you should learn them.
What Is a Data Warehouse?
A data warehouse is a central database that stores integrated, historical data from many source systems, organised for reporting and analysis. A bank, for example, combines core banking, cards, loans and CRM data so it can report on a customer across all products and years.
Bill Inmon's classic definition: subject-oriented, integrated, time-variant and non-volatile.
OLTP vs OLAP
| OLTP (source systems) | OLAP (data warehouse) | |
|---|---|---|
| Purpose | Run the business (orders, payments) | Analyse the business (reports, trends) |
| Operations | Many small inserts and updates | Large reads and aggregations |
| Design | Normalised | Denormalised (star schema) |
| History | Current state | Years of history |
| Example | UPI payment app database | Monthly payments dashboard |
Data Warehouse Architecture and Layers
Sources (CRM, ERP, files, APIs)
| Extract
Staging (raw copy, temporary)
| Transform (clean, conform, apply rules)
Integration / core warehouse (facts and dimensions)
|
Data marts / semantic layer
|
Reports, dashboards, ML
Cloud lakehouses use similar layers called bronze, silver and gold. See ADF and Databricks testing. The process that moves data between layers is ETL: what is an ETL pipeline.
Dimensional Modelling: Facts, Dimensions and Schemas
- Fact table: measurable events (sales). Dimension table: context (customer, product, date). Details: fact and dimension tables.
- Grain: what one fact row represents. Decide it first.
- Star schema: fact joined to denormalised dimensions. Snowflake schema: dimensions normalised into sub-tables. Comparison: star vs snowflake schema.
- Conformed dimensions: shared across facts so reports agree.
Keys in a Data Warehouse
| Key | Meaning | Example |
|---|---|---|
| Business (natural) key | ID from the source system | customer_id C1023 |
| Surrogate key | Warehouse-generated integer key | customer_key 501 |
| Foreign key | Fact column pointing to a dimension | fact_sales.customer_key |
| Degenerate dimension | Transaction ID kept in the fact | order_number |
Surrogate keys let the warehouse keep history (several rows per customer) and integrate sources with clashing IDs.
Slowly Changing Dimensions (SCD)
| Type | Behaviour | Example: customer moves Pune → Bengaluru |
|---|---|---|
| Type 0 | Never change | City stays Pune |
| Type 1 | Overwrite | City becomes Bengaluru; history lost |
| Type 2 | New row with dates/flag | Old row Pune (expired), new row Bengaluru (current) |
| Type 3 | Previous-value column | current_city Bengaluru, previous_city Pune |
How to test them: SCD testing.
Full Load, Incremental Load and CDC
- Full load: reload everything. Simple, slow.
- Incremental load: only new or changed rows since the last run, usually using a timestamp watermark.
- Change data capture (CDC): reads changes from database logs.
- Late-arriving data: facts arriving before their dimension row; handled with an "unknown" member.
Data Marts, Data Lakes and Lakehouses
- Data mart: a subject-specific subset (sales mart, finance mart).
- ODS (operational data store): near-real-time integrated copy for operational reporting.
- Data lake: raw files of any format, cheap storage.
- Lakehouse: lake storage with warehouse features (tables, transactions), for example Delta Lake on Databricks.
Kimball vs Inmon
| Kimball (bottom-up) | Inmon (top-down) | |
|---|---|---|
| Approach | Build dimensional data marts by business process | Build a normalised enterprise warehouse first |
| Model | Star schemas with conformed dimensions | 3NF core, then marts |
| Speed to value | Faster | Slower start, strong integration |
| Reference | The Data Warehouse Toolkit (Ralph Kimball) | Building the Data Warehouse (Bill Inmon) |
Data Warehouse Concepts Interview Questions
1. What is a data warehouse?
A central, integrated and historical store of data from many sources, designed for reporting and analysis.
2. What is the difference between OLTP and OLAP?
OLTP runs day-to-day transactions in normalised databases; OLAP supports analysis on large historical data, usually in star schemas.
3. Why use surrogate keys?
To keep history for SCD Type 2, integrate sources with conflicting IDs and keep joins fast with integer keys.
4. What is a conformed dimension?
A dimension shared by several fact tables with the same meaning, so reports across processes agree.
5. What is the difference between a data warehouse and a data lake?
A warehouse stores structured, modelled data for reporting; a lake stores raw data of any format for flexible use.
40 more: data warehouse interview questions.
Data warehouse basics: star vs snowflake schema · fact and dimension tables · SCD types · data reconciliation · data warehouse testing · data warehouse interview questions
More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · SQL for testers · QA to data engineer · ETL testing kya hai (Hindi) · ETL testing projects for practice · ETL testing notes · work from home ETL jobs
Frequently Asked Questions
What are the basic concepts of a data warehouse?
OLTP vs OLAP, staging and layers, ETL, fact and dimension tables, star and snowflake schemas, surrogate keys, slowly changing dimensions, full and incremental loads, and data marts.
What are the 4 characteristics of a data warehouse?
According to Bill Inmon: subject-oriented, integrated, time-variant and non-volatile.
What is the difference between a data warehouse and a database?
An operational database runs transactions and holds current data. A data warehouse integrates data from many databases and keeps history for analysis.
What is Kimball methodology?
A bottom-up approach that builds dimensional star schemas by business process, linked by conformed dimensions, described in The Data Warehouse Toolkit.
Do ETL testers need data warehouse concepts?
Yes. ETL testers validate data moving into warehouse tables, so they need facts, dimensions, keys, SCDs and load types to design meaningful tests.
Asim Noaman Lodhi
Certified Google Partner · QA Consultant · 12+ Years IT
QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.