La imagen de Superset está personalizada para incluir los controladores de Snowflake y
ClickHouse. Usa --build en la primera ejecución para que Docker la construya:
cd "$(git rev-parse --show-toplevel)/workshop_public/snowflake_migration_lab/01-setup-snowflake/superset"source ../.envdocker compose up -d --build
Todos los gráficos usan conjuntos virtuales: consultas SQL guardadas con un nombre.
Cómo crear cada conjunto:
Ve a Datasets → + Dataset
Selecciona la base NYC Taxi — Snowflake (Source)
Haz clic en Create dataset from SQL query y pega el SQL siguiente
Guárdalo con el nombre indicado
Después de guardar, ve a Datasets → icono de lápiz → pestaña Columns →
"Sync columns from source" → Save. Sin este paso, el editor mostrará 0 columnas.
SELECT DATE_TRUNC('hour', pickup_at) AS hour_bucket, pickup_borough, COUNT(*) AS trip_count, SUM(total_amount_usd) AS total_revenue, AVG(tip_amount_usd / NULLIF(fare_amount_usd, 0)) AS avg_tip_rate, AVG(trip_distance_miles) AS avg_distance_milesFROM ANALYTICS.FACT_TRIPSWHERE pickup_at >= DATEADD('day', -7, CURRENT_TIMESTAMP()) AND pickup_borough IS NOT NULLGROUP BY 1, 2ORDER BY 1 DESC, total_revenue DESC
SELECT pickup_at::DATE AS trip_date, COUNT(*) AS daily_trip_count, AVG(trip_distance_miles) AS daily_avg_distance, AVG(AVG(trip_distance_miles)) OVER ( ORDER BY pickup_at::DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS rolling_7d_avg_distance, SUM(total_amount_usd) AS daily_revenue, SUM(SUM(total_amount_usd)) OVER ( ORDER BY pickup_at::DATE ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS rolling_7d_revenueFROM ANALYTICS.FACT_TRIPSGROUP BY 1ORDER BY 1 DESCLIMIT 365
SELECT CASE WHEN surge_multiplier >= 2.0 THEN 'High Surge (2x+)' WHEN surge_multiplier >= 1.5 THEN 'Medium Surge (1.5–2x)' WHEN surge_multiplier > 1.0 THEN 'Low Surge (1–1.5x)' ELSE 'No Surge (1x)' END AS surge_category, COUNT(*) AS trip_count, ROUND(AVG(total_amount_usd), 2) AS avg_total_fare, ROUND(AVG(fare_amount_usd), 2) AS avg_base_fare, ROUND(AVG(surge_multiplier), 2) AS avg_surgeFROM ANALYTICS.FACT_TRIPSWHERE surge_multiplier IS NOT NULLGROUP BY 1ORDER BY avg_surge DESC
Consulta RAW.TRIPS_RAW directamente con la sintaxis de ruta de VARIANT. Es la
consulta lenta intencional, usada como objetivo del benchmark de ClickHouse.
SELECT ROUND(TRIP_METADATA:driver.rating::FLOAT, 1) AS rating_bucket, COUNT(*) AS trip_count, ROUND(AVG(TOTAL_AMOUNT), 2) AS avg_fare, ROUND(AVG(DATEDIFF('minute', PICKUP_DATETIME, DROPOFF_DATETIME)), 1) AS avg_duration_minutesFROM RAW.TRIPS_RAWWHERE TRIP_METADATA:driver IS NOT NULL AND TRIP_METADATA:driver.rating IS NOT NULLGROUP BY 1ORDER BY 1
SELECT vehicle_type, COUNT(*) AS trip_count, SUM(total_amount_usd) AS total_revenue, AVG(total_amount_usd) AS avg_fare, AVG(trip_distance_miles) AS avg_distanceFROM ANALYTICS.FACT_TRIPSWHERE vehicle_type IS NOT NULLGROUP BY 1ORDER BY total_revenue DESC
SELECT traffic_level, COUNT(*) AS trip_count, AVG(duration_minutes) AS avg_duration_minutes, AVG(trip_distance_miles) AS avg_distance_miles, AVG(total_amount_usd) AS avg_fareFROM ANALYTICS.FACT_TRIPSWHERE traffic_level IS NOT NULLGROUP BY 1ORDER BY avg_duration_minutes DESC
SELECT pickup_at::DATE AS trip_date, app_platform, COUNT(*) AS trip_count, AVG(surge_multiplier) AS avg_surgeFROM ANALYTICS.FACT_TRIPSWHERE app_platform IS NOT NULL AND pickup_at >= DATEADD('day', -30, CURRENT_TIMESTAMP())GROUP BY 1, 2ORDER BY 1 DESC
[ Total Trips — Big Number ] [ Total Revenue — Big Number (add 2nd) ][ Trips per Hour — Line chart (full width) ][ Revenue by Borough — Bar ] [ Payment Type Split — Pie ][ Borough Performance — Table (full width) ]
Objetivo: vista estratégica para la revisión semanal del negocio. Muestra funciones
de ventana y sintaxis específica de Snowflake (QUALIFY) que deben reescribirse en
ClickHouse.
Objetivo: análisis detallado del rendimiento de conductores y la calidad de viajes.
Es intencionadamente el panel más lento: consulta RAW.TRIPS_RAW directamente mediante
acceso VARIANT. Anota aquí el tiempo como referencia para el benchmark de rendimiento
de ClickHouse de la Parte 2.
[ Trip Count by Rating — Bar ] [ Avg Fare by Rating — Line ][ Revenue by Vehicle Type — Horizontal bar (full width) ][ Traffic Level Impact — Bar (50%) ] [ Surge by Platform — Table (50%)][ Daily Trips by Platform — Line chart (full width) ]
Abre cada panel y confirma que todos los gráficos se carguen sin errores
Para el Panel 3, anota el tiempo de ejecución de la consulta dqa_rating_dist en
Snowflake UI → Activity → Query History y guárdalo como benchmark de la migración
Importación automática (./init_superset.sh): funciona sin cambios. El script
registra la conexión real de Snowflake desde .envantes de importar y vuelve a
aplicar el URI correcto después de cada importación (consulta _update_db en
init_superset.sh), por lo que reemplaza los valores de marcador por tus credenciales.
Importación manual en la interfaz de Superset: la base importada se crea con el
URI de marcador y no se conecta. Después de importar, ve a Settings → Database
Connections → Edit y sustituye sqlalchemy_uri por tu URI real de Snowflake (por
ejemplo, snowflake://<USER>:<PASSWORD>@<ORG>-<ACCOUNT>/NYC_TAXI_DB/ANALYTICS?role=ANALYST_ROLE&warehouse=ANALYTICS_WH).
Volver a exportar tus paneles: Superset incorpora tu localizador de cuenta y tu
usuario en databases/*.yaml. Antes de incluir los ZIP reexportados, anonimízalos de
nuevo como MYORG-MYACCOUNT / LAB_USER para no filtrar identificadores al historial de git.
