Data Modelling &amp; Warehousing Interview Questions for Data Analytics

Show answer

Dimensional modelling is a way of designing analytical tables around business processes. Numeric events such as sales or payments go into fact tables, and descriptive context such as customer, product, store and date goes into dimension tables. The design is easy for business users to understand and fast for BI tools to aggregate. For example, a retail model might have one sales fact joined to date, product, store and customer dimensions.

What is a snowflake schema and when would you choose it over a star schema?

Show answer

A snowflake schema normalises dimensions into several related tables, for example product linked to subcategory linked to category, instead of keeping one flat product dimension. It saves some storage and avoids repeating attribute values, but needs more joins and is harder for users and BI tools. Most analytics teams prefer a star schema; snowflaking makes sense when a hierarchy is huge, shared by many dimensions or maintained by a separate team.

What is the difference between a surrogate key and a natural key?

Show answer

A natural key comes from the source system and has business meaning, such as a customer mobile number or product SKU. A surrogate key is a meaningless integer or hash generated in the warehouse. Surrogate keys protect the model when source keys change, get reused or clash across systems, and they are essential for slowly changing dimensions where one customer has several historical rows. Keep the natural key as an attribute for lookups.

What is a degenerate dimension?

Show answer

A degenerate dimension is a dimension key stored directly in the fact table with no separate dimension table, because it has no other attributes worth storing. Invoice number, order number and transaction ID are typical examples. They are useful for grouping order lines back into an order, counting distinct orders or tracing a row to the source system. Creating a separate table just for the number would add a useless join.

What is the difference between ETL and ELT?

Show answer

In ETL, data is extracted from sources, transformed on a separate engine and then loaded into the warehouse. In ELT, raw data is loaded first and transformed inside the warehouse using SQL, often with a tool like dbt. Cloud warehouses with cheap storage and scalable compute made ELT the common choice in 2026, because raw data stays available for reprocessing. ETL still suits cases needing masking or heavy cleansing before data lands.

What is the difference between OLTP and OLAP systems?

Show answer

OLTP systems run day-to-day operations, such as billing or order entry. They handle many small inserts and updates, use normalised tables and need fast single-row access. OLAP systems support analysis, scanning millions of rows to aggregate sales by month or region. They use denormalised star schemas, columnar storage and are mostly read-heavy. Analysts should query OLAP copies so heavy reports do not slow down the live application.

Write a CREATE TABLE statement for a sales fact table linked to date, product and store dimensions.

Show answer

CREATE TABLE fact_sales ( date_key INT NOT NULL REFERENCES dim_date(date_key), product_key INT NOT NULL REFERENCES dim_product(product_key), store_key INT NOT NULL REFERENCES dim_store(store_key), invoice_no VARCHAR(20) NOT NULL, -- degenerate dimension quantity INT NOT NULL, net_amount DECIMAL(12,2) NOT NULL, discount_amount DECIMAL(12,2) DEFAULT 0 );

What is a data mart?

Show answer

A data mart is a subset of the warehouse built for one department or subject, such as sales, finance or HR. It contains the facts and dimensions that team needs, often pre-aggregated and with business-friendly names. Marts make reporting faster and simpler for that team. Ideally they are built from the central warehouse using conformed dimensions, so the finance mart and the sales mart still agree on revenue.

What is the grain of a fact table and why should you decide it first?

Show answer

The grain is the exact meaning of one row in a fact table, such as one order line, one daily store total or one monthly account balance. You decide it first because it controls which dimensions can join, which measures are valid and how totals behave. Mixing grains in one table, for example order lines and order-level shipping charges, causes double counting. Always state the grain in one sentence before adding columns.

What are additive, semi-additive and non-additive facts?

Show answer

Additive facts, like sales amount or quantity, can be summed across every dimension. Semi-additive facts, like account balance or stock on hand, can be summed across accounts or stores but not across time; for time you take the closing or average value. Non-additive facts, like percentages, ratios or unit prices, cannot be summed at all, so you store their components (profit and revenue) and calculate the ratio at query time.

What is a conformed dimension?

Show answer

A conformed dimension is one dimension table with the same keys, attributes and meanings shared by several fact tables. For example, one date and one product dimension used by sales, returns and inventory facts. Because they conform, you can compare sales and returns by the same product category in one report without mismatched definitions. Conformed dimensions are the main way an enterprise warehouse stays consistent across departments.

What is a role-playing dimension?

Show answer

A role-playing dimension is one physical dimension used several times by the same fact table in different roles. The classic case is the date dimension: an orders fact has order date, ship date and delivery date keys, all pointing to the same date table. In SQL you join it three times with different aliases; in Power BI you keep one active relationship and use USERELATIONSHIP or create separate date views for each role.

Write SQL to generate a simple date dimension for 2026.

Show answer

