如何使用SQL建模构建生产级工作流

2014年1月，Shopify为名为Starscream的数据科学团队构建了一个数据管道平台。那时，我们是一个较小的团队，需要一个工具来处理从临时探索到机器学习模型的所有问题。我们选择使用PySpark进行构建，以获得通用的分布式计算机平台的功能，行业标准的支持以及开发Python人才市场的能力。

快进了六年，我们的数据需求已经改变。红蜘蛛现在可运行76,000个工作，每天可写入300 TB！随着我们的成长，某些类型的工作消失了，但是其他类型的工作（例如简单的报告）变得如此司空见惯，我们每天都要做。尽管我们基于PySpark的Python工具具有强大的计算功能，但并未针对这些常见任务进行优化。如果产品经理需要按国家/地区进行新功能的简单汇总，则对其进行提取和建模并不是一项快速的任务。

我们将向您展示如何利用dbt（数据构建工具）并在其之上创建用于测试和文档编制的工具，从而转向SQL建模工作流程。这些功能共同为Shopify的数据科学家提供了强大的，可立即投入生产的工作流，可快速构建简单的管道。

当我们采访用户以了解他们在Starscream上的工作流程时，我们发现了两个问题：开发时间和思考。

开发时间包括数据科学家用来建立，运行，查看结果并进行迭代的数据模型原型所花费的时间。 PySpark平台不适用于运行简单的报告任务，通常会迫使数据科学家编写样板，并且运行时间长。尝试在不熟悉的数据上构建模型时，这导致较长的迭代周期。

思考的第二个问题更加微妙，涉及编程语言迫使您查看数据的方式。我们的许多数据科学家更喜欢SQL，而不是python，因为它的结构迫使业务指标保持一致。在采访用户时，我们发现大多数人会用SQL编写查询，然后在进行原型制作时将其转换为Python。不幸的是，查询翻译很耗时，并且无法为管道增加价值。

为了了解这些问题的普遍性，我们审核了运行的作业并调查了数据科学团队的用例。我们发现，Starscream上的PySpark作业中约有70％是完全批量查询，不需要通用计算。我们认为这是对痛苦的工作流程进行优化的机会。

我们的目标是创建一个用于报告的SQL管道，使数据科学家能够更快地创建简单的报告数据，同时仍可以进行生产。在研究了一些替代方法之后，我们认为dbt库最接近我们的需求。他们的标语“通过软件工程实践更快地部署分析代码”正是我们在工作流程中寻找的。我们选择将其与Google BigQuery配对作为我们的数据存储，并命名为系统及其工具Seamster。

我们知道，任何现成的系统都无法满足所有需求。在转向dbt时，我们必须实现自己的：

数十名数据科学家在共享存储库中建立数据模型，那么出色的用户体验将

缺省情况下，dbt在中央sources.yml中声明原始源。这很快就变成了一个非常大的文件，因为除了源名称之外，它还包括每个源的架构。它为团队在多个PR中编辑同一文件创建了巨大的瓶颈。

为了缓解瓶颈，我们利用dbt的灵活性，并创建了一个顶级“ sources”目录来用其自己的源格式的yaml文件表示每个原始源。这样，数据科学家就可以仅解析与其相关的源文档，并为sources.yml文件做出贡献，而不会互相干扰。

我们还使用dbt的“登台”概念创建了模型的基础层，以实现其将引用限制为原始数据的最佳实践。我们的基本模型充当原始资源的一对一接口。它们不会改变原始数据的来源，但是会应用重命名，重铸或其他任何与源数据收集系统相关的清理操作。

基本层用于保护用户免受原始资源变化的破坏。根据定义，原始外部资源不在Seamster的控制范围内，并且可以在任何时间点出于多种原因引入重大更改。如果发生这种情况，则只需要将修复程序应用于代表原始源的基本模型，而不是依赖于原始源的每个单独的下游模型。

我们知道，Seamster的工具改进只是Shopify更大数据平台的一部分。我们想确保我们提供的机制可以支持良好的尺寸建模实践并支持数据发现。

在dbt中，模型只是一个.sql文件。我们在Seamster中扩展了此定义，以将模型定义为包含四个文件的目录：

您可以将模型进一步组织到目录中，以指示Shopify中的数据科学团队，例如“财务”或“市场营销”。

为了支持干净的数据仓库，我们还将数据模型组织到这些粗糙的层中，以区分以下内容：

适用于应用的数据：尚未进行尺寸建模但仍经过转换和清理以供其他工具使用的数据（例如，针对机器学习算法的训练数据）

演示文稿：遵循维度建模最佳实践的可共享且可靠的数据模型，可供跨不同领域的数据科学家使用。

通过这两个更改，数据使用者可以快速了解他们可以从模型中获得的数据质量，并在出现问题时找到所有者。我们还将此元数据上游传递给其他工具，以帮助进行数据发现工作流。

dbt对“模式测试”具有本机支持，该模式编码在模型的schema.yml文件中。这些测试针对生产数据进行，以验证数据不变性，例如空值的存在或特定键的唯一性。 dbt中的此功能很好地实现了其目的，但是我们还希望使数据科学家能够为针对固定输入数据（而不是生产数据）运行的模型编写单元测试。

在固定输入上进行测试使用户可以测试可能尚未投入生产的边缘情况。在大型组织中，单个模型可能会而且会经常更新，并且会有很多合作者。单元测试使用户充满信心，他们所做的更改不会破坏现有行为或引入回归。

