Data Engineering Interview Questions: Data Warehousing

Inmon's approach treats the enterprise data warehouse as the central, normalized (typically third normal form) store of integrated data, and department data marts are derived from it as dependent marts. Kimball's bus architecture works bottom-up, building dimensional marts that share conformed dimensions. A single wide table and schema-on-read raw storage are not the top-down, normalized-core pattern described.

Data must first be extracted from operational sources before anything can be stored. The raw extracts are landed in a staging area so they are preserved as received. Only then can they be transformed, conformed across sources, and loaded into the central warehouse core. Data marts are built from that core for specific subject areas, and BI dashboards and reports read from the marts last. Each step depends on the output of the one before it, so this sequence is the only valid order.

OLAP workloads are read-heavy and analytical: they scan large volumes of data and compute aggregations such as sums and counts across many rows. Columnar storage suits this pattern because a query touching a few columns reads only those columns from disk. The other two options describe OLTP traits: high-frequency small writes from transactions, and fast single-row lookups by key for operational use cases like checkout.

The fact rows for product 1 (120 and 80) map to Electronics, giving 200. The single row for product 2 maps to Grocery, giving 30. Product 3 has no matching dimension row, so dict.get falls back to Unknown and that row contributes 50. Sorting the (category, total) pairs alphabetically gives Electronics, Grocery, Unknown, and printing the list of tuples yields the stated output. This illustrates how a fact row with an orphan foreign key is kept in the rollup under an Unknown member rather than silently dropped.

This describes Inmon's top-down approach, not Kimball's. Kimball's bottom-up methodology builds dimensional data marts (star schemas) first, using conformed dimensions shared across marts to integrate them into an enterprise view. In Inmon's approach, a normalized 3NF enterprise data warehouse is built first and dependent data marts are derived from it. Because the statement attributes Inmon's sequencing to Kimball, it is false.

The join on line 7 matches each order to every historical version of its customer, because it only compares customer_id and never restricts the dimension row to the version valid on order_date. A customer with several SCD Type 2 versions therefore produces several joined rows per order, and SUM(f.amount) counts the same amount once per version. The fix is to add a validity-range predicate such as f.order_date >= d.valid_from AND f.order_date < COALESCE(d.valid_to, '9999-12-31') to the join condition, so each order matches exactly one dimension row.

Kimball integrates by conforming dimensions across marts so that drill-across queries line up, coordinated through the bus matrix. Inmon integrates by funneling all source data into a single normalized enterprise warehouse first, and marts are dependent views or extracts of that store. An answer is correct when it names conformed dimensions for Kimball and a normalized (3NF) central warehouse feeding dependent marts for Inmon; the two differ in whether integration happens across marts (bus) or in one central model before marts (Inmon).

Data Vault separates the three concerns into distinct table types. Hubs hold the unique business keys of an entity and little else. Links record relationships or transactions between hubs, so they capture many-to-many associations without embedding descriptive data. Satellites hang off hubs or links and store descriptive attributes along with load-date history, which lets the model absorb source changes without restructuring the core keys and relationships.

The key idea is that a deterministic key removes the sequence-generation lookup, which is what forces Kimball-style loads to run dimension-before-fact and makes parallel loading and cross-environment consistency hard. The trade-off is that correctness now depends on consistent normalization and on the hash function's collision resistance and key width, which a sequence never required. A strong answer names both the decoupling benefit and a concrete risk such as collisions, key width/storage, or normalization drift.