dbt의 고급 테스트 기법으로 데이터 품질을 보장하는 방법을 배웁니다. 모델, sources, seeds를 검증하기 위한 기본 제공 테스트, 단일(singular) 테스트, 재사용 가능한 테스트를 살펴봅니다. Jinja로 사용자 지정 테스트를 정의하고, 실패를 디버깅하며, 하류 분석에 영향을 주기 전에 불일치를 잡아내도록 검증 워크플로를 최적화하는 방법을 이해합니다.

dbt 테스트 소개

기본 제공 테스트

모델에 대한 테스트 정의하기

불량 데이터 찾기

단일 테스트 만들기

단일(singular) 테스트 개발 단계

여정 소요 시간 검증하기

테스트 쿼리 검증하기

맞춤형 재사용 테스트 만들기

Testing, testing, testing

재사용 가능한 테스트 구현하기

단일 테스트를 재사용 가능한 테스트로 업데이트하기

테스트와 문서화

확장 가능하고 운영 준비가 된 워크플로를 구축하며 dbt 실무 역량을 한 단계 높여 보세요. 데이터 계보를 개선하기 위해 dbt sources와 seeds를 사용하는 법, 히스토리 변경을 추적하기 위한 snapshots 구현, 그리고 dbt build로 변환 작업을 자동화하는 방법을 학습합니다. 강의가 끝나면 대규모 데이터 파이프라인을 자신 있게 관리할 수 있게 됩니다.

dbt 소스

Options

Only dbt models

Common to models and sources

Only dbt sources

Models, sources, or both?

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')}}

select * from ____
        


version: 2

sources:
  - name: ____
    tables:
      - name: taxi_rides


#!/usr/bin/env python3
import duckdb
con = duckdb.connect('dbt.duckdb', read_only=True)
print(con.sql('select * from taxi_rides_raw'))
print(con.sql('select count(*) from taxi_rides_raw'))
if (con.execute('select count(*) 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

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

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


version: 2

sources:
  - name: raw
    tables:
      - name: taxi_rides


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

소스 추가하기

dbt seeds

진실의 알맹이

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


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

select * from source_data


version: 2

seeds:
  - name: ____
    config:
      column_types:
        ____: ____(5)


#!/usr/bin/env python3
import duckdb
import os.path

try:
  con = duckdb.connect('dbt.duckdb', read_only=True)
  print(con.sql('select * from nynj_zipcodes'))
  if (con.execute('select count(*) as total_zipcodes from nynj_zipcodes').fetchall()[0][0] == 2877):
    print("Your data counts are correct!")
    with open('/home/repl/workspace/successful_data_check', 'w') as f:
      f.write('2877')
  else:
    print("There appears to be an issue with your data counts.")
except duckdb.CatalogException:
  if not os.path.isfile('/home/repl/workspace/nyc_yellow_taxi/seeds/nynj_zipcodes.csv'):
    print("It looks like the nynj_zipcodes.csv file is\n not present in the seeds/ directory.\n\nPlease move the file and try again.\n")
  else:
    print("It looks like your data warehouse doesn't exist yet\n\n Please run the dbt seed command and try again.\n")

version: 2

seeds:
  - name: nynj_zipcodes
    config:
      column_types:
        zipcode: varchar(5)


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


- Run the following command in the terminal window (or drag and drop the nynj_zipcodes.csv file into the seeds folder using the file explorer window.)

mv nynj_zipcodes.csv seeds/

- Modify the properties.yml file as described in the solution.

- Run dbt seed in the terminal window.

- Run the command ./datacheck in the terminal window and verify you see zipcode information in the printed table.

ZIP is the code

dbt snapshots로 구현하는 SCD2(구 버전)

스냅샷 생성 절차

스냅샷 프로세스

스냅샷 문제

version: 2

sources:
  - name: raw
    tables:
      - name: taxi_rides
      - name: vehicle_list


____

{{
    config(
      target_schema='main',
      unique_key='____',
      strategy='timestamp',
      updated_at='____'
    )
}}

select * from ____
{% endsnapshot %}


{% snapshot vehicle_list_snapshot %}

{{
    config(
      target_schema='main',
      unique_key='license_id',
      strategy='timestamp',
      updated_at='last_updated'
    )
}}

select * from {{ source('raw', 'vehicle_list') }}
{% endsnapshot %}


- Edit the snapshot_vehicle_list.sql file as shown
- Run the command dbt snapshot

스냅샷 추가하기

dbt build로 자동화하기

dbt build가 이 단계를 실행함

dbt build가 이 항목은 실행하지 않음

dbt build가 할 수 없는 것은 무엇인가요?


# 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


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

select * from 'taxi_rides'
        


{% snapshot vehicle_list_snapshot %}

{{
    config(
      target_schema='main',
      unique_key='license_id',
      strategy='timestamp',
      updated_at='last_updated'
    )
}}

select * from {{ source('new', 'vehicle_list') }}
{% endsnapshot %}


with avg_daily_fare as (
  select date_part('day', tpep_pickup_datetime) as day,
         avg(total_amount) as avg_amount
  from {{ ref('taxi_rides_raw') }}
  group by day
  order by day
)
select * from avg_daily_fare


version: 2

models:
  - name: taxi_rides_raw
    description: Initial import of the NYC Yellow Taxi trip data from Parquet source
    access: public
  - name: avg_ride_cost_per_day
    description: Avg amount spent per ride, per day
    access: public

sources:
  - name: raw
    tables:
      - name: taxi_rides
      - name: vehicle_list


version: 2

models:
  - name: taxi_rides_raw
    description: Initial import of the NYC Yellow Taxi trip data from Parquet source
    access: public
  - name: avg_ride_cost_per_day
    description: Avg amount spent per ride, per day
    access: public
sources:
  - name: raw
    tables:
      - name: taxi_rides
      - name: vehicle_list


- Run dbt build and look for errors. You should see an issue in the snapshots/vehicle_list_snapshot.sql.

- Edit this file as shown.

- Run dbt build again. You should see errors relating to the models/taxi_rides_raw.sql file.

- Edit this file as shown.

- Run dbt build once more and you should not see any errors.

인턴 돕기!

Run the following commands in the terminal:

dbt build
dbt docs generate
dbt docs serve

모든 것을 종합해 보기

운영 환경에서 dbt 구현

데이터 엔지니어와 분석 전문가를 위한 실습 중심 강의로 dbt 역량을 한 단계 끌어올려 보세요. 사용자 지정 테스트 전략, 재사용 가능한 테스트, sources, seeds, snapshots 등 고급 dbt 기능을 다루며, 히스토리 데이터 변경 추적 방법을 익힙니다. 또한 dbt build를 사용해 dbt 파이프라인을 자동화하여 운영 환경에서 안정적이고 효율적인 변환을 보장하는 방법을 배웁니다. 대화형 연습과 실제 시나리오를 통해 대규모 데이터 워크플로에 dbt를 자신 있게 적용할 수 있습니다.

Introduction to dbt

데이터 엔지니어와 분석 전문가를 위한 실습 과정으로 dbt 기술을 향상하세요.

중급 dbt

데이터 엔지니어와 분석 전문가를 위한 실습형 과정으로 dbt 역량을 한 단계 끌어올리세요.

zipcode,place,state
07093,West New York,New Jersey
12007,Alcove,New York
12009,Altamont,New York
12023,Berne,New York

ZIP is the code

중급 dbt

실습형 인터랙티브 연습문제