事实表的分析工程

如何根据实际分析用例为事实表建模数据。

数据建模的目标是使数据检索对于处理查询的引擎来说快速，并且对于编写这些查询的人来说容易。

大多数数据仓库实践旨在强调速度。 分析工程（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  |

第 1 部分：设计事实表

要基于 account 设计事实表模式，我们需要考虑人们可能提出的关于客户帐户随时间变化的分析问题类型。由于 account 表包含 status 字段，我们可以回答诸如以下问题：

每个月新增了多少个帐户？
每个月流失（变为非活动状态）了多少个帐户？
按客户群组划分的流失率是多少？

第 2 部分：实施事实表

要从 account 中存储的数据创建 fact_account，我们将编写一个 SQL 脚本来

使用今天的 account 数据初始化 fact_account。
获取 account 中行的快照（假设它由另一个系统更新）。
将每天的 account 快照与 fact_account 中的历史数据进行比较。
对于自前一天快照以来发生更改的每个帐户，在 fact_account 中插入新行。

第 3 部分：使用常见用例测试事实表

为了检查我们的事实表在实践中是否有用，我们将使用 Metabase 设置它，并尝试回答我们所有的三个示例分析问题。

第 4 部分：提高事实表性能

本教程的最后一部分让您了解当您的事实表扩展以适应更多历史记录（和更多问题！）时，迭代它会是什么样子。

如何跟随本教程进行操作

如果您想将以下步骤应用于您自己的数据，我们建议您使用由您的源系统定期更新的维度表，以及您选择的数据库或数据仓库。

在本教程中，我们使用 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 脚本模板进行操作。

接下来，我们将使用当前 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 等工具。有关更多信息，请查看 Learn 中 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。
从筛选按钮中，单击是否为第一条记录，然后选择“是”（默认设置），值为“True”。
从筛选按钮中，单击状态，然后选择“不是”，值为“Test”。
单击上次更新时间，然后选择“上个月”。

或者，他们可以使用如下代码片段，从任何 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 的查询构建器自助获取“每月流失账户”汇总表

转到新建 > 问题。
选择 fact_account 作为起始数据。
从选择您要查看的指标中，选择不同值的数量 > ID。
从选择要分组的列中，选择更新时间: 月。
单击筛选按钮。
单击状态，然后选择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          |

SQL 代码片段

 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       |

SQL 代码片段

 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 |

SQL 代码片段

 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 的更多信息

下一步：数据规范化

规范化数据库的外观以及表结构为何重要。

下一篇文章

商业智能

嵌入式分析

文档

学习

课程

Metabase 基础知识

提升您的数据技能

速查表

事实表的分析工程

如何根据实际分析用例为事实表建模数据。

简介

概述

如何跟随本教程进行操作

设计事实表

基本事实模式

更好的事实模式

初始化事实表

增量加载事实表

使用常见用例测试事实表

设置 Metabase

新账户

流失账户

高级用例：队列表

提高事实表性能

进一步阅读

下一步：数据规范化

这有帮助吗？

感谢您的反馈！