Cómo Optimizar Consultas SQL para BigData

La mayoría de los problemas de rendimiento en SQL para big data tienen la misma causa raíz: no pensar en cómo el motor va a leer los datos. Este artículo va al grano con las técnicas que más impacto tienen.

La regla de oro: reduce los datos que el motor tiene que leer

Antes de cualquier optimización, pregúntate: ¿cuántos bytes va a escanear esta query? En BigQuery puedes verlo antes de ejecutar. En Snowflake, usa EXPLAIN.

-- ❌ Escanea toda la tabla (200 GB)
SELECT user_id, SUM(revenue)
FROM transactions
GROUP BY user_id;

-- ✅ Escanea solo la partición del mes (8 GB)
SELECT user_id, SUM(revenue)
FROM transactions
WHERE DATE_TRUNC(transaction_date, MONTH) = '2025-03-01'
GROUP BY user_id;

1. Particionamiento: el mayor impacto por el menor esfuerzo

Si tus tablas no están particionadas, empieza por aquí. En BigQuery, particionar una tabla de transacciones por fecha puede reducir el costo de las queries hasta en un 90%.

-- Crear tabla particionada en BigQuery
CREATE TABLE `proyecto.dataset.transacciones`
PARTITION BY DATE(transaction_date)
CLUSTER BY user_id, category
AS SELECT * FROM transacciones_sin_particionar;

Clustering (el segundo campo) es como un índice secundario. Úsalo en las columnas que más aparecen en WHERE y GROUP BY después de la fecha.

2. Evita SELECT * en producción

Parece obvio, pero en analítica es común ver SELECT * en CTEs intermedias. Cada columna extra que no necesitas es costo innecesario en columnar databases.

-- ❌ Carga todas las columnas (tabla con 80 columnas)
WITH base AS (
  SELECT * FROM orders WHERE status = 'completed'
)

-- ✅ Solo las columnas necesarias
WITH base AS (
  SELECT order_id, user_id, total, created_at 
  FROM orders 
  WHERE status = 'completed'
)

3. Materializa CTEs costosas

Las CTEs (Common Table Expressions) en BigQuery y Snowflake NO son materializadas por defecto — se recomputan cada vez que se referencian. Si una CTE pesada se usa más de una vez, crea una tabla temporal.

-- ❌ Esta CTE se ejecuta dos veces
WITH usuarios_activos AS (
  SELECT DISTINCT user_id FROM events WHERE DATE(event_time) >= '2025-01-01'
)
SELECT u.*, COUNT(o.order_id) as total_orders
FROM usuarios_activos u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN returns r ON u.user_id = r.user_id  -- CTE se recomputa aquí
GROUP BY 1, 2;

-- ✅ Materializa si la CTE es costosa
CREATE TEMP TABLE usuarios_activos AS
SELECT DISTINCT user_id FROM events WHERE DATE(event_time) >= '2025-01-01';

4. JOINs: ordena las tablas por tamaño

En motores distribuidos, el orden de los JOINs importa. La tabla más grande va primero (a la izquierda), las dimensiones pequeñas van a la derecha.

-- ✅ Tabla de hechos grande LEFT JOIN dimensiones pequeñas
SELECT 
  t.transaction_id,
  u.country,
  p.category
FROM transactions t          -- ~500M filas (tabla de hechos)
LEFT JOIN users u ON t.user_id = u.user_id      -- ~2M filas (dimensión)
LEFT JOIN products p ON t.product_id = p.id     -- ~50K filas (dimensión)

5. Approximate functions para métricas de cardinalidad

Si necesitas contar usuarios únicos sobre millones de filas, COUNT(DISTINCT user_id) puede ser muy lento. APPROX_COUNT_DISTINCT tiene un error menor al 1% y es 10-50x más rápido.

-- ❌ Exacto pero lento sobre millones de filas
SELECT COUNT(DISTINCT user_id) as usuarios_unicos FROM events;

-- ✅ ~0.5% de error, 20x más rápido
SELECT APPROX_COUNT_DISTINCT(user_id) as usuarios_unicos FROM events;

Checklist de optimización

Antes de ejecutar una query pesada en producción:

• [ ] ¿La tabla está particionada? ¿El WHERE filtra por la columna de partición?
• [ ] ¿Estás usando SELECT *? Limítalo a las columnas necesarias
• [ ] ¿Las CTEs se referencian más de una vez? Considera materializar
• [ ] ¿El JOIN tiene la tabla grande a la izquierda?
• [ ] ¿Puedes usar APPROX_COUNT_DISTINCT en lugar de COUNT(DISTINCT)?

Aplicar estos cinco puntos de forma consistente es lo que redujo el gasto en BigQuery de un equipo con el que trabajé de $8,000/mes a $2,400/mes en tres meses.