Data Warehouse: The Single Source of Truth for Analytics
A data warehouse is a central database optimized for analytics, not transactions. It integrates historical data from disparate sources like sales and marketing to create a single source of truth for business intelligence.
The mental model
A data warehouse is the company's single source of truth for analytics. Think of it as a central library for historical business data, designed specifically for reading and analysis, not for the day-to-day transactions of a live application. It combines information from many different systems into one place to answer big-picture questions.
How it works
Data is periodically pulled from various operational systems like customer relationship management (CRM) tools, e-commerce platforms, and application databases. This raw data is then cleaned, standardized, and reshaped into a consistent format—a process known as ETL (Extract, Transform, Load). Inside the warehouse, the data is organized in a way that makes it fast to query, often using specialized structures. This structure allows analysts to easily slice, dice, and aggregate vast amounts of historical and current data to generate reports.
When to use it
A data warehouse is the foundation for business intelligence (BI). Use it when you need to run complex queries across data from different departments without slowing down your live production systems. It's perfect for generating management dashboards, creating quarterly sales reports, analyzing customer behavior over time, and allowing analysts to perform ad-hoc data exploration to uncover insights.
When not to use it
Do not use a data warehouse for Online Transaction Processing (OLTP). It is not built for the high-frequency, small-scale reads and writes required by a live application, such as processing an order or updating a user's account information. Because the data is loaded periodically (e.g., daily), it is not real-time and is unsuitable for systems that need up-to-the-second accuracy.
One canonical example
A retail company wants to understand the relationship between in-store promotions and online sales. It pulls data from its point-of-sale system, its e-commerce platform's database, and its marketing automation tool. This data is integrated and loaded into a data warehouse. An analyst can then query the warehouse to see if a promotion in a specific city led to an increase in online orders from that same geographic area over the following week.
Interview question
Which scenario best illustrates the primary function of a data warehouse?
- a.An e-commerce site updating product inventory levels after every sale.
- b.A retail company analyzing quarterly sales trends across different product lines and regions.Correct
- c.A bank processing thousands of customer deposits and withdrawals hourly.
- d.A social media platform storing user-generated content for immediate display.
Why? this is the answer
A data warehouse is designed for complex analysis of historical data from various sources, as described in option B. Options A, B, and D describe real-time transactional operations, which are explicitly stated as scenarios where a data warehouse should not be used.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #data warehouse
- #analytics
- #business intelligence
- #etl
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data warehouse — each one lists the topics its interview covers.
See open roles