事实表的分析工程
如何根据真实分析用例为事实表建模。
数据建模的目标是使数据检索“快速”（对于处理查询的引擎）和“容易”（对于编写这些查询的人）。
大多数数据仓库实践旨在强调速度。分析工程（dbt 推广的术语，有时也捆绑在全栈分析这个术语中）是为可用性建模数据的过程。即使您不这样称呼它，当您需要为其他人整理精选数据集、细分或指标或仪表板时，您可能正在实践分析工程。
本教程将向您展示如何将分析工程方法应用于数据仓库级别的数据集，更具体地说，应用于一种名为事实表的数据集。
介绍
维度表包含某个时间点的数据快照，例如您工作日结束时拥有的半成品杯子数量。
| time | total mugs |
|---------------------|------------|
| 2022-08-16 17:30:00 | 3 |
事实表包含信息的历史记录，例如您一天中喝咖啡的速度。
| time | mug | coffee remaining |
|---------------------|----------|------------------|
| 2022-08-16 08:00:00 | 1 | 100% |
| 2022-08-16 08:01:00 | 1 | 0% |
| 2022-08-16 09:00:00 | 2 | 100% |
| 2022-08-16 12:00:00 | 3 | 100% |
| 2022-08-16 12:30:00 | 3 | 99% |
| 2022-08-16 17:30:00 | 3 | 98% |
事实表和维度表在星型模式（或密切相关的雪花模式）中一起使用，以在数据仓库中组织信息。
您可能希望在以下情况下构建事实表：
-
您的数据源（生成数据的系统，例如您的应用程序数据库）仅通过覆盖先前的快照来存储信息的当前快照。
-
您正在创建用于为客户提供嵌入式分析的数据集。独立事实表非常适合自助式分析，因为它们可以涵盖广泛的用例，而无需依赖联接。
但在开始之前，让我们为您的日常总量再加一杯咖啡——我们还有很多事情要做！
| time | total mugs |
|---------------------|------------|
| CURRENT_TIMESTAMP() | n+1 |
概述
在本教程中，我们将使用一个维度表，我们将其称为 account，就像您可能从 CRM 中获取的维度表一样。我们假设此 account 维度表存储我们客户的当前状态，并且当前状态由我们的应用程序更新。
这个 account 表看起来像这样：
| id | country | type | status |
|------------------|-------------|------------|-----------|
| 941bfb1b2fdab087 | Croatia | Agency | Active |
| dbb64fd5c56e7783 | Singapore | Partner | Active |
| 67aae9a2e3dccb4b | Egypt | Partner | Inactive |
| ced40b3838dd9f07 | Chile | Advertiser | Test |
| ab7a61d256fc8edd | New Zealand | Advertiser | Inactive |
为了基于 account 设计事实表模式，我们需要考虑人们可能会问的关于客户账户随时间变化的分析问题。由于 account 表包含一个 status 字段，我们可以回答以下问题：
为了从 account 中存储的数据创建 fact_account，我们将编写一个 SQL 脚本来：
- 使用今天的
account数据初始化fact_account。 - 获取
account中行的快照（假设它由另一个系统更新）。 - 将每天的
account快照与fact_account中的历史数据进行比较。 - 对于自前一天的快照以来已更改的每个账户，向
fact_account插入新行。
为了检查我们的事实表在实践中是否有效，我们将在 Metabase 中设置它，并尝试回答我们所有三个示例分析问题。
本教程的最后一节将向您介绍如何根据事实表扩展以适应更多历史数据（和更多查询！）进行迭代。
如何跟随本教程
如果您想将以下步骤应用于您自己的数据，我们建议您使用一个维度表，该维度表由您的源系统定期更新，以及您选择的数据库或数据仓库。
在本教程中，我们使用 Firebolt 测试其合作伙伴驱动程序与 Metabase 的集成。Firebolt 是一个数据仓库，它采用了一些稍微修改过的 SQL DDL 来加载数据，其格式旨在使查询运行更快。
如果您正在使用自己的数据进行本教程，您的 SQL 语法可能不会与示例代码完全匹配。欲了解更多信息，您可以查看常见 SQL 方言的参考指南。
设计事实表
基本事实模式
首先，我们将为我们的事实表（我们将称之为 fact_account）起草一个模式。将模式以如下表所示的视觉参考形式呈现，可以更容易地验证 fact_account 是否将支持我们想要进行的查询（即人们想要回答的分析问题）。视觉参考还可以作为日后任何不熟悉 fact_account 的人的有用资源。
在此示例中，我们将保留 account 中的所有原始列。如果我们需要省略任何列，我们始终可以通过 Metabase 中的数据模型页面隐藏这些列。在 Metabase 中隐藏列比一开始就从我们的模式中排除过多列的干扰性要小，因为每次我们需要恢复列时都必须重新生成模式。
我们还将包含一个名为 updated_at 的新列，以指示行插入到表中的时间戳。实际上，updated_at 可用于近似帐户发生更改的日期或时间。
这个附加是基于以下假设：除了 id 之外，所有 account 属性都可以更改。例如，给定账户的状态可以从 Active 更改为 Inactive，或者账户的 Type 可以从 Partner 更改为 Advertiser。
基本 fact_account 模式示例
| Column name | Data type | Description | Expected values |
|-----------------|-----------|--------------------------------------------------------------|---------------------------------------------------|
| id | varchar | The unique id of a customer account. | 16 character string |
| status | varchar | The current status of the account. | Active, Inactive, or Test |
| country | varchar | The country where the customer is located. | Any of the "English short names" used by the ISO. |
| type | varchar | The type of account. | Agency, Partner, or Advertiser |
| updated_at | datetime | The date a row was added to the table | |
一个更好的事实模式
为了检查模式的可用性，我们将为我们的一个分析问题写一个伪 SQL 查询。
-- How many new accounts have been added each month?
WITH new_account AS (
SELECT
id,
MIN(updated_at) AS first_added_at -- Infer the account creation date
FROM
fact_account
GROUP BY
id
)
SELECT
DATE_TRUNC('month', first_added_at) AS report_month,
COUNT(DISTINCT id) AS new_accounts
FROM
new_account
GROUP BY
report_month;
当前的 fact_account 模式需要一个额外的步骤来获取（或估算）每个账户的“创建”时间戳（在这种情况下，对于在我们开始保留历史记录之前就已经活跃的账户，估算是必要的）。
如果我们在 fact_account 模式中简单地添加一个账户创建日期列，回答“新账户”的问题将变得容易得多。但是添加列会增加表的复杂性（人们理解和查询它的时间），以及 SQL 脚本的复杂性（更新表的时间）。
为了帮助我们决定是否值得向 fact_account 模式添加一个列，我们将考虑创建时间戳是否可以用于关于账户的其他类型的分析问题。
账户的创建时间戳也可以用于计算：
- 账户的年龄。
- 到重要事件的时间（例如账户流失或变为非活跃状态所需的天数）。
这些指标可以应用于有趣的使用场景，例如减少客户流失或计算 LTV，因此很可能值得将其包含在fact_account中。
我们将添加 is_first_record 列以保持我们的模式精简。此列将标记与帐户在事实表中的最早条目对应的行。
如果您计划创建事实表以简化自助服务（以便事实表包含通常在维度表中捕获的信息），您还可以为 is_latest_record 添加一列。此列将帮助人们筛选 fact_account 以获取当前数据（除了历史数据），以便他们可以使用同一张表快速回答诸如：“我们迄今为止有多少活跃帐户？”之类的问题。
使用此约定可能会导致查询速度变慢，但在首次推出自助服务时更容易被采用（这样人们就不必记住事实表和维度表之间的联接）。
一个更好的 fact_account 模式
| Column name | Data type | Description | Expected values |
|-----------------|-----------|---------------------------------------------------------------------|---------------------------------------------------|
| id | varchar | The unique id of a customer account. | 16 character string |
| status | varchar | The current status of the account. | "Active", "Inactive", "Test", or "Trial" |
| country | varchar | The country where the customer is located. | Any of the "English short names" used by the ISO. |
| type | varchar | The type of account. | "Agency", "Partner", or "Advertiser" |
| ... | ... | ... | |
| updated_at | datetime | The date a row was added to the table | |
| is_first_record | boolean | TRUE if this is the first record in the table for a given id | |
| is_latest_record| boolean | TRUE if this is the most current record in the table for a given id | |
初始化事实表
为了实现事实模式，我们将首先创建一个空的 fact_account 表，以随时间存储 account 表快照。
我们正在使用 Firebolt 数据仓库，所以我们将在 Firebolt 控制台创建事实表。我们将选择 SQL 工作区 > 新脚本，然后写入：
-- Create an empty fact_account table in your data warehouse.
CREATE FACT TABLE IF NOT EXISTS fact_account
(
id varchar
status varchar
country varchar
type varchar
updated_at timestamp
is_first_record boolean
is_latest_record boolean
);
请注意，Firebolt 的 DDL 包含 FACT 关键字（在标准 SQL DDL 中可以省略）。
如果您在用于摄取数据的同一 SQL 脚本中创建事实表，您可以按照可折叠右侧边栏上“导入脚本”按钮中的带有详细注释的 SQL 脚本模板。
接下来，我们将用当前账户表中的所有内容填充 fact_account。您可以将这些语句包含在创建事实表的同一 SQL 脚本中。
-- Put an initial snapshot of data from "account" into "fact_account".
-- Add "quality of life" columns to make the data model nicer to work with.
INSERT INTO fact_account (
SELECT
*,
CURRENT_TIMESTAMP() AS updated_at,
is_first_record = TRUE,
is_latest_record = TRUE
FROM
account);
增量加载事实表
为了用 account 的常规快照更新 fact_account，我们将编写另一个 SQL 脚本来：
- 查询
account以获取当前数据的快照。 - 将当前数据与
fact_account中上次更新的数据进行比较。 - 对于自上次快照以来已更改的记录，将行插入到
fact_account中。
您需要使用 dbt 或 Dataform 等工具，在数据仓库之外保存并调度此 SQL 脚本。有关更多信息，请查看学习中的 ETL、ELT 和反向 ETL 教程的数据转换部分。
-- Add the latest snapshot from the account table.
-- This assumes that account is regularly updated from the source system.
INSERT INTO fact_account
SELECT
*,
is_first_record = TRUE
FROM
account
WHERE
id = id
AND CURRENT_TIMESTAMP() <> updated_at ();
-- Update the rows from the previous snapshot, if applicable.
WITH previous_snapshot AS (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) AS row_number
FROM
fact_account
WHERE
is_first_record = TRUE)
UPDATE
fact_account fa
SET
is_latest_record = FALSE
FROM
previous_snapshot ps
WHERE
ps.row_number = 2;
使用常见用例测试事实表
以下是我们期望 fact_account 在开始填充每日 account 快照后会是什么样子：
| id | country | type | status | updated_at | is_first_record | is_latest_record |
|------------------|-----------|------------|-----------|---------------------|-----------------|------------------|
| 941bfb1b2fdab087 | Croatia | Agency | Active | 2022-02-04 09:02:09 | TRUE | FALSE |
| 941bfb1b2fdab087 | Croatia | Partner | Active | 2022-07-10 14:46:04 | FALSE | TRUE |
| dbb64fd5c56e7783 | Singapore | Partner | Active | 2022-05-10 02:42:07 | TRUE | FALSE |
| dbb64fd5c56e7783 | Singapore | Partner | Inactive | 2022-07-14 14:46:04 | FALSE | TRUE |
| ced40b3838dd9f07 | Chile | Advertiser | Test | 2022-07-02 06:22:34 | TRUE | TRUE |
现在，我们可以将事实表放入 Metabase，看看它在回答我们的示例分析问题方面的表现如何：
设置 Metabase
如果您的数据库尚未与 Metabase 设置好，您可以在几分钟内完成设置：
- 下载并安装 Metabase，或者注册Metabase Cloud 的免费试用。
- 添加数据库并连接您的事实表。
如果您正在使用 Firebolt 学习本教程，您将需要用于登录Firebolt 控制台的用户名和密码，以及数据库名称（列在控制台主页上）。
- 从 Metabase 主页的右上角，点击新建 > 问题。
新账户
假设我们想知道上个月新增的账户总数。
这种结果适用于自助服务用例，例如：
人们可以从 Metabase 的查询构建器中通过以下步骤自助获取“上月新增账户”等指标：
- 转到新建 > 问题。
- 选择
fact_account作为起始数据。 - 在选择您要查看的指标中，选择唯一值的数量 > ID。
- 点击筛选器按钮，点击Is First Record（是否为第一条记录），选择“是”（默认设置），值为“True”。
- 点击筛选器按钮，点击状态，选择“不是”，值为“测试”。
- 点击上次更新时间，选择“上个月”。
或者，他们可以使用任何 SQL IDE（包括 Metabase SQL 编辑器）通过以下代码片段自助获取相同的值：
SELECT
COUNT(DISTINCT id) AS new_accounts
FROM
fact_account
WHERE
is_first_record = TRUE
AND status <> "Test"
AND DATE_TRUNC('month', updated_at) = DATE_TRUNC('month', CURRENT_TIMESTAMP) - INTERVAL '1 MONTH';
流失账户
除了新增的业务账户，我们还希望追踪流失的账户。这一次，我们不会将结果限制在上个月的数据，而是获取一个像这样的月度汇总表：
| report_month | churned_accounts |
|--------------|------------------|
| 2022-05-01 | 23 |
| 2022-06-01 | 21 |
| 2022-07-01 | 16 |
这种结果可以帮助人们自助服务：
- 一个柱状图或折线图，用于绘制每个
report_month中churned_accounts的变化。 - 一种“趋势”可视化，用于显示流失账户数量的环比百分比变化。
- 一个已保存的问题或模型，可以在
report_month上与其他表联接。这使得人们能够在与其他列（不在fact_account中）的计算中使用churned_accounts列。
人们可以通过以下步骤从 Metabase 的查询构建器中自助获取“每月流失账户”汇总表：
- 转到新建 > 问题。
- 选择
fact_account作为起始数据。 - 在选择您要查看的指标中，选择唯一值的数量 > ID。
- 从选择要分组的列中，选择更新时间：按月。
- 点击筛选器按钮。
- 点击状态并选择真。
他们也可以使用任何 SQL IDE（包括 Metabase 的SQL 编辑器）通过这样的查询获取结果：
SELECT
DATE_TRUNC('month', updated_at) AS report_month,
COUNT(DISTINCT id) AS churned_accounts
FROM
fact_account
WHERE
status = 'inactive';
高级用例：同期群表
群组表是可以通过精心设计的事实表实现的最复杂的用例之一。这些表衡量流失率作为账户年龄的函数，可用于识别特别成功或不成功的客户群组。
我们希望得到这样的结果：
| age | churned_accounts | total_accounts | churn_rate |
| --- | ---------------- | -------------- | ---------- |
| 1 | 21 | 436 | = 21 / 436 |
| 2 | 26 | 470 | = 26 / 470 |
| 3 | 18 | 506 | = 18 / 506 |
由于这是一个高级用例，我们将重点向您展示 fact_account 表的“形状”如何转换为群组表。这些步骤可以在 Metabase 中通过创建一系列相互依赖的已保存 SQL 查询来完成。
-
创建一个已保存的问题，获取每个账户的
first_added_month和churned_month示例结果
| id | first_added_month | churned_month | | ---------------- | ----------------- | ------------- | | 941bfb1b2fdab087 | 2022-02-01 | null | | dbb64fd5c56e7783 | 2022-05-01 | 2022-07-01 | | 67aae9a2e3dccb4b | 2022-07-01 | null |代码片段
SELECT id, CASE WHEN is_first_record = TRUE THEN DATE_TRUNC('month', updated_at) END AS first_added_month, CASE WHEN status = 'inactive' THEN DATE_TRUNC('month', updated_at) ELSE NULL END AS churned_month FROM fact_account; -
将步骤 1 中保存的查询连接到一个每月一行的数据列。您可以通过生成序列在 SQL 中完成此操作（或者您可以使用数据仓库中现有的表）。请注意月份上的连接条件。
示例结果
| id | first_added_month | churned_month | report_month | age | is_churned | |------------------|-------------------|---------------|--------------|-----|------------| | dbb64fd5c56e7783 | 2022-05-01 | 2022-07-01 | 2022-05-01 | 1 | FALSE | | dbb64fd5c56e7783 | 2022-05-01 | 2022-07-01 | 2022-06-01 | 2 | FALSE | | dbb64fd5c56e7783 | 2022-05-01 | 2022-07-01 | 2022-07-01 | 3 | TRUE |代码片段
WITH date_series AS ( SELECT * FROM GENERATE_SERIES('2022-01-01'::date, '2022-12-31'::date, '1 month'::interval) report_month ) SELECT *, age, CASE WHEN s.churned_month = d.report_month THEN TRUE ELSE FALSE END AS is_churned FROM step_1 s FULL JOIN date_series d ON d.report_month >= s.first_added_month AND (d.report_month <= s.churned_month OR d.report_month <= CURRENT_TIMESTAMP::date); -
现在，您可以通过查询构建器将步骤 2 的结果聚合为最终结果（您可以使用自定义列计算流失率）。
示例结果
| age | churned_accounts | total_accounts | churn_rate | | --- | ---------------- | -------------- | ---------- | | 1 | 21 | 436 | = 21 / 436 | | 2 | 26 | 470 | = 26 / 470 | | 3 | 18 | 506 | = 18 / 506 |代码片段
SELECT age, COUNT(DISTINCT CASE WHEN is_churned = TRUE THEN id END) AS churned_accounts, COUNT(DISTINCT CASE WHEN is_churned = FALSE THEN id END) AS total_accounts, churned_accounts / total_accounts AS churn_rate FROM step_2 GROUP BY age;
提高事实表性能
一旦我们的事实表投入生产，我们就会关注它如何扩展，因为
- 表格更新了更多历史记录。
- 更多人开始并行地对表运行查询。
假设流失逻辑变得非常流行，以至于我们的 fact_account 成为许多下游仪表板和聚合的依赖项（和瓶颈）。
为了提高针对事实表的查询性能，我们需要预先计算对流失计算中最常用列的聚合。
在 SQL 数据库中，有几种方法可以做到这一点：
- 为
GROUP BY语句中最常用的列添加索引。 - 创建汇总（预聚合）数据的视图。
在我们的 Firebolt 数据仓库中，我们可以使用聚合索引结合这些优化。定义聚合索引会告诉 Firebolt 引擎创建额外的表（在底层），当 SQL 查询请求对给定列应用特定聚合时，应引用这些表而不是主事实表。
聚合索引也可以包含在您用于初始化和加载事实表的 SQL 脚本中（但更容易在实际观察人们如何使用该表后选择正确的索引）。
以下是 Firebolt 聚合索引的一个示例，它有助于加速计算不同报告期间的累计和当前流失账户数。
CREATE AGGREGATING INDEX IF NOT EXISTS churned_accounts ON fact_account
(
updated_at,
DATE_TRUNC('day', updated_at),
DATE_TRUNC('week', updated_at),
DATE_TRUNC('month', updated_at),
DATE_TRUNC('quarter', updated_at),
COUNT(DISTINCT CASE WHEN status = 'inactive' then id end),
COUNT(DISTINCT CASE WHEN status = 'inactive' AND is_latest_record = TRUE then id end)
);
延伸阅读
了解有关数据建模、数据仓库和使用 SQL 的更多信息
下一步：数据规范化
规范化数据库的样子以及表结构的重要性。