Data Warehouse; Data Mart; Data Engineering Lifecycle

Data Warehouse

What is a Data Warehouse?

A Data Warehouse (DWH) is a central repository integrating data from multiple sources for:

• Business intelligence (BI)
• Reporting and analytics
• Decision support

Key characteristics:

• Subject-oriented (organized by topic: sales, finance, HR)

Integrated (combines data from multiple systems)

• Non-volatile (data is not updated; only new data added)
• Time-variant (historical data matters)

Data Warehouse Architecture

Source Systems         ETL Layer           Data Warehouse       Consumers
+----------+          +-------+           +----------+       +----------+
|  CRM     |  ─────>  |       |  ─────>  |  Staging |       | BI Tools |
|  ERP     |          |  ETL  |           |   Layer  |       | Tableau  |
|  OLTP DB |          |  Tool |           +----------+       | Power BI |
|  Files   |          +-------+           | Core DWH |  ──>  +----------+
|  Logs    |                              | (Data)   |       | Reports  |
+----------+                              +----------+       | Dashboards|
                                          | Data Mart|       +----------+
                                          +----------+

Data Warehouse Schemas

Star Schema

                   FACT TABLE
                   (Sales_Fact)
                   +----------+
                   | sales_id |
                   | date_id  | ──> Date_Dim  (Jan, Feb, daily...)
                   | prod_id  | ──> Product_Dim (Name, Category, Price)
                   | cust_id  | ──> Customer_Dim (Name, City, Segment)
                   | store_id | ──> Store_Dim  (Location, Manager)
                   | amount   |
                   | quantity |
                   +----------+

Star Schema: One fact table; dimension tables connect directly. Simple; fast queries; denormalized

Snowflake Schema

Extension of Star Schema where dimension tables are normalized into sub-dimensions.

Product_Dim
  └── Category_Dim
        └── Department_Dim

Snowflake Schema: More normalized; reduces redundancy; more joins needed.

Data Mart

What is a Data Mart?

A Data Mart is a subset of a Data Warehouse focused on a specific business function or department.

Data Warehouse (entire enterprise data)
         |
   +-----+-----+------+
   |           |       |
Sales DM   Finance DM  HR DM
(sales team) (finance)  (HR team)

Data Warehouse vs Data Mart

Data Engineering Lifecycle

The Data Engineering Lifecycle

Source → Ingestion → Storage → Transformation → Serving

Stage 1: Source

Where data originates:

• OLTP databases (MySQL, PostgreSQL) — transactional data

APIs (REST, GraphQL) — third-party services

• Log files — server access logs, application logs
• IoT devices — sensors, cameras, wearables
• Streaming systems — Kafka, Kinesis
• Files — CSV, JSON, XML, Excel
• Web scraping — public websites

Stage 2: Ingestion

Moving data from sources to storage:

• Batch Ingestion — periodic bulk loads (daily, hourly)
• Stream Ingestion — real-time continuous data flow

Tools: Apache Kafka, AWS Kinesis, Apache Nifi, Fivetran, Airbyte

Stage 3: Storage

Where data is stored:

• Data Lake — raw, unprocessed data (S3, Azure Data Lake, HDFS)

Data Warehouse — processed, structured analytical data (Snowflake, BigQuery, Redshift)

NoSQL — flexible structured data (MongoDB, Cassandra)

Stage 4: Transformation

Converting raw data to useful form:

• Cleaning — remove duplicates, handle nulls

Enrichment — add additional context

• Aggregation — summarize data

Normalization/Denormalization — structure for analytics

Tools: Apache Spark, dbt, AWS Glue

Stage 5: Serving

Delivering data to end users:

• BI Dashboards — Tableau, Power BI, Looker
• Reports — scheduled automated reports
• ML Models — trained and deployed models
• APIs — serve data to applications