Optimizacion

BigQuery: optimización de rendimiento y costo

Nota de actualización frente al material original

Antes de entrar al contenido, estas son las correcciones más importantes que ya dejé integradas en esta versión:

• Hoy conviene explicar el cómputo de BigQuery como on-demand vs capacity-based pricing. La antigua narrativa de “flat-rate” quedó como referencia heredada para clientes con compromisos existentes; la página oficial de precios hoy publica el modelo on-demand a USD 6.25 por TiB después del primer 1 TiB gratuito mensual. (Google Cloud)
• BI Engine ya no se describe con un máximo de 10 GB; el máximo predeterminado actual es 250 GiB por proyecto y ubicación, con posibilidad de solicitar aumento. (Google Cloud Documentation)
• El límite total de particiones por tabla es 10,000; el valor de 4,000 sigue existiendo, pero corresponde al máximo de particiones que puede modificar un solo job. (Google Cloud Documentation)
• Las materialized views ya no están en beta y deben residir en la misma organización que sus tablas base, o en el mismo proyecto si no hay organización. (Google Cloud Documentation)
• Para nuevos flujos de streaming, Google recomienda Storage Write API sobre tabledata.insertAll; para lecturas masivas, Storage Read API sigue siendo la vía rápida y hoy queda habilitada cuando ya está habilitada la BigQuery API. (Google Cloud Documentation)

---

Objetivo

Optimizar BigQuery no significa escribir SQL más críptico. Significa hacer menos trabajo útilmente: leer menos datos, mover menos datos entre stages, evitar sorts y joins innecesariamente caros, y reutilizar resultados cuando el patrón de consulta se repite. En on-demand, eso suele bajar costo porque pagas por bytes procesados; en capacity-based, suele bajar slot time, contención y colas. (Google Cloud Documentation)

1. Mide antes de optimizar

La primera regla es simple: no empieces reescribiendo SQL sin saber dónde está el problema. BigQuery hoy te da cuatro herramientas principales para eso: dry run para estimar bytes antes de ejecutar, maximum bytes billed para poner un tope por consulta, Execution graph / Query Insights para ver stages y cuellos de botella, e INFORMATION_SCHEMA.JOBS* para auditar qué consultas gastan más bytes o más slot time. Query Insights además puede señalar problemas como slot contention, high-cardinality joins, insufficient shuffle quota y data skew. (Google Cloud Documentation)

Un patrón útil para empezar es revisar las consultas más pesadas de los últimos días:

SELECT
  job_id,
  user_email,
  total_bytes_processed,
  total_slot_ms,
  query_info.performance_insights,
  query_info.optimization_details
FROM `mi-proyecto`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND error_result IS NULL
ORDER BY total_slot_ms DESC
LIMIT 20;

Si la consulta es cara en bytes, normalmente el problema está en I/O. Si la consulta no lee tanto pero consume mucho total_slot_ms, suele haber más peso en joins, sorts, skew, UDFs o concurrencia. Esa distinción te evita optimizar en la dirección equivocada. (Google Cloud Documentation)

2. Reduce bytes leídos primero

BigQuery es columnar. Por eso, una de las optimizaciones más rentables sigue siendo la más simple: leer menos columnas y menos particiones. La documentación oficial sigue insistiendo en dos reglas clave: evita SELECT * cuando no lo necesitas, y recuerda que LIMIT por sí solo no reduce los bytes facturados en consultas on-demand. Para bajar lectura de verdad, usa partition pruning y, cuando aplique, clustering. (Google Cloud)

En tablas particionadas, evita filtros que “envuelvan” la columna de partición de forma que el motor no pueda podar particiones con facilidad. Esta diferencia importa mucho:

-- Menos favorable para pruning
WHERE EXTRACT(YEAR FROM order_date) = 2025

-- Mejor para pruning
WHERE order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2026-01-01'

Si además sabes que la mayoría de tus consultas filtran por ciertas claves de alta cardinalidad, añade clustering. BigQuery usa esas columnas para organizar bloques y puede saltarse bloques completos cuando el filtro coincide con el orden de clustering. También conviene activar require_partition_filter cuando una tabla particionada es propensa a consultas accidentales de “full scan”. (Google Cloud Documentation)

