SQL y modelado de datos que escala más allá del primer panel de control

He heredado tres de estos almacenes de datos y el diagnóstico siempre es el mismo. Hay una consulta que alguien pegó en un mensaje directo de Slack a las 4pm de un viernes. Marketing construyó un panel de control sobre ella. Ventas construyó otro panel de control sobre ese. Seis meses después, el CFO abre ambos en la misma reunión, ve dos números de ingresos diferentes y deja de confiar en el equipo de datos por el resto del trimestre.

La solución no es un SQL más inteligente. Es disciplina: capas, tests, nomenclatura y una cadencia de retirada gradual. Esta guía es esa disciplina, escrita para analistas que ya saben hacer combinaciones de tablas y quieren dejar de estar de guardia por «por qué este panel de control dice algo diferente».

La consulta del hilo de Slack

Así empieza. Alguien en #revops hace una pregunta. Usted escribe una consulta de 60 líneas contra la réplica de producción, deja la respuesta en el hilo y continúa. Esa consulta se copia en un notebook de Mode. El notebook de Mode se convierte en un tile de Looker. El tile de Looker se convierte en un gráfico del informe del consejo.

Noventa días después el esquema subyacente cambia, la consulta se rompe en silencio y nadie lo nota porque el gráfico sigue renderizando, solo con números incorrectos. O peor aún, la consulta nunca se rompió pero nunca tuvo una definición única de «cliente activo» y ahora tres equipos están mirando tres conteos diferentes.

La consulta del hilo de Slack está bien como consulta del hilo de Slack. El problema es la ruta de «búsqueda» a «panel de control estructuralmente necesario» sin ningún punto de control en el medio. Usted necesita un punto de control. El punto de control es un modelo.

Dos definiciones de ingresos son un evento de confianza

El día en que finanzas y producto aparecen en la misma reunión con dos números de ingresos diferentes, ha perdido un año de confianza y pasará seis meses recuperándola. Lo he visto ocurrir en tres empresas diferentes. No es un problema de herramientas. Snowflake no le falló. dbt no le falló. Lo que falló es que nadie tomó la decisión sobre qué significa «ingresos» antes de que cinco personas escribieran cinco consultas.

Esto se puede prevenir con ingeniería. Las capas y los tests previenen el problema de los dos ingresos. La nomenclatura y la retirada gradual previenen que el almacén de datos se degrade. El trabajo es aburrido a propósito. Si su stack de datos se siente emocionante día a día, probablemente algo está en llamas.

Prácticas de SQL que realmente escalan

Cuatro hábitos distinguen a los analistas que pueden hacer handoff de su almacén de datos de aquellos cuyo código muere en el momento en que se van.

CTE en lugar de subconsultas anidadas. Una subconsulta anidada es un artefacto de solo escritura. Usted puede escribirla, pero seis semanas después no puede leerla, y tampoco puede el siguiente analista. Los CTE le permiten nombrar cada paso, depurar cada paso y razonar sobre un modelo mental por bloque.

-- No haga esto
SELECT customer_id, SUM(amount)
FROM (
  SELECT * FROM orders
  WHERE status = 'completed'
    AND created_at >= (SELECT MIN(created_at) FROM orders WHERE ...)
) o
JOIN (SELECT * FROM customers WHERE deleted_at IS NULL) c
  ON o.customer_id = c.id
GROUP BY 1;

-- Haga esto
WITH completed_orders AS (
  SELECT *
  FROM {{ ref('stg_shopify__orders') }}
  WHERE status = 'completed'
),

active_customers AS (
  SELECT *
  FROM {{ ref('stg_shopify__customers') }}
  WHERE deleted_at IS NULL
),

revenue_by_customer AS (
  SELECT
    c.customer_id,
    SUM(o.amount) AS lifetime_revenue
  FROM active_customers c
  LEFT JOIN completed_orders o
    ON o.customer_id = c.customer_id
  GROUP BY c.customer_id
)

SELECT * FROM revenue_by_customer;

La segunda versión es más larga. También es depurable. Puede resaltar cualquier CTE, ejecutarlo solo y verificar el recuento de filas. Ese es todo el juego.

Tests con nombre en cada modelo. No «agregaré tests después». En cada modelo, en el mismo pull request. Los cuatro que justifican su existencia:

# models/marts/_marts.yml
version: 2

