IMEN891K / IDSC501 / AXGS-501 · Data Engineering · Fall 2026
12. Big Data II: Data Warehousing
Sangwoo Mo · POSTECH
Industrial & Management Engineering
Where We Are
Recap: Batch Results
A saved result can serve repeated analysis
Outline: Big Data II
01Analytical Copies
keep reporting work separate from sales transactions
02Rows for Analysis
connect each sale to its product and daily total
03Refreshing Reports
refresh the copy in the right order without counting twice
01 · Analytical Copies
01Analytical Copies
keep reporting work separate from sales transactions
02Rows for Analysis
connect each sale to its product and daily total
03Refreshing Reports
refresh the copy in the right order without counting twice
The Daily Sales Report
A report needs a clear date range and one correct total for each date
Sales Transactions
The operational database records each sale as it happens
An Analytical CopyA data warehouse stores data for repeated analytical queries
A data warehouse keeps an organized copy for analysis
Transactions and AnalysisThe same sales data supports different kinds of work
OLTP records individual purchases; OLAP summarizes and compares many purchases
Warehousing Becomes a ServiceWarehouses organized company data before cloud services managed the infrastructure
Managed services change who operates the system; analysts still define the query
A Managed WarehouseServerless means the service manages the servers for the user
The service runs the infrastructure; the analyst defines the tables and query
Copy Freshness
The report includes only the sales already copied
02 · Rows for Analysis
01Analytical Copies
keep reporting work separate from sales transactions
02Rows for Analysis
connect each sale to its product and daily total
03Refreshing Reports
refresh the copy in the right order without counting twice
Four Sales
| sale_id | sale_date | product_id | amount |
|---|
| 1 | 2025-10-02 | P1 | 300 |
| 2 | 2025-10-02 | P2 | 200 |
| 3 | 2025-10-03 | P1 | 400 |
| 4 | 2025-10-03 | P2 | 100 |
October 2 has 300 + 200; October 3 has 400 + 100
A Row Has a MeaningThe grain of fact_sales is one sale
Grain means what one row represents
A Sales FactA fact row records one sale and its amount
Sale 3 records 400 for product P1 on October 3
Product Descriptionsdim_product has one row per product
| product_id | name |
|---|
| P1 | Chocolate |
| P2 | Ice-cream |
A dimension gives a description for each product code
Facts and a Dimensionproduct_id connects each sale to one product description
Two P1 sales share the same Chocolate description
Names on Sales
SELECT sale_id,name,amount
FROM fact_sales JOIN dim_product USING(product_id)
WHERE sale_date='2025-10-03'
ORDER BY sale_id;
| sale_id | name | amount |
|---|
| 3 | Chocolate | 400 |
| 4 | Ice-cream | 100 |
The join adds names while keeping one row per sale
A Daily TotalGrouping changes the grain from one sale to one date
SELECT sale_date,SUM(amount) AS total
FROM fact_sales
GROUP BY sale_date ORDER BY sale_date;
| sale_date | total |
|---|
| 2025-10-02 | 500 |
| 2025-10-03 | 500 |
Four sale rows become two daily totals
Two Views of the Same SalesChanging the grouping answers a different analytical question
| product | October 2 | October 3 | total |
|---|
| Chocolate | 300 | 400 | 700 |
| Ice-cream | 200 | 100 | 300 |
| All products | 500 | 500 | 1000 |
The same four sales support daily totals and product totals
Detail and Summary
The daily total alone cannot recover the product amounts
Demo: Daily Sales
- Sum amount for each sale_date
- Keep only product P1 and compare the daily totals
Query result
| sale_date | amount |
|---|
| 2025-10-02 | 300 |
| 2025-10-02 | 200 |
| 2025-10-03 | 400 |
| 2025-10-03 | 100 |
Answer: Daily Sales
SELECT sale_date,SUM(amount) AS total
FROM fact_sales
GROUP BY sale_date ORDER BY sale_date;
| sale_date | total |
|---|
| 2025-10-02 | 500 |
| 2025-10-03 | 500 |
Each result row is one date, not one sale
Answer: Chocolate Sales
SELECT sale_date,SUM(amount) AS total
FROM fact_sales WHERE product_id='P1'
GROUP BY sale_date ORDER BY sale_date;
| sale_date | total |
|---|
| 2025-10-02 | 300 |
| 2025-10-03 | 400 |
The filter changes which sales contribute to each total
03 · Refreshing Reports
01Analytical Copies
keep reporting work separate from sales transactions
02Rows for Analysis
connect each sale to its product and daily total
03Refreshing Reports
refresh the copy in the right order without counting twice
From Received Data to a ReportExtract, transform and load connect the source to analytical tables
A pipeline turns received data into the table the report reads
Adding the New Date
| date | already in report | new batch |
|---|
| October 2 | 500 | — |
| October 3 | — | 500 |
October 3 contributes one new total of 500
Caveat: Counting a Batch TwiceSuccessful SQL can still produce a wrong report
Repeating an append can double the same sales
Replace the Affected DateA repeatable load preserves one correct row per date
Replacing the same date twice still leaves one total for that date
Demo: Repeat the Load
- Run the supplied date replacement twice in one script
- Check that both dates still have one total of 500
Query result
| sale_date | total |
|---|
| 2025-10-02 | 500 |
| 2025-10-03 | 500 |
Answer: One Total per Date
| date | after one replacement | after two replacements |
|---|
| October 2 | 500 | 500 |
| October 3 | 500 | 500 |
Both executions leave the same correct report
A Corrected Sale
A correction changes the affected date, not every date
The Corrected Report
| date | before correction | after refreshing October 2 |
|---|
| October 2 | 500 | 550 |
| October 3 | 500 | 500 |
Refreshing October 2 changes 500 to 550 and leaves October 3 unchanged
Run the Steps in OrderOrchestration manages dependencies between pipeline steps
The totals depend on copied sales; the report depends on checked totals
Takeaway: Data Warehousing
1An analytical copy gives repeated reports a clear input
2The meaning of one row determines how facts can be joined and summed
3Replace the affected date and check the result so reruns do not double sales