3. Reduce shuffle y cómputo caro

Muchas consultas lentas no fallan por leer mucho, sino por barajar demasiados datos entre stages. Los sospechosos habituales son los joins grandes, los GROUP BY sobre claves sesgadas, los sorts globales y ciertos self-joins. La guía oficial recomienda preagregar antes de hacer JOIN, evitar repetir el mismo subquery caro, y considerar nested/repeated fields cuando la relación sea jerárquica y se consulte junta con frecuencia. También documenta que, cuando la tabla grande está a la izquierda y la pequeña a la derecha, BigQuery puede usar broadcast join. (Google Cloud Documentation)

Un patrón práctico es persistir un resultado intermedio cuando lo vas a reutilizar varias veces:

CREATE TEMP TABLE daily_events AS
SELECT
  user_id,
  DATE(event_ts) AS event_date,
  COUNT(*) AS events
FROM raw.events
WHERE event_ts >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY user_id, event_date;

SELECT
  d.event_date,
  u.country,
  SUM(d.events) AS total_events
FROM daily_events AS d
JOIN dim_users AS u
USING (user_id)
GROUP BY d.event_date, u.country;

Esto es especialmente importante porque un CTE (WITH) no garantiza materialización. BigQuery lo documenta explícitamente: los CTEs existen para legibilidad, no para performance, y pueden evaluarse varias veces según las decisiones del optimizador. Si el cálculo es caro y reutilizado, persístelo en una tabla temporal, de destino o una materialized view. (Google Cloud Documentation)

También conviene modernizar dos recomendaciones clásicas. La primera: para UDFs, prefiere SQL UDFs cuando la lógica sea simple, porque el optimizador puede trabajar sobre ellas; las JavaScript UDFs suelen consumir más recursos y degradar el rendimiento. La segunda: cuando aceptas un pequeño error estadístico, usa APPROX_COUNT_DISTINCT, APPROX_QUANTILES, APPROX_TOP_COUNT, APPROX_TOP_SUM o sketches como HLL++ para reducir memoria y tiempo en agregaciones grandes. (Google Cloud Documentation)

4. Reutiliza resultados cuando el patrón se repite

No toda optimización viene de tocar el SQL. A veces la mejor mejora es no recalcular lo mismo una y otra vez. En BigQuery hoy tienes tres niveles claros para eso: query cache, materialized views y BI Engine. El cache de resultados dura aproximadamente 24 horas y solo se reutiliza cuando el texto de la consulta es un duplicado exacto; es útil para exploración repetida, pero no debe ser la base de un pipeline productivo. (Google Cloud Documentation)

Las materialized views son la opción correcta cuando tienes un patrón recurrente y estable de agregación o filtrado. Físicamente almacenan resultados precomputados, BigQuery puede hacer smart tuning / query rewrite para reusarlas, pero siguen teniendo SQL restringido, costos de almacenamiento y refresh, y reglas de ubicación/organización que debes respetar. (Google Cloud Documentation)

CREATE MATERIALIZED VIEW mart.daily_sales_mv AS
SELECT
  sale_date,
  product_id,
  SUM(amount) AS revenue
FROM raw.sales
GROUP BY sale_date, product_id;

Para dashboards y cargas BI repetitivas, el complemento natural es BI Engine, que acelera consultas con caché en memoria. Hoy el máximo predeterminado es 250 GiB por proyecto y ubicación, no 10 GB. (Google Cloud Documentation)

5. Modela el almacenamiento según cómo consultas

El mejor tuning suele empezar antes de escribir la consulta. Si filtras por fecha, particiona. Si además filtras por claves selectivas, clusteriza. Si la relación es jerárquica y se consulta junta, usa nested and repeated fields. La documentación oficial sigue recomendando nested/repeated como estrategia de desnormalización cuando eso evita joins repetidos y mejora rendimiento de lectura. (Google Cloud Documentation)

También vale la pena declarar primary key y foreign key constraints cuando tus datos realmente cumplan esas reglas. BigQuery no las aplica físicamente como restricciones enforced, pero sí puede usar esa información para optimizar planes. (Google Cloud Documentation)

Una plantilla útil para muchos hechos grandes es esta:

