Communitygithub.com

dbt (Data Build Tool) 专家技能

专业 dbt(数据构建工具)辅助,涵盖 SQL 建模、Jinja 宏、测试、文档和语义层。适用于在 BigQuery、Snowflake 或 PostgreSQL 上构建分析工程管道。

dbt (Data Build Tool) 专家技能 是什么?

此 AI 代理技能专为需要 dbt(数据构建工具)分析工程工作流的开发人员设计。它提供 SQL 建模(声明式、增量式)、使用 Jinja 宏实现 DRY 可重用逻辑、模式测试(唯一性、非空、参照完整性)以及自动文档生成(通过 schema.yml 和编译的 Markdown)方面的专家级帮助。此技能可在 BigQuery、Snowflake、Databricks 和 Redshift 等热门云仓库上运行。通过利用 `ref()` 和 `source()` 函数来维护清晰的 DAG 依赖关系、强制执行 `{{ is_incremental() }}` 等最佳实践以节省成本,以及利用自定义的 `cents_to_dollars()` Jinja 宏,它可以帮助用户直接解决常见的 dbt 挑战,而无需提示过多代码。它还集成了故障排除指南,可以诊断编译错误、重复键和模式更改失败,帮助用户直接编写生产级 dbt 项目。

兼容平台~Claude Code~Codex CLI~Cursor
npx skills add https://github.com/G1Joshi/Agent-Skills/tree/HEAD/skills/ai-ml/dbt

在你喜欢的 AI 中提问

打开一个已预加载此 Agent Skill 的新对话。

文档

dbt(数据构建工具)

dbt 使用 SQL 在数据仓库中管理数据转换。v2.0 引入了 Fusion Engine(Rust 编写)以提升性能。

何时使用

  • 分析工程与数据转换:在云端现代数据仓库(Snowflake、BigQuery、Databricks、Redshift)内转换原始数据。
  • 使用 Jinja 与版本控制的模块化 SQL:构建具有 DAG 依赖关系和宏的可重用 SQL 模型。
  • 数据质量与模式测试:强制执行 unique、not_null、参照完整性以及自定义单数测试。
  • 自动化数据文档与血缘:直接从代码库模式生成交互式依赖关系血缘图。

快速开始

-- models/marts/fct_orders.sql
{{ config(materialized='table') }}

with orders as (
    select * from {{ ref('stg_orders') }}
),
payments as (
    select * from {{ ref('stg_payments') }}
)

select
    orders.order_id,
    orders.customer_id,
    orders.order_date,
    coalesce(payments.amount, 0) as total_amount
from orders
left join payments using (order_id)

核心概念

使用 ref() 和 source() 的声明式 SQL 建模

构建具有依赖关系解析的转换模型:

-- models/marts/core/fct_customer_orders.sql
{{
  config(
    materialized = 'incremental',
    unique_key = 'order_id',
    on_schema_change = 'fail'
  )
}}

with orders as (
    select * from {{ ref('stg_orders') }}
    {% if is_incremental() %}
      where order_date >= (select coalesce(max(order_date), '1970-01-01') from {{ this }})
    {% endif %}
),

customers as (
    select * from {{ ref('stg_customers') }}
)

select
    o.order_id,
    o.customer_id,
    c.full_name as customer_name,
    o.order_date,
    o.total_amount_usd
from orders o
inner join customers c on o.customer_id = c.customer_id

YAML 中的模式测试与文档

强制执行列约束和文档:

# models/marts/core/schema.yml
version: 2

models:
  - name: fct_customer_orders
    description: "每日客户订单事实表,合并了阶段订单和客户维度。"
    columns:
      - name: order_id
        description: "订单的主键。"
        tests:
          - unique
          - not_null

      - name: customer_id
        description: "引用阶段客户的外键。"
        tests:
          - not_null
          - relationships:
              to: ref('stg_customers')
              field: customer_id

      - name: total_amount_usd
        description: "以美元计的总交易金额。"
        tests:
          - not_null

用于 DRY 可重用逻辑的 Jinja 宏

创建自定义的可重用 SQL 工具:

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, decimal_places=2) -%}
    round(cast(({{ column_name }} / 100.0) as numeric), {{ decimal_places }})
{%- endmacro %}

-- 在模型中的用法:
select
    id,
    {{ cents_to_dollars('amount_in_cents') }} as amount_usd
from {{ ref('stg_payments') }}

常见模式

带时间戳过滤的增量模型

问题:每次运行 dbt 都从头重建数十亿行表既慢又昂贵。