models:
  - name: dim_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['active', 'churned', 'paused']
      - name: account_owner_id
        tests:
          - relationships:
              to: ref('dim_users')
              field: user_id

Cuatro tests, quince líneas de YAML y detectará el noventa por ciento de las roturas silenciosas que de otro modo aparecerían en el informe del consejo. unique y not_null en cada clave primaria. accepted_values en cada columna con forma de enumeración. relationships en cada clave foránea. Si no prueba las claves, no tiene un modelo. Tiene una consulta.

Un único modelo de fuente de verdad por entidad de negocio. Un dim_customers. Un fct_orders. Un dim_users. No dim_customers_v2, no dim_customers_marketing, no dim_customers_for_finance_q3. Si marketing y finanzas necesitan cortes diferentes de clientes, obtienen diferentes columnas en el mismo dim_customers, o construyen marts posteriores sobre él. No se les permite clonar la tabla.

La clonación es cómo se obtiene el problema de los dos ingresos. Elimínela de inmediato.

Funciones de ventana en lugar de auto-combinaciones de tablas. Si está haciendo una auto-combinación de una tabla consigo misma para calcular «fecha del pedido anterior» o «posición dentro del cliente», está trabajando demasiado. LAG(), LEAD(), ROW_NUMBER() y RANK() hacen el mismo trabajo en un solo paso con la mitad de las líneas y una décima parte del costo en el almacén.

Capas del modelado de datos: staging, intermedia, marts

Esta es la parte que nadie quiere hacer porque parece trabajo extra. No es trabajo extra. Es el trabajo que hace posible todo lo que viene después.

Staging (stg_). Un modelo por tabla fuente. Renombre las columnas según la convención de su proyecto. Cambie los tipos. Nada más. Sin combinaciones de tablas, sin lógica de negocio, sin agregaciones. El trabajo de la capa de staging es dar a cada modelo posterior una vista limpia, predecible y nombrada de una tabla fuente. Si omite el staging porque «es solo un renombre», lo lamentará el día en que Stripe renombre una columna o su sincronización de Fivetran cambie la distinción entre mayúsculas y minúsculas.

-- models/staging/stripe/stg_stripe__charges.sql
WITH source AS (
  SELECT * FROM {{ source('stripe', 'charges') }}
),

renamed AS (
  SELECT
    id              AS charge_id,
    customer        AS customer_id,
    amount / 100.0  AS amount_usd,
    status          AS charge_status,
    created::TIMESTAMP AS charged_at
  FROM source
)

SELECT * FROM renamed;

Ese es el modelo completo. Es aburrido. Ese es el punto.

Intermedia (int_). Aquí viven las combinaciones de tablas, la lógica de negocio y los bloques reutilizables. Los modelos intermedios nunca se exponen a la herramienta BI. Existen para mantener legibles sus modelos de mart y para reutilizar piezas. Si tres marts necesitan «MRR mensual por cliente», eso es un modelo int_customer_mrr_monthly, construido una vez, referenciado tres veces.

-- models/intermediate/int_customer_mrr_monthly.sql
WITH subscriptions AS (
  SELECT * FROM {{ ref('stg_stripe__subscriptions') }}
),

months AS (
  SELECT * FROM {{ ref('int_calendar_months') }}
),

active_in_month AS (
  SELECT
    m.month_start,
    s.customer_id,
    s.monthly_amount_usd
  FROM months m
  LEFT JOIN subscriptions s
    ON m.month_start BETWEEN s.started_at AND COALESCE(s.canceled_at, '9999-12-31')
)

SELECT
  customer_id,
  month_start,
  SUM(monthly_amount_usd) AS mrr_usd
FROM active_in_month
GROUP BY 1, 2;

Marts (fct_ / dim_). Esto es lo que consultan las herramientas BI. Desnormalizados, legibles para el negocio y nombrados como los nombraría un ejecutivo. fct_ para tablas de hechos (pedidos, cobros, sesiones). dim_ para tablas de dimensiones (clientes, productos, empleados). Su capa de marts es la API de su almacén de datos. Trate los cambios en ella como cambios de API: versionados, comunicados, dados de baja con aviso previo.

Un equipo de datos de diez personas suele terminar con entre 200 y 400 modelos en estas tres capas. Aproximadamente 50-60% staging, 25-30% intermedia, 15-20% marts. Si el conteo de marts supera esa proporción, está haciendo lógica de negocio en marts que debería estar en la capa intermedia.

