Data Warehouse Concepts

Thursday, 27 November 2025

🏢 Data Warehouse (DWH): A central repository of integrated, historical data designed for query and analysis. Its primary purpose is to support business intelligence.
🏪 Data Mart: A subset of a data warehouse tailored to serve a specific business line (e.g., Sales, Finance). It contains a focused collection of data.
🔄 Operational Data Store (ODS): A database for integrating data from multiple sources for operational reporting. It is more current and volatile, often acting as a staging area.
🌊 Data Lake: A vast repository holding massive amounts of raw data in its native format (structured and unstructured) until needed. No predefined schema required.
🏡 Data Lakehouse: A modern architecture combining the flexibility of data lakes with the data management and ACID transactions of data warehouses.
🚧 Staging Area: A temporary storage area for data extraction, cleansing, and transformation before loading into the warehouse. Not typically queried by end-users.
📊 Business Intelligence (BI): Technologies and practices for collecting, analyzing, and presenting business information to support decision-making.

🗺️ Schema: The logical description of the entire database, including table structures and relationships.
⭐ Star Schema: The simplest schema consisting of a central fact table connected to multiple dimension tables in a star shape.
❄️ Snowflake Schema: A variation of the star schema where dimension tables are normalized into multiple related tables. Reduces redundancy but increases complexity.
🌌 Galaxy Schema (Fact Constellation): A complex schema with multiple fact tables sharing dimension tables.
🔐 Data Vault Modeling: A hybrid method for long-term historical storage, composed of Hubs, Links, and Satellites. Resilient to change and highly scalable.
🏷️ Dimension: A category of information (the "who, what, where, when"). Provides context to facts (e.g., Customer, Product).
🔢 Fact: A measurement or metric, typically numerical (e.g., Sales Amount, Quantity).
🟢 Fact Table: The central table containing measurements (facts) and foreign keys.

🔵 Dimension Table: A table storing descriptive attributes related to a business dimension (e.g., Customer Name, City).
🌾 Grain (Granularity): The level of detail in a fact table (e.g., "one row per line item").
🔑 Surrogate Key: A system-generated unique identifier (integer) used as a primary key, independent of the source system.
🆔 Natural Key (Business Key): An identifier from the operational source system (e.g., CustomerID).
🕰️ Slowly Changing Dimension (SCD): Techniques to manage data changes over time.
- Type 1: Overwrite old value (No history).
- Type 2: Add new row (Preserve history).
- Type 3: Add new column (Limited history).
🤝 Conformed Dimension: A dimension that represents the same thing across different fact tables (e.g., Date).

🚚 ETL (Extract, Transform, Load): The process of moving data from source to warehouse.
- Extract: Reading data.
- Transform: Cleaning and structuring data.
- Load: Writing data to the target.
☁️ ELT (Extract, Load, Transform): Loading data into the target system before transformation. Common in modern cloud platforms (Snowflake, BigQuery).
🚰 Data Pipeline: A system moving data from one place to another; may or may not involve heavy transformation.
📸 Change Data Capture (CDC): Identifying and capturing changes (inserts, updates, deletes) in a source database to apply them to the warehouse in near real-time.
🧹 Data Cleansing: Detecting and correcting corrupt or inaccurate records.
🔍 Data Profiling: Examining source data to collect statistics and assess quality.
🛡️ Data Governance: Managing data availability, usability, integrity, and security across an enterprise.

📈 KPI (Key Performance Indicator): A measurable value demonstrating how effectively a company achieves objectives.
📏 Measure (Metric): A numerical value that can be aggregated.
➕ Additive Measure: Can be summed across all dimensions (e.g., Sales Amount).
🌗 Semi-Additive Measure: Can be summed across some dimensions but not all (e.g., Account Balance).
🚫 Non-Additive Measure: Cannot be summed (e.g., Ratios, Percentages).

🧠 OLAP (Online Analytical Processing): Technology for interactive, multidimensional data analysis.
🧾 OLTP (Online Transactional Processing): Systems managing transaction-oriented applications (e.g., ERP, CRM).
📦 Cube: A multi-dimensional array of pre-aggregated data for fast querying.
↕️ Drill Down / Roll Up: Navigating data hierarchy from summary to detail (Drill Down) or detail to summary (Roll Up).
🍰 Slice and Dice: Viewing data from different perspectives by selecting subsets.
🔄 Pivot: Changing the dimensional orientation of a report.
⌨️ MDX / DAX: Query languages for OLAP cubes (MDX) and Power BI/Analysis Services (DAX).

🕸️ Data Mesh: Decentralized architecture organizing data by business domains, treating data as a product.
🧵 Data Fabric: Architecture providing a unified layer for data management across disparate environments.
🖥️ Data Warehouse Appliances: Pre-configured hardware/software bundles (e.g., Teradata, Netezza).
⚡ MPP (Massively Parallel Processing): Multiple processors working simultaneously on a task; used by cloud DWHs like Snowflake.
🐊 Data Swamp: A deteriorated, unmanaged data lake with little value.
👻 Serverless: Cloud execution model where the provider manages machine resources dynamically (e.g., BigQuery).

🏷️ Metadata: "Data about data." Describes structure, source, and characteristics.
👣 Data Lineage: Visual representation of data's origin and movement through systems.

📖 Data Catalog: Centralized inventory of data assets helping users find and understand data.
👑 Master Data Management (MDM): Managing critical data (customer, product) for a single point of reference.
✅ Data Quality: The accuracy, completeness, consistency, and timeliness of data.
📉 BI Tool: Software for creating reports/dashboards (Tableau, Power BI).
❓ Ad-hoc Query: A non-standard, one-time query created by a user.
💾 Materialized View: A pre-computed view stored physically to improve performance for complex queries.