Data is the lifeblood of business analytics—whether it's manufacturers forecasting stock production or banks evaluating if a new customer mirrors past risk profiles. That's why data analytics is such a critical domain.
In large companies, data doesn't live in just one neat database; it's scattered across dozens of systems. When you need quick answers to complex analytical queries, querying live operational databases isn't practical. Instead, all that useful data is consolidated into a much biiigger database: a Data Warehouse.
Data Warehouse
Data makes its way into a warehouse in one of two ways:
- Source-driven architecture: Data sources push information continually or periodically (like using webhooks).
- Destination-driven architecture: The warehouse systematically polls the sources for new data.
(Note on Eventual Consistency: Warehouse syncs are delayed by design. Because warehouses favor append-only writes over updates, data is usually transferred only after an operational event reaches its terminal state (e.g., Order_Delivered).)
Fact and Dimension Tables
Databases generally store two kinds of attributes (columns):
- Measures (Quantitative): Numerical values you aggregate (e.g., revenue, quantity_sold). Fact tables sit at the center and contain these along with foreign keys.
- Dimensions (Qualitative): Contextual attributes you group or filter by (e.g., store_location, customer_category, date). Dimension tables hold these descriptive details.
A single fact table at the center with dimension tables branching out like points—that's a Star Schema.
When those dimension tables are further normalized into even more dimension tables, the branches split and spread—forming a Snowflake Schema.
Row vs. Column-Oriented Storage: How Do We Get Fast Queries?
Traditional transactional databases store data row-by-row. But if you only want to compute the average price of millions of rows, row-oriented storage forces the CPU to fetch every single attribute per row into cache—wasting memory bandwidth and processing time.
Column-oriented storage flips this: it groups data on disk by attribute rather than by row. You only load the exact columns required for your query. Because contiguous values in a column share the exact same data type (and often repeated values), data compression is dramatically faster and more effective.
Data Lake
While warehouses require structured, formatted data, a Data Lake accepts raw, unformatted data (logs, images, raw JSON). Think of it as cold storage—you dump everything in first and define the schema later when reading it. Hence, queries require more work here.
OLAP (Online Analytical Processing)
When a system can answer aggregation queries (SUM, MAX, AVG..) over biiig data in near real-time, we call it an OLAP system.
Pivoting, Cubes, and Navigation
To extract insights, we often slice data into Pivot / Cross Tables. These use dimension values as row/column headers, with the cell values representing aggregated measures. Since a 2D screen limits us to 2 or 3 visible attributes at a time, we use a conceptual model called a Data Cube to generalize cross-tabs across N dimensions.
When navigating an OLAP cube, you use four main operations:
- Drill-down: Going from a high-level summary to fine-grained detail (e.g., moving from Year > Month > Day).
- Rollup: Aggregating data to a coarser grain (e.g., aggregating Cities into Countries).
- Pivoting: Rotating the cube axes to view the data from a different dimension.
- Slice & Dice: Selecting a specific subset of the cube (e.g., filtering for Year = 2026 only).
In SQL
You can perform pivoting directly in modern SQL dialects:
SELECT *
FROM (
SELECT product_category, size, price
FROM sales
)
PIVOT (
SUM(price)
FOR size IN ('L', 'XL', 'XXXL')
);
This transforms the values 'L', 'XL', and 'XXXL' from rows into distinct columns in the result set, making comparative analysis effortless.
TL;DR
Building a data stack is all about trade-offs:
- OLTP keeps the business running second-by-second (Row-oriented).
- Data Lakes hoard raw data cheaply for the future (Unstructured).
- Data Warehouses & OLAPs turn that data into fast, actionable decisions (Column-oriented & Aggregated).
Top comments (0)