Cómo Instalar y Configurar dbt Core con BigQuery desde CERO

Fuentes
  │
  ▼
BigQuery RAW
  │
  │  dbt source()
  ▼
STAGING
  │
  │  dbt ref()
  ▼
INTERMEDIATE
  │
  │  lógica de negocio
  ▼
MARTS
  │
  ▼
BI / Analytics / ML

mi_proyecto:
  target: dev

  outputs:
    dev:
      type: bigquery
      method: service-account
      project: "TU_PROJECT_ID"
      dataset: "analytics_dev"
      keyfile: "/ruta/segura/service-account.json"
      location: "EU"
      threads: 4
      timeout_seconds: 300
      priority: interactive
      retries: 1

mkdir dbt-bigquery-tutorial
cd dbt-bigquery-tutorial

dbt init tutorial_dbt_bigquery

tutorial_dbt_bigquery/
├── analyses/
├── macros/
├── models/
│   └── example/
├── seeds/
├── snapshots/
├── tests/
├── dbt_project.yml
└── README.md

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

select
    cast(customer_id as int64) as customer_id,
    cast(first_name as string) as first_name,
    cast(last_name as string) as last_name,
    lower(trim(email)) as email,
    cast(created_at as timestamp) as created_at
from `TU_PROJECT_ID.raw.customers`
where customer_id is not null

version: 2

sources:
  - name: raw
    database: TU_PROJECT_ID
    schema: raw

    tables:
      - name: customers
      - name: orders

select
    cast(customer_id as int64) as customer_id,
    lower(trim(email)) as email,
    cast(created_at as timestamp) as created_at
from {{ source('raw', 'customers') }}
where customer_id is not null

name: 'tutorial_dbt_bigquery'
version: '1.0.0'
config-version: 2

profile: 'tutorial_dbt_bigquery'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

models:
  tutorial_dbt_bigquery:
    staging:
      +materialized: view

    intermediate:
      +materialized: view

    marts:
      +materialized: table

dbt debug

dbt parse

dbt run

dbt test

{{ config(
    materialized='view',
    tags=['staging']
) }}

with source_data as (

    select
        order_id,
        customer_id,
        order_status,
        order_total,
        created_at,
        updated_at
    from {{ source('raw', 'orders') }}

),

cleaned as (

    select
        cast(order_id as int64) as order_id,
        cast(customer_id as int64) as customer_id,
        upper(trim(order_status)) as order_status,
        cast(order_total as numeric) as order_total,
        cast(created_at as timestamp) as created_at,
        cast(updated_at as timestamp) as updated_at
    from source_data
    where order_id is not null

)

select *
from cleaned

version: 2

models:
  - name: stg_orders
    description: "Normalización inicial de pedidos procedentes de RAW."

    columns:
      - name: order_id
        description: "Identificador único del pedido."
        data_tests:
          - not_null
          - unique

      - name: customer_id
        description: "Identificador del cliente."
        data_tests:
          - not_null

      - name: order_status
        description: "Estado normalizado del pedido."
        data_tests:
          - not_null

      - name: order_total
        description: "Importe total del pedido."

# Crear proyecto
dbt init tutorial_dbt_bigquery

# Validar configuración
dbt debug

# Parsear proyecto
dbt parse

# Ejecutar todos los modelos
dbt run

# Ejecutar un modelo concreto
dbt run --select stg_customers

# Ejecutar un modelo y sus descendientes
dbt run --select stg_customers+

# Ejecutar tests
dbt test

# Ejecutar modelo y tests asociados
dbt build

# Generar documentación
dbt docs generate

# Servir documentación local
dbt docs serve