从零开始练习搭建一个 dbt 项目。将不同类型的数据加载到 dbt 项目中，并设置多种分层的 dbt 暂存模型。本章聚焦于 ELT 流程中的 E 和 L 部分。

dbt 案例学习简介

检查 dbt 安装

初始化 dbt 项目

设置 dbt 项目并加载数据

version: 2

seeds:
  - name: looker__distribution_centers



# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'looker_ecommerce'
version: '1.0.0'

# This setting configures which "profile" dbt uses for this project.
profile: 'looker_ecommerce'

# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


looker_ecommerce:
  outputs:
    dev:
      type: duckdb
      path: /home/repl/dbt.duckdb
  target: dev


id,name,latitude,longitude
1,Memphis TN,35.1174,-89.9711
2,Chicago IL,41.8369,-87.6847
3,Houston TX,29.7604,-95.3698
4,Los Angeles CA,34.05,-118.25
5,New Orleans LA,29.95,-90.0667
6,Port Authority of New York/New Jersey NY/NJ,40.634,-73.7834
7,Philadelphia PA,39.95,-75.1667
8,Mobile AL,30.6944,-88.0431
9,Charleston SC,32.7833,-79.9333
10,Savannah GA,32.0167,-81.1167

将配送中心作为 dbt 种子文件加载

version: 2

models:
  - name: _____ # Fill in the model name
    description: _____ # Fill in an informative description of the model


SELECT *
FROM {{ ref('looker__distribution_centers') }}

version: 2

models:
  - name: stg_looker__distribution_centers # Fill in the model name
    description: Distribution center name and location # Fill in an informative description of the model


创建配送中心的分层（staging）模型

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: ____ # Fill in the staging model name 
    description: Users demographics and location information 

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: ____ 	# Fill in the source name

SELECT *
FROM {{ source(____, ____) }} -- Fill in the source 

SELECT *
FROM {{ source('looker_ecommerce', 'users') }} -- Fill in the source 

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users 	# Fill in the source name

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: stg_looker__users # Fill in the staging model name 
    description: Users demographics and location information

通过 dbt source 加载并创建用户模型

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: stg_looker__users
    description: Users demographics and location information
    
  - name: stg_looker__events
    description: User event data

  - name: stg_looker__inventory_items
    description: Inventory items information

  - name: stg_looker__order_items
    description: Order information on the item level

  - name: stg_looker__orders
    description: Order information such as status and timestamps
        
    # Fill in for the staging model for products
    

SELECT *
FROM {{ source('looker_ecommerce', 'users') }}

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      # Fill in for the dbt source for products
      ____

SELECT *
FROM {{ source('looker_ecommerce', 'events') }}

SELECT *
FROM {{ source('looker_ecommerce', 'inventory_items') }}

SELECT *
FROM {{ source('looker_ecommerce', 'order_items') }}

SELECT *
FROM {{ source('looker_ecommerce', 'orders') }}

SELECT *
FROM {{ source('looker_ecommerce', 'products') }}

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      # Fill in for the dbt source for products
      - name: products

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: stg_looker__users
    description: Users demographics and location information
    
  - name: stg_looker__events
    description: User event data

  - name: stg_looker__inventory_items
    description: Inventory items information

  - name: stg_looker__order_items
    description: Order information on the item level

  - name: stg_looker__orders
    description: Order information such as status and timestamps
    
  # Fill in for the staging model for products
  - name: stg_looker__products
    description: Product information such as SKU and category


进一步练习 dbt source 与 staging 模型

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location    

  - name: stg_looker__users
    description: Users demographics and location information
    
  - name: stg_looker__events
    description: User event data

  - name: stg_looker__inventory_items
    description: Inventory items information

  - name: stg_looker__order_items
    description: Order information on the item level

  - name: stg_looker__orders
    description: Order information such as status and timestamps
    
  - name: stg_looker__products
    description: Product information such as SKU and category


SELECT *
FROM {{ source('looker__distribution_centers') }}

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      - name: stg_looker__products

version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      - name: products

调试常见错误

设置 dbt

深入掌握 dbt 的数据建模。从初步的暂存模型逐步构建到最终的数据集市模型，以回答关键业务需求。过程中，您还将练习创建数据测试，以防止数据质量漂移并提供保护。

创建 dbt 描述与测试

添加 dbt 列描述

添加 dbt 列级测试

构建 dbt 数据集市与快照模型

复习：dbt 模型层级

为 customers 创建数据集市模型

为商品创建数据集市模型

调试更多错误

构建 dbt 快照模型

构建 dbt 模型

学习如何用 Jinja 增强您的 dbt 项目，从而精简代码、减少冗余并提升可维护性。您将练习使用变量、循环和宏，构建更高效的转换工作流。

在 dbt 中介绍 Jinja

用 dbt compile 热身

用 dbt compile 热身 2

使用 dbt compile 进行调试

使用 dbt Jinja 设置变量

使用 dbt Jinja 2 设置变量

在 dbt 中更复杂的 Jinja 用法

在重复 SQL 上使用 Jinja for 循环 1

在重复 SQL 上使用 Jinja 循环 2

结合 Jinja 的宏

使用 Jinja 去除空白字符

回顾：dbt 案例研究

用 Jinja 改进 dbt

通过我们的动手实践型 dbt（Data Build Tool）案例研究课程，释放数据转换的力量。本课程面向 dbt 初学到入门阶段的用户，聚焦实用的真实场景练习，为您提供多次机会去构建、转换并加深对核心 dbt 命令的理解。通过亲手实践，您将在流程的每一步建立信心——从搭建 dbt 环境起步，到创建能够解决关键业务问题的高级模型。通过交互式练习，您将积累处理真实 ETL 管道项目所需的经验与信心。

使用 dbt（数据构建工具）将原始数据转化为干净、可靠的模型——这是一款现代的、基于 SQL 的转换框架，全球各地的数据团队都在使用。 这门实战案例课程专为初学者设计，帮助你通过循序渐进的实践练习，掌握真实场景中的实用技能。 你将搭建自己的 dbt 环境，大规模建模数据，并使用 dbt 的内置功能编写可复用代码。<br><br><h2>设置你的项目并探索数据</h2>首先搭建一个 dbt 项目，并使用真实的电子商务数据集进行实践。 你将对原始数据进行结构化处理，配置配置文件，并调试语法问题，同时深入了解每个转换步骤背后的业务背景。<br><br><h2>构建并验证模型</h2>学习创建可扩展的暂存模型，并应用数据质量检查，确保您的数据集准确且可直接用于分析。 你将打下坚实基础，学会回答关键的业务问题。<br><br><h2>使用 Jinja 实现自动化</h2>通过学习如何使用 Jinja 编写可复用、易维护的代码，完成本课程。 你将使用变量、控制流和循环来遵循 DRY（不要重复自己）原则，从而简化你的 dbt 工作流程。

Data Manipulation in SQL

Intermediate dbt

通过实践练习，学习使用dbt将原始数据转化为清晰可靠的模型。

案例研究：使用 dbt 构建电商数据模型

通过动手实战，学习如何用 dbt 将原始数据转化为干净、可靠的模型。 

dbt 案例学习简介

Create Your Free Account