Bloque 2 — Desarrollo de Consultas

Los ejemplos usan el dominio de demo (flujos migratorios de estorninos), servido por el motor de sources nativo de Carbon (no Fivetran — no hay columnas _fivetran_*; consultas las columnas de negocio directamente). Recuerda la convención de coordenada: en FROM el nombre resuelve solo; en filtros de datos usa tu coordenada real (SHOW SCHEMAS).

0. Cómo se resuelve una consulta — el modelo de 3 fases

Cuando envías una consulta, no ocurre solo un "validar y correr". Ocurren tres cosas, y la frontera entre ellas es lo que hace el diseño sostenible:

Consulta del usuario
   │
   ├─ ①  RESOLVER   nombres → dataset real vía el catálogo          ┐
   │        bare · catalog.schema.tabla · display-name              │  DURABLE
   │                                                                │  (el lenguaje
   ├─ ②  VALIDAR    seguridad: solo-lectura · tenencia por usuario  │   + el contrato)
   │        (created_by) · la tabla existe                          ┘
   │
   └─ ③  EJECUTAR   correr la consulta resuelta contra los datos    ← AQUÍ INVIERTE KARMA
            hoy: CTE-shim sobre Postgres  →  mañana: nativo Iceberg    (desechable)

• ① Resolver y ② Validar son el lenguaje y el contrato de seguridad del Warehouse. No dependen de con qué motor ejecutes. Son lo que este bloque construye y endurece.
• ③ Ejecutar es el ejecutor. Hoy es DuckDB sobre Iceberg, por la puerta (Junction). El shim de traducción a CTE Postgres que ocupaba este sitio se retiró en el F3-RETIRO (2026-07-10). El ejecutor es intercambiable por política de la puerta; la frontera ①②|③ es lo que no cambia.

1. Direccionamiento durable — catalog.schema.tabla

El direccionamiento es la pieza más durable del lenguaje: es idéntico al namespacing nativo de Iceberg, así que Karma lo habla sin traducir. Tres formas de nombrar una tabla, todas resueltas por la fase ①:

La FQN sin comillas (a.b.c) no es un identificador válido de Postgres, así que la fase ① la reescribe a su segmento tabla y resuelve por el catálogo (rewriteDottedFromRefs). Si el catalog.schema no casa con tu coordenada real (p. ej. pegas workspace.public.x estilo Databricks), cae al segmento pelado y resuelve igual. En Karma la FQN es nativa: no habrá reescritura.

Cross-catalog / cross-schema (punto 26 de Databricks). El mismo direccionamiento cubre consultar tablas de distintos catálogos/esquemas en una misma query — cada FROM/JOIN se resuelve independientemente:

SELECT f.especie_nombre_comun, i.prioritaria
FROM main.default.flujos_migratorios_estorninos f
JOIN main.default.informes_especies i
  ON i.especie = f.especie_nombre_comun;

2. El set de comandos

El vocabulario que la consola del Warehouse entiende hoy. Los verbos de query son el corazón del desarrollo de consultas; los de metadatos y DDL curado se documentan en sus bloques y se listan aquí por completitud del lenguaje.

Todo lo que no sea un comando curado o un SELECT/WITH/EXPLAIN se rechaza en la fase ② (modo interno = solo lectura).

3. Los 35 puntos de Databricks — cobertura

Databricks organiza "Desarrollo de Consultas" en 35 áreas. Este es el mapa de cobertura del lenguaje del Warehouse. Leyenda de estado:

• ✅ hoy — expresable y ejecuta hoy (vía el shim PG); Karma lo hará nativo.
• 🌱 lenguaje durable — elemento del lenguaje/direccionamiento que este bloque fija.
• ⚠️ parcial — expresable con equivalente PG; la forma Spark nativa llega con Karma.
• 🔜 Karma — el lenguaje lo admite, pero la ejecución seria es del ejecutor nativo.
• ↗ otro bloque — pertenece a otro de los 13 bloques (cross-ref).

Lectura del mapa: de los 35, la mayoría son SQL que el lenguaje ya expresa y ejecuta hoy sobre el shim; 5 son elementos durables que este bloque fija (2·vistas-como-objeto-del-catálogo, 8·EXPLAIN, 22·metadata, 26·direccionamiento, 30·lineage); el resto son inversión de Karma (motor nativo, time-travel, geoespacial, sampling, semi-estructurado). Ninguno pide hacer el shim "más motor" — piden fijar el lenguaje.

4. Capacidades propias del bloque (ejemplos nativos)

4.1 Subconsultas y derived tables

-- Scalar: comparar contra un agregado global
SELECT especie_nombre_comun, velocidad_promedio_kmh
FROM bronze_flujos
WHERE velocidad_promedio_kmh > (SELECT AVG(velocidad_promedio_kmh) FROM bronze_flujos)
ORDER BY velocidad_promedio_kmh DESC;

