Top 10 Data Warehouse Interview Questions Every 2026 Engineer Must Master

Data Warehouse Fundamentals

A data warehouse is a subject‑oriented, integrated, and relatively stable collection of historical data that supports management decision‑making. It centralizes storage, improves query performance, and enables multi‑dimensional analysis.

Layered Architecture

ODS (Operational Data Store) : Stores raw data extracted directly from source systems.

DWD (Data Warehouse Detail) : Cleanses and standardizes ODS data, providing a detailed, trustworthy layer.

DWS (Data Warehouse Service) : Performs light aggregations on DWD data to serve various business needs.

ADS (Application Data Service) : Supplies final reporting or metric data for downstream applications.

SQL Optimization Techniques

Create appropriate indexes on filter and join columns.

Leverage partition pruning to limit scanned data.

Push predicates down to the storage layer whenever possible.

Select suitable join types; prefer INNER JOIN and avoid Cartesian products.

Utilize window functions such as ROW_NUMBER() and RANK() for ranking without costly subqueries.

Window Functions

ROW_NUMBER()

: Assigns a unique sequential number to each row, regardless of duplicate values. RANK(): Gives identical values the same rank but leaves gaps in subsequent ranks. DENSE_RANK(): Gives identical values the same rank without gaps.

Hive vs. Spark SQL

Execution engine : Hive runs on MapReduce; Spark SQL runs on Spark Core.

Performance : Spark SQL is generally faster due to in‑memory computation and optimized shuffle.

Usability : Spark SQL provides richer APIs (DataFrame, Dataset) and a more flexible programming model.

Handling Data Skew in Spark

Salted keys : Prefix skewed keys with a random value to distribute them across partitions.

Broadcast join : Broadcast small tables to avoid shuffling large datasets.

Custom partitioner : Define a partitioning function based on data distribution.

Adaptive skew‑join : Enable Spark 3.x automatic skew‑join optimization (e.g., spark.sql.adaptive.skewJoin.enabled=true).

User Retention Metric Design

Define an active user (e.g., a login event). Compute:

New users per day.

Next‑day active new users (users who log in again the following day).

Retention = (next‑day active new users / new users) × 100%.

Slowly Changing Dimensions (SCD)

Type 1 : Overwrite the existing record; no history is kept.

Type 2 : Insert a new row with effective start/end dates to preserve historical versions.

Type 3 : Add extra columns (e.g., prev_value) to store the previous attribute value.

Workflow Scheduling: Airflow vs. DolphinScheduler

Community : Airflow is an Apache project; DolphinScheduler is developed by a Chinese open‑source community.

Scheduling focus : Airflow emphasizes workflow orchestration with DAGs; DolphinScheduler provides stronger distributed scheduling capabilities.

UI : DolphinScheduler offers a Chinese‑language UI that many local users find more intuitive.

Data Quality Monitoring Practices

Monitor null‑value rates for critical fields.

Validate primary‑key uniqueness to detect duplicate records.

Set threshold‑based alerts for abnormal metric fluctuations.

Track data lineage to trace source tables and transformation steps.