简单的时点数据快照表构建方法

许多数据工程师质疑数据仓库模式为何将数据管道中的阶段数据拆分成三种类型的表（枢纽表、链接表和卫星表），仅仅是为了在消费数据时再把它们合在一起。这是一个很好的问题。数据工程师、数据架构师和解决方案架构师都希望找到高效的方法来自动化数据移动以回答业务案例。复制并拆分数据似乎是最不高效的，我们最好有充分的理由这样做！

原始应用数据以多种形式和数据类型存在；结构化、半结构化、符合第三范式，甚至是维度模型！遵循数据仓库架构和建模模式的一个优势在于，无论是哪种形态的原始数据，或者其批量快照或增量数据，或流数据提供的节奏如何，最终结果总是我们熟悉的三种标准表类型——hub、link 和 satellite。

跟踪业务对象，关注业务中的关系、事件和交易，同时记录真实的变化。

• 中心表 形成了您的业务对象在所有相关源应用（用于您建模的业务流程的自动化系统）之间的被动集成接口。
• 链接表 记录了业务对象之间的互动，这些互动可以是业务事件、交易，或仅仅是表示这些业务对象之间存在着某种关系。源链接表记录了源应用中体现的内容，而业务视图链接表则描绘了这些互动的业务视角。它们可以完全不同！
• 卫星表 仅记录基于源数据描述的真正变化。如果真正变化的属性记录了业务对象的变化，那么这些变化会被记录在中心表-卫星表中；如果真正变化的属性基于业务对象之间的互动，那么这些变化会被记录在链接表-卫星表中。从源应用中提取的每个数据都会加载到一个或多个原始卫星表中，我们不会将来自不同源的数据合并到一个原始卫星表中，也不会将原始卫星表复制到业务卫星表中。如果需要进一步转换原始数据，我们会记录该转换结果作为业务卫星表。

枢纽表反映了业务对象类型中的top-down model（自上而下的模型），这些对象类型是业务所关注的；枢纽策略表关注策略，而枢纽贷款账户表则关注贷款账户，等等类似。通过将数据拆分为这些简单的三种表类型，我们就能。

• 将我们对一项政策、贷款账户或任何其他业务对象的所有了解，围绕从每个源系统记录的真实变更（如“缓慢变化维度”）进行整合。
• 我们也可以整合我们对互动/关系/业务事件的所有了解，如果这种关系恰好被记录在不同的源系统中（这种情况很少见——这可能表明你有多余的源系统在自动化相同的业务流程！）。但是，联接表确实提供了机会，可以部署专业的数据仓库组件，例如使用一个或多个基于相同联接表构建的时效性卫星表。

最具挑战性的是，将这些数据从多个原始数据卫星表和业务金库卫星中整合在一起的能力。数据金库考虑到这一点，并因此提供了时间点（PIT）表结构来解决这一问题。

走啦！

表格结构

标准查询表和数据集市视图

时间点表（绝对不是SQL视图）被用来在一个特定时间点上（想象拍照）捕捉与某个业务对象或关系（父实体）相关的记录。对于单一的PIT表，你需要支持所谓的“管理的PIT窗口”和“对数型PIT结构”（关于这些后面会详细介绍），目前先这样考虑：

• 场景1：对于实体或关系的当前状态，PIT表当然会是这样的快照。
• 场景2：对于历史快照，它将是该信息在那个时间点的状态，如果父实体的状态从未改变，则PIT表将始终引用同一记录，自该实体首次引入卫星表以来。
• 场景3：对于无状态父实体的数据（此时该父实体尚无描述性属性），则PIT表将引用所谓的“幽灵记录”（与零键完全不同），直到记录开始进入该卫星表为止。你可能会好奇，那个业务键是如何出现的呢？这是一个占位符条目 😉

让我们澄清上面的对话中的一些难懂的术语。

• 幽灵记录 — 当创建一个卫星表时，会插入一条单独的幽灵记录，以确保查询时该PIT表只需与周围的卫星表进行一次等值连接。
• 零键 — 一个动态概念，用于处理和解释业务键列为空的情况。零键在数据加载阶段自然出现，无需预先加载任何数据仓库零键。零键的动态特性使得在链接表中表示多对多关系成为可能，从而确保链接表与枢纽表之间的连接始终是等值连接。
• 截至 — 在这个时间点上，该业务对象或关系的状态是什么。这是我们在时间序列分析中使用的一个概念，Snowflake有一个特定的构造称为ASOF连接。我们在PIT表的构建和管理中也使用相同的概念（但不是相同的技术）作为ASOF表，其构造和内容可以与Kimball风格的维度建模中的日期时间维度进行比较（后续章节会详细介绍）。