Seamster提供了一个基于Python的单元测试框架。数据科学家将其单元测试写入模型目录中的test_model_name.py文件中。该框架可从固定数据构建“模拟”输入模型。该框架的中心对象是“模拟”数据模型，该模型具有Pandas数据框的基本表示形式。您可以将固定数据作为csv样式的字符串，Pandas数据框或字典列表传递给模拟构造器，以指定输入数据。

输入和预期的MockModels是从静态数据构建的。实际的MockModel是由BigQuery从输入MockModels构建的。实际和预期的MockModels可以声明相等性或任何“ Great Expectations”期望。

构造函数会创建一个测试查询，其中一个通用表表达式（CTE）代表每个输入的模拟数据模型，并且对生产模型的任何引用（使用dbt的“ ref”宏标识）都被对相应CTE的引用替换。执行查询后，可以将输出与预期结果进行比较。除了相等声明外，我们还扩展了框架，以支持开源的Great Expectations库中的所有期望，以提供更详细的声明和错误消息。

该框架的主要缺点是，在给定一组输入的情况下，它需要往返于查询引擎才能构建测试数据模型。即使查询本身是轻量级的并且仅处理少量的行，这些到引擎的往返行程仍会累加。在每次本地或CI运行中运行整个测试套件的成本很高。为了解决这个问题，我们在开发和CI中引入了工具，以运行可能会因更改而中断的最少测试集。由于dbt的血统追踪支持，因此可以轻松，准确地实现。我们只需为每个更改的模型找到所有下游模型（直接模型和间接模型）并进行测试即可。

我们在Seamster CI中的目标是让数据科学家放心，他们的更改不会在下次建造仓库时引入生产错误。他们不必怀疑删除列是否会导致下游依赖项中断，或者他们是否在SQL模型定义中进行了小的打错。

为了准确实现这一目标，我们将需要在每次提交时构建和拆除整个仓库。从时间和成本角度来看这都是不可行的。相反，在每次提交时，我们都会在临时BigQuery数据集中将每个模型具体化为视图，该数据集是在验证过程开始时创建的，并在验证完成后立即删除。如果我们由于其上游模型未提供特定列而无法构建视图，或者如果SQL由于任何原因无效，则BigQuery无法构建视图并产生相关的错误消息。

目前，我们有一个包含100多个模型的仓库，此验证步骤大约需要2分钟。通过像单元测试方法一样，仅构建受更改模型影响的有向无环图（DAG）部分，可以进一步减少验证时间。

dbt的schema.yml纯粹用作元数据，并且可以包含具有无效名称或类型（data_type）的列。我们采用相同的基于视图的策略来验证模型的schema.yml文件的内容，以确保schema.yml是实际SQL模型的准确描述。

像许多大型组织一样，我们维护一个数据仓库以报告准确性是关键。为了支持我们独立的数据科学团队，Seamster通过在前面提到的各层（基础层，应用就绪层和表示层）上执行一致性规则来提供帮助。示例包括命名规则或继承规则，它们可以帮助用户在构建自己的依赖模型时对数据进行推理。

Seamster CI运行这些规则的集合，以确保不同数据科学团队之间的文档和建模实践的一致性。例如，一个仓库规则强制模式中的所有列均符合规定的命名法。另一个仓库规则规定，只有基本模型才能直接引用原始资源（通过“源”宏）。

一些仓库规则仅适用于某些图层。在表示层中，我们强制要求任何列名称都需要一个全局唯一的描述，以避免定义的差异。由于dbt中的所有内容都是YAML，因此大多数规则执行都是简单的解析。

为了确保我们做对并解决问题，我们与一些在实际模型上测试了系统的数据科学家一起运行了Seamster的多周Beta版。由于您正在阅读它，因此您现在可以猜到它运行良好！

尽管生产率衡量一直很困难，但是绝大多数用户报告说，它们是在几天而不是几周内交付模型的。此外，由于这是模型规格中内置的功能，因此增加了模型文档。

有否负面结果？当然。 dbt当前的增量支持无法提供安全一致的方法来处理延迟到达的数据，关键解决方案和重建。因此，目前尚无法使用少数需要增量语义的模型（Type 2维度或1.5B +事件区域中的模型）。不过，我们有很大的计划！

我们专注于更新该工具，以确保它是为Shopify的数据科学家量身定制的。新产品（内部和外部）的最大障碍是采用。我们知道，当用户进行简单（但不容易）的报告工作时，仍然需要确保我们的工具成为首要任务。我们正在与每个团队共度时光，以找出可以通过使用Seamster加快速度的近期工作。他们的问题和评论将成为我们面向新数据科学家的教程和文档的一部分。

在工程方面，令人振奋的下一步是超越批处理数据处理。 Apache Beam和Beam SQL提供了一个机会来考虑针对批处理和流传输用例的单个以SQL为中心的数据建模工具。

我们也是Shopify开源的忠实拥护者。根据dbt社区的需求，我们还想探索为该项目贡献我们的验证策略和单元测试框架。

如果您有兴趣从头开始构建解决方案，并希望与我们一起工作，请查看Shopify的职业页面。

来自构建和扩展Shopify的团队的故事，Shopify是领先的基于云的多渠道商业平台，为全球超过1,000,000家企业提供支持。