CREATE TABLE mart.events
PARTITION BY DATE(event_ts)
CLUSTER BY customer_id, country AS
SELECT * FROM raw.events;

Al actualizar el capítulo original, también conviene dejar claras dos notas operativas. Primero, una tabla particionada puede tener hasta 10,000 particiones, y un esquema de range partitioning puede definir hasta 10,000 rangos; el límite de 4,000 aplica al número de particiones que modifica un solo job. Segundo, los table decorators pertenecen a legacy SQL; en GoogleSQL, para time travel el equivalente moderno es FOR SYSTEM_TIME AS OF, y para el resto normalmente conviene modelar con tablas particionadas. (Google Cloud Documentation)

Cuando el patrón de acceso es búsqueda sobre texto o JSON, otra palanca moderna son los search indexes. BigQuery los usa para acelerar búsquedas con SEARCH y ciertos patrones soportados sobre columnas STRING, ARRAY<STRING>, STRUCT y JSON. (Google Cloud Documentation)

6. Controla costo e ingestión de forma explícita

Si tu organización usa on-demand, la prioridad es controlar bytes procesados con dry run, maximum bytes billed y custom cost controls a nivel de usuario o proyecto. Si usa capacity-based, la prioridad es dimensionar bien reservations, autoscaling y concurrencia para que el slot budget no se desperdicie ni se sature. BigQuery documenta ambos modelos como los dos esquemas actuales de workload management. (Google Cloud Documentation)

Para cargas de datos, separa bien dos escenarios. Si toleras algunos minutos de latencia, los batch load jobs siguen siendo una excelente opción y son gratuitos cuando usan el shared slot pool. Si necesitas streaming o baja latencia, para proyectos nuevos conviene empezar por Storage Write API, que unifica streaming y batch, ofrece mejor funcionalidad y puede dar exactly-once semantics con offsets en streams creados por la aplicación. (Google Cloud)

Para lectura masiva desde herramientas externas, notebooks o librerías cliente, Storage Read API sigue siendo la vía rápida. Además, hoy ya no necesitas activarla aparte si BigQuery API ya está habilitada en el proyecto. (Google Cloud Documentation)

Y una corrección útil del material viejo: batch priority ya no es una opción “más barata”, sino una opción para trabajo menos sensible al tiempo. Las batch queries tienen menor prioridad y son más propensas a quedar en cola; una vez que empiezan, corren igual que una interactiva. También suelen ser la mejor opción cuando quieres separar cargas no urgentes del trabajo interactivo diario. (Google Cloud Documentation)

Checklist de diagnóstico rápido

Cuando una query vaya lenta o salga cara, usa este mapa mental:

• Input stage lento o bytes muy altos: reduce columnas, evita SELECT *, usa filtros que permitan partition pruning, activa require_partition_filter y considera clustering. (Google Cloud)
• Mucho shuffle o joins caros: preagrega antes del JOIN, reduce filas temprano, reutiliza resultados intermedios, y usa nested/repeated fields cuando la relación sea jerárquica. (Google Cloud Documentation)
• Resources exceeded o sorts globales: limita ORDER BY globales, reparte el trabajo por particiones lógicas, revisa data skew y evita patrones que obliguen a un solo worker a ordenar demasiado. (Google Cloud Documentation)
• La misma lógica se ejecuta muchas veces: apóyate en query cache, tablas temporales, tablas de destino, materialized views o BI Engine según el caso. (Google Cloud Documentation)
• El problema es costo: usa dry run, maximum_bytes_billed, cost controls, elige bien entre on-demand y capacity-based, y mueve procesos no urgentes a batch o schedules. (Google Cloud Documentation)

Cierre

La versión corta es esta: en BigQuery, casi todo el rendimiento sale de leer menos, mover menos, persistir solo lo que conviene y modelar el almacenamiento de acuerdo con el patrón real de consulta. Si mides primero y luego atacas bytes leídos, shuffle, reutilización y modelado, normalmente resuelves la mayor parte de los problemas sin volver el SQL inmantenible. (Google Cloud Documentation)

Puedo hacerte la misma conversión ahora en una versión más pedagógica para curso, o en una más ejecutiva para guía interna de equipo.