Interview Questions and Practice

Twenty questions. That's roughly the pool a data analyst, BI developer or junior data engineer interview at a Pakistani bank, telco, fintech, retailer or consultancy draws from, and we've collected them from students who came back and told us what was asked. Each has a model answer you can adapt. Practise saying them out loud, ideally to another person, because interviewers judge clarity as much as correctness, and the answer in your head is always better than the one that comes out.

Concepts

1. What is a data warehouse and why not report from the operational database?

A separate store that integrates data from many systems, cleaned and modelled for analysis, with history. Reporting on the operational database interferes with transactions, lacks history (values are overwritten), and its normalised row store is slow for aggregates over millions of rows.

2. Explain OLTP versus OLAP.

OLTP records many small transactions with millisecond response on a normalised row store. OLAP answers few large queries that scan millions of rows on a dimensional columnar store. Different workload, schema, storage layout and history needs.

3. Describe the layers of a warehouse.

Sources; staging (raw copies with load metadata); core (cleaned, keyed, historical single source of truth); data marts (subject-specific, often aggregated); BI. Data flows one way; each layer is derived from the previous one. In a lakehouse the same layers are bronze, silver and gold.

4. ETL or ELT: which and why?

ETL transforms on a separate tool before loading. ELT loads raw data and transforms with SQL inside the warehouse. ELT is the cloud default because storage is cheap, compute scales, raw history is kept for replay, and the logic is version-controlled SQL. ETL persists in on-premise SQL Server and Oracle shops, so say you've seen both.

Modelling

5. What are facts and dimensions?

Facts are numeric measurements of business events (quantity, amount) stored one row per event with foreign keys. Dimensions are the descriptive context (who, what, where, when) stored in wide tables with verbose attributes and flattened hierarchies. Every "by" in a question is a dimension; every "how much" is a fact.

6. Star schema versus snowflake schema?

A star keeps each dimension as one flat table, so one join per dimension, user-friendly, fast on columnar engines, some repeated text. A snowflake normalises dimensions into sub-tables, so less repetition, more joins, harder for users. Default to a star; Power BI prefers it.

7. What is the grain of a fact table, and why is it important?

The precise meaning of one row, stated in one sentence, such as "one row per line item on a receipt". It decides which dimensions and facts are valid, and mixed grain causes double counting. Choose the lowest available grain and enforce it with the primary key. If you have a double-counting story from a project or assignment, this is where to tell it.

8. Explain the three types of fact tables.

Transaction (one immutable row per event, sparse, additive); periodic snapshot (one row per thing per period, dense, semi-additive, for balances and stock); accumulating snapshot (one row per process instance with milestone dates, updated in place, for durations and bottlenecks).

9. What is additivity? Give examples.

Fully additive facts sum across all dimensions (amount, quantity). Semi-additive facts cannot sum across time (balance, stock). Non-additive facts never sum (ratios, unit prices); store their components instead.

10. Why use surrogate keys?

Warehouse-generated integers isolate facts from source key changes and cross-system collisions, allow multiple versions of one natural key for SCD Type 2, join faster, and support an unknown row (key 0).

11. What is a conformed dimension?

A dimension shared, with identical keys and labels, by several fact tables, so measures from different processes (sales and returns) can be compared on one report. It is the Kimball "bus", and hardest to negotiate for customer and product.

12. Explain junk and degenerate dimensions.

A junk dimension groups low-cardinality flags (payment type, promo flag, channel) into one small table so the fact carries one key. A degenerate dimension is an identifier such as a receipt number kept on the fact because it has no attributes of its own.

13. Describe SCD Types 0, 1, 2 and 3 with an example.

Take a customer moving city. Type 0 keeps the original. Type 1 overwrites, so history is lost. Type 2 closes the old row and inserts a new version with a new surrogate key and valid_from/valid_to, which keeps full history and is the analysis default. Type 3 adds a previous_city column. Types are chosen per attribute, not per table, and saying so is what separates a textbook answer from a practitioner's.

