frosty friday w066

query.py

# https://frostyfriday.org/blog/2023/10/06/week-66-intermediate/
import snowflake.snowpark as snowpark
from snowflake.snowpark import functions as F

def main(session: snowpark.Session): 
    tableName = 'snowflake.account_usage.tables'
    dataframe = (
        session.table(tableName)
        .filter(
            (F.col('row_count') > 0)
            & (~F.col('is_transient'))
            & F.col('deleted').is_null()
        )
        .select(F.col('table_catalog').as_('database'))
        .group_by(F.col('database'))
        .agg(F.count('*').as_('tables'))
    )

    return dataframe