Data Warehousing concepts
Every serious data analyst interview touches these concepts — they're the foundation under Power BI and Tableau. Eight concepts, interview-ready.
📅 Last updated: August 2026
EasyWhat is a data warehouse and why do companies need one?
A data warehouse is a central repository that stores integrated, historical data from multiple sources, structured for analysis and reporting rather than day-to-day transactions. Companies need it because operational databases are optimized for fast transactions, not for the heavy analytical queries a business runs across years of data.
EasyOLTP vs OLAP
| OLTP (transactional) | OLAP (analytical) |
|---|---|
| Runs the business — orders, payments | Analyzes the business — trends, reports |
| Many small, fast reads/writes | Few large, complex read queries |
| Current data, normalized schema | Historical data, star/snowflake schema |
| Example: banking app database | Example: sales analytics warehouse |
EasyFact table vs Dimension table
- Fact table: the numbers (measures) — sales amount, quantity, profit — plus foreign keys to dimensions. Long and narrow, millions of rows.
- Dimension table: the context — who, what, where, when (Customer, Product, Region, Date). Wide and short.
Every BI question — "sales by region by month" — is facts sliced by dimensions.
MediumETL vs ELT
- ETL: Extract → Transform (in a staging tool) → Load into the warehouse. Traditional approach.
- ELT: Extract → Load raw data first → Transform inside the warehouse using its compute (modern cloud approach — Snowflake, BigQuery, Databricks).
ELT dominates now because cloud warehouses are cheap and powerful enough to transform at scale.
HardWhat are Slowly Changing Dimensions (SCD)?
How you handle dimension attributes that change over time (a customer moves city):
- Type 1: overwrite the old value — no history kept
- Type 2: add a new row with start/end dates or a current flag — full history preserved (most asked)
- Type 3: add a "previous value" column — limited history
MediumWhat is a surrogate key and why use it instead of a natural key?
A surrogate key is a system-generated integer ID with no business meaning, used as the dimension's primary key. Preferred because natural keys (email, product code) can change, repeat across source systems, or be reused — and integer joins are faster. Surrogate keys are also what make SCD Type 2 possible (same customer, multiple rows, different surrogate keys).
MediumWhat is data granularity (grain)?
The level of detail of one row in the fact table — one row per order line? per order? per day per store? Defining the grain is the first design decision in warehousing; mixing grains in one fact table is a classic design failure interviewers probe.
EasyData warehouse vs data lake vs data mart
- Warehouse: structured, modeled data for BI and reporting
- Data lake: raw data of all types (structured/semi/unstructured) stored cheaply for flexible future use, incl. ML
- Data mart: a small, department-focused slice of a warehouse (e.g. just the sales mart)
Download
Full slide deck by Mahendra
