Lesson 15 — OLTP, OLAP, Data Warehouse & Modern Data Systems

1 What is OLTP?

OLTP stands for:

Simply put:

A system where the day-to-day transactions of an application are processed rapidly and reliably represents an OLTP workload.

Think about our e-commerce application. A customer:

These are classical operational transactions.

2 An Example of OLTP

Suppose a customer purchases a laptop. The application must execute:

Create Order
     ↓
Create Order Items
     ↓
Reduce Stock
     ↓
Create Payment Record
     ↓
Update Order Status
      

Here, our system priorities are:

These are the defining traits of an OLTP system.

3 Examples of OLTP Databases

The database engines we studied previously:

These engines excel at handling high-throughput operational workloads. In a typical e-commerce schema:

customers
orders
order_items
products
payments
inventory
      

This is where the live, authoritative operational state of the business lives.

4 What Queries Look Like in OLTP

When an authenticated user loads their order history:

SELECT *
FROM orders
WHERE customer_id = 25
ORDER BY created_at DESC
LIMIT 20;
      

Or when placing a checkout transaction:

INSERT INTO orders (customer_id, total, status) VALUES (25, 1200, 'pending');
      

These are highly targeted, index-assisted operations that read or write a few rows for individual users in milliseconds.

5 What is OLAP?

OLAP stands for:

The architectural objective here is completely different:

Executing large-scale analytical scans and complex aggregations across massive volumes of historical data.

Example: "Over the past 3 years, which product category had the highest gross sales volume across South Asia?" This is not a single user's transaction. It requires aggregating across millions or billions of rows.

6 OLTP vs. OLAP

Architectural relationship:

             DATA SYSTEM
                  │
        ┌─────────┴─────────┐
        ↓                   ↓
      OLTP                OLAP
        ↓                   ↓
  Daily Operations      Analytics
        ↓                   ↓
 Orders / Payments     Reports / Insights
      

7 What Happens If You Run Analytics on Your Operational DB?

Imagine your operational PostgreSQL table contains 100 million orders. An analyst executes:

"Calculate total revenue for every country over the past 5 years grouped by month."

The database engine must scan gigabytes of data pages into its Buffer Pool. At the exact same moment, thousands of concurrent customers are trying to:

• Place live orders
• Execute payment checkouts
• Browse product availability

The heavy analytical query evicts all hot caching pages from the buffer pool, pegs disk I/O at 100%, exhausts CPU worker threads, and causes live customer checkout transactions to time out!

8 What is a Data Warehouse?

Simply defined:

A Data Warehouse is a centralized, optimized repository where cleaned, curated data from disparate business sources is stored specifically for analysis and reporting.

Conceptually:

Applications
     ↓
Operational Databases
     ↓
Data Pipeline
     ↓
Data Warehouse
     ↓
Analytics / BI
      

9 Why Do We Need a Data Warehouse?

In any modern company, data is fragmented across silos:

When executive leadership asks: "Which marketing acquisition channel yields the highest customer lifetime value (LTV)?"—the answer requires joining data across all 5 systems. A data warehouse provides the unified, centralized environment to achieve this.

10 What is ETL?

ETL stands for:

Source
  ↓
EXTRACT
  ↓
TRANSFORM
  ↓
LOAD
  ↓
Warehouse
      

11 An Example of Extraction

Suppose three independent upstream sources:

The data extraction pipeline collects raw orders, completed transactions, and visitor events periodically or via event streams.

12 What is Transformation?

Raw data arriving from multiple systems is notoriously dirty and heterogeneous. For instance, timestamp strings arrive formatted inconsistently:

2026-09-23
23/09/2026
Sep 23, 2026
      

The transformation step converts timestamps into a unified ISO standard. Other critical transformation tasks include:

• • Deduplicating records
• • Handling null/invalid values
• • Currency conversion to USD/EUR
• • Product category harmonization
• • Standardizing field naming
• • Pre-calculating derived metrics

13 What is Loading?

The sanitized, standardized data is loaded into the target analytical warehouse:

Raw Data → Cleaned Data → Warehouse
      

From here, business analysts, data scientists, and BI tools can query the curated tables directly.

14 What is ELT?

Modern cloud data platforms have popularized a powerful alternative: ELT (Extract → Load → Transform).

Raw data is extracted and loaded directly into the cloud warehouse or data lake in its raw form. Transformations are then executed directly inside the warehouse engine using tools like dbt (data build tool), leveraging the massive parallel compute power of modern cloud clusters.

Extract → Load → Transform → Analytics
      

15 ETL vs. ELT

The choice depends on data volume, cloud infrastructure, latency requirements, governance, and organizational transformation tooling.

