事实表分析工程
如何根据真实分析用例来设计事实表数据模型。
数据建模的目的是使检索数据快速（对于处理查询的引擎来说），并且容易（对于编写查询的人来说）。
大多数数据仓库实践都旨在强调速度。分析工程（一个由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 脚本中创建事实表，您可以按照可折叠右侧边栏上的“Import Script”按钮中的有详细注释的 SQL 脚本模板进行操作。
接下来，我们将用当前 account 表中的所有内容填充 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中。
您需要将此 SQL 脚本保存在数据仓库之外，并安排其运行，使用 dbt 或 Dataform 等工具。有关更多信息，请查看“学习”中 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 主页的右上角，点击 **New** > **Question**。
新账户
假设我们想知道上个月添加的总新账户数量。
这种结果适用于自助用例，例如：
人们可以通过以下步骤使用 Metabase 的查询构建器自助获取“过去一个月新增账户”之类的指标：
- 转到 **New** > **Question**。
- 选择
fact_account作为起始数据。 - 在 **Pick the metric you want to see** 中，选择 **Number of distinct values of** > **ID**。
- 在 **Filter** 按钮上，点击 **Is First Record** 并选择“Is”（默认设置），值为“True”。
- 在 **Filter** 按钮上，点击 **Status** 并选择“Is Not”，值为“Test”。
- 点击 **Last Updated At** 并选择“Last Month”。
或者，他们可以使用类似这样的代码片段从任何 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与其他表进行连接。这使得人们可以使用churned_accounts列在与其他不在 fact_account 中的列进行计算。
人们可以通过以下步骤使用 Metabase 的查询构建器自助获取“每月流失账户”汇总表：
- 转到 **New** > **Question**。
- 选择
fact_account作为起始数据。 - 在 **Pick the metric you want to see** 中，选择 **Number of distinct values of** > **ID**。
- 在 **Pick a column to group by** 中，选择 **Updated At: Month**。
- 点击 **Filter** 按钮。
- 点击 **Status** 并选择 **True**。
他们也可以使用如下查询从任何 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 的知识