了解 dbt 存在的原因，以及它如何用于创建数据转换项目。

dbt 基础

运行 dbt 命令

dbt 简介

dbt 的版本

获取帮助

dbt 的用户

创建 dbt 项目

初始化一个 dbt 项目

创建项目配置文件

欢迎使用 dbt

通过使用数据模型并在 dbt 中创建基于 SQL 的模型，深入掌握 dbt 的核心。学习基础 SQL 模型、定义依赖关系、进行更新，并排查各类模型问题。

操作第一个项目

dbt 项目工作流

运行项目

什么是 dbt 模型？

数据模型的特性

创建一个 dbt 模型

更新 dbt 模型

配置文件

更新一个 dbt 模型

dbt 项目与模型

学习如何使用 Jinja 模板简化 dbt 模型，并通过 `ref` 函数定义分层依赖，以确保模型按正确顺序执行。

创建并生成 dbt 文档

dbt docs 命令选项

dbt 文档流程

nyc_yellow_taxi:
  outputs:
    dev:
      type: duckdb
      path: dbt.duckdb
  target: dev



# 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: 'taxi_project'
version: '1.0.0'
config-version: 2

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

# 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"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/
# directory as views. These settings can be overridden in the individual model
# files using the `{{ config(...) }}` macro.
models:
  nyc_yellow_taxi:
  # Config indicated by + and applies to all files under models/example/
    taxi_rides:
    +materialized: view


{{ config(materialized='view')}}


with source_data as (
    select * from read_parquet('yellow_tripdata_2023-01-partial.parquet')
)

select * from source_data


version: 2

models:
  - name: taxi_rides_raw
    description: ____
    access: public


Run the following commands in the terminal:

dbt docs generate
dbt docs serve

version: 2

models:
  - name: taxi_rides_raw
    description: Initial import of the NYC Yellow Taxi trip data from Parquet source
    access: public


创建 dbt 文档

Jinja 模板

Jinja 能做什么？


# 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: 'nyc_yellow_taxi'
version: '1.0.0'
config-version: 2

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

# 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"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/
# directory as views. These settings can be overridden in the individual model
# files using the `{{ config(...) }}` macro.
#models:
#  nyc_yellow_taxi:
#  # Config indicated by + and applies to all files under models/example/
#    taxi_rides:
#    +materialized: view
    



select * from 'yellow_tripdata_2023-01-partial.parquet'



select
{% for column_name in ['fare_amount', 'tip_amount', 'tolls_amount', 'total_amount'] %}
  {{column_name}}{% if not loop.last %},{% endif %}
{% endfor %}
from taxi_rides_raw
   

Run the following commands in the terminal:
  
  dbt run
  

处理一个 Jinja 模板

dbt 中的分层模型

模型创建顺序

分层模型创建


-- Update SQL to use Jinja reference
select 
   date_part('day', tpep_pickup_datetime) as day,
   count(*) as total_riders
-- Update the line below to use a Jinja function
from taxi_rides_raw
where payment_type = 1
group by day


#!/usr/bin/env python3
import duckdb
con = duckdb.connect('dbt.duckdb', read_only=True)
print(con.sql('select * from creditcard_riders_by_day'))
if (con.execute('select * from creditcard_riders_by_day').fetchall()[0][1] == 54743):
  with open('/home/repl/workspace/successful_data_check', 'w') as f:
    f.write('54743')
    


Run the following commands in the terminal:
dbt run
dbt run -f



# 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: 'nyc_yellow_taxi'
version: '1.0.0'
config-version: 2

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

# 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"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/
# directory as views. These settings can be overridden in the individual model
# files using the `{{ config(...) }}` macro.
models:
  nyc_yellow_taxi:
  # Config indicated by + and applies to all files under models/example/
    taxi_rides:
    +materialized: view



-- Update SQL to use Jinja reference
select 
   date_part('day', tpep_pickup_datetime) as day,
   count(*) as total_riders
-- Update the line below to use a Jinja function
from {{ ref('taxi_rides_raw') }}
where payment_type = 1
group by day


更新模型层级

更深入的 dbt 模型

本课程将向您介绍 dbt（data build tool），这是一款广受欢迎的工具，推动了分析工程的发展，用于创建和维护抽取、转换、加载（ELT）流程。您将学习如何构建数据仓库、进行数据建模与转换，并设计测试来执行数据验证。您还将使用 SQL 和 shell 命令在 dbt 中完成多种任务，并了解如何为仓库用户生成文档！

dbt，或称数据构建工具，已经席卷了数据领域。 本课程将带您了解使用 dbt 实现示例数据仓库的概念、术语和方法。 你将了解 dbt 是什么、何时应该使用它，以及在实施数据仓库时的最佳实践。 您将使用真实数据集，并执行数据分析师、数据工程和分析工程岗位中常见的提取、加载和转换操作。

学习者将掌握从零定义数据仓库、建模和转换数据，以及构建测试的技能！这门课程将为您提供坚实的基础，助您在 dbt 学习之旅中不断进阶，无论您打算实施哪种类型的数据仓库。 通过使用 SQL 和 shell 命令完成练习，增强你对如何以及何时使用 dbt 的信心。

分层模型创建

dbt 入门

动手互动练习