Technical interview guide

Data Warehousing & Modeling

Star and snowflake schemas, and the fact/dimension split that makes analytical queries fast.

Read: 29 min
Practice MCQs: 25
Interview QA: 25
Edition: v4
Editorial status: Reviewed
Relevant for: Data ArchitectData EngineerForward Deployed Engineer (FDE)

Scope: BigQuery, Amazon Redshift, Snowflake, Apache Iceberg 1.11, PostgreSQL 18, dbt MetricFlow, and OpenLineage documentation current 2026-08-31.

Interview QA

Treat each question like a live interview question: answer out loud first (structure, assumptions, tradeoffs), then open the model answer to spot gaps and rehearse a tighter follow-up.

Curated: 2026-10-05 · Written: 2026-10-05 · Reviewed: 2026-10-03

QA-1

Design a sales warehouse model for orders, shipments, refunds, and customers.

QA-2

Define and enforce fact-table grain for a new analytical product.

QA-3

Implement a trustworthy SCD Type 2 customer dimension.

QA-4

Handle early-arriving facts and late-arriving dimensions.

QA-5

Choose among transaction, periodic snapshot, and accumulating snapshot facts.

QA-6

Create conformed dimensions across independently owned data marts.

QA-7

Design a semantic metric layer over warehouse models.

QA-8

Choose partitioning, clustering, and sort design for a large fact table.

QA-9

Choose a distribution strategy for a distributed warehouse star schema.

QA-10

Design warehouse data-quality checks and reconciliation.

QA-11

Design an incremental warehouse load from change data capture.

QA-12

Backfill and restate a large historical warehouse partition safely.

QA-13

Evolve a warehouse schema and metric contract without corrupting history.

QA-14

Design aggregate tables or materialized views for dashboard performance.

QA-15

Propagate privacy deletion through a dimensional warehouse.

QA-16

Secure a multi-tenant analytical warehouse and semantic layer.

QA-17

Implement useful warehouse lineage and impact analysis.

QA-18

Control cost in a cloud data warehouse without weakening correctness.

QA-19

Migrate operational reporting tables into a governed warehouse model.

QA-20

Recover a warehouse model after a partial or corrupt publication.

QA-21

Diagnose and improve a slow warehouse query without changing its answer.

QA-22

Manage small files and compaction in a lakehouse warehouse table.

QA-23

Model a many-to-many relationship with a bridge table.

QA-24

How do you reconcile and validate historical metrics between an existing reporting system and a newly migrated dimensional warehouse?

QA-25