-- PostgreSQL syntax CREATE TABLE dim_date AS SELECT TO_CHAR(d, 'YYYYMMDD')::INT AS date_key, d::DATE AS full_date, EXTRACT(YEAR FROM d) AS year, EXTRACT(QUARTER FROM d) AS quarter, EXTRACT(MONTH FROM d) AS month_no, TO_CHAR(d, 'Mon') AS month_name, TO_CHAR(d, 'Dy') AS weekday, EXTRACT(ISODOW FROM d) IN (6, 7) AS is_weekend FROM GENERATE_SERIES(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day') AS d;

Orders arrive in the warehouse with customer IDs that do not exist yet in the customer dimension. What do you do?

Show answer

These are late-arriving dimensions. Do not drop the orders, or revenue will be understated. Common practice is to insert a placeholder customer row with the natural key and 'Unknown' attributes, link the order to it, and update that row when full details arrive. Alternatively map to a single Unknown member with key -1. Also monitor how many such rows appear daily, because a spike usually means a broken feed.

A query on a 2-billion-row fact table in a cloud warehouse scans the whole table even when you filter one week. How do you fix it?

Show answer

Partition the table by date so the engine can prune partitions outside the filtered week, and cluster or sort by frequently filtered columns such as store or customer. Make sure the filter is on the partition column directly, not wrapped in a function like CAST or DATE_TRUNC that blocks pruning. Select only needed columns, because storage is columnar. For dashboards, also consider a pre-aggregated daily summary table.

What is columnar storage and why do analytical warehouses use it?

Show answer

Columnar storage keeps all values of one column together on disk instead of storing full rows together. Analytical queries usually read a few columns across millions of rows, so the engine reads only those columns. Similar values sit side by side and compress very well, which cuts storage and scan cost. The trade-off is slower single-row inserts and updates, which is why OLTP databases stay row-based.

What is the medallion architecture (bronze, silver, gold)?

Show answer

Medallion architecture organises a lakehouse into three layers. Bronze holds raw data exactly as ingested, for audit and reprocessing. Silver holds cleaned, deduplicated, typed and joined data. Gold holds business-ready tables such as star schemas and aggregates used by dashboards. Each layer is built from the previous one by repeatable jobs, so if a cleaning rule changes you can rebuild silver and gold from bronze.

A junior analyst wants to load one huge flat table with every column into Power BI instead of a star schema. How do you advise them?

Show answer

A flat table is quick to start and fine for a small one-off analysis. But at scale it bloats the model because repeated text attributes compress poorly, makes DAX filters and time intelligence harder, and breaks when you add a second fact like targets or returns. I would show them a star schema with one fact and a few dimensions, compare model size and measure speed, and let the results convince them.

Tell me about a time you designed or improved a data model.

Show answer

Situation: our sales reports used five separate Excel extracts and numbers never matched. Task: build one reliable model. Action: I listed the business questions, fixed the grain at invoice line, built conformed date, product and branch dimensions and moved logic into SQL views, then rebuilt the Power BI reports on top. Result: refresh time dropped from 40 to 6 minutes and month-end reconciliation queries fell sharply.

What is a junk dimension?

Show answer

A junk dimension combines several low-cardinality flags and indicators, such as payment type, is_gift, is_express and order channel, into one small dimension table. Each row is a unique combination of those values, and the fact table stores a single key to it. This keeps the fact table narrow and avoids many tiny dimensions or loose flag columns. It works best when the combinations are limited, typically a few hundred rows.

What is a factless fact table? Give an example.

Show answer

A factless fact table records that an event or relationship happened but has no numeric measures, only dimension keys. Examples are student attendance (student, class, date) or which products were on promotion in which store each day. You analyse it by counting rows. A promotion coverage table is useful for questions like which promoted products did not sell, by comparing it with the sales fact.

What are transaction, periodic snapshot and accumulating snapshot fact tables?

Show answer

A transaction fact stores one row per event, such as each payment. A periodic snapshot stores the state at regular intervals, such as daily stock per store or month-end loan balance. An accumulating snapshot has one row per process instance, like an order, updated as it reaches milestones such as placed, packed, shipped and delivered, with a date for each. Choose based on whether the question is about events, states or process durations.

What is an enterprise bus matrix?

Show answer

A bus matrix is a planning grid with business processes as rows, such as orders, shipments and returns, and conformed dimensions as columns, such as date, customer, product and warehouse. A tick shows which dimensions each process uses. It helps prioritise which fact tables to build, shows which dimensions must be shared and conformed first, and gives leaders a one-page roadmap of the warehouse without technical detail.

Write SQL to get each order's customer city as it was on the order date, using an SCD Type 2 customer dimension.

Show answer

-- dim_customer has valid_from, valid_to (NULL for current row) SELECT o.order_id, o.order_date, c.customer_id, c.city FROM fact_orders o JOIN dim_customer c ON c.customer_id = o.customer_id AND o.order_date >= c.valid_from AND o.order_date < COALESCE(c.valid_to, DATE '9999-12-31');

What is a bridge table and when do you need one?

Show answer

A bridge table resolves a many-to-many relationship between a fact and a dimension. For example, one bank account can have several holders and one customer can hold several accounts, so a bridge table stores account_key, customer_key and often an allocation weight. Without it, joining facts to customers duplicates amounts. Use weights to split balances, or report clearly that totals across customers will exceed the true total.

Write SQL to incrementally load new and changed products into a dimension using MERGE.

Show answer

MERGE INTO dim_product t USING stg_product s ON t.product_id = s.product_id WHEN MATCHED AND (t.name <> s.name OR t.category <> s.category) THEN UPDATE SET name = s.name, category = s.category, updated_at = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (product_id, name, category, updated_at) VALUES (s.product_id, s.name, s.category, CURRENT_TIMESTAMP);

Leadership wants sales by store manager, but managers move between stores every few months. How would you model it?

Show answer

First agree the business rule: should a sale belong to the manager in charge on the sale date, or to the current manager? For history, track the manager on the store dimension as SCD Type 2, or keep a separate store-manager assignment table with start and end dates and join on the sale date. Add a current-manager attribute too, so both views are possible. Document the rule on the report.

Actual sales are at order-line level but targets are set per region per month. How do you model them together?