Cómo identificar tablas sin uso en Snowflake

Access History es una vista del esquema Account Usage de la base de datos de Snowflake. Está disponible en todas las cuentas de Snowflake con Enterprise Edition o superior. Sirve para consultar los objetos de Snowflake (tablas, vistas y columnas) a los que accedió cada query, ya sea de forma directa o indirecta.

Direct Objects vs. Base Objects

Para saber qué columnas consultó una query existen dos columnas relevantes: direct_objects_accessed y base_objects_accessed. La diferencia clave entre ambas está en cómo tratan las vistas. Veamos la siguiente definición:

create or replace view orders_view as (
	select *
	from orders
	where
		not test
		and success
);

La query select * from orders_view accede directamente al objeto orders_view e indirectamente a la tabla base orders. Por eso orders_view aparecerá en la columna direct_objects_accessed de access_history, mientras que orders aparecerá en base_objects_accessed.

A la hora de decidir si una tabla está sin uso, conviene usar base_objects_accessed, ya que tiene en cuenta las queries que acceden indirectamente a una tabla a través de una vista.

Cómo parsear base_objects_accessed

base_objects_accessed es un array JSON con todos los objetos base a los que se accedió durante la ejecución de una query. Aquí tienes un ejemplo del contenido de la columna, tomado de la documentación:

1[\
\
2  {\
\
3    "columns": [\
\
4      {\
\
5        "columnId": 68610,\
\
6        "columnName": "CONTENT"\
\
7      }\
\
8    ],\
\
9    "objectDomain": "Table",\
\
10    "objectId": 66564,\
\
11    "objectName": "GOVERNANCE.TABLES.T1"\
\
12  }\
\
13]

El array de objetos accedidos por cada query se puede transformar a una fila por objeto con lateral flatten y luego filtrarse para considerar únicamente objetos de tipo tabla, como se muestra a continuación:

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Expandir código

Con el access_history "aplanado" de la query anterior podemos determinar la hora exacta en que se accedió por última vez a una tabla y qué usuario ejecutó la query:

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Expandir código

Al identificar tablas sin uso para eliminar, conviene conocer los costos de almacenamiento asociados. Con la vista de account usage table_storage_metrics y asumiendo un costo de almacenamiento de $23 por terabyte al mes, se puede calcular el costo anual de almacenamiento de cada tabla:

select
    id as table_id,
    table_catalog || '.' ||table_schema ||'.' || table_name as fully_qualified_table_name,
    (active_bytes + time_travel_bytes + failsafe_bytes + retained_for_clone_bytes)/power(1024,4) as total_storage_tb,
    -- Asume un costo de almacenamiento de $23/TB/mes
    -- Ajústalo al valor que corresponda según tu contrato de Snowflake
    total_storage_tb*12*23 as annualized_storage_cost
from snowflake.account_usage.table_storage_metrics
where
    not deleted

Hasta aquí vimos cómo determinar cuándo se accedió a una tabla por última vez y los costos de almacenamiento asociados a cada una. Podemos combinar estos bloques para identificar todas las tablas no consultadas en los últimos 90 días y mostrar el ahorro anual que se obtendría si se eliminaran.

El SQL siguiente depende de la vista account_usage.access_history, que solo está disponible para clientes de Snowflake con Enterprise Edition o superior.

Si usas dbt, considera la versión alternativa de este SQL, que se ejecuta mucho más rápido.

with
access_history as (
    select *
    from snowflake.account_usage.access_history
),
access_history_flattened as (
    select
        access_history.query_id,
        access_history.query_start_time,
        access_history.user_name,
        objects_accessed.value:objectId::integer as table_id,
        objects_accessed.value:objectName::text as object_name,
        objects_accessed.value:objectDomain::text as object_domain,
        objects_accessed.value:columns as columns_array

Expandir código

Consultar y aplanar la vista access_history puede ser muy lento por el volumen de datos que hay que procesar. Para acelerar las consultas sobre el historial de acceso a tablas, recomendamos materializar estos datos de forma incremental con nuestro paquete open-source de dbt: dbt_snowflake_monitoring. Una vez instalado el paquete, las queries para identificar tablas sin uso se simplifican mucho. El código anterior se puede reescribir así:

with
table_access_summary as (
    select
        table_id,
        max(query_start_time) as last_accessed_at,
        max_by(user_name, query_start_time) as last_accessed_by,
        max_by(query_id, query_start_time) as last_query_id
    from query_base_table_access
    group by 1
),
table_storage_metrics as (
	select
      id as table_id,
      table_catalog || '.' ||table_schema ||'.' || table_name as fully_qualified_table_name,
      (active_bytes + time_travel_bytes + failsafe_bytes + retained_for_clone_bytes)/power(1024,4) as total_storage_tb,

Expandir código

Al decidir si eliminar una tabla, conviene saber cuándo se actualizó por última vez mediante una operación DDL o DML. La query siguiente muestra cómo encontrar todas las tablas que se actualizaron en la última semana con la vista de account usage tables:

select
    table_id,
    table_catalog||'.'||table_schema||'.'||table_name as fully_qualified_table_name,
    last_altered as last_altered_at
from snowflake.account_usage.tables
where
    last_altered > current_date - 7