Data Warehousing Interview Questions and Answers

OLTP runs the day-to-day transactions of an app. OLAP supports analysis over large amounts of old data.

Keep them separate. Heavy analysis queries on the OLTP database slow the app down. Data moves from OLTP to the warehouse through ETL or ELT.

What they are checking: Basic data platform words.

Both are dimensional models. Each has a central fact table with dimension tables around it.

• Star schema: each dimension is one flat table, joined directly to the fact. It has fewer joins, simpler queries and is faster for BI. Some data is repeated.
• Snowflake schema: dimensions are split into sub-tables (product to category to department). It repeats less data and makes hierarchies easier to maintain. But it needs more joins and the queries are harder.

-- Star: one join to get category
SELECT d.category, SUM(f.amount)
FROM fact_sales f JOIN dim_product d ON d.product_key = f.product_key
GROUP BY d.category;

Columnar warehouses make storage cheap, so a star schema is the better choice in most cases. Use snowflaking only when many dimensions share a hierarchy, or when the hierarchy changes often.

What they are checking: Can you compare the two and explain why star is the default?

A fact table stores measurable events at a chosen grain (the level of detail of one row). A dimension table stores the descriptive details used to filter and group those events.

• Fact: numbers (quantity, amount, duration) plus foreign keys to dimensions. It is very large and narrow, for example one row per order line.
• Dimension: attributes such as customer name, city, product category and date. It is smaller, wide and mostly text.

Example: fact_sales(date_key, product_key, customer_key, quantity, amount) with dim_date, dim_product and dim_customer.

A business question maps neatly: "sales amount (fact) by product category and month (dimension attributes)". Always state the grain of the fact table first. The grain decides which dimensions and measures make sense.

What they are checking: Core words of dimensional modeling.

SCD means slowly changing dimension. It is a way to handle dimension attributes that change over time.

• Type 1: overwrite the old value. There is no history. Use it for corrections, or when history does not matter.
• Type 2: add a new row for each change, with valid_from, valid_to and is_current. It keeps full history. Facts link to the version that was valid at that time through the surrogate key. This is the most common type when you need history.
• Type 3: keep limited history in extra columns (current_city, previous_city). It is simple, but it stores only one earlier value.

Also good to know: Type 0 (never changes), Type 4 (history in a separate table) and Type 6 (a mix of 1, 2 and 3).

Example of Type 2:

What they are checking: Can you describe the SCD types and compare them?

A surrogate key is an ID made by the system. It has no business meaning and is usually an integer or a hash. It is used as the primary key of a dimension.

Reasons to use it:

• It supports SCD Type 2. One natural key (customer C1) has several rows, and each row needs its own key.
• It protects the warehouse from source changes. Natural keys can be reused, change format, or differ between systems.
• It works with many sources. You can combine systems that identify customers in different ways.
• Joins are smaller and faster than with long composite or string keys.
• It allows special members, such as -1 for Unknown.

Fact tables store the surrogate key. During load, look up the key by natural key and business date. Keep the natural key as an attribute so you can trace the data.

What they are checking: Do you know why SCD2 needs surrogate keys?

• Data warehouse: structured, modelled data in a store that checks the schema when data is written (schema-on-write). It is built for SQL analysis and BI. Quality and governance are high. But it is usually limited to structured data, and storage costs more.
• Data lake: raw data of any type (files, logs, images, JSON) in cheap object storage. The schema is applied when data is read (schema-on-read). It is flexible and cheap. Without governance it can turn into a swamp. Its transaction support is weak.
• Lakehouse: lake storage plus open table formats (Delta, Iceberg, Hudi). These add ACID transactions, schema checks and fast SQL. One platform serves BI, ML and streaming.

Many companies use a lake to land raw data. They then use a warehouse or lakehouse layer for clean, ready-to-use data.

What they are checking: Can you compare the three designs and say when to use each?

Both approaches build an enterprise data warehouse, but in opposite directions.

• Kimball (bottom-up): build data marts for each business process as star schemas, using conformed dimensions. The warehouse is the sum of the marts. Value arrives fast. It is easy for users and flexible.
• Inmon (top-down): first build a normalised (3NF) enterprise data warehouse as the single source of truth. Then create department data marts from it. Integration and consistency are strong. But it is slower and costs more at the start.

Most modern teams use Kimball-style marts in the warehouse. Often a raw and staged layer sits in front, and it works like an Inmon integration layer.

What they are checking: Do you know both ideas and their trade-offs?

The grain says exactly what one row in the fact table means. For example: "one row per order line item" or "one row per account per day".

Why it matters:

• It decides which dimensions can attach and which measures are valid.
• Mixing grains in one table causes double counting and wrong totals.
• It sets the size of the table and how detailed the analysis can be.

Process: declare the business process, then the grain, then the dimensions, then the facts. Prefer the lowest practical (atomic) grain. You can always add up detailed data, but you cannot split summary data back into detail. Write the grain in the table description. Test it with a uniqueness check on the grain columns.

What they are checking: Do you start modeling from the grain?

Snowflake separates storage, compute and services.

Warehouses are read-heavy and run big summary queries. Fewer joins give faster and simpler queries. A denormalised dimension keeps related attributes in one table (product, category and brand together). Storage is cheap in columnar systems, because they compress repeated values well.

