dbt
dbt ermöglicht es Analyse-Ingenieuren, Daten durch das Schreiben von SQL SELECT-Anweisungen zu transformieren. Es kümmert sich um Materialisierung (Tabellen, Views, inkrementell), Tests, Dokumentation und Abhängigkeitsverfolgung.
Installation
# Installiere dbt mit dem PostgreSQL-Adapter
pip install dbt-postgres
# Oder mit anderen Adaptern
pip install dbt-bigquery
pip install dbt-snowflake
# Initialisiere ein neues Projekt
dbt init my_project
cd my_project
Projektstruktur
my_project/
├── dbt_project.yml # Projektkonfiguration
├── profiles.yml # Verbindungsprofile (normalerweise in ~/.dbt/)
├── models/
│ ├── staging/ # Bereinigung der Rohdaten
│ │ ├── _staging.yml # Schema + Tests für Staging-Modelle
│ │ ├── stg_users.sql
│ │ └── stg_orders.sql
│ └── marts/ # Geschäftslogik
│ ├── _marts.yml
│ └── fct_revenue.sql
├── tests/ # Benutzerdefinierte Datentests
├── macros/ # Wiederverwendbare SQL-Makros
└── seeds/ # CSV-Dateien zum Laden
Konfiguration
# dbt_project.yml: Projektkonfiguration
name: my_project
version: '1.0.0'
profile: my_project
models:
my_project:
staging:
+materialized: view
+schema: staging
marts:
+materialized: table
+schema: analytics
# profiles.yml: Datenbankverbindung (~/.dbt/profiles.yml)
my_project:
target: dev
outputs:
dev:
type: postgres
host: localhost
port: 5432
user: analyst
password: "{{ env_var('DBT_PASSWORD') }}"
dbname: analytics
schema: dev
threads: 4
prod:
type: postgres
host: prod-db.example.com
port: 5432
user: dbt_prod
password: "{{ env_var('DBT_PROD_PASSWORD') }}"
dbname: analytics
schema: public
threads: 8
Staging-Modelle
-- models/staging/stg_users.sql: Bereinigt Rohdaten von Benutzern
WITH source AS (
SELECT * FROM {{ source('raw', 'users') }}
),
cleaned AS (
SELECT
id AS user_id,
LOWER(TRIM(email)) AS email,
name,
created_at::timestamp AS signed_up_at,
CASE WHEN status = 'active' THEN TRUE ELSE FALSE END AS is_active
FROM source
WHERE email IS NOT NULL
)
SELECT * FROM cleaned
-- models/staging/stg_orders.sql: Bereinigt Rohdaten von Bestellungen
SELECT
id AS order_id,
user_id,
amount_cents / 100.0 AS amount,
status,
created_at::timestamp AS ordered_at
FROM {{ source('raw', 'orders') }}
WHERE status != 'test'
Mart-Modelle
-- models/marts/fct_revenue.sql: Umsatz-Fakten-Tabelle
{{
config(
materialized='incremental',
unique_key='order_date',
on_schema_change='sync_all_columns'
)
}}
WITH orders AS (
SELECT * FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE ordered_at > (SELECT MAX(order_date) FROM {{ this }})
{% endif %}
),
daily AS (
SELECT
DATE_TRUNC('day', ordered_at)::date AS order_date,
COUNT(*) AS total_orders,
COUNT(DISTINCT user_id) AS unique_customers,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY 1
)
SELECT * FROM daily
Schema und Tests
# models/staging/_staging.yml: Definiere Quellen, Spalten und Tests
version: 2
sources:
- name: raw
schema: public
tables:
- name: users
loaded_at_field: created_at
freshness:
warn_after: {count: 12, period: hour}
error_after: {count: 24, period: hour}
- name: orders
models:
- name: stg_users
description: Bereinigte Benutzerdaten
columns:
- name: user_id
tests: [unique, not_null]
- name: email
tests: [unique, not_null]
- name: stg_orders
columns:
- name: order_id
tests: [unique, not_null]
- name: status
tests:
- accepted_values:
values: ['pending', 'completed', 'cancelled', 'refunded']
CLI-Befehle
# commands.sh: Häufige dbt CLI-Befehle
# Alle Modelle ausführen
dbt run
# Bestimmtes Modell und seine vorgelagerten Abhängigkeiten ausführen
dbt run --select +fct_revenue
# Tests ausführen
dbt test
# Dokumentation generieren und bereitstellen
dbt docs generate
dbt docs serve --port 8081
# Quellaktualit�t prüfen
dbt source freshness
# Vollständiger Build (Ausführen + Testen + Snapshot)
dbt build
# Gegen Produktion ausführen
dbt run --target prod
Makros
-- macros/cents_to_dollars.sql: Wiederverwendbares Makro für Währungsumrechnung
{% macro cents_to_dollars(column_name) %}
({{ column_name }} / 100.0)::numeric(10,2)
{% endmacro %}
-- Verwendung in einem Modell: SELECT {{ cents_to_dollars('amount_cents') }} AS amount