Which data warehousing topics do interviewers ask about most?
Most interview loops cover the same six areas, and the depth rises with seniority:
- Foundations: what a warehouse is, OLTP versus OLAP, the four classic characteristics.
- Dimensional modeling: facts, dimensions, grain, star versus snowflake schema.
- History handling: slowly changing dimensions and surrogate keys.
- Pipelines: ETL versus ELT, staging, incremental loads, data quality checks.
- Performance and platforms: columnar storage, partitioning, distribution and sort keys, materialized views.
- Design scenarios: model a business process from scratch or explain a mismatch between two reports.
Entry-level rounds stay mostly in the first three; senior rounds lean on the last three and expect trade-offs, not recited definitions. Keep each answer short: the core idea in two or three sentences, then one concrete example.
How do you answer the definition and OLTP versus OLAP questions?
What is a data warehouse? A database designed for query and analysis rather than transaction processing. It usually holds historical data taken from operational systems and other sources, integrated into one consistent format so reports agree with each other.
How does OLTP differ from OLAP? OLTP systems run predefined operations that touch a handful of records, such as saving one order. A warehouse query often scans thousands or millions of rows, such as total sales for all customers last month. OLTP schemas are fully normalized to protect writes; warehouse schemas are partially denormalized to speed up reads. OLTP keeps weeks or months of data; a warehouse keeps months or years. Microsoft describes OLAP as technology that organizes large business databases for complex calculations and trend analysis without disrupting transactional systems.
What are the characteristics of a data warehouse? Subject oriented, integrated, nonvolatile and time variant, the four characteristics set out by Bill Inmon. Give one line on each and note that nonvolatile means data should not change once loaded, because the warehouse records what already happened.
How should you explain facts, dimensions and grain?
A fact table stores the numeric measures of a business event plus a foreign key to each related dimension. A dimension table stores descriptive context, usually wide and flat, and supplies the filters and labels on reports. Keep the example concrete: a sales fact with quantity and amount, joined to date, product, store and customer dimensions.
Interviewers then ask about grain. The Kimball Group calls declaring the grain the pivotal step, because it fixes exactly what one fact row represents. Mention the four-step process in order: choose the business process, declare the grain, identify the dimensions, identify the facts. Then say you would start at the atomic grain, the lowest level the process captures, because it survives questions nobody predicted.
Expect a follow-up on the three fact table types. A transaction fact has one row per event. A periodic snapshot has one row per period, even when nothing happened. An accumulating snapshot tracks a process with a date key per milestone and is the only type whose rows are routinely updated. Bonus points for knowing that balances are semi-additive: they sum across every dimension except time.
How do you answer slowly changing dimension questions?
Start by defining the problem: a dimension attribute, such as a customer's region, changes, and reports must show old facts under either the old value or the new one. Then walk through the three core slowly changing dimension (SCD) types.
| SCD type | What happens on change | History kept |
|---|---|---|
| Type 1 | Old value is overwritten in place | None; aggregates built on the old value must be recomputed |
| Type 2 | A new row is added with a new surrogate key | Full; at least three extra columns: effective date, expiration date, current row indicator |
| Type 3 | A new column holds the prior value | One previous value, used relatively rarely |
A common follow-up is to describe a Type 2 load in SQL terms. Answer in two steps: update the current row for that natural key to set its expiration date and turn off its current flag, then insert a new row with a fresh surrogate key, the new values, today as the effective date and the current flag on. Finish by explaining why surrogate keys are required: the natural key repeats across rows once changes are tracked, so it can no longer serve as the primary key.
What ETL and ELT questions should you expect?
ETL versus ELT: ETL is a data integration process that consolidates data from many sources into one store, with transformations applied by a separate engine, often through staging tables. ELT differs only in where the transformation runs: inside the target data store, using its own processing power.
Why use a staging area? It holds raw extracts so cleansing and consolidation happen before data reaches reporting tables. Oracle notes that most data warehouses use one.
How do you load incrementally? Describe the options you have actually used: a last-modified timestamp or high-water mark, change data capture from the source log, or comparing a new snapshot to the previous one. Mention that loads should be rerunnable without creating duplicates, usually with a MERGE or delete-then-insert on the batch key.
How do you protect data quality? Row counts and sums reconciled against the source, uniqueness checks on keys, null checks on required columns, and referential checks that every fact key finds a dimension row.
What schema design questions come up for mid-level roles?
- Star or snowflake? A snowflake normalizes dimension hierarchies into extra tables. The Kimball Group advises against it because it is harder for users and can slow queries, and a flat dimension holds the same information.
- What is a conformed dimension? One with the same column names and values across fact tables, so results from different processes line up in one report. The bus matrix plans this: rows are business processes, columns are dimensions.
- What is a factless fact table? A fact table with keys but no measures, such as student attendance. Paired with a coverage table, it can also show what did not happen.
- What is a degenerate dimension? A key like an invoice number kept in the fact table with no dimension table behind it.
- What is a junk dimension? One table that combines scattered low-cardinality flags, holding only the combinations that actually occur.
- What is a role-playing dimension? One physical dimension, usually date, used several times in a fact table, such as order date and ship date, each through its own view.
What platform questions come up for Snowflake, Redshift and BigQuery roles?
If the job names a platform, expect questions about how it stores and scans data.
- Columnar storage: each block holds one column's values for many rows. Amazon Redshift's documentation gives the example of a 100-column table where a query using five columns reads about five percent of the data.
- Redshift distribution: the styles are AUTO, EVEN, KEY and ALL, and AUTO applies if you specify none. KEY places matching join values on the same slice; ALL copies the whole table to every node, which suits small dimensions.
- Redshift sort keys: data is stored in 1 MB blocks with min and max values kept per block, so range filters can skip blocks that cannot match.
- BigQuery partitioning and clustering: partitions can be based on a date or timestamp column, ingestion time or an integer range, and filters let BigQuery prune partitions it does not need. Clustering sorts storage blocks by chosen columns.
- Snowflake compute: each virtual warehouse is an independent compute cluster that does not share resources with other warehouses, so workloads can be isolated.
- Snowflake Time Travel: the standard retention period is 1 day; Enterprise Edition allows up to 90 days for databases, schemas and tables.
- Materialized views: a pre-computed query result stored for later use, useful when the same heavy query runs often. Snowflake materialized views require Enterprise Edition, and Redshift refreshes them with REFRESH MATERIALIZED VIEW or an auto refresh setting.
How do you handle design scenario questions?
Prompts such as design a warehouse for an online store test structure. Use the four-step process out loud: pick one business process (order lines), declare the grain (one row per order line), list dimensions (date, customer, product, promotion, ship method) and facts (quantity, net amount, discount). Then say how you would load it, which dimensions need Type 2 history, and how you would partition the fact table by date.
For troubleshooting prompts, such as two dashboards showing different revenue, walk from the report back to the source: compare filters and grain, check whether one report counts cancelled orders, reconcile row counts at each pipeline step, and confirm both use the same conformed dimensions.