A common layered design:

Normal forms reduce repeated data and update errors in relational design.

A data mart is a small part of the warehouse data. It focuses on one department or business area, such as sales, finance or marketing. The data warehouse is the company-wide store that joins all areas.

• Degenerate dimension: an identifier with no attributes of its own. It is stored in the fact table, such as an order number or invoice number. It is used to group line items.
• Junk dimension: puts several flags with few values (is_gift, payment_type, channel) into one small dimension. This avoids many tiny tables...

• Additive: can be summed across all dimensions. Examples are sales amount and quantity.
• Semi-additive: can be summed across some dimensions, but not across time. Account balance or inventory level can be summed across accounts. Summing it across days makes no sense. For time, use the average or the last value. -...

A date dimension is a ready-made table with one row per calendar day. It has many attributes that SQL date functions do not give in a consistent way.

MPP spreads one query across many nodes. Each node works on its own slice of data at the same time. Columnar storage keeps each column together.

An ODS is a database that joins current or near-real-time data from several operational systems. It is used for operational reports and lookups. It sits before the warehouse or next to it.

Kimball describes three main types, plus factless tables.

• Transaction fact: one row per event at the lowest grain (each order line, each click). This is the most common and most flexible.
• Periodic snapshot: one row per item per period (account balance at month end, daily inventory). It suits status measures that are not events.
• Accumulating snapshot: one row per process instance. It is updated as the item moves through stages (order placed, shipped, delivered), with several date columns. It is used to study pipelines and lead times.
• Factless fact: records that an event happened or a link exists, with no number to measure (student attended class, product on promotion).

Pick the type from the business process. Events lead to transaction facts. Regular status checks lead to snapshots. A workflow with milestones leads to accumulating snapshots.

What they are checking: Can you choose the right fact table type?

BigQuery is a serverless, columnar data warehouse. Storage (Capacitor format) and compute (Dremel engine) are separate. Google manages the resources.

Redshift is an MPP (massively parallel) columnar warehouse. Data is spread across the slices of the compute nodes. Two table settings change performance.

Both reduce the data scanned, but in different ways.

Conformed dimensions are dimensions shared by many fact tables or data marts. They have the same keys, attribute names and meanings. Date, customer and product are typical.

Time Travel lets you query, clone or restore data as it was at an earlier time. It works inside a retention window (1 day by default, up to 90 days on higher editions).

Use a bridge table between the fact (or dimension) and the dimension that has many values.

Structure:

• fact_visit has a diagnosis_group_key.
• bridge_diagnosis_group(diagnosis_group_key, diagnosis_key, weighting_factor).
• dim_diagnosis holds the diagnoses.

Load dimensions first, then facts, because facts need dimension keys.

Key controls:

• Login and role-based access control with least privilege. Use roles and groups, not grants to single users.
• Column-level security and masking policies for PII (most users see a masked email).
• Row-level security, so users see only their own region or tenant.
• Encryption at rest and in transit, with...

Both store results that are calculated in advance. So frequent queries do not need to scan the detailed facts again.

Data Vault is a modeling method for the integration layer of an enterprise warehouse. It is built for auditing, growth and easy change.

Core parts:

• Hubs: unique business keys.
• Links: relationships between hubs.
• Satellites: descriptive attributes with history. They are loaded with timestamps and the record source.

A factless fact table has foreign keys to dimensions but no numbers to measure. It records that an event happened, or that a relationship exists.

A semantic layer defines business metrics and dimensions once, in a central place (revenue, active customer, churn rate). Every BI tool or notebook then uses those definitions.

A centralised warehouse has one central team. The team loads, models and serves data for the whole company. A data mesh spreads ownership. Each business domain owns its data and publishes it as a product.

Start with the business process (placing an order). Then declare the grain: one row per order line item.

Fact: fact_order_line

• Keys: date_key, customer_key, product_key, store_or_channel_key, promotion_key, payment_key.
• Degenerate dimension: order_number.
• Measures: quantity, unit_price, discount_amount, tax_amount, net_amount (all additive).

Dimensions:

• dim_date (day, week, month, quarter, holiday flag).
• dim_customer (SCD Type 2 for segment and city; keep the natural key).
• dim_product (name, brand, category, hierarchy).
• dim_channel, dim_promotion, and a junk dimension for flags like is_gift.

Extras: a separate fact_returns that shares the conformed dimensions. A periodic snapshot for inventory. An accumulating snapshot for order fulfilment (order, ship and delivery dates).

Use surrogate keys. Use an Unknown member (-1) for missing references. Add tests for uniqueness at the grain and for foreign key integrity.

What they are checking: Can you model from start to finish using the Kimball steps?

First check what the query does and how much data it touches.

First decide if it is a correction or a real change.

Late-arriving fact: the fact shows up after its date. Load it normally. But look up the dimension version that was valid on the event date, not the current one. Then reprocess the aggregates and snapshot tables for that date.

Business process: completed trips. Grain: one row per trip.

Find the cost drivers first: query history, warehouse usage and storage.

Plan in phases and keep the risk low.

Never drop the fact silently, because the totals would be wrong. Choose a documented strategy: