11 Data Engineering Data Modeling Interview Questions and Answers

Short answer: OLTP serves short transactions — inserting and updating individual rows with low latency, while OLAP answers analytical queries that scan millions of rows. OLTP stores data row-wise, OLAP column-wise. You don't run heavy analytics on the production OLTP database because it competes for resources with transactions and tanks production latency.

In depth:

1. Workload profile — OLTP: many small operations (INSERT/UPDATE by primary key); OLAP: few heavy queries with GROUP BY and aggregations.
2. Storage model — row storage reads the whole row (good for transactions); columnar reads only the needed columns and compresses better (good for analytics).
3. Normalization — OLTP is normalized (3NF) for write integrity; OLAP is denormalized for read speed.
4. Resource isolation — an analytical scan floods the buffer cache and disk, so transactions start to wait.

⚠️ Common mistake: running reports directly against the production database. Extract data into a separate warehouse (ETL/ELT) so analytics doesn't disturb transactions.

Short answer: Dimensional modeling splits data into fact tables (measurable business events) and dimension tables (context: who, what, when, where). A star is one fact table in the center surrounded by denormalized dimensions. It beats a fully normalized model for analytics because it needs fewer joins and is intuitive for analysts.

In depth:

1. Fact table — holds numeric metrics (amount, quantity) and foreign keys to dimensions; many rows, few columns.
2. Dimensions — descriptive attributes for filtering and grouping (date, product, customer); few rows, many columns.
3. Grain — the level of detail of one fact row; declare it first.
4. Why star — predictable fact→dimension joins, simple queries, and the optimizer handles it well.

               ┌────────────┐
               │  dim_date  │
               └──────┬─────┘
                      │
┌────────────┐   ┌────┴───────┐   ┌──────────────┐
│ dim_product│───│ fact_sales │───│ dim_customer │
└────────────┘   │  metrics + │   └──────────────┘
                 │   dim FKs  │
               ┌─┴────────────┘
               │
         ┌─────┴──────┐
         │ dim_store  │
         └────────────┘

⚠️ Common mistake: not declaring the grain. Without a clear grain, rows of different levels land in the fact table and metrics double-count on aggregation.

Short answer: In a star, dimensions are denormalized — each dimension is one flat table. In a snowflake, dimensions are normalized and split into a hierarchy of related tables (product → category → department). The snowflake saves space and removes duplication but adds joins and complicates queries; on modern columnar warehouses star is usually preferred.

In depth:

• Star — a dimension keeps its whole hierarchy in one table (denormalized); fewer joins, simpler SQL, faster reads.
• Snowflake — the hierarchy is spread across several tables (normalized); less duplication, easier to maintain reference data, but more joins.
• Denormalization tradeoff — you pay in storage and drift risk for speed and simplicity. On columnar warehouses storage is cheap while extra joins are expensive — so star usually wins.

⚠️ Common mistake: normalizing dimensions to save gigabytes on a warehouse where storage is cheap — paying for it with joins in every analytical query.

Short answer: SCD (Slowly Changing Dimensions) describe how to store changes to dimension attributes over time. Type 1 overwrites the value (no history), Type 2 creates a new row version (full history), Type 3 keeps the prior value in a separate column (limited history). SCD2 is the standard when you need to see how data looked at the time of an event.

In depth:

1. Type 1 — UPDATE over the old value. Simple, but history is lost.
2. Type 2 — a new row with a surrogate key, effective dates (valid_from/valid_to) and an is_current flag. Facts reference the version valid at the event date.
3. Type 3 — a previous_value column. Keeps only one step back.

-- SCD2: close the old version; insert the new one as a separate INSERT
MERGE INTO dim_customer AS tgt
USING staging_customer AS src
  ON tgt.customer_nk = src.customer_nk
 AND tgt.is_current = TRUE
WHEN MATCHED AND tgt.city <> src.city THEN
  UPDATE SET tgt.valid_to   = CURRENT_DATE,
             tgt.is_current = FALSE
WHEN NOT MATCHED THEN
  INSERT (customer_sk, customer_nk, city,
          valid_from, valid_to, is_current)
  VALUES (GENERATE_SK(), src.customer_nk, src.city,
          CURRENT_DATE, DATE '9999-12-31', TRUE);

⚠️ Common mistake: using SCD1 where you need history. Overwriting a customer's city retroactively rewrites every past report too — the event binds to the new value.

Short answer: There are three fact table types: transaction (one row per event), periodic snapshot (a slice of metrics per period — day, month) and accumulating snapshot (one row per process, updated as it moves through stages). The type dictates the grain, and you declare the grain before choosing columns.

In depth:

1. Transaction — a row per event (sale, click); the most detailed grain, grows fastest.
2. Periodic snapshot — a regular slice (stock balance at end of day); rows aren't updated, great for trends.
3. Accumulating snapshot — a row per process instance (an order), with stage-date columns filled as it progresses (created → paid → shipped); the row is updated.
4. Why grain first — the grain defines what one row means; without it, metrics of different granularity mix and double-count.

⚠️ Common mistake: designing fact columns before declaring the grain — later you find the metrics can't be summed without double-counting.

Short answer: A data warehouse stores structured, modeled data for BI with a strict schema (schema-on-write). A data lake stores raw files of any format cheaply on object storage (schema-on-read). A lakehouse adds a layer with ACID transactions and tables (Delta/Iceberg/Hudi) on top of the lake, combining the lake's cheap storage with the warehouse's reliability and performance.

In depth:

• Warehouse — Snowflake/BigQuery/Redshift; data is cleaned and modeled, excellent BI and SQL, but pricier and worse for raw data and ML.
• Data lake — S3/GCS with Parquet/JSON; cheap and flexible, stores everything, but without transactions it easily turns into a data swamp.
• Lakehouse — table formats (Delta Lake, Apache Iceberg, Hudi) over object storage; provide ACID, time travel, and schema enforcement directly on files.

⚠️ Common mistake: dumping raw data into a lake with no catalog, schema, or transactions — a year later it's an unmanageable data swamp nobody can query.

Short answer: A natural key is a business identifier from the source (email, tax ID, order number). A surrogate key is an artificial technical identifier (usually an integer auto-increment) carrying no meaning. In a warehouse, dimensions are keyed by surrogates: they're stable, compact in joins, and — crucially — let you store multiple versions of one business entity under SCD2.

In depth:

1. Stability — a source natural key can change (a customer changes email); a surrogate never does.
2. SCD2 — under versioning one natural key spawns several rows, so the dimension's primary key must be the surrogate.
3. Performance — an integer surrogate is more compact and faster in joins than a composite or string natural key.
4. Source isolation — swapping the source system doesn't break facts that reference surrogates.

CREATE TABLE dim_customer (
  customer_sk   BIGINT   PRIMARY KEY,   -- surrogate key
  customer_nk   VARCHAR  NOT NULL,      -- natural key from source
  email         VARCHAR,
  valid_from    DATE     NOT NULL,
  valid_to      DATE     NOT NULL,
  is_current    BOOLEAN  NOT NULL
);
-- the fact references the surrogate, not the business key
-- fact_sales.customer_sk -> dim_customer.customer_sk

⚠️ Common mistake: making the natural key the primary key of an SCD2 dimension — once a second row version appears, the key is no longer unique.

Short answer: Inmon builds top-down: first a normalized enterprise warehouse (3NF) as the single source of truth, then marts from it. Kimball builds bottom-up: dimensional marts (stars) right away, tied together by conformed dimensions (the bus). Inmon is pricier and slower up front but more consistent; Kimball delivers business value faster. Modern ELT on dbt usually leans Kimball.

In depth:

• Inmon (top-down) — a central normalized EDW feeding dependent marts; strong integrity, high cost of entry.
• Kimball (bottom-up) — a set of stars with conformed dimensions; fast time-to-value, integration via the dimension bus.
• Where ELT/dbt fits — raw data is loaded into the warehouse (ELT), and dbt transformations build staging → intermediate models → dimensional marts. In spirit it's Kimball, with elements of both.

⚠️ Common mistake: building independent marts with no conformed dimensions — you get silos whose metrics can't be compared.

Short answer: Data Vault is a modeling method for integrating data from many sources with full history and auditability. It splits data into three entity types: hubs (business keys), links (relationships between keys), and satellites (descriptive attributes with history). Unlike a star, Data Vault is optimized not for analyst queries but for flexible loading and data lineage.

In depth:

1. Hub — the unique business keys of an entity (customer_id) plus load metadata; the stable core.
2. Link — a many-to-many relationship table between hubs (order ↔ customer).
3. Satellite — context and attributes attached to a hub or link, with change history (like SCD2).
4. Why — you can plug in new sources without reworking the model, everything is historized and auditable. Analysts build star marts on top of the Vault.

  ┌───────────┐        ┌───────────┐
  │   HUB     │        │   HUB     │
  │ customer  │        │  order    │
  └─────┬─────┘        └─────┬─────┘
        │      ┌──────┐      │
        └──────┤ LINK ├──────┘
               └──┬───┘
     ┌─────────┐  │  ┌─────────┐
     │   SAT   │◄─┴─►│   SAT   │
     │  attrs  │     │  attrs  │
     └─────────┘     └─────────┘

⚠️ Common mistake: handing Data Vault to analysts as a query mart — its many joins make it awkward for BI; you still build dimensional marts on top of it.

Short answer: One Big Table (OBT) is a single wide denormalized table where facts are already joined to all dimension attributes, with no joins on read. On modern columnar warehouses, where unused columns aren't scanned, OBT gives very fast, simple queries. The price is data duplication, bloat when dimensions change, and loss of dimension reusability.

In depth:

• OBT — everything in one table; zero joins, simple SQL, ideal for dashboards and BI tools. But changing a dimension attribute must be smeared across every row.
• Star — facts + separate dimensions; reusable dimensions, cheaper storage, cleaner SCD, but joins are required.
• When OBT — stable dimensions, speed and simplicity matter, a columnar engine (BigQuery/Snowflake). When star — many shared dimensions, active SCD, storage savings.

⚠️ Common mistake: using OBT for fast-changing dimensions — every attribute change forces rewriting millions of fact rows instead of one row in a dimension.

Short answer: Partitioning physically splits a table into parts by a column's value (usually a date) so a query scans only the needed partitions — that's partition pruning. Clustering (a sort key) orders data within a partition by frequently filtered columns so the engine skips irrelevant blocks. Together they cut the scan volume, and thus query time and cost.

In depth:

1. Partitioning — splitting by date/region; a filter on the partition key prunes irrelevant partitions. Choose a column that's almost always in the WHERE.
2. Clustering / sort key — sorting data within a partition; using block metadata the engine skips blocks that don't match the filter (block/zone skipping).
3. Granularity — too-fine partitions (hourly) spawn tiny files and overhead; too-coarse ones prune poorly.

-- BigQuery: partition by date + clustering
CREATE TABLE sales.fact_orders
PARTITION BY DATE(order_ts)          -- prune by day
CLUSTER BY customer_id, product_id   -- skip blocks within a day
AS SELECT * FROM staging.orders;

-- the query scans just 1 partition out of thousands:
SELECT SUM(amount)
FROM sales.fact_orders
WHERE order_ts >= '2026-06-01'
  AND customer_id = 42;

⚠️ Common mistake: partitioning by a high-cardinality column (e.g. customer_id) — you get thousands of tiny partitions, which slows queries down rather than speeding them up.