Account Usage: Análisis de Costos

El schema ACCOUNT_USAGE

SNOWFLAKE.ACCOUNT_USAGE es el esquema que contiene el historial completo de actividad y costos de tu cuenta. Los datos tienen una latencia de 45 minutos a 3 horas.

Las vistas más importantes para FinOps:

Costo por Warehouse

-- Top 10 warehouses por costo en el último mes
SELECT
    warehouse_name,
    ROUND(SUM(credits_used_compute), 2) AS creditos_compute,
    ROUND(SUM(credits_used_cloud_services), 2) AS creditos_cloud_services,
    ROUND(SUM(credits_used), 2) AS creditos_total
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('month', -1, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY creditos_total DESC
LIMIT 10;

Costo por Usuario

-- Top usuarios por créditos consumidos
SELECT
    user_name,
    COUNT(*) AS queries_ejecutadas,
    ROUND(SUM(credits_used_cloud_services), 4) AS creditos_cloud_services,
    AVG(execution_time) / 1000 AS avg_ejecucion_seg
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('month', -1, CURRENT_TIMESTAMP())
    AND execution_status = 'SUCCESS'
GROUP BY 1
ORDER BY queries_ejecutadas DESC
LIMIT 20;

Análisis de Queries Costosas

-- Queries que más créditos de cloud services consumieron
SELECT
    query_id,
    user_name,
    warehouse_name,
    query_text,
    ROUND(credits_used_cloud_services, 6) AS creditos,
    execution_time / 1000 AS segundos,
    rows_produced,
    bytes_scanned / 1024 / 1024 / 1024 AS gb_escaneados
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
    AND credits_used_cloud_services > 0.01
ORDER BY credits_used_cloud_services DESC
LIMIT 20;

Dashboard de Costos Diarios

-- Tendencia de costos diarios por tipo de servicio
SELECT
    TO_DATE(start_time) AS fecha,
    service_type,
    ROUND(SUM(credits_used), 2) AS creditos
FROM SNOWFLAKE.ACCOUNT_USAGE.METERING_HISTORY
WHERE start_time >= DATEADD('month', -3, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1, 2;

Cloud Services: el costo oculto

Los créditos de Cloud Services (metadata, autenticación, query compilation) son gratuitos hasta un 10% del cómputo diario. Si superan ese umbral, se cobran. Monitorea esto:

-- Ver si Cloud Services supera el umbral gratuito
SELECT
    TO_DATE(start_time) AS fecha,
    SUM(credits_used_compute) AS compute,
    SUM(credits_used_cloud_services) AS cloud_svc,
    ROUND(SUM(credits_used_cloud_services) / NULLIF(SUM(credits_used_compute), 0) * 100, 2) AS pct_cloud_svc,
    CASE WHEN SUM(credits_used_cloud_services) > SUM(credits_used_compute) * 0.1
         THEN 'EXCEDE UMBRAL GRATUITO' ELSE 'OK' END AS estado
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('month', -1, CURRENT_TIMESTAMP())
GROUP BY 1
ORDER BY 1;

Próximos pasos

La siguiente lección va más profundo: atribuir costos a nivel de query individual.