sql-server - 事实表分区: how to handle updates in ETL?

Question

我们正在尝试为包含大约 400M 行的数据仓库事实表实现表分区。我们的 ETL 从上次加载的源系统中提取 50 天后的数据(新行、修改行，基于源系统时间戳)。因此，在每个 ETL 周期中，都会有新行进入，同时也会有旧行更新事实表中的相应行。这个想法是将新行插入到事实表中并更新修改的行。

分区列将是日期(int，YYYYMMDD)，我们正在考虑按月分区。

就我而言，表分区可以通过 fast partition switch operations 简化我们的插入操作。我们可以分割最近的分区来创建一个新的空闲分区，将新行加载到临时表中(使用日期约束，例如最近一个月)，然后使用分区切换操作将新行“移动”到分区事实表中。但是我们如何处理修改后的行并更新事实表中的相应行呢？这些行可以包含前一个月的数据。分区开关在这里有帮助吗？通常，INSERT 和 UPDATE 行由 ETL 工具(例如我们案例中的 SSIS)或 MERGE 语句确定。在这种情况下分区是如何工作的？

Answer 1

我会重新审视设计，并尝试找出是否有解决更新的方法。以下是更新事实表的一些含义:

性能:更新是完整记录的事务。大事实表也有大量数据需要读取和写入。

多维数据集:更新事实表需要重新处理受影响的分区。随着事实表不断增长，多维数据集处理时间也会持续增长。

预算:快速存储是昂贵的。更新大型事实表将需要大量快速读取和写入。

纯粹主义理论:除非初始值有误(即用户输入 $15,000 而不是 $1,500)，否则不应更改事实表。任何非错误情况都会改变最初记录的交易。

发生了什么变化？变化的碎片真的是维度的属性吗？如果是这样，是否可以将它们移动到某个维度并通过“缓慢变化的维度”类型任务来处理更改？

另一种可能性，可以通过抵消交易来实现吗？示例:

初始发票金额为 10.00 美元。会计人员随后添加了 1.25 美元的税费，然后向客户收取了 11.25 美元的费用。不要将值更新为 $11.25，而是插入 $1.25 的记录。发票的总金额仍为 11.25 美元，您可以执行最小记录插入而不是完全记录更新来完成。

更新事实表不仅在理论上是一个坏主意，而且随着事实表的增长，它会变得非常昂贵且不可扩展。您将读取和写入更多数据，从而需要存储子系统提供更多 IOPS。当您准备好进行分析时，多维数据集处理将引发更多问题。

您还必须不断向管理层证明为什么数据仓库需要如此多的 IOPS。需要所有这些 IOPS 来满足不断变化的“事实”表是否具有商业值(value)/理由？

如果您找不到解决事实表更新的方法，至少要建立一个将数据确定为只读的截止点。否则，您将永远无法扩展。