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

dbt 基础

运行 dbt 命令

dbt 简介

dbt 的版本

获取帮助

dbt 的用户

创建 dbt 项目

初始化一个 dbt 项目

创建项目配置文件

欢迎使用 dbt

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

操作第一个项目

dbt 工作流的步骤

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: '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
    


with source_data as (
    -- Add the query as described to generate the data model
    ____
)

select * from source_data


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


Run the following commands in the terminal:
dbt run
./datacheck



# 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


with source_data as (
    -- Add the query as described to generate the data model
    select * from read_parquet('yellow_tripdata_2023-01-partial.parquet')
)

select * from source_data


Run the following commands in the terminal:
dbt run
./datacheck   


运行项目

什么是 dbt 模型？

选项

数据模型为真

数据模型为假

数据模型的特性


# 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
    


Run the following command in the terminal:

dbt run


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


Run the following command in the terminal:

cd nyc_yellow_taxi
dbt run

创建一个 dbt 模型

更新 dbt 模型

项目

仅模型

配置文件


-- Update with SQL to return requested information
select 
  date_part('day', tpep_pickup_datetime) as day,
  ____ as total_cc_riders
from taxi_rides_raw
-- Refer to the Payment_type in schema doc
where payment_type = ____
group by day


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



-- Update with SQL to return requested information
select 
   date_part('day', tpep_pickup_datetime) as day,
   count(*) as total_cc_riders
from taxi_rides_raw
-- Refer to the Payment_type in schema doc
where payment_type = 1
group by day


更新一个 dbt 模型

dbt 项目与模型

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

创建并生成 dbt 文档

dbt docs 命令选项

dbt 文档流程

创建 dbt 文档

Jinja 模板

Jinja 能做什么？

处理一个 Jinja 模板

dbt 中的分层模型

分层模型创建

更新模型层级

更深入的 dbt 模型

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

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

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

数据模型的特性

dbt 入门

动手互动练习