Spark & Lakehouse Study Guide

DBT is a transformation engineering framework, not a database, storage system, ingestion tool, or compute engine. It organizes SQL transformations as version-controlled models, resolves ref-based dependencies, compiles Jinja and SQL, calls the connected platform to execute them, and attaches tests, documentation, lineage, environments, and CI workflows to the models.

DBT 是数据转换工程框架，不是数据库、存储系统、采集工具或计算引擎。它把 SQL 转换组织成可版本管理的 Model，用 ref 解析依赖，编译 Jinja 与 SQL，调用连接的数据平台执行，并把测试、文档、血缘、环境和 CI 流程绑定到模型上。

Your warehouse is already a complete payment-data platform. Apache InLong captures MySQL changes, MQ buffers events, ODS preserves source truth, DWD and DIM standardize detailed facts and dimensions, DM builds reusable Merchant, Store, and Order subjects, ADS and BI publish outputs, Spark or Hive and TDW perform computation, and Airflow manages the end-to-end DAG, SLA, retries, and alerts.

你现在的方案已经是一套完整的支付数据平台：Apache InLong 捕获 MySQL 变化，MQ 缓冲事件，ODS 保留源端事实，DWD 与 DIM 统一明细和维度，DM 构建 Merchant、Store、Order 可复用主题，ADS 与 BI 输出结果；Spark 或 Hive/TDW 负责计算，Airflow 管理端到端 DAG、SLA、重试和告警。

DBT is not automatically a good fit just because this is a warehouse. Your internal Hive or TDW environment may not have a mature supported adapter; many transformations are Spark jobs rather than portable warehouse SQL; Airflow already owns orchestration; and your layered modeling, governance, SLA, reconciliation, security, and cost controls already exist. Migrating mature payment pipelines would add adapter, rewrite, validation, and dual-operation cost without replacing ingestion, Spark tuning, security, or platform operations.

不能因为这是数仓就默认应该使用 DBT。你的内部 Hive/TDW 环境可能没有成熟、可靠的 Adapter；很多转换是 Spark Job，而不是可直接迁移的标准数仓 SQL；Airflow 已经承担调度；分层建模、治理、SLA、对账、安全和成本控制也已经存在。迁移成熟的支付链路会增加适配、重写、验证和双轨运行成本，却无法替代采集、Spark 调优、安全或平台运维。

A pilot makes sense only when the target platform has a reliable adapter and a real pain exists: scattered SQL, unclear lineage, detached tests and documentation, weak code review, or difficult environment isolation. Start with one non-critical DM or ADS domain on a supported engine, keep InLong, MQ, Spark, Airflow, and governance unchanged, then measure maintainability, failure rate, delivery speed, and operating cost before expanding.

只有当目标平台具备可靠 Adapter，并且确实存在 SQL 分散、血缘不清、测试文档脱节、代码评审薄弱或环境隔离困难等痛点时，才值得试点。可以在受支持引擎上选择一个非关键 DM 或 ADS 主题域；InLong、MQ、Spark、Airflow 和治理体系保持不变，再通过维护性、失败率、交付速度和运维成本判断是否扩大。

DBT is a transformation framework that turns SELECT statements into managed warehouse models. It resolves dependencies, compiles SQL, asks the data platform to execute it, and records tests, documentation, and lineage.

DBT 是管理数据转换工程的框架：把 SELECT SQL 组织成模型，解析依赖，编译 SQL，调用数据平台执行，并记录测试、文档和血缘。

Without a framework, SQL often lives in scattered scripts, dependencies are implicit, data tests run separately, and documentation becomes stale. DBT puts model code, dependency declarations, tests, and documentation in one version-controlled project.

没有统一框架时，SQL 往往散落在脚本中，依赖靠人记，测试单独维护，文档很快过期。DBT 把模型代码、依赖声明、测试和文档放进同一个可版本管理的项目。

DBT does not ingest MySQL binlogs, replace MQ, store data, or provide distributed compute by itself. It starts after source data is already available in a supported data platform. Airflow can still schedule the whole pipeline.

