数据仓库构建：从分析建模到dbt实践

# 数据仓库构建：从分析建模到 dbt 实践 ## 1. 数据加载至 BigQuery 首先，我们需要将数据从 MySQL 加载到 BigQuery 中，代码示例如下： ```python # Call main function kwargs = { # BigQuery connection details 'bq_project_id': <ADD_YOUR_BQ_PROJECT_ID>, 'dataset': 'omnichannel_raw', # MySQL connection details 'mysql_host': <ADD_YOUR_HOST_INFO>, 'mysql_user': <ADD_YOUR_MYSQL_USER>, 'mysql_password': <ADD_YOUR_MYSQL_PASSWORD>, 'mysql_database': 'OMNI_MANAGEMENT' } data_pipeline_mysql_to_bq(**kwargs) ``` 执行上述代码后，原始数据就会被加载到 BigQuery 的目标数据集中，为后续转换为维度模型做准备。 ## 2. 分析数据建模 分析数据建模是一个系统的过程，包含几个关键步骤，以创建对业务流程有意义的表示。 ### 2.1 确定业务流程 在开发有效的分析数据模型时，第一步是确定组织内的业务流程。通过与关键利益相关者讨论和深度访谈，发现两个主要业务流程： - **销售跟踪**：捕获和分析通过各种渠道（如移动、移动应用、Instagram 等）产生的销售数据，以全面了解销售绩效，支持渠道优化和销售预测的决策。 - **网站性能分析**：收集各渠道的网站访问和跳出率数据，通过网络分析工具监测和衡量用户行为，识别流量来源和改进方向，以提高用户参与度。 ### 2.2 确定维度数据模型中的事实和维度 基于上述业务流程，确定了四个维度和两个事实表： | 维度/事实表 | 描述 | | --- | --- | | 渠道（dim_channels） | 表示组织运营的各种销售和营销渠道，如网站、Instagram、移动应用等，用于分析各渠道的销售绩效。 | | 产品（dim_products） | 关注组织的产品供应，分析不同产品类别的销售模式，识别畅销产品或改进领域。 | | 客户（dim_customers） | 捕获组织客户群的信息，通过分析基于客户属性的销售数据，了解客户偏好、行为和购买模式。 | | 日期（dim_date） | 用于分析销售和网站性能随时间的变化，识别趋势、季节性和时间模式。 | | 购买历史（fct_purchase_history） | 作为捕获购买交易的中心点，与渠道、产品、客户和日期维度相关联，用于详细的销售数据分析。 | | 访问历史（fct_visit_history） | 专注于网站性能分析，捕获与网站访问相关的数据，主要与渠道、客户和日期维度相关联。 | ### 2.3 确定维度的属性 为每个维度确定属性可以丰富数据模型，使其更具深度和完整性，以下是各维度的属性： - **渠道维度（dim_channels）**： - 渠道代理键（sk_channel）：为数据模型中的每个渠道提供唯一标识符。 - 渠道自然键（nk_channel_id）：表示源系统中的键，确保与外部数据源的无缝集成。 - 渠道名称属性（dsc_channel_name）：捕获每个渠道的描述性名称，便于在数据模型中识别和理解。 - **产品维度（dim_products）**： - 产品代理键（sk_product）：为数据模型中的每个产品提供唯一标识符。 - 产品自然键（nk_product_sku）：捕获源系统中的键，用于一致地链接产品相关数据。 - 产品名称属性（dsc_product_name）：为每个产品提供描述性名称，便于理解。 - 产品单价属性（mtr_unit_price）：记录每个产品的价格，便于价格分析和收入计算。 - **客户维度（dim_customers）**： - 客户代理键（sk_customer）：为数据模型中的每个客户提供唯一标识符。 - 客户自然键（nk_customer_id）：保留源系统中的键，确保与外部数据源的无缝集成。 - 其他属性：如客户姓名（dsc_name）、出生日期（dt_date_birth）、电子邮件地址（dsc_email_address）、电话号码（dsc_phone_number）和国家（dsc_country）等，用于客户细分、个性化营销和深入的客户行为和人口统计分析。 - **日期维度（dim_date）**：包括日期本身以及月、季度、年等属性，便于进行时间聚合分析和模式识别。 ### 2.4 确定业务事实的粒度 粒度指的是在维度数据模型中捕获和分析数据的详细程度。确定合适的粒度对于确保数据模型有效支持组织的分析需求和目标至关重要。 - **销售数据**：粒度确定在交易级别，即捕获单个客户的购买记录（fct_purchase_history），便于详细分析销售模式、客户行为趋势和产品级别的分析。 - **网站性能分析**：粒度选择在访问级别，即收集单个客户的访问记录和访问渠道（fct_visit_history），便于了解客户参与度、跟踪各渠道的流量模式和衡量营销活动或网站功能的效果。 此外，还可以根据需要确定更高级别的聚合粒度，如每日、每周或每月的汇总数据，以简化分析和识别更广泛的趋势。 ### 分析数据建模流程 ```mermaid graph LR A[确定业务流程] --> B[确定事实和维度] B --> C[确定维度属性] C --> D[确定业务事实粒度] ``` ## 3. 使用 dbt 创建数据仓库 完成分析数据建模后，接下来使用 dbt 开发数据仓库。数据仓库作为结构化和集成数据的中央存储库，支持组织内的强大报告、分析和决策过程。 ### 3.1 环境准备 已经设置好 BigQuery，接下来需要设置 dbt 项目并将其连接到 BigQuery 和 GitHub。 ### 3.2 开发 dbt 模型和 YAML 文件 - **命名约定**：为确保数据仓库中表、列和其他数据库对象的命名清晰一致，采用以下命名约定： | 约定 | 字段类型 | 描述 | | --- | --- | --- | | stg | 表/CTE | 暂存表或公共表表达式 | | dim | 表 | 维度表 | | fct | 表 | 事实表 | | nk | 列 | 自然键 | | sk | 列 | 代理键 | | mtr | 列 | 度量列（数值） | | dsc | 列 | 描述列（文本值） | | dt | 列 | 日期和时间列 | - **项目文件夹结构**：创建 staging 和 marts 目录，项目结构如下： ```plaintext root/ ├─ models/ │ ├─ staging/ │ ├─ marts/ ├─ dbt_project.yml ``` - **创建暂存 YAML 文件**：创建 `_omnichannel_raw_sources.yml` 文件，内容如下： ```yaml version: 2 sources: - name: omnichannel database: analytics-engineering-book schema: omnichannel_raw ```