Key Data Warehouse Concepts Every Interviewee Should Know

1. What Is a Data Warehouse

Bill Inmon defines a data warehouse as a subject‑oriented, integrated, time‑variant, and relatively stable collection of data that supports management decision‑making. Ralph Kimball describes it as a system that extracts, cleans, normalizes, and loads source‑system data into dimensional storage to enable query and analysis. Combining both views, a data warehouse aggregates data from source systems and provides a stable, integrated dataset for managerial decisions.

2. Why Build a Data Warehouse

The purpose is to support management decisions. For a sales company, the most basic support is providing sales performance data. Without a warehouse, managers must query the production database directly, causing two problems: it impacts production workloads and the data cannot be reused. Therefore a separate repository for online analytical processing (OLAP) is needed.

3. Theories and Models

Effective data organization is the focus of data‑warehouse modeling. Two main modeling approaches are discussed:

ER Model (Entity‑Relationship Modeling) – Commonly used for OLTP databases; when applied to warehouses it emphasizes enterprise‑wide data integration, requiring a comprehensive analysis of all business processes and data flows. It is costly and time‑consuming but yields a unified view. The classic example is Teradata’s Financial Services Logical Data Model (FS‑LDM), which partitions financial business into ten subjects.

Dimensional Modeling – Designed for analytical scenarios, it builds warehouse models around analysis needs, prioritizing fast, flexible query performance. It does not require a full business‑process map, allowing quicker demos and easier understanding. The star schema is its concrete form. Other models such as Dan Linstedt’s Data Vault and the Anchor model exist, but Kimball’s dimensional model and its variants dominate current practice.

4. Core Concepts in Dimensional Modeling

Dimension – Represents the context of analysis (e.g., city, department, product category). In practice, dimensions correspond to the fields appearing after WHERE or GROUP BY clauses.

Fact – The measurable values (e.g., quantity sold, revenue, profit margin). Facts are numeric, usually integer or floating‑point, and are classified as additive, semi‑additive, or non‑additive.

Dimension Table – Stores descriptive attributes for each dimension (e.g., user, product, date, region). An example of a slowly changing dimension is a table that captures daily snapshots to handle gradual changes over time.

Fact Table – Centers on a business process; each row records an event (order, payment, refund) and includes additive measures plus foreign keys to dimension tables. Fact tables are categorized as:

Transactional fact tables – capture individual business events; can be single‑transaction or multi‑transaction.

Periodic snapshot fact tables – measure the state of a business at regular intervals (e.g., monthly credit‑card spend).

Cumulative snapshot fact tables – track elapsed time between events (e.g., time from order to delivery).

Snowflake vs. Star Schema – The snowflake schema normalizes dimension tables into multiple related tables, reducing storage (e.g., saving ~11 bytes per row). The star schema denormalizes dimensions into a single table, simplifying joins and improving query speed. In practice, many implementations embed frequently used dimension attributes directly into the fact table, creating controlled redundancy to avoid costly multi‑level joins.

Technical considerations include decreasing storage costs versus increasing CPU and memory efficiency, the growing importance of time cost (join latency), and Hadoop‑based environments where each join may spawn an additional MapReduce job, adversely affecting performance.

Data Domain – A logical grouping of business processes and dimensions (e.g., product domain, log domain, transaction domain) that can accommodate new business needs without impacting existing structures.

Granularity – Defines the meaning of each row in a table (e.g., a log row represents a single user action, an order row represents a single purchase).