creating-dbt-models

分类: 数据与AI | 上传者: AltimateAI | 下载: 0 | 版本: v1.0（最新）

按照项目约定创建 dbt 模型。使用 dbt 模型时用于： (1) 创建新模型（任何层 - 首先发现项目的命名约定） (2) 任务提及模型、表或 SQL 的“创建”、“构建”、“添加”、“写入”、“新建”或“实现” (3) 修改现有模型逻辑、列、连接或转换 (4) 根据 schema.yml 规范或预期输出要求实现模型在编写之前发现项目约定。运行 dbt build（不仅仅是编译）来验证。 --- # dbt 模型开发 **先读再写。写完后构建。验证您的输出。** ## 关键规则 1. **创建/修改模型后始终运行 `dbt build`** - 编译还不够 2. **使用 `dbt show` 构建后始终验证输出** - 不要假设成功 3. **如果构建失败 3 次以上**，停止并重新评估您的整个方法 ## 工作流程 ### 1. 了解任务要求 - 需要哪些列？明确列出它们。 - 表格的粒度是多少（每行一行）？ - 需要什么计算或聚合？ ### 2. 发现项目约定 ```bash cat dbt_project.yml find models/ -name "*.sql" | head -20 ``` 阅读 2-3 个现有模型以学习命名、配置和 SQL 模式。 ### 3. 查找相似模型 ```bash # 查找具有相似用途的模型 find models/ -name "*agg*.sql" -o -name "*fct_*.sql" |头-5```

更新日志: Source: GitHub https://github.com/AltimateAI/data-engineering-skills

目录结构

当前层级: 根目录

📄 SKILL.md 3.5 KB

SKILL.md

---
name: creating-dbt-models
description: |
  Creates dbt models following project conventions. Use when working with dbt models for:
  (1) Creating new models (any layer - discovers project's naming conventions first)
  (2) Task mentions "create", "build", "add", "write", "new", or "implement" with model, table, or SQL
  (3) Modifying existing model logic, columns, joins, or transformations
  (4) Implementing a model from schema.yml specs or expected output requirements
  Discovers project conventions before writing. Runs dbt build (not just compile) to verify.
---

# dbt Model Development

**Read before you write. Build after you write. Verify your output.**

## Critical Rules

1. **ALWAYS run `dbt build`** after creating/modifying models - compile is NOT enough
2. **ALWAYS verify output** after build using `dbt show` - don't assume success
3. **If build fails 3+ times**, stop and reassess your entire approach

## Workflow

### 1. Understand the Task Requirements

- What columns are needed? List them explicitly.
- What is the grain of the table (one row per what)?
- What calculations or aggregations are required?

### 2. Discover Project Conventions

```bash
cat dbt_project.yml
find models/ -name "*.sql" | head -20
```

Read 2-3 existing models to learn naming, config, and SQL patterns.

### 3. Find Similar Models

```bash
# Find models with similar purpose
find models/ -name "*agg*.sql" -o -name "*fct_*.sql" | head -5
```

Learn from existing models: join types, aggregation patterns, NULL handling.

### 4. Check Upstream Data

```bash
# Preview upstream data if needed
dbt show --select <upstream_model> --limit 10
```

### 5. Write the Model

Follow discovered conventions. Match the required columns exactly.

### 6. Compile (Syntax Check)

```bash
dbt compile --select <model_name>
```

### 7. BUILD - MANDATORY

**This step is REQUIRED. Do NOT skip it.**

```bash
dbt build --select <model_name>
```

If build fails:
1. Read the error carefully
2. Fix the specific issue
3. Run build again
4. **If fails 3+ times, step back and reassess approach**

### 8. Verify Output (CRITICAL)

**Build success does NOT mean correct output.**

```bash
# Check the table was created and preview data
dbt show --select <model_name> --limit 10
```

Verify:
- Column names match requirements exactly
- Row count is reasonable
- Data values look correct
- No unexpected NULLs

### 9. Verify Calculations Against Sample Data

**For models with calculations, verify correctness manually:**

```bash
# Pick a specific row and verify calculation by hand
dbt show --inline "
  select *
  from {{ ref('model_name') }}
  where <primary_key> = '<known_value>'
" --limit 1

# Cross-check aggregations
dbt show --inline "
  select count(*), sum(<column>)
  from {{ ref('model_name') }}
"
```

For example, if calculating `total_revenue = quantity * price`:
1. Pick one row from output
2. Look up the source quantity and price
3. Manually calculate: does it match?

### 10. Re-review Against Requirements

**Before declaring done, re-read the original request:**
- Did you implement what was asked, not what you assumed?
- Are column names exactly as specified?
- Is the calculation logic correct per the requirements?
- Does the grain (one row per what?) match what was requested?

## Anti-Patterns

- Declaring done after compile without running build
- Not verifying output data after build
- Getting stuck in compile/build error loops
- Assuming table exists just because model file exists
- Writing SQL without checking existing model patterns first

登录后下载/点赞/收藏 ❤ 73 | ★ 0

creating-dbt-models

目录结构

SKILL.md

举报内容

提示