当我们很快将这些子主题合并成一个图像时，这些概念开始变得清晰。

PIT 表的数据字典：

哈希键和应用日期时间戳列的对的数量由该PIT表将从其检索数据的卫星表的数量决定。如果你愿意的话，你可以根据需要向PIT表添加描述性属性，这些属性可以来自周围卫星表，这些卫星表属于该父实体，甚至（如果需要的话）包括一些简单的转换以派生列值（例如滚动余额或简单的条件语句输出）。我们希望保持PIT表简单高效，因为PIT表是在OLAP平台上构建的，列式压缩会减少重复且未更改的代理哈希键和应用日期在快照日期期间的存储空间占用。

PIT表本质上只是复制了原始库和业务库中的键和应用日期，因此这些PIT表因此是可一次性使用的。因为PIT表充当了连接索引，基于这些PIT表构建数据仓库时，默认方法是将它们部署为SQL视图。一个PIT可以支持多个业务场景，因此可以创建多个视图。

持续的数据流是独立于获取相关键和日期的快照以传递信息的，参见kappa vault了解更多信息。

ASOF 表的数据字典

我们谈到了ASOF表是什么；类似于日期时间维度表，你会发现日期字段有多种格式，同时有业务标志或指标来标识一些重要的业务日。

ASOF表很少更新，在与业务方协商后，ASOF表可以逐步更新为包含从财年末起接下来12个月或更长时间的快照时刻和业务标志和指标。

让我们把上面学到的内容应用到一个简单的例子中（请注意，为了保持示例简单，在这些示例中不使用哈希）。

场景 1-3

• 场景1 (101)：每天的快照记录会指向卫星表中的一个新记录。
• 场景2 (456)：在1月1日，该卫星表没有描述属性，因此在该快照日期，PIT表会引用卫星表的“幽灵记录”。然而，在2月1日，描述属性到达，并且在3月1日没有变化。因此，对于该业务对象的3月1日快照日期，将引用该日期点适用的描述属性，即2月1日的描述。
• 场景3 (789)：该卫星表没有描述细节，因此在该快照窗口期间，PIT表将在每个快照间隔引用“幽灵记录”。

现在让我们对比快照和实际的变化，注意实体789在这个卫星表中不存在，但之前在中心表中存在。

幽灵记录这种机制支持等值连接，你不需要混合使用内连接和左连接来支持从多个围绕PIT表的卫星表进行查询。这种结构实现了维度建模社区所说的星型连接，而对于数据库开发人员来说，这通常被称为hash连接。在你的查询计划中，你应该能看到计划节点向右倾斜，这种情况通常被称为右深连接树。我们已经在那篇文章中讨论了这种优化及SNOPIT的实现：文章。

管理：

PIT表的标准列结构保持一致，但我们可以选择PIT表包含的内容及其形式。下面的每个主题都可以结合使用，从而使PIT表模式可以重复使用和扩展。

管理 PIT 窗口

最简单的配置是定义开始和结束快照日期时间戳，我们通过在ASOF日期表中限制使用的日期来实现这一点（或者创建一个独立的ASOF表，包含你需要的日期）。你可以将快照开始日期定义为你想要的任意时间，但如果你计划对更多数据进行快照，那么构建PIT表的成本会更高。另一种你可以考虑的方法是，在需要向PIT表添加新的快照日期时，你是从头开始重建PIT表（全量刷新），还是创建一个新的中间快照表，包含你需要的新快照日期，并将其插入你已经构建的PIT表中。在这种情况下，构建成本可能会降低，但你需要构建编排和一致性检查测试，以确保PIT表与相邻的数据仓库卫星表之间的参照完整性仍然准确。

不含状态数据供参考的PIT窗口将指向一个幽灵记录

构建PIT表时需要特别小心，如果为业务对象/关系构建的PIT表没有可参考的状态数据，则该PIT表中的每条记录（直到卫星表中存在实际数据为止）都将指向幽灵记录。特别是Snowflake，在星联查询的probe-side（即查询中的左侧）存在大量相同值的join-key时性能会下降。这种情况称为探针侧的偏斜，这是我们在这篇文章文章中讨论过的一个主题，该文章提供了一个在查询时间（即在您的数据仓库中）处理这种情况的解决办法。当然，您不应一开始就创建这么多的幽灵记录，但如果PIT表中有过多重复的join-key值，该文章确实描述了处理这种情况的方法。

鬼影偏差，导致探针偏移

对数PIT（对数坑）