El diagnóstico de «dos definiciones de ingresos»

Puede ejecutar esto en veinte minutos. Hágalo antes de escribir un nuevo modelo en un almacén de datos que haya heredado.

1. Extraiga cada tile de panel de control, informe BI y consulta guardada que tenga la palabra «ingresos» en su título o columna.
2. Para cada uno, copie el SQL en un único documento.
3. Compare las cláusulas WHERE, las condiciones de combinación de tablas y los objetivos de SUM.
4. Haga una tabla: nombre del panel de control, propietario, definición, fuente.
5. Llévela a finanzas. Obtenga una definición comprometida por escrito.
6. Construya un modelo fct_revenue que coincida con la definición comprometida.
7. Migre cada panel de control a ese modelo. Dé de baja las consultas antiguas.

La mayoría de los equipos encuentran entre tres y cinco definiciones de ingresos diferentes en la primera auditoría. A veces todas son «correctas» para algún propósito específico, pero solo una de ellas es la que el CFO defenderá en una llamada de resultados. Esa es la que vive en el modelo. Las demás se reescriben como vistas sobre él (vw_revenue_marketing_attributed, vw_revenue_booking_basis) para que sea obvio que son cortes del mismo número, no números separados.

Ejecute la misma auditoría en «cliente activo», «abandono de clientes», «ARR» y «MRR». Encontrará el mismo problema. Corríjalo de la misma manera.

Convenciones de nomenclatura de dbt

La nomenclatura no es una discusión trivial. Es cómo el próximo analista se orienta en su almacén de datos sin tener que preguntarle.

• stg_{fuente}__{entidad}. El doble guión bajo separa la fuente de la entidad. stg_stripe__charges, stg_hubspot__contacts, stg_shopify__orders. El doble guión bajo es la señal visual de que el lado izquierdo es el nombre de un sistema.
• int_{entidad}_{verbo}. Describe lo que el modelo hace. int_orders_pivoted, int_customers_enriched, int_sessions_attributed.
• fct_{evento} y dim_{entidad}. Ejemplos: fct_orders, fct_charges, fct_page_views, dim_customers, dim_products, dim_dates.
• Alias de fuente en tests, no alias de columna en SQL de mart. Su dim_customers.customer_id debe ser customer_id, no dim_customers__customer_id.

El sufijo _v2 es una señal de alerta. Si ve dim_customers_v2.sql junto a dim_customers.sql, alguien tuvo miedo de romper el original y entregó una bifurcación. El control de versiones pertenece a Git, no al nombre del archivo. La acción correcta es: reescriba dim_customers, entregue el cambio con una nota de migración, dé de baja el comportamiento antiguo en un calendario. Mantener ambos para siempre garantiza que el problema de los dos ingresos regresará con un sombrero diferente.

Control de versiones y disciplina en el registro de cambios

Sin fusiones directas a main. Nunca. Ni siquiera para la corrección de un solo carácter. En el momento en que establece una excepción, esta se convierte en la regla.

Cada pull request tiene una entrada de una línea en el CHANGELOG.md:

## 2026-04-29
- `fct_orders`: columna `is_first_order` agregada. Afecta: paneles de control marketing-attribution, lifecycle-emails. (@camellia)
- `breaking: dim_customers`: `acct_owner` renombrado a `account_owner_id`. Afecta: todos los paneles de control de CRM. Migración: ver #1284. (@camellia)
- `stg_stripe__charges`: manejo de `customer_id` nulo en checkouts como invitado. (@camellia)

Tres reglas sobre el registro de cambios:

1. Cada pull request agrega una entrada. Ningún pull request se entrega sin una. El CI puede aplicar esto.
2. Los cambios de esquema se etiquetan como breaking: y se notifican en #data-changes antes de que se fusione el pull request.
3. El campo «afecta» nombra paneles de control o modelos posteriores, no frases vagas como «varios informes». Si no puede nombrar lo que afecta, no conoce suficientemente bien su grafo posterior para fusionar.

Los cambios de esquema también tienen una retirada gradual programada en el calendario. La columna antigua permanece durante 30 días con un comentario, la nueva columna se agrega en paralelo, los consumores posteriores migran y luego la columna antigua se elimina. Sin excepciones para renombres «fáciles». Los renombres fáciles son como descubre que alguien construyó un flujo de Zapier sobre su almacén de datos y nadie se lo dijo.

