Business Intelligence & Data Lakehouses | Draftsbook

Module Overview

Transactional databases (OLTP) are optimized to handle millions of low-latency writes and updates per second without corrupting row data. However, executing a massive multi-year analytical query (OLAP) directly against an active production OLTP database introduces severe lock contention that degrades user checkouts. In this fifth installment, we analyze the analytical data stack: Business Intelligence (BI), ETL versus ELT pipelines, Dimensional Modeling (Star vs. Snowflake schemas), OLAP Cube operations, and the modern unified Data Lakehouse paradigm.

1. OLTP vs. OLAP: Fundamental Architectural Dichotomy

Architectural Dimension	Online Transaction Processing (OLTP)	Online Analytical Processing (OLAP)
Primary Objective	High-concurrency, real-time read/write transaction execution.	Multi-year historical analysis, reporting, and predictive modeling.
Data Structure	Normalized relations (3NF/BCNF) to eliminate update anomalies.	Denormalized dimensional schemas (Star, Snowflake, Wide Tables).
Storage Orientation	Row-oriented storage (fast whole-row inserts and lookups).	Columnar storage (Parquet, ORC) optimizing compression & vector scans.
Query Pattern	Simple point queries fetching small numbers of records via primary key.	Complex aggregation scans over billions of rows across multiple years.

2. Dimensional Modeling: Star Schema vs. Snowflake Schema

Formulated by Ralph Kimball, dimensional modeling structures data into two distinct table categories:

Fact Tables (The Metrics)

Store numeric, quantitative measurements generated by business events. Contains foreign key pointers referencing dimension tables alongside additive metrics: sales_amount, units_sold, freight_cost.

Dimension Tables (The Context)

Store textual, qualitative contextual descriptors that provide filtering and slicing attributes: Dim_Customer, Dim_Product, Dim_Store_Location, Dim_Date_Time.

Star Schema

A central fact table surrounded by completely denormalized dimension tables. Dimension tables contain redundant text descriptions without sub-table joins, yielding lightning-fast single-hop join query performance.

Snowflake Schema

Normalizes dimension tables into secondary lookup hierarchies (e.g., Dim_Product joins to Dim_Category which joins to Dim_Department). Saves disk storage and eliminates dimension redundancy at the cost of additional relational join overhead.

3. Modern Data Lakehouse Architecture (Medallion Pattern)

Historically, enterprises maintained two separate systems: an inexpensive Data Lake (storing unstructured files, JSON, images on object storage like S3) and an expensive Data Warehouse (Snowflake, BigQuery, Redshift).

The modern Lakehouse paradigm (Delta Lake, Apache Iceberg) unifies both using open columnar file formats on cloud object storage with ACID transactional guarantees. Data progresses through a three-tier Medallion Pattern:

Bronze Layer (Raw Ingestion): Direct append-only landing of raw data in native schema formats without cleaning. Serves as the immutable replay log.
Silver Layer (Cleansed & Conformed): De-duplicated, schema-validated, and normalized tables with bad records rejected to dead-letter queues.
Gold Layer (Business Curated): Aggregated star-schema dimensional models optimized for executive BI dashboards and machine learning feature stores.

Next in Curriculum

In Part 6: Managerial Decision-Making: Decision Support Systems (DSS) & AI, we explore Herbert Simon's decision-making phases and how predictive AI enhances managerial judgment.