你真的能说在你需要新数据和已经老化数周、数月甚至数年的数据时的快照，需要同样细粒度的快照吗？你的业务案例真的需要知道两年前某个账户在星期四早上9点5分的具体余额吗？这通常不会发生。在这里，我们将提出不同的PIT表结构，这些结构更适合实际的商业智能报告需求。

我们这么说是什么意思呢？填充PIT表的数据管道可以一次性加载所有需要的快照频率以刷新整个表，或者（如前文所述）通过管道编排来加载PIT增量到现有的PIT表中。无论你选择哪种方法，你都将根据你在ASOF表中定义的报告周期来构建或加载PIT表——你选择的增量将根据你的业务报告需求而定，比如：

• 日内 PIT，对于您需要在一天内多次数据快照的数据，比如9点、12点和下午4点。
• 每日日终 PIT，每天的快照，包括周末。
• 营业日 PIT，仅在营业日进行数据快照，并排除公共假期和公司假期。
• 每周 PIT，每月 PIT，每季 PIT，财年末 PIT 等等。
• 当前活跃 PIT（简称CPIT），仅包含活跃的业务实体或关系的代理哈希键和应用日期时间戳。

比较对数快照频率来说

根据您的卫星表嵌套程度 (数十亿……甚至数万亿条记录)，检索每个PIT表类型创建时的代理哈希键和应用日期时间戳可能会觉得浪费。如果你在刷新时构建PIT表，PIT表在快照日期的增量中，代理哈希键和应用日期时间戳可能会出现重叠。同样，如果你是逐步填充PIT表，也会出现这种情况。

一个看来的替代方案是建立滑动窗口管道，这样的。

咱们接着详细说说吧！

翻滚的PIT窗口.

如果我们用对数型PIT表，不需要从数据仓库（原始数据仓库和业务数据仓库）为每个重叠的PIT表获取相同的代理哈希键和应用日期戳，我们只需将现有PIT表中的某些键推送或提取到另一个PIT表，不论是做刷新还是增量更新。

从粒度最细的 PIT 表（比如每日 PIT）中 提取 需要填充较低层级 PIT 表（比如每周 PIT）的键和日期。顶层 PIT 可能会变得很大（如果符合你的业务报告目的，你可以修剪较早的快照），因此对于更低层级的 PIT 表（比如每月 PIT），我们从中间层级的 PIT 中获取所需的快照键和日期。这些滚动窗口的层级（层级数量）由你决定，当然这种方法需要更多的协调工作，但可以减少处理非常大的 PIT 表的需求（也许可以考虑使用 Snowflake streams 处理这些数据）。对于存储在（例如）每日 PIT 中的较早快照期间，可能不需要保留那些更细粒度的键和日期，因此较低层级的 PIT 表充当了该快照期间的摘要。

如果 PIT 已经有了我们需要的密钥和日期，为什么还要回数据仓库拿这些呢？

实现方法

效果因人而异哦！选最适合你的选项吧，找到最合适的那个！

刷新与增量更新PIT表

我们之前在这篇文章中讨论过增量和刷新PIT表的实现：文章。增量PIT数据管道的一个主要区别是，如果数据仓库的原始或业务部分中的某条记录被删除会发生什么情况？这种情况可能会因为组织需要遵守法律、合同和监管义务而发生。

我们讨论了将标识符隔离到其自己的个人可识别信息（PII）卫星表中的方案，并通过SQL更新永久模糊化处理标识符，同时保持记录摘要（哈希差异）不变，以符合GDPR第17条的要求。这意味着如果将PII卫星表包含在PIT表的连接中，无需更新该表，从而避免了对不存在记录的引用。

记住，这是一个等值连接，如果有缺失的相邻辅助表中的记录丢失了，那么整个业务对象或关系在该时间点的信息集市中将无法显示。

使用 Snowflake，我们展示了如何使用相同的 刷新 PIT 表 数据管道逻辑来更新自身为 Snowflake 的 动态表 (Dynamic Table) 的过程，该过程是 逐步地 更新的。你可以设置目标滞后，初始构建将是一个完整的刷新，但随后从底层数据仓库生成的数据快照将被逐步地添加到动态表中。哪些增量会被添加进来仍然由你设置在 ASOF 表中的那些业务报告标志和指标控制。

点击这里阅读更多关于完整实现的信息。

SNOPIT表的数据字典：

在 Data Vault Alliance 社区的论坛（https://datavaultalliance.com/）上，有人提出了一个挑战，即找到一种方法来提高 Snowflake 中 SQL 联接的性能，考虑到 Snowflake 独有的 flocon de neige（雪花）表（FDN 表）。Snowflake 并不支持表索引、主键和外键。我针对这个挑战提出了一个 序列-编号-仅期（S-N-O-PIT） 结构，并在这里分享了我的想法。最近，SNOPIT 在实际应用中也得到了实施，详情请参见这里。

