Data warehouse
A data warehouse is a database built for analysis. It collects copies of data from the systems that run the business, such as the app's PostgreSQL, the payment provider and the CRM. The app database keeps serving logins and checkouts. The warehouse answers questions like "revenue by city by month", often over years of history. BigQuery, Snowflake, Amazon Redshift and StarRocks are common choices.
How it works
Data gets in through pipelines. A nightly ETL job extracts, cleans and loads it. Many teams now do ELT instead: load raw tables first, then transform them with SQL inside the warehouse. For fresher numbers, change data capture reads the source database's log and ships each insert, update and delete within minutes.
Inside, most warehouses use a star schema. A fact table holds one row per event, such as an order, with numbers like the amount. Its keys point at dimension tables that describe the customer, the city and the date. A snowflake schema splits those dimensions into more normalised tables, so every report needs more joins. When a customer moves city, a type 2 slowly changing dimension adds a new dated row and keeps the old one.
Take a food delivery startup with 40 million orders in one PostgreSQL primary. Finance points a BI tool at it and asks for weekly revenue by city for the past year.
The query joins orders, restaurants and addresses, and reads every column of every row. It runs for 9 minutes, and checkout gets slower while it does.
The team streams orders into a warehouse and builds a fact table keyed on city and date. The report now reads 3 columns: 40 million rows x 3 columns x 8 bytes = 960 MB. It returns in seconds, on machines no customer is waiting on.
Why it matters in production
You trade freshness against cost. A nightly load is cheap, but every dashboard can be a day old. CDC gets that down to minutes, and adds a pipeline someone has to watch. In One2N's post on a Debezium feed into a StarRocks warehouse, one Postgres parameter stalled replication for 3 weeks. Warehouses billed per byte scanned also punish careless dashboards: one that refreshes every minute runs its query 1,440 times a day.
Modelling matters as much as the engine. One2N rebuilt a bus entertainment client's analytics as a Spark and HDFS warehouse and swapped its snowflake schema for a star. A 30-day unique visitors query fell from 3 hours to 2 minutes. The query workload a warehouse serves is called OLAP.
See also
On the One2N blog
Implementing a real-time analytics engine using big data principles
How one parameter stalled Postgres replication with Debezium for 3 weeks
Exposing SaaS APIs privately to customers with AWS PrivateLink
References