解决方案: 使用 incremental 物化方式配合 is_incremental():

{{ config(
    materialized='incremental',
    unique_key='event_id'
) }}

select * from {{ source('raw', 'events') }}

{% if is_incremental() %}
  -- 仅查询在目标表中最新事件之后创建的行
  where event_timestamp > (select max(event_timestamp) from {{ this }})
{% endif %}

最佳实践(2026)

  • 应当 将项目组织成标准层级:staging(与原始源一对一映射)、intermediate 和 marts。
  • 应当 始终使用 {{ ref('model_name') }} 和 {{ source('source_name', 'table_name') }} 维护 DAG 血缘。
  • 应当 对数百万行的事实表实现增量模型(materialized='incremental')。
  • 应当 在每个拉取请求的 CI 流水线中运行 dbt test,以捕获数据模式回归。
  • 不应 编写原始数据库表引用(例如 analytics.raw.users);始终使用 source() 或 ref()。
  • 不应 在阶段模型中执行业务逻辑;阶段模型应仅负责清洗、转换类型和重命名列。
  • 不应 硬编码环境(dev 与 prod);使用 profiles.yml 中的 target.name 条件。

故障排除

错误原因解决方案
Compilation Error: Model '...' depends on a node named '...' which was not found{{ ref('model_name') }} 中拼写错误或模型文件缺失。检查模型文件名是否与引用的名称完全匹配。
dbt test failed: unique constraint violated由非唯一连接条件生成的重复键。检查 target/compiled/ 中已编译 SQL 的重复键。
Schema change error during incremental run上游模型添加/删除了列,破坏了现有的增量表。执行一次 --full-refresh 以重建模式:dbt run --full-refresh。

参考

Individual skills in this repo

This repo contains 11 individual skills — each has its own dedicated page.

G1Joshi/Agent-Skills

Expert Dask distributed computing assistance covering Dask DataFrames, Arrays, Futures, and cluster scaling. Use when analyzing datasets too large for pandas on a single machine or multi-node cluster.

G1Joshi/Agent-Skills

Expert JAX assistance covering Autograd, XLA compilation (`jit`), vectorization (`vmap`), and parallelization (`pmap`). Use when building high-performance numerical computing and cutting-edge deep learning research.

G1Joshi/Agent-Skills

Expert Ray distributed computing assistance covering Ray Core (actors, tasks), Ray Train, Ray Tune, and Ray Serve. Use when scaling Python compute and ML training across multi-node clusters.

G1Joshi/Agent-Skills

Expert Git version control assistance covering branching, interactive rebase, cherry-pick, submodules, worktrees, and conflict resolution. Use when managing source code history and collaboration workflows.

G1Joshi/Agent-Skills

Expert K9s CLI assistance covering terminal Kubernetes cluster navigation, real-time log streaming, port forwarding, and pod debugging. Use when managing and troubleshooting Kubernetes clusters with speed.

G1Joshi/Agent-Skills

Expert SWC assistance covering high-performance Rust-based JavaScript/TypeScript compilation, .swcrc configuration, Jest testing via @swc/jest, and minification. Use when replacing Babel with SWC for faster builds, accelerating test execution, or compiling modern ECMAScript features.

G1Joshi/Agent-Skills

Expert Tig assistance covering text-mode interface for Git, interactive staging, commit graph visualization, diff exploration, and blame navigation. Use when navigating Git commit history in the terminal, staging hunks interactively, browsing file changes, or reviewing revisions.

G1Joshi/Agent-Skills

Expert Vim assistance covering modal editing, .vimrc configuration, registers, macros, search/replace, and plugin management via vim-plug. Use when editing text efficiently in terminal environments, writing Vimscript, recording macros, or configuring core Vim settings.

G1Joshi/Agent-Skills

Expert Zed editor assistance covering high-performance Rust-based text editing, multi-buffer editing, language server protocols (LSP), and AI assistant integrations. Use when configuring Zed settings.json, setting up language extensions, collaborating in real-time channels, or optimizing editor startup speed.

G1Joshi/Agent-Skills

Expert Zsh assistance covering shell customization, Oh My Zsh plugins, Zinit plugin manager, prompt engineering (Starship/Powerlevel10k), and shell scripting. Use when configuring .zshrc, writing Zsh automation scripts, optimizing shell startup time, or configuring tab-completion.

G1Joshi/Agent-Skills

Expert [skill-name] assistance covering [feature 1], [feature 2], and [feature 3]. Use when [working with X], [debugging Y], or [implementing Z].

相关技能