Essential Data Warehouse Terms Every Developer Should Master

1. Data Layer Terminology

Data warehouses are organized into layers: ODS (Operational Data Store) directly ingests raw data from source systems; DWD (Data Warehouse Detail) cleans and standardizes the data; DWS (Data Warehouse Summary) pre‑aggregates data for common analytical needs; ADS (Application Data Service) serves business‑facing reports, APIs, and recommendation systems. Each layer depends on the one above it, ensuring a clear data flow and traceability.

2. Data Modeling Terminology

Data modeling in warehouses follows dimensional modeling, consisting of fact tables (recording measurable events such as sales or clicks) and dimension tables (providing contextual attributes like time, location, product, or customer). Fact tables can be transaction, periodic snapshot, or cumulative snapshot types, each serving different analytical scenarios.

Slowly Changing Dimensions (SCD) handle attribute changes over time. Three common strategies are:

SCD Type 1 : Directly overwrite the old value, losing history.

SCD Type 2 : Insert a new row with effective dates, preserving full history (often implemented with a “link table”).

SCD Type 3 : Add a new column for the previous value, keeping only one level of change.

3. Star and Snowflake Schemas

A star schema uses a single fact table linked directly to denormalized dimension tables, offering simple structure and high query performance. A snowflake schema further normalizes dimension tables, reducing redundancy at the cost of more complex joins.

4. Data Development Process Terminology

Traditional ETL extracts data, transforms it on a separate server, and loads it into the warehouse. With cheaper storage and compute, ELT loads raw data into a data lake first and performs transformations in‑place, providing greater flexibility for exploration.

5. Partitioning and Bucketing

Partitioning : Divides a table into independent physical sections based on a column (often a date), allowing queries to skip irrelevant partitions and improve performance.

Bucketing : Distributes rows into a fixed number of files using a hash of a column, optimizing equality joins and sampling queries.

6. Data Quality and Governance

Key data‑quality dimensions include completeness, accuracy, consistency (uniform metric definitions), and timeliness. Common data problems are dirty data (format errors, out‑of‑range values) and data skew (uneven workload distribution across nodes, e.g., one city’s data overwhelming a compute node).

7. Operational Monitoring Terminology

Batch Jobs : Scheduled bulk data‑processing tasks.

Re‑run : Manually re‑executing a failed job after fixing upstream data.

Data Lineage : Tracks the origin, transformations, and downstream usage of data, enabling rapid impact analysis when issues arise.

8. Analysis Application Terminology

OLAP (Online Analytical Processing) supports complex, large‑scale analytical queries, while OLTP (Online Transaction Processing) handles high‑frequency, short‑lived transactional operations. Their design philosophies differ markedly.

Core OLAP operations include drill‑down (navigating from summary to detailed data) and slice‑and‑dice (filtering on one or multiple dimensions, e.g., Q1 2024 sales in East China).

9. Related Architecture Concepts

A data lake stores all raw enterprise data at low cost, serving as a flexible repository for data whose future use is unknown. In contrast, a data warehouse stores cleaned, structured data with a defined schema, optimized for reporting and analysis.

A data middle platform (data‑center) extends the warehouse concept by providing unified data assets, governance, and service capabilities to support diverse front‑office business needs, emphasizing reuse and efficiency.