这个想法很简单，借鉴了维度建模中的一个概念叫做时间戳代理键，即在每个卫星表中实现一个自增标识列（ID列）。在构建PIT表的过程中，我们不再抽取代理哈希键和应用日期时间戳，而是直接使用自增列。由于Snowflake架构将计算和存储分离，通过本质上将云服务提供商的虚拟机实例（如AWS EC2、Azure Virtual Machines、Google Compute Engine — Snowflake将其称为虚拟仓库）包裹起来，在Snowflake管理的存储桶上执行查询操作，我认为，提高查询性能的关键在于减少Snowflake管理组件与查询结果需求方之间的网络流量。一种实现方法是减少通过云服务提供商的安全内部网络传输的字节数。使用自增标识列可以减少PIT表和相邻卫星表之间进行连接的列的数量，并且整型数据类型表示这些记录所需的字节更少。SNOPIT是一种查询引擎优化技术，因此如果底层表是Apache Iceberg表，也可以使用相同的构建。

SNOPIT 表比其他标准的 PIT 表更胜一筹，并且生成了完全相同的输出结果。

比较PITLESS、PIT和SNOPIT的连接表现，关于上述图片的博客可以在那里找到这里。

1. 没有 PIT 表连接卫星的结果是：8 分 19 秒
2. 使用 PIT 表构建的结果是：19 秒
3. 使用 SNOPIT 表构建的结果是：15 秒

想更深入地了解PIT和SNOPIT表格，可以点击这里观看这个网络研讨会。

如果我们把之前的例子重新构想成一个 SNOPIT，它会是这样的

用 SNOPIT 来代替 PIT。

并行加载 PITs.

PIT表填充的另一种选择是在构建CPIT表时非常有用，即在加载数据仓库表的同时填充PIT表。当你不想为该卫星表启用 自动重组 时，CPIT 在查询卫星表中的当前记录时非常有用。因为这些操作对用户是隐藏的，所以在视图下，您将执行一个卫星表和CPIT表之间的等值连接，正如我们在本文后面部分所述的文章中所述，在Snowflake中执行一个JoinFilter（也称为布隆过滤器）。

另一种选项是如果可以并行加载所有层级的PIT表并在一个语句中完成，我们就可以通过Snowflake的条件多表插入来实现，如文中所述文章链接。

并行加载CPITs（如需进一步解释，请参见上下文或术语表）

几个场景

几乎所有的数据仓库双时段应用场景都是可能的，如果采用双时段数据仓库。是的，PITs 和桥梁使用原始和业务数据仓库作为来源。那我们继续吧……

一个 PIT（信息传递点），多种信息传递方式

PIT 表提供了一个 访问方式 或者（就像一位前同事所说的）基于你要查询的业务对象或关系的特定时间点，找到你需要的快照日期时间戳属性的精确位置。单个 PIT 表可以被许多业务案例重复使用，这些案例需要相同时间段的数据来满足各自的业务需求，这些数据都源自相同的数据仓库卫星表。一个卫星表可能包含一个或数百个描述性属性。最初引入这些属性（包括不需要的那些）的业务案例现在也让后续的业务案例受益，减少了为引入额外描述性属性而需要的开发周期。

这也是我们说初始业务案例在启动数据仓库之初成本较高，但后续的业务案例则能更快地交付，并缩短开发周期的原因之一。（想想 Gregor Hohpe 的 卖期权的比喻）。

请记住，并不是所有的数据都具有相同的价值。如果你提取的数据包含与业务运行无关的应用相关的数据，那么你应该去掉那些无关的属性。

多条活动记录条目

不同粒度的卫星表如下所述：here。详情请见链接。

如果在某一时间点，该父实体的状态有多个现行记录；这不会影响PIT表（时间切片表）的构建。无论你是从一个多活动卫星表还是多个这样的表，或者带有依赖子键的卫星表进行报告，PIT表（时间切片表）主要是为了 找到 那些在该时间点适用的记录。因此，对于任何类型的卫星表，PIT表的结构 不会 改变，因为不需要记录多活动卫星表中的子序列键。

同样，当我们从源应用程序中处理了批量事务时也是如此。正如我们在本文文章中讨论的那样，我们将批量事务加载到卫星表中的方式是，定义卫星表的依赖子键为事务ID或事务日期时间戳（我们将其称为日内键）。由于批量事务携带了批处理/提取日期，即批量事务的应用日期，PIT表只需要代理键和应用日期时间戳即可返回该时点相关的所有事务。