DBT 不负责读取 MySQL Binlog，不替代 MQ，不自己存数据，也不独立提供分布式计算。它通常从原始数据已经进入受支持的数据平台后开始工作；Airflow 仍可负责整条链路调度。

DBT Core is the open-source command-line engine you install and run yourself. DBT Cloud is a managed service built around the same project concepts, adding a browser IDE, hosted job scheduling, managed credentials, collaboration, and platform features. Learn the shared project language first; choose the operating model later.

DBT Core 是需要自行安装和运行的开源命令行引擎；DBT Cloud 是围绕同一套项目概念提供的托管服务，增加浏览器 IDE、托管任务调度、凭据管理、协作与平台能力。先掌握两者共用的项目语言，再决定采用哪种运行方式。

Models contain transformation SQL. YAML files describe sources, tests, and documentation. Macros package reusable Jinja logic. Project configuration controls folders, materializations, schemas, and environments.

Models 保存转换 SQL；YAML 描述 Source、测试和文档；Macros 封装可复用的 Jinja 逻辑；项目配置负责目录、物化方式、Schema 和环境。

DBT reads the project, resolves ref and source dependencies, renders Jinja into SQL, orders models as a DAG, and submits the compiled statements to the connected data platform. The adapter translates DBT operations for that platform.

DBT 读取项目，解析 ref 与 source 依赖，把 Jinja 渲染成 SQL，按 DAG 排序模型，再把编译后的语句提交给连接的数据平台；Adapter 负责适配具体平台。

DBT is the control and engineering layer, not the compute engine. Snowflake, BigQuery, Databricks, Redshift, Postgres, or another supported platform stores data and executes SQL. A slow query still needs platform-level SQL and storage optimization.

DBT 是控制与工程化管理层，不是计算引擎。Snowflake、BigQuery、Databricks、Redshift、Postgres 或其他受支持的平台负责存储和执行 SQL；慢查询仍要在平台层优化 SQL、存储和资源。

Developers build selected models in a personal schema, CI validates only the changed dependency graph, and production runs approved code on a schedule. Separate targets and credentials prevent practice work from overwriting production data.

开发者在个人 Schema 中构建选定模型；CI 验证发生变化的依赖图；生产环境按计划运行已审批代码。不同 Target 和凭据可防止练习或开发数据覆盖生产数据。

dbt_project.yml describes the project, while profiles.yml usually supplies the connection profile outside the project repository. A target selects an environment such as dev or prod, and the adapter translates DBT operations into the dialect and connection behavior of the chosen data platform. Keep passwords out of Git and inject secrets through environment variables or a managed credential store.

dbt_project.yml 描述项目本身；profiles.yml 通常在项目仓库之外提供连接配置。Target 用来选择 dev、prod 等环境；Adapter 把 DBT 操作翻译成目标数据平台的 SQL 方言与连接行为。密码不要提交到 Git，应通过环境变量或托管凭据系统注入。

collection_dbt:
  target: dev
  outputs:
    dev:
      type: <adapter>
      schema: dev_alice
      password: "{{ env_var('DBT_PASSWORD') }}

A DBT model is usually one SQL file containing a SELECT statement. The file name becomes the model name. DBT turns that SELECT into a view, table, incremental table, or another supported object.

一个 DBT Model 通常就是一个包含 SELECT 的 SQL 文件；文件名就是模型名。DBT 会把这条 SELECT 创建成 View、Table、Incremental Table 或其他受支持对象。

source() points to an external table that DBT does not build, such as your ODS settlement table. ref() points to another DBT-managed resource. ref() also creates a dependency edge, so DBT knows the build order and resolves the correct environment schema.

source() 指向 DBT 不负责构建的外部表，例如你的 ODS 结算表；ref() 指向另一个由 DBT 管理的资源。ref() 还会创建依赖边，因此 DBT 知道构建顺序，并能解析开发或生产环境的正确 Schema。

DBT parses project files, compiles Jinja into executable SQL, orders models through the dependency graph, executes them on the data platform, and then evaluates tests. A failure should be classified as parse, compile, run, or test before troubleshooting.

