Data Warehouses, OLTP, OLAP, Dimensional Models and ETL

OLTP

Online transaction processing serves many short concurrent operations over current detailed data with strong integrity and predictable response time.

OLAP

Online analytical processing performs read-heavy multidimensional summaries over large historical datasets for exploration and decision support.

data warehouse

A data warehouse integrates cleaned historical data from multiple sources into structures optimized for consistent analytical queries.

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

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.

subject-oriented integrated time-variant nonvolatile

A data warehouse is organized by business subject, integrates sources, preserves historical time context and is stable for analysis.

Use subject-oriented integrated time-variant nonvolatile only after defining the grain of one row and the business meaning of every key. Work through duplicates, absent values, concurrent changes and rollback before tuning the statement. Validate the returned row count and representative values before accepting an index or rewrite merely because it runs faster. Capture the execution plan together with representative data volume because an empty demonstration table proves little about production behavior.

fact and dimension

A fact table records events or measurements at a declared grain, while dimension tables describe the people, products.

star and snowflake schema

A star schema links a fact table directly to denormalized dimensions; a snowflake normalizes some dimension hierarchies into additional tables.

slowly changing dimension

A warehouse technique for handling dimension attribute changes by overwriting, adding history or storing limited prior values.

ETL and ELT

ETL transforms data before warehouse loading, while ELT loads source-shaped data first and performs transformations within the target platform.

data mart

A focused analytical store serving one department, function or subject area, often sourced from an enterprise warehouse.

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

warehouse grain

The exact business event or level represented by one fact-table row.

OLTP vs OLAP

OLTP — Online Transaction Processing

Examples: Banking transactions, e-commerce orders, inventory management

OLAP — Online Analytical Processing

Feature	OLAP
Purpose	Analysis, reporting, decision support
Data	Historical, aggregated data
Queries	Complex aggregations, multi-dimensional analysis
Response time	Seconds to minutes
Users	Few analysts and managers
Data size	TB to PB
Denormalized	Star schema, snowflake schema

Examples: Sales trend analysis, customer behavior analysis, financial reporting

OLTP vs OLAP Comparison

Feature	OLTP	OLAP
What	Record transactions	Analyze data
Query type	Simple (INSERT, simple SELECT)	Complex (GROUP BY, JOIN across years)
Users	Many concurrent (1000s)	Few (10s)
Data	Current (days)	Historical (years)
Schema	Normalized	Denormalized (star/snowflake)
Optimization	Fast transactions	Fast reads at scale
Database	MySQL, PostgreSQL	Snowflake, BigQuery, Redshift

ETL vs ELT

ETL — Extract, Transform, Load

Process:

Source Data → EXTRACT → TRANSFORM (outside warehouse) → LOAD into DWH

When ETL?

When transformation is complex
When target system has limited compute

Traditional data warehouse era

When data governance requires transformation before storage

ELT — Extract, Load, Transform

Process:

Source Data → EXTRACT → LOAD into DWH (raw) → TRANSFORM (inside DWH)

When ELT?

Modern cloud warehouses (BigQuery, Snowflake) with massive compute
Need to keep raw data available
Transformations defined later
Data Lake approach

ETL vs ELT Comparison

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

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

Data Modeling

What is Data Modeling?

Data modeling is the process of designing how data is organized, stored and related in a database.