16 Is Data in a Warehouse Just Raw Data?

No. While operational databases are normalized into 3NF (orders, customers, products, payments), analytical warehouses organize data into purpose-built analytical models:

This introduces the foundational concepts of Facts and Dimensions.

17 What is a Fact Table?

A Fact table represents quantitative business events or measurable activities:

fact_sales
----------------
date_id
customer_id
product_id
quantity
revenue
      

Fact tables are numeric, metric-heavy, and accumulate billions of rows over time.

18 What is a Dimension Table?

Dimension tables provide rich descriptive context answering who, where, what, and when:

Dimensions are denormalized and wide, allowing analytical queries to group and filter without complex recursive joins.

19 Star Schema

Organizing a central Fact table surrounded by Dimension tables forms the classical Star Schema:

              dim_customer
                    │
                    │
dim_product ── fact_sales ── dim_date
                    │
                    │
              dim_store
      

The fact table sits at the center, radiating outward to dimension tables like points of a star.

20 Columnar Storage

In operational databases, storage engines store records row by row:

Row 1 → [id: 1, name: "Salim", email: "s@byteion.com", rev: 1200]
Row 2 → [id: 2, name: "Rahim", email: "r@byteion.com", rev: 3400]
      

In analytical data warehouses, storage engines organize data column by column:

id column: [1, 2, 3, 4, ...]
name column: ["Salim", "Rahim", "Karim", ...]
revenue column: [1200, 3400, 560, ...]
      

All values of a single column are stored contiguous on disk.

21 Why Columnar Storage is Superior for Analytics

Consider an aggregation query:

SELECT SUM(revenue) FROM sales;
      

We have zero need for customer_name, email, shipping_address, phone_number. We only need the revenue numbers.

A columnar engine loads exclusively the revenue.col file from disk, skipping 90% of the table's total bytes. This eliminates gigabytes of unnecessary disk I/O.

22 Compression in Columnar Storage

Because contiguous data shares the exact same data type, values repeat frequently:

Bangladesh, Bangladesh, Bangladesh, Bangladesh, Bangladesh, ...
      

Compression algorithms like Run-Length Encoding (RLE) and Dictionary Encoding achieve 5× to 10× compression ratios, reducing 1 TB of raw data down to 100 GB on disk.

23 Examples of Data Warehouses

Leading modern cloud analytics data platforms:

Organizations also deploy specialized extensions such as PostgreSQL with Citus or TimescaleDB depending on architectural scope.

24 What is a Data Lake?

Alongside data warehouses, another foundational architecture is the Data Lake:

A Data Lake is a scalable, low-cost centralized storage environment where raw structured, semi-structured, and unstructured data is stored without strict upfront schema enforcement.

25 Why Do We Need a Data Lake?

Enterprises generate non-tabular assets: raw mobile telemetry, streaming JSON events, sensor logs, images, and chat recordings. Forcing these disparate streams into a rigid relational schema prior to ingestion is impractical. A data lake offers a flexible, cost-effective raw landing zone.

26 Challenges of Data Lakes: Preventing the Data Swamp

Unchecked flexibility breeds chaos. When teams dump uncataloged files arbitrarily:

file1.json
file2.csv
data_old.csv
final_data.csv
final_final_data.csv
      

The lake deteriorates into an unusable Data Swamp. This necessitates rigorous data engineering disciplines:

27 The Data Lakehouse

To combine the cost-effective flexibility of data lakes with the ACID transactions and high-speed query capabilities of warehouses, the industry developed the Data Lakehouse:

             Data Platform
                  │
       ┌──────────┴──────────┐
       ↓                     ↓
    Raw Data             Structured Data
       │                     │
       └──────────┬──────────┘
                  ↓
              Analytics
      

Technologies like Apache Iceberg, Delta Lake, and Apache Hudi bring ACID guarantees, schema evolution, and time-travel querying directly on top of open object storage.

28 What is a Data Pipeline?

A Data Pipeline is the automated sequence of steps that moves and transforms data across systems:

Applications
     ↓
Events / Transactions
     ↓
Data Collection
     ↓
Storage
     ↓
Transformation
     ↓
Warehouse / Lake
     ↓
Analytics
     ↓
Dashboard / ML / Decisions
      

29 Real-World Example — E-commerce Data Pipeline

A customer views an item: product_view. Then places an order: order_created. Then completes payment: payment_completed.

Website, Mobile App, Backend, Payment Gateway
                      ↓
                 Data Pipeline
                      ↓
                 Data Platform
                      ↓
                  Analytics
      