Panel de control frente a análisis puntual: cuándo construir qué

Regla de decisión: ¿se pedirá este número de nuevo dentro de 30 días?

• Sí → es un panel de control. El número vive detrás de un modelo. El panel de control tiene un propietario.
• No → envíe el CSV. Vincule el SQL en el hilo de la solicitud o en el pull request. Continúe.

El error es construir un panel de control para cada solicitud puntual. Seis meses después tiene 400 paneles de control, 350 de ellos no se han abierto desde el día en que se entregaron y el analista que los construyó dejó la empresa.

Los paneles de control tienen un campo de propietario. No «el equipo de datos». Un nombre. Si un panel de control existe sin un propietario nombrado, archívelo. El trabajo del propietario es responder «¿sigue siendo correcto esto?» cuando las partes interesadas preguntan. Si nadie es propietario, nadie puede responder y volvemos al problema de confianza.

El SQL puntual va en una carpeta analyses/ en su proyecto de dbt, comprometido y revisado pero sin modelar. Eso le da un rastro documentado sin inflar el almacén de datos.

Cadencia de retirada gradual

Los almacenes de datos se degradan como los áticos, lentamente y luego de golpe. La cadencia que lo previene:

• Revisión trimestral de paneles de control por fecha de última vista. La mayoría de las herramientas BI exponen esto. Extraiga una lista cada trimestre.
• 60 días sin vistas: banner de retirada gradual. Un mensaje visible de «este panel de control está programado para archivo el YYYY-MM-DD». Notifica al propietario. Fuerza una decisión.
• 90 días sin vistas: archivado. Muévalo a una carpeta archive/. Marque el modelo subyacente como enabled: false en la configuración de dbt para que deje de compilar.

# models/marts/legacy/_legacy.yml
models:
  - name: fct_orders_2024_legacy
    config:
      enabled: false
    description: |
      Archivado 2026-04-29, reemplazado por fct_orders.
      Última consulta: 2026-01-12. Propietario dejó la empresa Q4-2025.

Después de un año con esta cadencia, reducirá el conteo de modelos entre un 30-40% sin ninguna queja posterior. Los modelos que eliminó ya estaban inactivos. Solo está haciéndolo oficial para que dejen de costarle cómputo y de confundir a los nuevos analistas.

Errores comunes

Los cuatro que veo con más frecuencia, ordenados por cuánto daño causan:

1. Modelar antes de acordar las definiciones. Haga la auditoría. Obtenga la definición. Luego construya el modelo. El otro orden produce el problema de los dos ingresos en un almacén de datos nuevo.
2. Omitir staging porque «es solo un renombre». Omitir staging significa que cada modelo posterior se rompe el día en que un esquema fuente cambia. El staging es el amortiguador. Pague el costo aburrido.
3. Dejar que los archivos _v2 se multipliquen. Cada _v2 es una confesión de que alguien tuvo miedo de romper el original. Corrija el original. Comunique el cambio. Entregue un modelo.
4. Ningún propietario en modelos críticos. Un fct_revenue sin propietario es una responsabilidad con un archivo SQL adjunto. El campo de propietario es obligatorio en el YAML de su modelo, igual que los tests.

Cómo se ve «terminado»

Habrá construido esta disciplina cuando:

• Cada métrica de negocio tiene exactamente un modelo que la define.
• Cada modelo tiene al menos tests unique y not_null en su clave primaria.
• Cada panel de control tiene un propietario nombrado.
• Un nuevo analista puede rastrear cualquier número en cualquier panel de control hasta su fuente (a través de marts, intermedia, staging, fuente) en menos de diez minutos, sin preguntarle a nadie.
• El registro de cambios le dice qué cambió la semana pasada sin abrir Slack.
• Su gasto en cómputo es estable o decrece mientras el conteo de modelos crece, porque dio de baja lo que estaba inactivo.

Ese es el estándar. Es aburrido. También es cómo deja de ser el equipo al que llaman a las 11pm porque el informe del consejo no cuadra.

Más recursos

• Plantilla de descripción de puesto: Data Analyst
• Traducción para partes interesadas: de solicitudes vagas a análisis entregables
• Diseño de paneles de control que impulsan decisiones
• Errores comunes de los Data Analysts