Disenar Arquitectura Datasets Tablas Bigquery

Diseñar arquitectura de datasets y tablas en BigQuery (Detrics)

Estás ayudando a un equipo de datos a diseñar su arquitectura de data warehouse de marketing en BigQuery, alimentada por transferencias de Detrics. Esta habilidad es una herramienta de diseño: tu trabajo es entender el caso, presentar las opciones con sus trade-offs, y producir un diseño implementable — datasets, table groups, tablas con campos exactos, configuración de transferencias y la capa de consolidación que alimenta los dashboards.

El resultado es un diseño en el que todo el equipo puede apoyarse — explícito, documentado y reutilizable — en lugar de una arquitectura que vive en nombres de carpetas y en la cabeza de quien todavía no renunció.

El modelo mental de Detrics (aprendé esto primero)

Detrics sincroniza datos de marketing hacia el proyecto de BigQuery del propio usuario. Cuatro entidades (glosario):

Modos de sincronización (docs):

• Incremental (append + dedup) — el default para series de tiempo. Re-consulta una ventana móvil (ej. últimos 9–15 días para Meta, porque la atribución sigue cambiando) y reemplaza solo esa ventana. El más barato y correcto para métricas diarias.
• Full refresh — reemplaza la tabla completa. Para snapshots: configuración de campañas, catálogos de productos.
• Full append — nunca borra. Para trails de auditoría / evolución de métricas.

Rango histórico: el backfill inicial que se hace una sola vez (hasta años de historia). Recomendá rangos largos (12–36 meses) solo para esquemas de los que el usuario está seguro — el backfill es lento y debería pasar una única vez.

Documentación clave para consultar o compartir con el usuario:

• Catálogo de campos (todas las plataformas): https://support.detrics.io/fields-catalog
• Campos por plataforma: https://support.detrics.io/data-source/<slug>/fields (ej. facebook-ads, google-ads, google-analytics-4, tiktok-ads, shopify)
• Docs de BigQuery: https://support.detrics.io/bigquery/introduction (modos de sync, calendarios, nombres de columnas, monitoreo)
• Configuración del MCP: https://support.detrics.io/mcp/overview

Anclá el diseño en la realidad vía el MCP de Detrics

