A3.4.2 · Alternative databases and data warehouses
A3.4.2 · 其他数据库与数据仓库
What a data warehouse is actually for
数据仓库究竟是做什么用的
Every one of A3.3's SQL statements ran against a database built to record what is happening right now. A data warehouse is built for a completely different question: what happened, over years, across every store, and what does that tell the business?
A3.3 的每一条 SQL 语句,都是针对一个用于记录当下正在发生什么的数据库运行的。数据仓库则是为了一个完全不同的问题而建:多年来发生了什么、在每一家门店都发生了什么,以及这些数据能告诉企业什么。
Explainer
讲解
A point-of-sale system has to answer "what is today's total" instantly, and it has to let a mistaken sale be corrected. That is exactly what A3.3's transactional databases (OLTP, online transaction processing) are built for: fast, frequent, small updates to current data. A data warehouse is built for the opposite job: answering "how did sales change over the past three years, across every store" from data that mostly just sits there, unchanged, waiting to be analysed.
销售点系统必须能立即回答"今天的总额是多少",并且必须允许更正一笔错误的销售记录。这正是 A3.3 中的事务型数据库(OLTP,联机事务处理)所要实现的:对当前数据进行快速、频繁的小规模更新。数据仓库则是为完全相反的工作而建:从大多基本保持不变、等待分析的数据中,回答"过去三年、在每一家门店,销售额是如何变化的"这类问题。
A data warehouse exists to answer a completely different question than the databases seen so far in A3.3. Those were built to record what is happening right now, quickly, one small change at a time. A data warehouse instead pulls together years of history from every system an organization runs, all centred on one subject, so a person can look for patterns across it rather than just the current figure.
数据仓库所要回答的问题,与目前 A3.3 中所见的数据库完全不同。那些数据库是为了快速记录当下正在发生什么、一次一小步地更新而建的。数据仓库则相反,它汇集了一个组织所运行的每一个系统多年来的历史数据,全部围绕一个主题展开,让人能够从中寻找规律,而不只是查看当前的数字。
Getting data into that warehouse in the first place is its own job (ETL), and once it is there, something also has to keep track of what each piece of stored data actually means.
首先要把数据导入这个仓库,本身就是一项独立的工作(ETL),而数据一旦进入仓库,还需要有东西持续追踪每一份存储数据实际的含义。
ETL: how data actually gets into the warehouse
ETL:数据究竟是如何进入仓库的
Pull the raw records out of wherever they currently live, an operational database, a spreadsheet, an API response, whatever the source happens to be.
把原始记录从它们目前所在的位置提取出来,可能是一个运营数据库、一份电子表格、一个 API 响应,无论来源是什么。
Reshape and combine those records until they share one consistent format the warehouse can actually work with.
对这些记录进行重塑与合并,直到它们统一为仓库能够实际处理的一致格式。
Write the now-consistent records into the warehouse itself.
把已经格式一致的记录写入仓库本身。
ETL has to be done carefully, since it is pulling from systems that are still running. The live system being copied from cannot be slowed down or disrupted by that copying. Whatever gets pulled out has to actually be usable once it lands, which means fixing mismatched formats along the way rather than after. And pulling everything a source happens to hold, rather than only what will genuinely get analysed, just wastes storage on data nobody will ever query.
ETL 必须谨慎执行,因为它是从仍在运行的系统中提取数据。被复制的实时系统不能因为这种复制而变慢或中断。提取出来的数据一旦落地就必须真正可用,这意味着要在过程中就修正格式不一致的问题,而不是事后再处理。而且,如果只是把一个数据源恰好拥有的所有数据都提取出来,而不是只提取真正会被用于分析的数据,只会白白浪费存储空间在永远不会被查询的数据上。
Explainer
讲解
Six characteristics, one goal: efficient storage and analysis
六大特征,一个目标:高效的存储与分析
Each of these is a deliberate design choice, not an accident. Click through each one to see what problem it actually solves.
这些特征每一项都是刻意的设计选择,而非偶然。逐一点击查看它究竟解决了什么问题。
Worked example
示例讲解
Amberroot Coffee Roasters: one sale, two very different tables
Amberroot 咖啡烘焙坊:一笔销售,两张截然不同的表
Amberroot runs 40 coffee shops. Every espresso sold hits the point-of-sale system immediately, and every night that data also flows into the warehouse. The two tables holding that same sale barely resemble each other.
Amberroot 经营着 40 家咖啡店。每卖出一杯浓缩咖啡,数据都会立即进入销售点系统,而每晚这些数据也会流入数据仓库。记录同一笔销售的这两张表,彼此几乎没有相似之处。
OLTP: Sales (the live point-of-sale table)
OLTP:Sales(实时销售点表)
| SaleID | StoreID | Item | Qty | Revenue | Timestamp |
|---|---|---|---|---|---|
| S8821 | Store-14 | Flat White | 2 | 9.00 | 2027-02-11 08:42 |
A cashier rings up the wrong size. This row gets corrected in place: an UPDATE changes Revenue from 9.00 to 7.50, and nothing records that the mistake ever happened.
收银员输错了杯型规格。这一行会被原地更正:一条 UPDATE 语句把 Revenue 从 9.00 改为 7.50,而且没有任何记录留下这次错误曾经发生过。
Warehouse: Sales_Fact (the same sale, that night)
数据仓库:Sales_Fact(同一笔销售,当晚)
| FactID | StoreID | DateKey | Item | Qty | Revenue | LoadedAt |
|---|---|---|---|---|---|---|
| F55201 | Store-14 | 2027-02-11 | Flat White | 2 | 9.00 | 2027-02-11 23:00 |
| F55340 | Store-14 | 2027-02-11 | Flat White (correction) | 0 | -1.50 | 2027-02-12 23:00 |
The warehouse never edits F55201, that would be an UPDATE, and the warehouse does not do those. Instead, the next night's load appends a brand new correcting row. Both rows stay forever, so an analyst can see not just what the final number was, but that a correction happened at all.
仓库从不修改 F55201,那样做就是 UPDATE,而仓库不会这样做。相反,第二天晚上的加载会追加一条全新的更正行。两行数据都会永久保留,这样分析师不仅能看到最终数字是多少,还能看到确实发生过一次更正。
This single example demonstrates three characteristics at once. Append-only and non-volatile: the fix arrives as a new row, never as an edit to the old one. Time-variant: because both rows are kept with their own dates, an analyst can reconstruct exactly how Amberroot's numbers looked on any given day, corrections included. None of this would be possible if the warehouse behaved like the OLTP table above it.
这一个例子同时展示了三个特征。仅追加与非易失:更正是以一条新行的形式出现,而绝不是对旧行的编辑。随时间变化:因为两行数据各自保留了自己的日期,分析师能够准确还原 Amberroot 在任意一天的数字是什么样的,包括更正在内。如果仓库的行为和上面的 OLTP 表一样,这一切都不可能实现。
Check your understanding
检验你的理解
Amberroot's warehouse never runs UPDATE on an existing sales row, even to fix a mistake, it appends a new correcting row instead. Which characteristic does this describe?
Amberroot 的仓库从不对现有的销售行执行 UPDATE,即使是为了修正错误,而是追加一条新的更正行。这描述的是哪一个特征?
Common mistake
常见错误
A3.4.2
考官在 A3.4.2 中报告了这一模式
RecurringCandidates often describe a data warehouse as if it were simply a large, general-purpose database used for day-to-day operations, storage and updates rather than analysis. Once that core purpose, supporting analysis and decision-making rather than running the business's live transactions, gets lost, every characteristic that follows from it (append-only, non-volatile) stops making sense too.
反复出现考生常常把数据仓库描述成一个用于日常操作、存储与更新的大型通用数据库,而不是用于分析。一旦丢失了这个核心目的,即支持分析与决策,而不是运行企业的实时事务,由此衍生出的每一个特征(仅追加、非易失)也就跟着说不通了。
What a weak answer looks like: "A data warehouse stores a company's data so it can be updated and accessed by staff." That sentence describes an ordinary transactional database, not what makes a warehouse distinct.
薄弱答案的样子: "数据仓库存储公司的数据,以便员工进行更新和访问。" 这句话描述的是一个普通的事务型数据库,而不是数据仓库真正与众不同之处。
What it should say: "A data warehouse stores large volumes of historical data for analysis and decision-making, not for day-to-day transaction processing. Because analysis needs a stable, unchanging record, data is loaded as append-only and stays non-volatile once it arrives, unlike an operational database's records, which are updated constantly." Naming the analytical purpose FIRST is what makes everything after it make sense.
应该这样写: "数据仓库存储大量历史数据,用于分析与决策,而不是用于日常事务处理。因为分析需要稳定、不变的记录,数据以仅追加的方式加载,一旦到达就保持非易失,这与不断被更新的运营数据库记录不同。" 首先点明分析目的,才能让后面的一切都说得通。
Revision questions
复习题
Past-paper style. Write an answer, then reveal the marking points and mark yourself.
采用历年真题风格。写下答案,然后展开评分要点给自己打分。
[2]Explain what is meant by a data warehouse being non-volatile.
[2]解释数据仓库"非易失"的含义。
Marking points
评分要点
- Non-volatile means data is loaded and then read many times but not updated or deleted in place.
- Once written, a record stays, so the warehouse preserves a stable history rather than only the latest state.
- 非易失指数据被加载后被多次读取,但不会就地更新或删除。
- 记录一旦写入就保留,所以仓库保存的是稳定的历史,而不只是最新状态。
[4]Explain how the append-only and time-variant characteristics of a data warehouse work together to support historical analysis.
[4]解释数据仓库的仅追加与随时间变化这两个特征如何共同支持历史分析。
Marking points
评分要点
- Append-only loading means new data arrives as fresh records rather than overwriting old ones.
- Time-variant means every record is tagged with the time period it refers to.
- Together, each load adds a new time-stamped snapshot, so the warehouse can show what any measure looked like at any past date.
- This makes trend analysis and period-to-period comparison possible, which an overwrite-in-place operational system cannot support.
- 仅追加加载指新数据以新记录形式到来,而不是覆盖旧记录。
- 随时间变化指每条记录都标注了它所指的时间段。
- 两者合起来,每次加载都增加一个带时间戳的新快照,所以仓库可以显示任何度量在任何过去日期的样子。
- 这使趋势分析和期间对比成为可能,而就地覆盖的业务系统无法支持这一点。
[6]A retail chain combines sales data from its point-of-sale systems, supplier invoices and a loyalty app into one data warehouse. Explain the role of the ETL process in this, and explain why the resulting warehouse would be described as integrated and subject-oriented.
[6]某连锁零售商将其销售点系统、供应商发票和会员应用程序的销售数据合并到一个数据仓库中。解释 ETL 流程在其中的作用,并解释为什么最终形成的仓库会被称为集成的与面向主题的。
Marking points
评分要点
- ETL extracts data from the point-of-sale systems, supplier invoices and loyalty app.
- It transforms them into a common clean format: matching product codes, currencies, date formats and removing errors.
- It loads the cleaned, consistent data into the warehouse on a schedule.
- The warehouse is integrated because ETL has reconciled the three sources so they use the same codes and units.
- It is subject-oriented because the data is organised around business subjects such as sales and customers, not around the three source applications.
- This lets analysts query sales across all channels as one consistent history.
- ETL 从销售点系统、供应商发票和会员应用中抽取数据。
- 它把它们转换为统一干净的格式:统一产品编码、货币、日期格式并去除错误。
- 它按计划把清洗后一致的数据加载进仓库。
- 仓库是集成的,因为 ETL 已协调三个来源,使它们使用相同的编码和单位。
- 它是面向主题的,因为数据围绕销售、顾客等业务主题组织,而不是围绕三个来源应用程序。
- 这让分析师能把所有渠道的销售当作一段一致的历史来查询。
Flashcards
记忆卡片
Active recall for this statement's key terms.
针对本条考纲陈述关键术语的主动回忆练习。
Keep this statement's practice with you
把这条考纲陈述的练习保存下来
Free account: unlock every revision question and flashcard for A3.4.2, and every other IB CS 2027 statement.
免费账户:解锁 A3.4.2 以及所有其他 IB CS 2027 考纲陈述的全部复习题和记忆卡片。