原文：
towardsdatascience.com/data-warehouse-design-patterns-d7c1c140c18b
最近，我需要一个数据仓库工具来处理我的新数据项目。这个故事讲述了我是如何从头开始构建它并组织其中的所有内容的。设计数据平台不是一个简单任务，并且现代数据仓库解决方案通常是其架构的核心。它提供了强大的数据治理功能、使用 ANSI SQL 简化数据查询以及增强的数据建模能力。由于数据源数量众多和所需转换的复杂性，组织内部的所有内容，即数据环境、测试、命名约定、数据库、模式和表可能是一项具有挑战性的任务。这个故事可能对希望学习高级数据仓库技术的初学者和中级用户有所帮助。与经验丰富的数据从业者一起，我想讨论他们对数据仓库设计的看法以及他们通常如何组织内部的所有内容。
设计数据平台
作为一名数据工程师，我每天都在设计数据管道。这是现代数据平台所包含的内容，并且它必须具有成本效益、可扩展性，并且长期易于维护。为数据密集型应用程序设计管道始终具有挑战性，而现代数据仓库（DWH）旨在简化并增强这一过程，提供轻松访问数据、更好的数据治理能力和易于维护的数据转换，这些转换对于分析和商业智能至关重要。
当用户希望自行访问和探索数据，并且有报告的业务需求时，在我们的数据平台中使用 DWH 总是有意义的。现代数据仓库简化了数据访问和数据治理，我相信这是任何现代数据平台的一个基本组成部分。我之前在这里提出了这个讨论[1]：
我选择使用数据湖作为永久着陆区，并在实际将其加载到数据仓库之前对数据进行预置。云服务提供商提供了易于管理的云存储解决方案。我决定将其用作我部署的所有数据管道的单一事实来源。
云存储充当一个代理多边形，我在其中首先放置原始数据，并在存档中保持其未更改状态。
数据湖屋架构。图片由作者提供。
为什么选择数据仓库作为您的数据平台？
数据仓库解决方案通常作为现代数据堆栈的基础。数据可以以原始格式或应用所需的转换（ETL）后摄入，我们可以使用 SQL 访问它。市场上有很多工具允许你这样做，例如 Fivetran、Stitch、Airbyte 等。如果我们知道如何编码，我们可以部署定制服务来提取和摄入数据。我之前在一篇教程中写过它[2]。
在数据仓库内部，数据通过现代数据转换工具如 DBT 和 Dataform 使用 SQL 进行转换。这些工具使用类似 SQL 的模板，并根据数据环境应用数据和模式后缀。
数据环境在实时和预发布之间的划分是数据仓库设计中最重要的任务之一。
最终，现代数据仓库是一个强大的工具，我们可以有效地管理数据，并且从一开始就正确设置一切对于确保一切按预期进行至关重要。我们希望正确声明我们的数据库、模式和表。应用相关的命名约定可能有助于解决未来许多潜在问题。
现代数据仓库工具如 Snowflake、Redshift、BigQuery 等通常由不同的数据库组成，每个数据库都有其独特的功能和目的。在数据库内部，我们会使用模式，这些模式由表和视图组成，以进一步分类我们的数据集。
模式分类帮助用户识别他们需要的数据集。现在他们知道在哪里可以找到确切的数据。
根据我们的数据平台类型以及我们在数据仓库内部应用的转换，为原始或源数据创建一个专门的数据库是有意义的。这将是我们数据仓库的第一个组件，数据在这里以未转换的“原样”着陆。然后我们可以使用它来丰富、净化数据，并应用基本的字段类型转换，将其提升到生产状态。这里的“生产”是指经过必要的数据操作后，数据可以被认为是纯净无瑕且达到生产级别的状态，这样我们就可以将其展示给某人，例如 BI 团队或其他任何业务利益相关者。这些“PROD”级别的视图和表可以轻松连接到任何 BI 解决方案，在那里它们可以被 BI 开发者建模。
最好的做法是将数据仓库中的数据仅保留一部分在 RAW 数据库中，并使用它来更新我们的“BASE”或“PROD”数据库表。我们可以为此定义数据过期策略，并使用 SQL MERGE 在生产中更新数据。考虑以下 MERGE 示例。它解释了数据集将如何更新。
-- Creating Tables
CREATE OR REPLACE TABLE target_table (
customer_id NUMBER,
is_active BOOLEAN,
updated_date DATE
)
;
CREATE OR REPLACE TABLE source_table (
customer_id NUMBER,
is_active BOOLEAN
)
;
-- Inserting test values
INSERT INTO target_table VALUES
(1, TRUE, '2024-01-01'),
(2, TRUE, '2024-02-01'),
(3, FALSE, '2024-03-01')
;
INSERT INTO source_table VALUES
(1, FALSE), -- update record
(2, TRUE), -- update record
(4, TRUE) -- new record
;
-- UPSERT STARTS HERE
-- Insert a missing row and update the existing ones
MERGE INTO target_table tgt
USING source_table src
ON tgt.customer_id = src.customer_id
WHEN MATCHED THEN
UPDATE SET
tgt.is_active = src.is_active,
tgt.updated_date = '2024-04-01'::DATE
WHEN NOT MATCHED THEN
INSERT
(customer_id, is_active, updated_date)
VALUES
(src.customer_id, src.is_active, '2024-04-01'::DATE)
;
-- Output
| Number of Rows Inserted | Number of Rows Updated |
|-------------------------|------------------------|
| 1 | 2 |
在这个故事中有一个使用 MERGE 的好例子：
我还推荐创建一个专门的数据库用于分析。这将是一个用于临时分析和物化查询及视图的地方。
的确，通常创建一个单独的数据库来存储较长的分析 SQL 查询和数据探索的结果是有意义的。
此数据库也可以连接到 BI 解决方案。分析师经常编写查询来探索数据并生成一次性报告，这些报告不需要每日更新（或任何其他频率）。因此，我们不需要将这些查询保留在“PROD”或“BASE”数据库中。
因此，DWH 数据库拆分的最终图示如下：
DWH 数据库级别。图片由作者提供。
它看起来很简单，但功能强大。这个概念图为每个 DWH 数据库提供了一个简单的解释。数据库可以被视为一组 DWH 实例和对象，即模式、视图、表、UDFs 等。下一步是将它拆分到不同的数据环境中。
在分析数据库中，数据分析师可以创建他们需要的任何表格来保存查询结果。此数据库可以连接到任何 BI 工具或仪表板。
组织数据环境
理想情况下，我们希望有一个环境用于我们阶段化、模拟和开发数据转换，以及一个生产环境，其中经过批准和测试的数据转换按计划运行，例如每日、每小时等。因此，最终我们将拥有 ANALYTICS_DEV 和 ANALYTICS_LIVE 数据库。同样的方法也可以应用于 RAW 和 BASE 区域。我们可以设置一个 DEV 数据管道，将数据输入到我们的 RAW_DEV 表中的一个，或者我们可以手动使用 SQL INSERT 来模拟数据。
现代数据转换工具如 DBT 和 Dataform 可以提供此功能。我们可以告诉我们的数据转换引擎为特定的 DWH 项目使用特定的 git 分支，或者为单个项目中的数据库和模式应用额外的模式后缀（_DEV, _LIVE/_PROD）。我在我的 Snowflake DWH 中就是这样做的。
根据环境更改源数据库的 DBT 配置。图片由作者提供。
DEV 环境中的数据库。图片由作者提供。
让我们假设我们将数据转换脚本提升到“PROD”，通过将我们的 git 开发分支合并到主分支中。DBT 工具将通过在数据库名称后添加“_PROD”后缀来编译我们的 SQL 模板。
PROD 环境中的数据库。图片由作者提供。
我正在构建一个 Snowflake 数据仓库，我的数据库和模式将如下所示：
DATABASE_NAME SCHEMA_NAME
-------------------------------
RAW_DEV SERVER_DB_1 -- mocked data
RAW_DEV SERVER_DB_2 -- mocked data
RAW_DEV EVENTS -- mocked data
RAW_PROD SERVER_DB_1 -- real production data from pipelines
RAW_PROD SERVER_DB_2 -- real production data from pipelines
RAW_PROD EVENTS -- real production data from pipelines
...
BASE_PROD EVENTS -- enriched data
BASE_DEV EVENTS -- enriched data
...
ANALYTICS_PROD REPORTING -- materialised queries and aggregates
ANALYTICS_DEV REPORTING
ANALYTICS_PROD AD_HOC -- ad-hoc queries and views
在 Snowflake 中，我们可以使用此查询列出我们账户中的所有模式：
show schemas in account
;
select
"database_name" as DATABASE_NAME
,"name" as SCHEMA_NAME
from table(result_scan(last_query_id()))
where SCHEMA_NAME not in ('INFORMATION_SCHEMA') -- optional filter(s)
;
考虑以下另一个例子。在这个故事中，我使用了 Dataform（现在是 Google Cloud Platform 的一部分）[3]来创建数据环境，并将它们在两个 GCP 项目中分割开来——一个用于开发（DEV），一个用于生产（PROD）。
为我们的数据环境之一创建一个专用项目可能是个好主意，因为它使得管理和删除所有相关资源（如果需要的话）变得更容易。
Google BigQuery 中的预发布和生产环境。图片由作者提供。
在 Dataform 配置文件中，我们可以指定用于预发布目的的新数据库：
预发布环境中的 defaultDatabase。图片由作者提供。
此外，我们可能还想添加一个用于自动化测试的数据库，即 PROD_TEST。我们的 CI 管道可以在其中测试数据转换逻辑。我之前在这篇教程中写过[4]。
在 Dataform 中对具有依赖关系的 SQL 脚本进行单元测试
虽然这个例子可能看起来有点复杂，但它遵循了使用模板化 SQL 文件进行测试的想法，模拟测试数据，以便在运行添加模式后缀的转换 SQL 后与预期结果进行比较。
我从传统的单元测试中借用了这个逻辑，并且它对我很有效。如果你熟悉任何可以部署为简单微服务并添加到我们的 CI/CD 管道中的编码，那么可以这样做[5]。
警报和负载监控
我们可能想要对我们的数据仓库做一些事情，以确保一切按预期工作：
-
负载监控 – 检查是否有任何加载失败。这可能是一个格式错误的失败文件或不符合表模式的流数据记录。
-
数据质量检查 – 来自外部提供者的任何缺失数据集，数据格式不正确，即 NULL、格式等。
-
成本监控 – 识别任何运行时间较长的查询和成本
-
使用监控 – 如果有人试图做某事时发出警报。
有多种方法可以做到这一点。例如，我们可以在 Snowflake 中使用 SQL 创建 ALERTS，如下所示：
-- create email integration
CREATE NOTIFICATION INTEGRATION snowflake_notification_email_int
TYPE=EMAIL
ENABLED=TRUE
-- ALLOWED_RECIPIENTS=('[[email protected]](/cdn-cgi/l/email-protection)');
;
-- try and send
CALL SYSTEM$SEND_EMAIL(
'snowflake_notification_email_int',
'[[email protected]](/cdn-cgi/l/email-protection)',
'Email Alert: Task A has finished.',
'Currency conversion alert. Potential data issues. nStart Time: 10:10:32nEnd Time: 12:15:45n'
)
;
-- create and schedule alert
CREATE OR REPLACE ALERT currency_conversion_live_alert
WAREHOUSE = test_wh
SCHEDULE = 'USING CRON 0 6 * * * UTC'
IF ( exists (
select * from (
SELECT
count(*) = 0 as alert
FROM CURRENCY_CONVERSION
where
date(date) = current_date()
)
where alert = true
)
)
THEN
SET alert_name= '(Missing data)';
CALL SYSTEM$SEND_EMAIL(
'snowflake_notification_email_int',
'[[email protected]](/cdn-cgi/l/email-protection)',
'Email Alert: Currency conversion.',
$alert_name
);
ALTER ALERT currency_conversion_live_alert RESUME;
EXECUTE ALERT currency_conversion_live_alert;
show alerts;
这将导致有关潜在数据中断的电子邮件信号。
电子邮件通知示例。图片由作者提供。
这是一个简化的例子。类似的事情可以在 Google Cloud BigQuery 和其他数据仓库解决方案中完成。我之前在这里写过[6]。
理想场景包括将此 ALERT 数据发送到专门的监控服务，例如 Datadog 等。这可以通过编程方式完成。它可以轻松集成到任何其他服务，如 PagerDuty 等，以实际将工单自动分配给相关用户。
考虑以下示例。它将使用Snowpark库创建这样的管道，我们可以使用 AWS Lambda 函数来安排数据检查。或者，我们可以将此功能添加到我们创建的任何潜在数据服务中，作为 API 并定期运行这些检查。
import os
import requests
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives.asymmetric import dsa
from cryptography.hazmat.primitives import serialization
import snowflake.snowpark as snowpark
from snowflake.snowpark.session import Session
def lambda_handler(event, context):
try:
with open("./rsa_key.p8", "rb") as key:
p_key= serialization.load_pem_private_key(
key.read(),
password=os.environ['PRIVATE_KEY_PASSPHRASE'].encode(),
backend=default_backend()
)
pkb = p_key.private_bytes(
encoding=serialization.Encoding.DER,
format=serialization.PrivateFormat.PKCS8,
encryption_algorithm=serialization.NoEncryption())
connection_parameters = {
"user":'USERNAME',
"private_key":pkb, # Follow the Snowflake instructions to create a user with key-pair authentication
"account":'snowflake-account-name',
"session_parameters": {
'QUERY_TAG': 'Alerting',
},
"warehouse":"TEST_WH",
"database":"TEST",
"schema":"TEST"
}
new_session = Session.builder.configs(connection_parameters).create()
# Get alerts from Snowflake as a dataframe
alerts_df = new_session.sql("select * from TEST_ALERTS_VIEW")
new_session.close()
# POST alert data to a data monitpring service
r = requests.post('https://some.monitoring.service.org / post', data ={'alert': alerts_df['alert']})
except Exception as e:
print(e)
message = 'Successfully submitted {}.'.format('alerts')
return {
'statusCode': 200,
'body': { 'lambdaResult': message }
}
此场景需要一个具有密钥对身份验证的 Snowflake 用户[7]。请按照 Snowflake 说明创建一个。
结论
我总是以规划和设计会议开始一个新的数据仓库项目。首先一定要考虑数据库和模式，因为后期更改它们可能会很困难，因为它们最终将与 BI 工具和其他消费者连接。为源表和模式应用相关名称将帮助用户更快地找到数据。以正确的方式组织数据环境有助于自动化测试和 CI 管道，这将确保您的数据转换脚本按照业务需求中定义的数据转换逻辑正确运行。有许多可能的方法可以部署数据平台资源，将数据输入我们的数据仓库解决方案，将它们反映在表描述中是个好主意。这将使所有利益相关者都能看到，确保数据平台上的透明度。大多数监控 DBA 任务都可以轻松自动化，这将在长期内节省大量时间。这绝对是我们可能想要考虑的事情。我强烈建议使用数据建模或数据转换工具来记录一切。在这种情况下，我们可以将所有内容保存在 Git 中，并且可以在我们的模式和表定义中显示。
感谢阅读。我期待着了解您对此的看法。
推荐阅读
[1] towardsdatascience.com/data-platform-architecture-types-f255ac6e0b7
[2] towardsdatascience.com/building-a-batch-data-pipeline-with-athena-and-mysql-7e60575ff39c
[3] towardsdatascience.com/easy-way-to-create-live-and-staging-environments-for-your-data-e4f03eb73365
[5] towardsdatascience.com/test-data-pipelines-the-fun-and-easy-way-d0f974a93a59
[6] medium.com/towards-data-science/automated-emails-and-data-quality-checks-for-your-data-1de86ed47cf0
扫一扫
4891
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