14. Why have a date dimension rather than a date column?

It stores calendar facts a function cannot derive: public holidays, Ramadan and Eid, the July-to-June fiscal year, same-day-last-year keys. It gives consistent month names and sort orders, an integer key for partition pruning, and Power BI time intelligence requires it.

Loading and quality

15. How do you load incrementally and make the load safe to re-run?

Detect changes with an updated_at watermark, change data capture, or snapshot comparison. Load with MERGE on the business key so a re-run does not duplicate rows. Load dimensions before facts, log row counts, and gate publication on quality checks.

16. What data quality checks would you build?

Completeness (null rates), accuracy (ranges, and reconcile totals to POS or ledger), consistency (code lists), uniqueness (count versus count distinct of the business key), validity (03xxxxxxxxx phones, 13-digit CNICs), timeliness (freshness SLA). Quarantine failures with reasons and block the BI refresh on critical failures. Mention the reconciliation first. It's the check finance teams care about.

17. What is master data management?

Building one golden record per real-world entity across systems through matching (deterministic on CNIC or phone, then fuzzy), survivorship rules per attribute, and a cross-reference from every source key to a master ID that keys the conformed dimension.

Platforms and delivery

18. Compare a data lake, a warehouse and a lakehouse.

A lake stores raw files cheaply with schema on read and no transactions; a warehouse stores modelled tables with schema on write and ACID; a lakehouse adds an open table format (Delta, Iceberg) to a lake for ACID, schema enforcement and time travel, so BI and ML share one copy. Bronze, silver, gold mirror staging, core, marts.

19. How do you make a large fact table fast?

Columnar storage and narrow column lists; partition by date and filter directly on the partition column; cluster by the most common filter key; replicate small dimensions on MPP engines; integer keys; aggregates for repeated dashboard queries; read the plan to confirm pruning.

20. Where should business logic live: the warehouse or Power BI?

Row-level truths (cleansing, keys, SCD, net = gross minus discount) in the warehouse as columns; filter-dependent calculations (margin %, YTD, YoY) as measures in one certified semantic model with row-level security; thin reports on top so everyone sees the same definitions.

How to answer a design question live

When someone asks "design a warehouse for a food-delivery app", walk the loop in the diagram out loud. Which decisions matter, which sources exist, which processes (orders, deliveries, ratings, refunds) go on the bus matrix. Pick one process, declare its grain ("one row per order line"), list dimensions and facts, state SCD choices, describe the nightly ELT with a quality gate, and finish with the dashboard's measures. Ten structured minutes, and every lesson in this track is in it. If you'd like to rehearse this with someone who has sat on the other side of the table, our interview preparation sessions do exactly that.

The follow-up question that got her hired: a CSA graduate interviewing at a Karachi fintech was asked why "active customers" differed between two dashboards. She answered with conformed dimensions, a golden customer record and one semantic model with one measure definition, then asked whether their customer dimension was Type 1 or Type 2. She was hired that week. The interviewer told her later that the follow-up question decided it.

How to answer anything above

Define the term, say why it matters, give one example, name the trade-off. Four beats, every time. The most-asked topics are grain, star versus snowflake, SCD Type 2, fact table types, surrogate keys and ETL versus ELT. Design questions follow the lifecycle loop from decisions to measures. Pakistani examples (fiscal year, Ramadan flags, phone matching) show you've handled real data, and Gulf and remote employers like them for the same reason. Ask a question back; it shows you think like a designer.

Final practice

1. Record yourself answering questions 7, 13 and 20 in under 90 seconds each. Check each has a definition, a reason, an example and a trade-off.
2. Do the live design exercise for a Pakistani ride-hailing app: bus matrix (four processes, six dimensions), one grain sentence, and DDL for the main fact table.
3. Write three questions to ask an interviewer about their warehouse (customer SCD type, certified semantic model, quality gating) and what a good or bad answer tells you about the team.