script to find out DB size in Snowflake database

 

SELECT

    USAGE_DATE,

    DATABASE_NAME,

    ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 3), 2) AS DATABASE_GB,

    ROUND(AVERAGE_FAILSAFE_BYTES / POWER(1024, 3), 2) AS FAILSAFE_GB,

    ROUND(AVERAGE_HYBRID_TABLE_STORAGE_BYTES / POWER(1024, 3), 2) AS HYBRID_TABLE_GB,

    ROUND(AVERAGE_ARCHIVE_STORAGE_COOL_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COOL_GB,

    ROUND(AVERAGE_ARCHIVE_STORAGE_COLD_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COLD_GB,

    ROUND(AVERAGE_ARCHIVE_STORAGE_COOL_FAILSAFE_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COOL_FAILSAFE_GB,

    ROUND(AVERAGE_ARCHIVE_STORAGE_COLD_FAILSAFE_BYTES / POWER(1024, 3), 2) AS ARCHIVE_COLD_FAILSAFE_GB

FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY

ORDER BY USAGE_DATE DESC, DATABASE_NAME;

SELECT

    USAGE_DATE,

    DATABASE_NAME,

    ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 3), 2) AS DATABASE_GB,

    ROUND(AVERAGE_DATABASE_BYTES / POWER(1024, 2), 2) AS DATABASE_MB

FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASE_STORAGE_USAGE_HISTORY

ORDER BY USAGE_DATE DESC, DATABASE_NAME;

can we create the 2 object with same name in the Snowflake?

 we can create the same name table in Snowflake database.

we create permanent table, temporary table in same  database, But when we query the table temporary table takes priority.

create permanent table  in public schema  in SALES_DB

CREATE  TABLE CUSTOMERS

(

    ID INT,

    NAME STRING,

    CITY STRING

);

create temporary  table  in public schema  in SALES_DB

CREATE   temporary TABLE CUSTOMERS

(

    ID INT,

    NAME STRING,

    CITY STRING

);

insert some records into temporary table ,when you not mention the schema name  by default it will insert to temporary table

INSERT INTO CUSTOMERS (ID, NAME, CITY)

VALUES

    (1, 'John', 'New York'),

    (2, 'Alice', 'London'),

    (3, 'Bob', 'Chennai');

select count(*) from  public.CUSTOMERS; --0 records --permanent table

select count(*) from CUSTOMERS; --3 temp records

we can conclude that temporary table will take the precedence or priority when we use same name table in Snowflake database.