dbt
dbt permite a los ingenieros de análisis transformar datos escribiendo sentencias SQL SELECT. Maneja materialización (tablas, vistas, incremental), pruebas, documentación y seguimiento de linaje.
Instalación
# Instalar dbt con el adaptador de PostgreSQL
pip install dbt-postgres
# O con otros adaptadores
pip install dbt-bigquery
pip install dbt-snowflake
# Inicializar un nuevo proyecto
dbt init my_project
cd my_project
Estructura del proyecto
my_project/
├── dbt_project.yml # Configuración del proyecto
├── profiles.yml # Perfiles de conexión (normalmente en ~/.dbt/)
├── models/
│ ├── staging/ # Limpieza de datos crudos
│ │ ├── _staging.yml # Esquema + pruebas para modelos staging
│ │ ├── stg_users.sql
│ │ └── stg_orders.sql
│ └── marts/ # Lógica de negocio
│ ├── _marts.yml
│ └── fct_revenue.sql
├── tests/ # Pruebas de datos personalizadas
├── macros/ # Macros SQL reutilizables
└── seeds/ # Archivos CSV para cargar
Configuración
# dbt_project.yml: Configuración del proyecto
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: Conexión a la base de datos (~/.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
Modelos Staging
-- models/staging/stg_users.sql: Limpiar datos crudos de usuarios
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: Limpiar datos crudos de pedidos
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'
Modelos Mart
-- models/marts/fct_revenue.sql: Tabla de hechos de ingresos
{{
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
Esquema y Pruebas
# models/staging/_staging.yml: Definir fuentes, columnas y pruebas
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: Datos de usuarios limpios
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']
Comandos CLI
# commands.sh: Comandos CLI comunes de dbt
# Ejecutar todos los modelos
dbt run
# Ejecutar un modelo específico y sus dependencias ascendentes
dbt run --select +fct_revenue
# Ejecutar pruebas
dbt test
# Generar y servir documentación
dbt docs generate
dbt docs serve --port 8081
# Verificar la frescura de las fuentes
dbt source freshness
# Compilación completa (ejecutar + probar + snapshot)
dbt build
# Ejecutar contra producción
dbt run --target prod
Macros
-- macros/cents_to_dollars.sql: Macro reutilizable para conversión de moneda
{% macro cents_to_dollars(column_name) %}
({{ column_name }} / 100.0)::numeric(10,2)
{% endmacro %}
-- Uso en un modelo: SELECT {{ cents_to_dollars('amount_cents') }} AS amount