-- EXISTS correlacionada: especies con al menos un informe
SELECT DISTINCT f.especie_nombre_comun FROM bronze_flujos f
WHERE EXISTS (SELECT 1 FROM bronze_informes i WHERE i.especie = f.especie_nombre_comun);

-- Derived table: el alias externo (t) NO se confunde con una tabla del Warehouse
SELECT t.clima, t.n, ROUND(100.0 * t.n / SUM(t.n) OVER (), 2) AS pct
FROM (SELECT clima, COUNT(*) AS n FROM bronze_flujos GROUP BY clima) t
ORDER BY t.n DESC;

4.2 Operaciones de conjunto

UNION[ ALL], INTERSECT, EXCEPT. Cada rama resuelve su tabla (fase ①), así que combinas tablas distintas. Ramas con mismo nº de columnas y tipos compatibles.

-- Especies en flujos pero SIN informe (huérfanas)
SELECT especie_nombre_comun AS especie FROM bronze_flujos
EXCEPT
SELECT especie FROM bronze_informes;

4.3 Agregación avanzada — GROUPING SETS / ROLLUP / CUBE

Nativos en Postgres (ejecutan hoy), Spark-compatibles:

-- Subtotales por clima, por especie y total (ROLLUP)
SELECT clima, especie_nombre_comun, COUNT(*) AS n
FROM bronze_flujos
GROUP BY ROLLUP (clima, especie_nombre_comun)
ORDER BY clima NULLS LAST, especie_nombre_comun NULLS LAST;

4.4 Jerarquías — CTEs recursivos (punto 34)

El motor detecta WITH RECURSIVE, excluye la CTE recursiva de la resolución de tablas y antepone su propia CTE del dataset base — la recursión corre nativa.

-- Recorrido padre→hijo sobre una tabla de organigrama de especies
WITH RECURSIVE arbol AS (
  SELECT id, nombre, padre_id, 1 AS nivel
  FROM taxonomia_especies WHERE padre_id IS NULL
  UNION ALL
  SELECT t.id, t.nombre, t.padre_id, a.nivel + 1
  FROM taxonomia_especies t
  JOIN arbol a ON t.padre_id = a.id
)
SELECT nivel, nombre FROM arbol ORDER BY nivel, nombre;

4.5 Plan de ejecución — EXPLAIN (punto 8)

EXPLAIN va delante de cualquier SELECT/WITH; el motor lo separa, traduce el cuerpo y lo re-antepone a la sentencia completa (Postgres exige que preceda al WITH del motor).

EXPLAIN ANALYZE
SELECT especie_nombre_comun, COUNT(*) FROM bronze_flujos GROUP BY especie_nombre_comun;

4.6 Datos semi-estructurados — GET_JSON_OBJECT (punto 12)

El motor de sources nativo aterriza los objetos/arrays anidados como jsonb anidado bajo la columna. get_json_object(col, '$.ruta') extrae un campo por su JSONPath Spark y devuelve text (como Spark). Opera sobre columnas tipadas Object/Array/Map (o incluso un string con JSON serializado — el cast a jsonb es defensivo).

-- payload := {"sensor":{"id":"A12","bateria":0.83},"tags":["migracion","alpha"]}
SELECT
  GET_JSON_OBJECT(payload, '$.sensor.id')      AS sensor_id,
  GET_JSON_OBJECT(payload, '$.sensor.bateria') AS bateria,
  GET_JSON_OBJECT(payload, '$.tags[0]')        AS primer_tag
FROM lecturas_estacion
WHERE GET_JSON_OBJECT(payload, '$.sensor.id') RLIKE '^A';

Soporta .clave, [n] (índice de array) y ['clave']. Un JSONPath no parseable deja la llamada intacta (error claro, no resultado falso).

4.7 Regex — REGEXP_EXTRACT / REGEXP_REPLACE (punto 13)

Completa el pattern-matching que empieza con RLIKE (→ ~). Paridad Spark: REGEXP_REPLACE reemplaza todas las ocurrencias (flag g) y traduce backrefs $1→\1; REGEXP_EXTRACT usa el grupo 1 por defecto (idx=0 = match completo), y sin match devuelve ''.

-- Normalizar y extraer sobre el código de anilla
SELECT
  REGEXP_REPLACE(codigo_anilla, '\s+', '')            AS limpio,        -- quita espacios
  REGEXP_EXTRACT(codigo_anilla, '([A-Z]{2})\d+', 1)   AS pais,          -- prefijo país
  REGEXP_EXTRACT(codigo_anilla, '\d{1,4}$')           AS numero         -- coma en {1,4} OK
FROM anillamientos
WHERE codigo_anilla RLIKE '^[A-Z]{2}';

