أساسيات DBT (أداة بناء البيانات)

ما هو dbt

لماذا dbt؟

بنية المشروع

my_project/
  dbt_project.yml
  models/
    staging/
      stg_orders.sql
    marts/
      fct_revenue.sql
  tests/
  seeds/
  macros/

ملف نموذج

-- models/staging/stg_orders.sql
{{ config(materialized='view') }}
SELECT
  id,
  user_id,
  total,
  created_at
FROM {{ source('raw', 'orders') }}

أنواع materialisation

ref() والتبعيات

-- models/marts/fct_revenue.sql
SELECT
  date_trunc('day', created_at) AS day,
  SUM(total) AS revenue
FROM {{ ref('stg_orders') }}
GROUP BY 1

تشغيل dbt

dbt run                    # build all models
dbt run --select staging   # subset
dbt run --select +fct_revenue   # this model and upstream
dbt build                   # run + test

المصادر

# models/sources.yml
sources:
  - name: raw
    database: warehouse
    tables:
      - name: orders

النماذج التزايدية

{{ config(materialized='incremental', unique_key='id') }}
SELECT *
FROM {{ source('raw','orders') }}
{% if is_incremental() %}
  WHERE created_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}

قوالب Jinja

{% set tiers = ['free','pro','enterprise'] %}
SELECT user_id,
{% for t in tiers %}
  COUNT(*) FILTER (WHERE tier = '{{ t }}') AS {{ t }}_count {% if not loop.last %},{% endif %}
{% endfor %}
FROM users GROUP BY user_id

اللقطات

{% snapshot user_snapshot %}
  {{ config(
    target_schema='snapshots',
    unique_key='id',
    strategy='timestamp',
    updated_at='updated_at',
  ) }}
  SELECT * FROM {{ source('raw','users') }}
{% endsnapshot %}

التوثيق

dbt docs generate
dbt docs serve     # opens browser

خلاصة

اختبار سريع