Executive dashboards refresh in near real-time: How many orders today? What is the checkout funnel drop-off? What is our real-time revenue?

30 Are Event Data and Operational Data the Same?

No. Operational databases maintain the current state of the business (e.g., order_id = 1001, status = "shipped").

Event streams capture an immutable series of historical occurrences (e.g., product_viewed → cart_added → checkout_started → payment_completed). Both serve complementary purposes.

31 Why Historical Data is Essential

An operational database can only confirm: "Customer 42's current tier is Enterprise."

Business strategy requires asking: "How did Customer 42 transition from Free → Pro → Enterprise over the past 12 months, and what triggered each upgrade?" Only historical event data can answer this question.

32 Why Analytical Queries Are Massive

A single analytical query:

SELECT country, SUM(revenue)
FROM sales
WHERE order_date >= '2024-01-01'
GROUP BY country;
      

This query must process billions of records. Partition pruning, columnar layouts, vectorized SIMD execution, and parallel processing are mandatory to complete this query in seconds rather than hours.

33 Parallel Processing

Analytical engines decompose heavy aggregate queries into micro-tasks executed across a cluster of worker nodes:

Large Query
    ↓
┌────┬────┬────┬────┐
↓    ↓    ↓    ↓
W1   W2   W3   W4
└────┴────┴────┴────┘
          ↓
       Combine
          ↓
        Result
      

34 The Connection Between Data Systems and AI

High-performing artificial intelligence systems are entirely dependent on robust underlying data pipelines:

Applications
    ↓
Events / Data
    ↓
Data Lake / Warehouse
    ↓
Cleaning / Transformation
    ↓
Training Data
    ↓
ML Model
    ↓
Prediction
      

Database and data engineering form the foundational backbone of the modern artificial intelligence ecosystem.

35 Data and Machine Learning

Building an e-commerce recommendation model requires aggregating customer browsing history, cart additions, purchase records, clicks, and review ratings:

User Behavior → Data Pipeline → Training Dataset → ML Model → Recommendations
      

36 Data Quality

Regardless of how sophisticated an analytics platform is, corrupted input data produces corrupted insights ("Garbage In, Garbage Out"). If duplicated orders load into a fact table three times, reported financial revenue will be artificially inflated by 300%. Crucial safeguards:

37 Data Governance

At enterprise scale, governance answers critical compliance questions:

• Who possesses read/write authorization for this dataset?
• Where did this data originate?
• How old is this snapshot, and when was it refreshed?
• Which service or engineer modified this table?
• Where is sensitive PII (Personally Identifiable Information) stored and masked?

38 Data Lineage

Data Lineage is the traceable, auditable lifecycle of a data point from its source system through all downstream transformations to its final destination in a report or model.

Source Database → Raw Data → Transformation → Warehouse Table → Dashboard
      

When an executive dashboard displays anomalous revenue figures, end-to-end lineage allows engineers to pinpoint the exact broken pipeline or corrupted upstream source instantly.

39 Modern Data Architecture

The complete, unified modern enterprise data stack:

                USERS
                  ↓
        Web / Mobile / APIs
                  ↓
        ┌─────────┴─────────┐
        ↓                   ↓
     OLTP DB            Event Stream
        ↓                   ↓
        └─────────┬─────────┘
                  ↓
            Data Pipeline
                  ↓
        ┌─────────┴─────────┐
        ↓                   ↓
    Data Lake          Data Warehouse
        │                   │
        └─────────┬─────────┘
                  ↓
         Analytics / BI / ML
                  ↓
           Business / AI
      

40 The Complete Journey: From OLTP to OLAP

We began Course 07 with a single application database. Notice how all pieces fit together into a unified whole:

Every critical tier of modern software engineering connects across this lifecycle.

41 Are Databases Just Storage?

Absolutely not.

A database engine does not simply persist bytes. It:

42 Your New Mental Model From This Course

From this day forward, envision data architecture through this fundamental split:

                         DATA
                          │
             ┌────────────┴────────────┐
             ↓                         ↓
           OLTP                      OLAP
             ↓                         ↓
      Daily Operations           Analytics
             ↓                         ↓
       SQL / Transactions      Warehouse / Lake
             ↓                         ↓
        Application             Reports / ML
      

And the complete unified data engine:

        REAL WORLD
             ↓
          EVENTS
             ↓
       APPLICATION
             ↓
          OLTP DB
             ↓
       DATA PIPELINE
             ↓
    ┌────────┴────────┐
    ↓                 ↓
 DATA LAKE       DATA WAREHOUSE
    └────────┬────────┘
             ↓
        ANALYTICS
             ↓
       AI / DECISIONS