DBT 先解析项目文件，再把 Jinja 编译成可执行 SQL，根据依赖图排序模型，交给数据平台执行，最后运行测试。排错时应先判断失败属于 Parse、Compile、Run 还是 Test。

Materialization defines how a model is persisted. The same SELECT can become a view for light logic, a table for expensive reusable results, or an incremental model that processes only new and changed rows.

Materialization 决定模型怎样落地。同一条 SELECT 可以生成轻量 View、昂贵但可复用的 Table，或只处理新增和变化记录的 Incremental Model。

Tests express assumptions such as primary-key uniqueness, required fields, accepted status values, and valid relationships. Documentation lives beside the model, so the generated catalog and lineage are tied to the code version.

测试把数据假设写成代码，例如主键唯一、字段非空、状态值合法、外键关系有效。文档与模型放在一起，因此生成的数据目录和血缘能对应具体代码版本。

Organize by transformation stage and business domain. Staging stays close to source systems; intermediate models hold reusable joins or reshaping; marts expose stable business entities and metrics.

目录同时体现转换阶段和业务域。Staging 贴近源系统；Intermediate 放可复用 Join 或重塑逻辑；Marts 输出稳定的业务实体和指标。

collection_dbt/
├── dbt_project.yml
├── models/
│   ├── staging/collection/
│   │   ├── _collection__sources.yml
│   │   └── stg_collection__settlements.sql
│   ├── intermediate/
│   │   └── int_collection__settlement_enriched.sql
│   └── marts/
│       ├── mart_merchant_daily.sql
│       └── marts.yml
├── macros/
├── tests/
└── seeds/

Declare the existing ODS table in YAML. This does not copy the data. It gives the raw table a stable project name and a place for freshness, descriptions, and source-level tests.

在 YAML 中声明已有 ODS 表，这一步不会复制数据；它只是为原始表建立稳定的项目名称，并承载新鲜度、字段说明和源表测试。

version: 2

sources:
  - name: ods_collection
    schema: ods
    tables:
      - name: settlement_event
        description: "Raw Amazon settlement events / Amazon 原始结算事件"
        columns:
          - name: settlement_id
            tests: [not_null, unique]

Keep staging focused: rename fields, cast types, normalize codes, and perform source-specific cleanup. Do not mix final KPI aggregation into this layer.

Staging 应保持专注：重命名字段、统一类型、规范编码并处理源系统特有的清洗；不要在这一层混入最终 KPI 聚合。

-- models/staging/collection/stg_collection__settlements.sql
with source as (
    select *
    from {{ source('ods_collection', 'settlement_event') }}
),

renamed as (
    select
        cast(settlement_id as string)  as settlement_id,
        cast(merchant_id as string)    as merchant_id,
        cast(store_id as string)       as store_id,
        upper(currency)                as currency_code,
        cast(amount as decimal(18, 2)) as settlement_amount,
        cast(event_time as timestamp)  as settlement_at
    from source
)

select * from renamed

Join reusable business context in intermediate models, then aggregate at an explicitly declared mart grain. Here the grain is one merchant per day, matching the daily Merchant subject in your existing DM design.

先在 Intermediate 中连接可复用业务信息，再按明确粒度聚合到 Mart。这里的粒度是“每天每个 Merchant 一行”，对应你原 DM 中的日级 Merchant 主题。

-- models/marts/mart_merchant_daily.sql
{{ config(materialized='incremental', unique_key=['dt','merchant_id']) }}

select
    cast(settlement_at as date) as dt,
    merchant_id,
    count(*)                    as settlement_count,
    sum(settlement_amount)      as settlement_amount
from {{ ref('int_collection__settlement_enriched') }}

{% if is_incremental() %}
where settlement_at >= dateadd(day, -2, current_date)
{% endif %}

group by 1, 2

Your existing checks—zero records, primary-key uniqueness, valid status values, and SLA alerts—already express data contracts. DBT lets you attach many of those assertions directly to sources and models, then execute them with the build.