可以将多个活动记录（如事务卫星表、多活动卫星表以及带有依赖子键的卫星表）连接在一起，无需改动PIT表的结构或外观，PIT表与这些卫星表之间的连接是1对多的。

您在设计数据仓库时必须具有明确的目的，以将多个多活动记录从围绕PIT表的多个多活动卫星表中结合在一起。其中一些多活动行应被扁平化（可能转换为数组或JSON键值对列）。假设您希望在数据仓库中包含的维度围绕着您建模成具有日内（intra-day）键的批量交易的卫星表，那么这些交易记录可能需要保持在该粒度，而其他多活动记录则应进行扁平化处理。在所有这些情况下，PIT表的结构保持不变。

为了支持这些结构，SNOPIT的多活动部分需要重新使用代理密钥和应用日期进行检索，即(SN+PIT)。例外情况是当你试图合并多个卫星表，这些表有依赖的子表，以返回最新的子类别状态；对于给定的“截至”日期，你可能需要获取在连续快照期间没有更新的依赖子键的状态。从根本上说，这意味着你的PIT表需要增加一个子类别列来进行等值连接；我们将使用记录哈希(HashDiff)。

围绕PIT的Dep-key卫星，情况也会类似出现在SNOPIT中

你还需要考虑的一个场景是，返回的PIT表中的值是否应该在相邻的辅助表中通过依赖子键匹配，或者是否需要一个依赖子键的状态匹配该父键的所有记录，这可能会导致数据中的潜在笛卡尔积，因此，在决定PIT结构应该如何操作前，你必须先理解数据。

• 情况1：通过子键匹配
• 情况2：笛卡尔积

还有别的选择吗？

PITs和Bridges并不是将数据从数据仓库中取出的唯一方法！它们仅仅是已经被证明可以利用数据平台的OLAP算法用于高效数据查询的可重复使用的方法。

你可以考虑一下这些不同的替代方案。

• 直接查询数据仓库——面向那些精通SQL的商业分析师。
• 枝叶图法——在“数据仓库大师”这本书中描述的一种方法，适用于从卫星表中仅选择部分列时的数据去重。
• Twine——基于一个锚卫星表，通过使用UNION ALL改变数据连接方式为从相邻的卫星表中返回“截至”记录状态。
• 将内容推送到事实表和维度表，可以通过重复利用数据仓库的卫星表作为维度表和桥接表，该表包含派生内容，或者通过Snowflake流和任务或动态表从卫星表推送新数据来构建类型2的缓慢变化维度（SCD类型2）。
• 如果您的所有数据都来自一个单一的卫星表，那么您根本不需要使用PIT模型！请记住，您应该在卫星表中包含业务键以支持这一点。
• 您甚至可以采用传统方法，考虑构建一个超新星模式。
• 您在业务仓库中尝试过使用活动模式吗？

搞定啦!

数据仓库枢纽、链接表（链接层）和卫星表（卫星层）是基于建模和数据管道中的可重复模式。然而，这种简单性却使得许多可重复的查询模式成为可能，并能利用平台的OLAP能力。

• 根据枢纽表或链接表使用PIT表来检索描述性属性，顺便说一句，你也可以包含仅适用于基于链接的PIT表的有效性卫星，以及扩展记录跟踪卫星中的存储内容，详情 参见这里 。
• 使用桥接表来缩短数据仓库模型中分散的枢纽表之间的路径，解决链接有效性问题，或者持久化聚合指标。我们在这里讨论这三种用途参见这里。

我们还证明了使用PIT表并不是从数据仓库中检索数据的唯一方法。正如你所见，代理哈希键和自增标识列仅仅是将数据整合到数据仓库中的工具。你绝不会看到这些工具用在PIT和桥接表之外，因为它们本身没有任何业务相关性。

然而，无论你选择什么方式从数据湖中提取数据，都要确保它是可持续的、可重复的、可扩展的（尤其是）有目的的。

下次聊！

更多参考

• Snowflake 博客文章系列 — https://www.snowflake.com/en/blog/authors/patrick-cuba/
• 《Data Vault Guru》这本书 — https://www.amazon.com/Data-Vault-Guru-pragmatic-building/dp/B08KJLJW9Q
• 从零开始的 Snowflake 上的 Data Vault — https://www.snowflake.com/webinar/thought-leadership/zero-to-data-vault-on-snowflake-2023-04-27/

本文中的观点仅代表我个人，在决定采用这种实现方式之前，你应该先测试其性能。作者对此不提供任何保证，请自行承担风险。请自行验证。