Si el usuario tiene el MCP de Detrics conectado (servidor: https://mcp.detrics.io/mcp), usalo antes de proponer nada:

1. list_connections — qué plataformas tiene realmente conectadas.
2. list_accounts — cuántas cuentas por conexión (esto define el diseño multi-cliente de abajo).
3. list_contexts / get_context — cómo organizó sus clientes en contextos de AI, si lo hizo.
4. list_fields — la lista autoritativa de campos por plataforma. Validá contra ella cada campo que propongas. Nunca inventes nombres de campos.
5. query_marketing_data — opcionalmente traé una muestra chica (una cuenta, últimos 7 días) para verificar que una combinación de campos propuesta devuelve lo que el usuario espera.

Si el MCP no está conectado, ayudalo a conectarlo (https://support.detrics.io/mcp/overview) o avanzá con las respuestas de la entrevista y validá los campos contra las páginas públicas del catálogo.

Entrevistá al usuario

Preguntá (adaptate, no interrogues):

1. Forma del negocio — ¿agencia con muchos clientes o marca única? ¿Cuántos clientes? ¿Cuántas cuentas por plataforma?
2. Fuentes de datos — qué plataformas, y cuáles importan más.
3. Consumidores — qué dashboards/reportes tienen que existir. Quién los lee. ¿Consumo por AI/chat?
4. Granos necesarios — ¿alcanza nivel campaña? ¿Nivel anuncio/creativo? ¿Reportes de creativos con imágenes?
5. Historia — hasta dónde deben llegar las comparaciones (YoY necesita 24+ meses).
6. Tagging y atribución — ¿auto-tagging de Google Ads habilitado en GA4? ¿Hay gobernanza de UTMs? ¿Usan utm_id? (Esto abre o cierra opciones de unificación.)
7. Equipo — quién mantiene esto. ¿Riesgo de rotación? (Define cuán explícito debe ser el entregable.)
8. Casuísticas — pedidos especiales por cliente que se repiten. ¿Algún cliente usa conversiones custom (Meta), dimensiones/métricas custom (GA4) o métricas custom (Klaviyo)?

El principio central: tres capas (esto sí es opinado)

Lo único no negociable del diseño es la separación en capas entre datos crudos y datos consolidados:

Capa 1 — CRUDA:         tablas de transferencias Detrics, una por fuente × grano
                        (facebook_ads_campaign_performance_daily, ga4_campaign_performance_daily, …)
Capa 2 — CONSOLIDACIÓN: vistas / queries de SQL que unifican las tablas crudas
Capa 3 — MASTER:        tablas listas para dashboards. Un dashboard consume EXACTAMENTE UNA tabla.

Un dashboard debe consumir una sola tabla — un esquema master por cada N fuentes de datos. Nunca apuntes un dashboard a cuatro tablas crudas para mezclarlas en la herramienta de BI; la mezcla vive en SQL, donde queda versionada, testeable y reutilizable. La única excepción: un esquema que genuinamente sirve a una sola fuente (ej. una tabla operativa solo-Shopify) — ahí una fuente → un esquema está bien.

Creá la capa de consolidación con un MCP de BigQuery (mucho más rápido)

La capa 1 la crean las transferencias de Detrics. Las capas 2 y 3 las podés crear vos, directamente, si el usuario tiene un MCP de BigQuery conectado (el conector de BigQuery de claude.ai, el MCP oficial de Google Cloud, o cualquier equivalente):

1. Listá las tablas del dataset para confirmar que las tablas crudas ya existen (las crean las transferencias al correr).
2. Ejecutá los CREATE OR REPLACE VIEW ahí mismo.
3. Verificá con un SELECT de control (¿devuelve filas? ¿los totales cierran contra una tabla cruda?).
4. Iterá hasta que quede bien.

Esto es mucho más rápido que entregar SQL para que el usuario copie y pegue en la consola — el ciclo escribir → correr → corregir pasa de días a minutos. Si no hay MCP de BigQuery disponible, entregá los .sql listos para correr y sugerí conectar uno.

Decisiones de diseño: presentá opciones, no dogma

Todo lo que sigue son opciones con trade-offs. Tu trabajo no es imponer una — es presentarlas, recomendar según el caso concreto, y dejar la elección y su porqué escritos en el entregable. Cada empresa combina transferencias, fuentes, destinos y table groups a su manera; el diseño correcto depende de su realidad.

Unificación de fuentes — tres opciones por par de fuentes

Opción 1 — JOIN por ID compartido (cuando existe una clave real):

• Google Ads ↔ GA4 con auto-tagging: GA4 expone sessionGoogleAdsCampaignId (y sessionGoogleAdsCampaignName, googleAdsCustomerId), que coincide con campaign.id de Google Ads. Si el auto-tagging está habilitado, no requiere trabajo de tagging.
• Meta / TikTok / otras ↔ GA4 vía utm_id: GA4 expone sessionCampaignId, que carga lo que llegue en utm_id. Si el equipo pone el ID de campaña en sus URLs (ej. en Meta: utm_id={{campaign.id}} con macros dinámicos), el join es confiable.
• Da un master ancho a grano fecha × campaign_id, con costo y sesiones/revenue en la misma fila → ROAS y CPA por campaña a nivel fila.
• Cuidado con el fan-out de grano: uní GA4 a nivel campaña contra tablas de ads a nivel campaña (1:1 por fecha); nunca contra una tabla a nivel anuncio sin agregar primero.

Opción 2 — JOIN por convención de nombres: utm_campaign == nombre exacto de campaña en la plataforma. Funciona solo con gobernanza de nombres sostenida; cualquier renombre rompe el join en silencio. Si el equipo la elige, la convención queda escrita en el entregable como regla operativa.

Opción 3 — UNION ALL apilado (robusto sin clave): grano común fecha × cuenta × campaña × plataforma; cada fuente llena sus columnas y deja NULL el resto; una columna literal platform etiqueta cada fila. Las métricas blended (ROAS = SUM(revenue)/SUM(cost)) agregan bien en cualquier pivote sin claves.

Los diseños reales suelen combinar opciones: LEFT JOIN de GA4 sobre las filas de ads donde hay ID compartido, y UNION para las fuentes sin clave. Decidí por par de fuentes, según el tagging que tenga el equipo. Ver references/ejemplo-vista-master.sql con ambos patrones funcionando.

Multi-cliente — dos patrones

También hay híbridos válidos (compartido para la base + datasets aparte para clientes con requisitos de aislamiento). Toda tabla de Detrics trae _detrics_account_id, la clave de join del client map.

Organización de esquemas — una división que funciona bien

• Table groups base: los esquemas que recibe todo cliente (performance de campañas diario por plataforma, creativos para plataformas de ads). Se construyen una vez, se reutilizan.
• Casuísticas por cliente: los pedidos especiales viven aparte, con nombre claro por cliente.

Presentala como punto de partida; el equipo puede tener otra división que le sirva mejor.

Custom fields — campos propios de cada cuenta (GA4, Meta Ads, Klaviyo)

Algunas plataformas permiten sincronizar campos definidos por el usuario, además del catálogo estándar (docs):

• Google Analytics 4: dimensiones y métricas custom definidas en la propiedad (ej. user_type, content_category).
• Meta Ads: conversiones custom y offline event sets.
• Klaviyo: métricas de conversión custom de flows y campañas.

Los custom fields se descubren por cuenta (en el editor de tabla: Load Custom Fields → elegir una cuenta) porque cada cuenta puede tener campos distintos. Se normalizan a snake_case en BigQuery como cualquier campo.

La pieza clave para el diseño multi-cliente es el manejo sparse de columnas: si la cuenta A tiene la dimensión custom user_segment y la cuenta B no, la columna existe igual en la tabla — las filas de A la llenan y las de B quedan en NULL. BigQuery maneja columnas sparse nativamente, sin impacto de performance. Eso habilita dos opciones de diseño:

• Opción 1 — un table group con custom fields por cliente, enganchado a una transferencia específica de ese cliente. Aísla la casuística: el esquema custom vive junto al cliente que lo pidió.
• Opción 2 — una sola tabla con todos los clientes y todos sus custom fields juntos: una transferencia (incluso sync-all) contra un table group que incluye los custom fields de varias cuentas; cada cliente llena los suyos y deja NULL los ajenos. El sistema lo permite y evita la proliferación de tablas — a cambio de una tabla más ancha.

Elegí según cuántos clientes tienen custom fields y si el reporting los cruza: pocos clientes con campos muy distintos → opción 1; muchos clientes con reporting estandarizado que suma algunos campos custom → opción 2.

Ciclo de vida

Si el equipo sufre cambios que rompen reportes en vivo, sugerí separar desarrollo de activo (en nombres, carpetas o datasets): lo activo se cambia coordinando; lo de desarrollo, libremente.

Nombres

Una convención que funciona: tablas crudas <plataforma>_<entidad>_<grano> (ej. facebook_ads_campaign_performance_daily); masters con el nombre de su consumidor (marketing_master, <cliente>_master). Un grano por tabla, declarado. Si el equipo ya tiene convención propia, respetala — lo importante es que haya una y esté escrita.

Vistas o materializadas

Arrancar con vistas (siempre frescas, cero mantenimiento) y materializar con queries programadas solo cuando los dashboards se ponen lentos o el costo de query importa es el camino de menor riesgo. Si el equipo ya sabe que su volumen lo exige, materializá desde el día uno.

Qué hace cada cambio (incluí esta tabla en el entregable)

Para que cualquier persona del equipo — incluida la que entró ayer — sepa qué pasa antes de tocar un esquema activo:

Gotchas de plataforma para codificar en los diseños

• Atribución de Meta: los datos mutan ~15 días; ventana de refresco de 9–15 días en tablas incrementales.
• Costo de Google Ads: Detrics entrega cost_micros ya convertido a unidades de moneda — no dividir por 1e6 en el SQL de consolidación.
• GA4 no tiene columna de nombre de cuenta — mapeá vía _detrics_account_id.
• Joins con GA4: para que el join por ID funcione, la dimensión de ID (sessionGoogleAdsCampaignId o sessionCampaignId) tiene que estar incluida en la tabla del table group de GA4 — si no se sincroniza, no existe en BigQuery.
• Imágenes de creativos: Detrics puede persistir las imágenes de anuncios en el bucket de GCS del usuario (las URLs de los CDN de las plataformas expiran en días). Si hay reporte de creativos en el alcance, habilitá la persistencia de imágenes en el destino e incluí la dimensión de URL de imagen en las tablas a grano anuncio.

Entregables — elegí el formato con el usuario

El contenido es siempre el mismo: el diseño con sus decisiones y porqués, las specs de table groups y transferencias, y el SQL de consolidación. El envase depende del equipo — presentá las opciones:

Opción A — Documento de arquitectura: un markdown único (plantilla en references/plantilla-documento-arquitectura.md). Alcanza para equipos chicos o primeras versiones.

Opción B — Repo data-infrastructure: para equipos que quieren que las decisiones sobrevivan a la rotación — git aporta historia, diffs y review. Estructura sugerida:

data-infrastructure/
├── README.md                      ← el documento de arquitectura + la tabla de cambios
├── table-groups/
│   ├── base/*.yml                 ← esquemas compartidos (todos los clientes)
│   └── clientes/<cliente>/*.yml   ← casuísticas
├── transfers/*.yml                ← cuentas, destino, calendario, ventana
├── masters/*.sql                  ← un archivo por master
└── client_map.csv                 ← _detrics_account_id → cliente

Prácticas que podés sugerir si eligen este formato (opcionales, no reglas): cambios a esquemas activos vía pull request (el diff + la tabla de cambios dicen exactamente qué pasa); CODEOWNERS por carpeta de cliente si cada persona es responsable de sus clientes; reflejar en el repo lo que se cambie en la app para que no diverjan.

Opción C — Spec JSON legible por máquina: por grupo platformApi, name, tables[] con name, metrics, dimensions, timeAggregation, syncMode, historicalSyncRange — útil como spec de implementación precisa.

Y en todos los casos: el SQL de consolidación — ejecutado directamente vía MCP de BigQuery si está disponible (ver arriba), o entregado como archivos .sql.

Después el usuario implementa en la web app de Detrics (https://app.detrics.io): crea los destinos, crea los table groups desde la spec, crea las transferencias. Los destinos, las conexiones y la selección de cuentas son siempre decisión del usuario — cargan credenciales y costo; el diseño propone, el humano cablea.

Reglas de validación

• Todo campo de toda tabla propuesta DEBE existir en el catálogo de la plataforma (list_fields vía MCP, o las páginas de docs). Sin excepciones.
• Excepción única: los custom fields (GA4, Meta Ads, Klaviyo) no están en el catálogo estático porque son por cuenta — marcalos explícitamente como custom en el diseño y aclarà que se descubren y seleccionan por cuenta en el editor de tabla (Load Custom Fields).
• Toda columna de un master DEBE ser trazable a una columna de una tabla cruda — si ninguna transferencia la provee, o agregás el campo a un table group o eliminás la columna, y decís cuál de las dos hiciste.
• Los joins por ID solo se proponen si la dimensión de ID está incluida en las tablas de ambos lados.
• Antes de crear una vista vía MCP de BigQuery, verificá que las tablas crudas ya existen en el dataset — una vista sobre tablas inexistentes falla.
• Declará explícitamente el grano de cada tabla y de cada master.