Introduction to data warehousing and business intelligence, covering basic data warehouse concepts
Most systems are built to record things. Every time a customer places an order, a transaction gets written to a database. Every login, every payment, every support ticket — all captured, row by row, in an operational system optimized for speed and accuracy at the moment of the event. That’s OLTP — Online Transaction Processing and it’s very good at what it does.
But here’s the problem: those systems are terrible for answering business questions.
If a company wants to know which product lines drove revenue growth over the last three years across five regions, pulling that answer from an OLTP system means reaching into dozens of separate databases, each structured differently, each optimized for writes rather than reads. The data exists — but it’s scattered, inconsistent, and not designed for the kind of deep, historical analysis that actually drives decisions.
That’s the gap a Data Warehouse fills.
A Data Warehouse is a central, separate system where data from all those operational sources gets consolidated, cleaned, and stored specifically for analysis. It’s not updated in real time. It’s not touched every time a new transaction comes in. Instead, it holds a structured, historical record of the business designed to be queried, not written to.
- DWH is subject-oriented (data is categorized by business subject), integrated (data from disparate sources is stored in one place), time-variant (data is stored as snapshots representing periods of time), and non-volatile (data is typically not updated or deleted).
- Integrated- The data pulled from disparate sources is standardized into a single consistent format before it lands in the warehouse.
- Time-variant — rather than overwriting records, the warehouse stores snapshots over time, so you can ask “what did this look like in Q2 2022?” and get a real answer.
- Non-volatile — once data is in, it generally stays. The warehouse is a historical record, not a live scratchpad.
- ETL: ETL stands for Extract, Transform, and Load. Data is extracted from multiple disparate source systems, transformed to ensure consistency and standardization (since operational systems often lack uniformity), and then loaded into the Data Warehouse.
- OLAP — The Analytical Engine:Once data is in the warehouse, the querying model shifts entirely. Instead of OLTP’s row-by-row transaction model, the warehouse runs on OLAP — Online Analytical Processing which is built for slicing large datasets across multiple dimensions simultaneously.
Key terminologies covered:
- OLTP vs. OLAP
- ETL
- Data Mart- focused subset of Data warehouse which deals with a single subject area
- Metadata-structural layer that describes what the data is, where it came from, and how it was transformed
How to Build DWH:
Two approaches to building a Data Warehouse: the Top-Down approach, the Bottom-Up approach.
Top-Down approach: starts by building the full enterprise warehouse first, then carving out data marts from it
Bottom-Up approach:starts by building individual data marts first and integrating them over time
Key Takeaway
A Data Warehouse is not a product a company can simply purchase it must be designed from scratch based entirely on the company’s specific requirements. The combination of BI tools and a well-structured warehouse enables organizations to move from intuition-based to fact-based decision making.
Technical Log: Resolving Hierarchical Data Comparisons in SQL
(LeetCode: Employees Earning More Than Managers)
The challenge required comparing two values (salaries) that reside in the same table but on different rows. Specifically, I had to identify employees whose salary is higher than the salary of the person identified by their managerId
On the surface, it is simple, but it presents a fundamental architectural challenge: SQL is designed to evaluate data one row at a time. Since an employee’s salary and their manager’s salary live on different rows
Self-Join. Instead of viewing the Employee table as a single, static list, I treated it as two dynamic entities
- The Subordinate Perspective (Alias e): The source for employee names and their specific ManagerId
- The Manager Perspective (Alias m): The source for the target salary we need to compare against.
The most critical realization was that the direction of the join dictates the perspective of the data.By mapping ManagerID(the “pointer”) to Id(the “destination”), we effectively looked up the hierarchy.
By joining the table to itself, we transformed a vertical relationship into a horizontal one. This “flattening” process brought the manager’s salary onto the exact same row as the employee’s salary.Once the data was aligned side-by-side, the final comparison became trivial. We applied a simple filter to isolate only those rows where the employee’s salary attribute exceeded the manager’s salary attribute.
Leave a Reply