你原来的零记录检查、主键唯一性、状态值合法性和 SLA 告警，本质上已经是数据契约。DBT 可以把其中很多断言直接绑定到 Source 和 Model，并随构建一起执行。

version: 2

models:
  - name: mart_merchant_daily
    description: "One row per merchant per day / 每天每个商户一行"
    columns:
      - name: merchant_id
        tests: [not_null]
      - name: settlement_amount
        tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0"

      # Example for a status column:
      # - name: settlement_status
      #   tests:
      #     - accepted_values:
      #         values: ['PENDING', 'PAID', 'FAILED']

    # Composite-grain uniqueness can be implemented with a generic test
    # or a surrogate key such as dt + merchant_id.

Test source assumptions at the source or staging boundary, reusable business rules in intermediate models, and final grain and KPI guarantees in marts. Do not repeat every test on every layer; test the contract each layer promises.

源系统假设在 Source 或 Staging 边界测试；可复用业务规则在 Intermediate 测试；最终粒度和 KPI 保证在 Marts 测试。不要每层机械重复所有测试，而要检查该层承诺的契约。

Descriptions in YAML, ref-based dependencies, and generated artifacts make model ownership and impact analysis easier. A change to settlement_amount can be traced through intermediate models to Merchant marts and dashboards.

YAML 描述、ref 依赖和生成的元数据让模型责任和影响分析更清楚。settlement_amount 的变化可以沿 Intermediate、Merchant Mart 一直追踪到 Dashboard。

A seed loads a small version-controlled CSV such as a country-code mapping. A snapshot records how mutable source rows change over time, often as slowly changing history. An exposure declares a downstream dashboard, report, application, or ML use case so ownership and lineage extend beyond the final mart. These resources solve different problems and are not substitutes for models.

Seed 把国家代码映射等小型 CSV 作为版本化数据加载；Snapshot 记录可变源记录随时间的变化，常用于缓慢变化历史；Exposure 声明下游 Dashboard、报表、应用或机器学习用途，让责任人和血缘延伸到最终 Mart 之外。三者解决不同问题，都不能替代 Model。

DBT provides view, table, incremental, ephemeral, and materialized_view materializations. Adapter support varies, so the data platform determines which behaviors are available.

DBT 提供 View、Table、Incremental、Ephemeral 和 Materialized View 五种内置物化方式；不同 Adapter 的支持情况不同，最终能力由数据平台决定。

A safe incremental model needs a stable unique key, a strategy for inserts and updates, a lookback window for late records, deduplication of source versions, and reconciliation against trusted totals. A full refresh is required when historical logic changes beyond the incremental window.

安全的增量模型需要稳定唯一键、插入与更新策略、迟到数据回看窗口、源端版本去重以及可信总量对账。如果历史逻辑发生变化并超出增量窗口，就需要 Full Refresh。

A practical starting point is staging views, intermediate views or ephemeral helpers when they are small, tables for expensive reusable dimensions and marts, and incremental models for large settlement or transaction facts. Measure before optimizing.

实用起点是：Staging 用 View；较小的 Intermediate 用 View 或 Ephemeral；昂贵且高复用的维度和 Mart 用 Table；大型结算或交易事实表用 Incremental。先测量，再优化。

Work in a personal development schema, compile the selected model, build only the affected graph, inspect outputs and failing rows, open a pull request, run CI in an isolated environment, and deploy approved code to production.

在个人开发 Schema 中工作；编译目标模型；只构建受影响依赖图；检查输出与失败行；提交 Pull Request；在隔离环境运行 CI；审批后部署到生产。

Start with debug, compile, build, show, and selectors. Prefer dbt build for a selected graph because it runs the selected resources in dependency order and includes tests; remember that source freshness is normally a separate check.

第一天先掌握 debug、compile、build、show 和选择器。对选定依赖图优先使用 dbt build，因为它会按依赖顺序构建资源并执行测试；Source Freshness 通常需要单独检查。

# Validate connection and project configuration
dbt debug

# See compiled SQL without creating models
dbt compile --select mart_merchant_daily