4.8 Vistas nombradas — objeto del catálogo (punto 2)

Una vista es una consulta registrada en el catálogo (no un truco de sesión): una fila del Warehouse con su SELECT almacenada, que aparece en el árbol como objeto propio (icono 👁), se resuelve por su nombre/coordenada, y sobrevive a Karma como una view nativa. Desarrollas por partes nombrando pasos reutilizables.

-- Crear (o reemplazar) una vista sobre tablas del Warehouse
CREATE OR REPLACE VIEW flujos_2024 AS
SELECT especie_nombre_comun, velocidad_promedio_kmh, clima
FROM bronze_flujos
WHERE fecha_avistamiento >= '2024-01-01';

-- Consumirla como cualquier tabla (se EXPANDE a su SELECT al ejecutar)
SELECT clima, AVG(velocidad_promedio_kmh) AS vel_media
FROM flujos_2024 GROUP BY clima;

-- Componer vistas sobre vistas (resolución recursiva, con guard de ciclos)
CREATE VIEW flujos_rapidos_2024 AS
SELECT * FROM flujos_2024 WHERE velocidad_promedio_kmh > 60;

SHOW VIEWS;                    -- lista las vistas + preview de su definición
DESCRIBE flujos_2024;         -- columnas + el texto SQL de la vista (# View Text)
DROP VIEW IF EXISTS flujos_rapidos_2024;

Cómo se resuelve (las 3 fases §0): al ejecutar FROM flujos_2024, el motor ① resuelve la vista por el catálogo, ② valida (read-only, tenencia, existe), y ③ la expande a una CTE con su SELECT traducida, resolviendo recursivamente sus tablas base (orden topológico: dependencias primero). Fases ①② durables; ③ = Karma.

5. Matices y límites

• La fase ③ es de Karma. No se endurece el shim más allá de desbloquear las formas comunes; el rigor de ejecución (pushdown, pruning, time-travel, geoespacial, VARIANT) es inversión del motor nativo, no de aquí.
• Reuso nombrado = vistas (§4.8), no sesión. El reuso entre queries se hace con vistas (objeto del catálogo), no con estado efímero. Lo que sí difiere a Karma: temp tables y variables SQL de sesión. Dentro de una sola query, WITH (CTEs) sigue siendo el equivalente stateless.
• Columnas de vista no proyectadas aún (§4.8): DESCRIBE <vista> muestra su texto SQL pero el schema de columnas se rellena vacío (follow-up).
• information_schema no se une aún con tablas reales en la misma query (B1).
• Motor de sources nativo, no Fivetran: las tablas no llevan columnas _fivetran_*; consulta las columnas de negocio directamente. Lo anidado aterriza como jsonb bajo la columna → get_json_object lo lee directamente.
• JSON / regex (§4.6–4.7): get_json_object devuelve text y opera sobre jsonb (cast defensivo); explode/from_json/VARIANT diferidos a Karma. REGEXP_REPLACE es global (flag g, como Spark); REGEXP_EXTRACT sin match devuelve '' (no NULL).
• El motor es regex-heurístico: la composición común está cubierta; SQL muy exótico puede topar con bordes que resuelve Karma.

6. Referencia rápida

DIRECCIONAMIENTO  bare · catalog.schema.tabla · "catalog.schema.tabla"   (fase ①)
                  cross-catalog: cada FROM/JOIN resuelve por separado (#26)
QUERY             SELECT · WITH [RECURSIVE] · subquery(scalar/IN/EXISTS/correl.)
                  JOIN(INNER/LEFT/RIGHT/FULL/CROSS/SELF/semi/anti)
                  UNION[ALL]/INTERSECT/EXCEPT · window OVER() · NTILE
AGREGACIÓN        GROUP BY · GROUPING SETS · ROLLUP · CUBE · agg. condicional (CASE)
SEMI-ESTR./REGEX  GET_JSON_OBJECT(col,'$.a.b') · REGEXP_EXTRACT(str,pat[,idx]) · REGEXP_REPLACE(str,pat,repl)   (#12·#13)
JERARQUÍA         WITH RECURSIVE …  (org charts, BOM, path enumeration)  (#34)
VISTAS            CREATE [OR REPLACE] VIEW n AS SELECT … · DROP VIEW [IF EXISTS] n
                  SHOW VIEWS · DESCRIBE n     (objeto del catálogo; se expande a CTE)  (#2)
PLAN              EXPLAIN [ANALYZE|VERBOSE|(FORMAT JSON)] <SELECT|WITH>   (#8)
CONTRATO          ① resolver (catálogo) → ② validar (read-only·tenencia·existe) → ③ ejecutar (Karma)
DIFERIDO→KARMA    temp views/tables · VARIANT/explode · ST_* · TABLESAMPLE · join hints · pushdown