Definition
- Fact table – stores measurable events (one row per transaction) with numeric measures and foreign keys to dimensions.
- Dimension tables – store descriptive context (date, branch, product, customer) that you filter and group by.
- Star schema – denormalized dimensions (one flat table each) for fewer joins and faster reads.
- Snowflake schema – normalized dimensions into sub-tables, less redundancy but more joins.
- OLTP contrast – normalized to 3NF/BCNF for safe concurrent writes; poor for aggregation-heavy analytics.
- Logical vs physical – logical is abstract and DBMS-independent; physical adds data types, indexes, partitioning, and storage.
For National Bank of India's day-to-day operations you want clean, non-duplicated records so a deposit updates one place safely: that is the OLTP model. For the analytics team asking "total deposits by branch by month," you want a report-friendly layout: a central ledger of every transaction (fact) with easy-to-join description tables (dimensions). The logical model is the business blueprint on paper; the physical model is the actual building with wiring, rooms, and materials.
Understanding the Concepts
What is dimensional modeling?
Dimensional modeling, popularized by Kimball, is built for OLAP (analytical) workloads where you aggregate and slice large volumes of historical events.
-- Fact table: one row per transaction at National Bank of India
CREATE TABLE fact_transactions (
txn_id BIGINT PRIMARY KEY,
date_key INT REFERENCES dim_date(date_key),
branch_key INT REFERENCES dim_branch(branch_key),
product_key INT REFERENCES dim_product(product_key),
customer_key INT REFERENCES dim_customer(customer_key),
amount NUMERIC(18,2),
quantity INT
);
-- Dimension table: descriptive context
CREATE TABLE dim_branch (
branch_key INT PRIMARY KEY,
branch_name VARCHAR(100),
city VARCHAR(50),
region VARCHAR(50),
state VARCHAR(50)
);
What is a star schema?
A star schema keeps each dimension in a single flat, denormalized table.
-- Star schema query: total deposits by branch by month
SELECT d.month_name, b.branch_name, SUM(f.amount) AS total_deposits
FROM fact_transactions f
JOIN dim_date d ON d.date_key = f.date_key
JOIN dim_branch b ON b.branch_key = f.branch_key
WHERE d.year = 2026 AND f.amount > 0
GROUP BY d.month_name, b.branch_name
ORDER BY d.month_name, total_deposits DESC;
Few joins, simple SQL, fast reads. The trade-off is some redundancy inside dimensions (city repeated on every branch row).
What is a snowflake schema?
A snowflake normalizes dimensions into sub-tables to remove redundancy.
-- Snowflake: branch dimension split into sub-tables
CREATE TABLE dim_branch (
branch_key INT PRIMARY KEY,
branch_name VARCHAR(100),
city_key INT REFERENCES dim_city(city_key)
);
CREATE TABLE dim_city (
city_key INT PRIMARY KEY,
city_name VARCHAR(50),
region_key INT REFERENCES dim_region(region_key)
);
CREATE TABLE dim_region (
region_key INT PRIMARY KEY,
region_name VARCHAR(50),
state VARCHAR(50)
);
-- Snowflake query: more joins needed
SELECT r.region_name, SUM(f.amount)
FROM fact_transactions f
JOIN dim_branch b ON b.branch_key = f.branch_key
JOIN dim_city c ON c.city_key = b.city_key
JOIN dim_region r ON r.region_key = c.region_key
GROUP BY r.region_name;
How do star and snowflake compare?
| Feature | Star | Snowflake |
|---|---|---|
| Dimension structure | Flat, denormalized | Normalized sub-tables |
| Number of joins | Few (fact + dimensions) | More (through sub-tables) |
| Query complexity | Simple | More complex |
| Read performance | Faster | Slower (more joins) |
| Redundancy | Some in dimensions | Minimal |
| When to use | Default for analytics | Large or volatile dimensions |
How does this differ from the normalized OLTP model?
OLTP schemas are normalized to 3NF or BCNF for safe concurrent writes. This is the opposite of what analytics needs.
-- Normalized OLTP: many tables, many joins for a report
SELECT c.name, b.branch_name, SUM(t.amount)
FROM transactions t
JOIN accounts a ON a.id = t.account_id
JOIN customers c ON c.id = a.customer_id
JOIN branches b ON b.id = a.branch_id
JOIN branch_cities bc ON bc.id = b.city_id
GROUP BY c.name, b.branch_name;
-- Many joins, slow for aggregation over millions of rows
The same business data is modeled differently for different jobs: normalized for OLTP writes, dimensional for OLAP reads, with ETL moving data from one to the other.
What is a logical data model?
A logical model describes entities, attributes, and relationships abstractly, independent of any DBMS.
-- Logical model (DBMS-independent)
Entity: Customer
Attributes: customer_id (PK), name, email, phone
Relationships: has many Accounts
Entity: Account
Attributes: account_id (PK), customer_id (FK), type, balance
Relationships: belongs to Customer, has many Transactions
What is a physical data model?
A physical model adds DBMS-specific implementation details.
-- Physical model (PostgreSQL-specific)
CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
phone VARCHAR(20)
);
CREATE INDEX idx_customer_email ON customers (LOWER(email));
CREATE TABLE accounts (
account_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
type VARCHAR(20) NOT NULL,
balance NUMERIC(18,2) NOT NULL DEFAULT 0
) PARTITION BY RANGE (customer_id);
CREATE INDEX idx_account_customer ON accounts (customer_id);
The physical model includes data types, indexes, partitioning, tablespaces, and naming.
How does ETL connect OLTP to the analytics warehouse?
┌─────────────┐ ETL/ELT ┌──────────────────┐ │ OLTP (3NF) │──────────────▶│ Warehouse (Star) │ │ Normalized │ Extract │ Denormalized │ │ Safe writes │ Transform │ Fast reads │ │ PostgreSQL │ Load │ Redshift/BQ │ └─────────────┘ └──────────────────┘
At National Bank of India, Pankaj runs nightly ETL from the normalized PostgreSQL database into a star-schema Redshift warehouse for analyst queries.
What should a senior engineer know?
- Design logical first to capture the business correctly, then derive one or more physical models per engine and workload.
- Star is the default for analytics; snowflake only for large or frequently changing dimensions.
- The same business data may have different physical models for OLTP, OLAP, and search.
- There is also a conceptual model above the logical one: the high-level entity sketch for business stakeholders.
Visual Overview
Star Schema:
┌───────────┐
│ dim_date │
└─────┬─────┘
│
┌───────────┐ ┌──────▼──────┐ ┌──────────────┐
│dim_branch │────│ FACT │────│ dim_product │
└───────────┘ │ transactions│ └──────────────┘
└──────┬──────┘
│
┌──────▼──────┐
│dim_customer │
└─────────────┘
Snowflake Schema (branch dimension normalized):
dim_branch ──▶ dim_city ──▶ dim_region
Logical vs Physical:
Logical: Customer ──has many──▶ Account ──has many──▶ Transaction
Physical: CREATE TABLE, BIGINT, INDEX, PARTITION BY RANGE
Key Takeaway
Dimensional modeling is a schema design for analytics centered on a fact table surrounded by dimension tables. A star schema keeps each dimension flat and denormalized for simple SQL and fast reads. A snowflake normalizes dimensions into sub-tables, removing redundancy at the cost of more joins. In practice star is the default.
This is deliberately different from the normalized OLTP model, which is normalized to 3NF or BCNF so writes are cheap and consistent but analytics aggregations require many joins and run slowly. At the National Bank of India the same business data is modeled twice: normalized for the live system and a star-schema warehouse for analytics, with ETL moving data between them.
The logical model describes entities and relationships abstractly, independent of any DBMS. The physical model adds concrete data types, indexes, partitioning, and storage for a specific engine. A senior engineer designs logical first to get the business right, then derives one or more physical models per workload.
Remember one sentence: dimensional modeling optimizes analytics reads with star for speed and snowflake for less redundancy, unlike normalized OLTP built for safe writes, while the logical model is the abstract blueprint and the physical model is its DBMS-specific implementation.
---