# Create selected models only (does not run tests)
dbt run --select +mart_merchant_daily

# Run tests only
dbt test --select mart_merchant_daily

# Build a mart plus all upstream dependencies
dbt build --select +mart_merchant_daily

# Build everything downstream from staging
dbt build --select staging+

# Preview rows during development
dbt show --select mart_merchant_daily

# Generate and open the documentation site
dbt docs generate
dbt docs serve

# Check declared source freshness separately
dbt source freshness

# Rebuild complete history only when required
dbt build --full-refresh --select mart_merchant_daily

Airflow can continue coordinating CDC completion, upstream readiness, DBT builds, source freshness, exports, and alerts. DBT owns transformation models and their internal dependency graph; Airflow owns the cross-system workflow and operational schedule.

Airflow 仍可协调 CDC 完成、上游就绪、DBT Build、Source Freshness、导出和告警。DBT 管转换模型及内部依赖图；Airflow 管跨系统工作流和运行计划。

Check YAML indentation, project configuration, model names, source and ref spelling, Jinja braces, and the compiled SQL under target. dbt debug verifies connectivity and configuration; it does not prove every model is correct.

检查 YAML 缩进、项目配置、模型名、source/ref 拼写、Jinja 括号，以及 target 目录中的编译 SQL。dbt debug 验证连接和配置，但不能证明每个模型都正确。

Open the compiled SQL and run it against the target platform. Check permissions, missing relations, unsupported SQL functions, schema changes, query plans, data volume, and warehouse or Spark runtime logs.

打开编译后的 SQL，并在目标平台验证。检查权限、缺失表、平台不支持的函数、Schema 变化、执行计划、数据量，以及数仓或 Spark 运行日志。

Inspect the failing rows. Confirm the promised grain, duplicate keys, nulls, accepted values, join cardinality, and source quality. Do not disable a test simply because it blocks deployment; decide whether the data or the contract is wrong.

查看具体失败行，确认承诺的粒度、重复键、空值、合法值、Join 基数和源数据质量。不要因为测试阻塞部署就直接关闭它；应判断是数据错误，还是契约定义错误。

A DBT job can finish successfully while the business SLA still fails because ingestion was late, an upstream source was stale, or publication was delayed. Monitor the end-to-end path, not only the DBT command status.

DBT Job 即使成功，业务 SLA 仍可能失败，因为采集迟到、上游 Source 过期或发布延迟。监控必须覆盖端到端链路，不能只看 DBT 命令是否成功。

Learn what DBT is, where it starts in a data pipeline, how source and ref create a graph, and how staging, intermediate, marts, tests, and documentation fit together.

理解 DBT 是什么、在数据链路哪里开始、source/ref 如何形成依赖图，以及 staging、intermediate、marts、测试和文档怎样组合成完整项目。

Create the project tree, declare the ODS settlement source, build staging and intermediate models, create the daily Merchant mart, add tests, and run dbt build on the selected dependency graph.

创建项目目录，声明 ODS 结算 Source，构建 Staging 和 Intermediate，创建日级 Merchant Mart，添加测试，再对选定依赖图执行 dbt build。

Explain the difference between DBT and a compute engine, source and ref, run and build, and table and incremental. Then explain one model from source declaration through testing and production deployment.

脱稿说明 DBT 与计算引擎、source 与 ref、run 与 build、table 与 incremental 的区别，再讲清一个模型如何从 Source 声明走到测试和生产部署。

I use DBT to manage warehouse transformations as version-controlled models. Sources declare upstream tables, staging models standardize raw fields, intermediate models package reusable logic, and marts expose business-ready datasets. ref builds the dependency graph, tests enforce data contracts, and an external orchestrator can still manage the end-to-end SLA.

我使用 DBT 把数仓转换逻辑管理成可版本控制的模型。Sources 声明上游表；Staging 标准化原始字段；Intermediate 封装可复用逻辑；Marts 输出业务可用数据集。ref 构建依赖图，Tests 保障数据契约，外部调度器仍可管理端到端 SLA。