事实表的分析工程
如何根据实际分析用例为事实表建模。
数据建模的目标是让数据检索**快速**（对于处理查询的引擎）和**容易**（对于编写这些查询的人员）。
大多数数据仓库实践旨在强调速度。**分析工程**（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 Workspace > New Script，并编写：
-- 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 和逆向 ETLs 的转换数据部分。
-- 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 主页的右上角，单击 New > Question。
新账户
假设我们想知道上个月新增账户的总数。
这种结果适用于自助服务用例，例如：
人们可以使用 Metabase 的查询构建器通过以下步骤自助服务“上个月新增账户”等指标：
- 前往 新建 > 问题。
- 选择
fact_account作为起始数据。 - 从 选择您要查看的指标 中，选择 ID 的不同值数量。
- 从 筛选 按钮，单击 是否为第一条记录 并选择“是”（默认设置），值为“真”。
- 从 筛选 按钮，点击 状态 并选择“不是”，值为“测试”。
- 点击 上次更新时间 并选择“上个月”。
或者，他们可以使用以下代码片段从任何 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 数据库中有几种方法可以实现这一点：
在我们的 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 工作