Schemas in QA but not in Prod

WITH qa AS (
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
    FROM qa_edw_pstg.information_schema.tables
),
prod AS (
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
    FROM prd_edw_pstg.information_schema.tables
)
SELECT DISTINCT
    QA.TABLE_SCHEMA,
    COUNT(*)
FROM QA
LEFT JOIN PROD
    ON QA.TABLE_SCHEMA = PROD.TABLE_SCHEMA
WHERE PROD.TABLE_NAME IS NULL
GROUP BY QA.TABLE_SCHEMA;

Prod and QA Table Cross Reference

Shows tables that are:

not in PROD, in QA
in PROD, not in QA

USE ROLE RL_ARCHITECT;

WITH qa AS (
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
    FROM qa_edw_pstg.information_schema.tables
),
prod AS (
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
    FROM prd_edw_pstg.information_schema.tables
)
SELECT DISTINCT
    QA.TABLE_SCHEMA AS QA_TABLE_SCHEMA,
    QA.TABLE_NAME AS QA_TABLE_NAME,
    PROD.TABLE_SCHEMA AS PROD_TABLE_SCHEMA,
    PROD.TABLE_NAME AS PROD_TABLE_NAME
FROM QA
FULL JOIN PROD
    ON QA.TABLE_SCHEMA = PROD.TABLE_SCHEMA
    AND QA.TABLE_NAME = PROD.TABLE_NAME
WHERE QA.TABLE_NAME IS NULL
    OR PROD.TABLE_NAME IS NULL
ORDER BY
    QA.TABLE_SCHEMA,
    QA.TABLE_NAME;

Production and QA column cross reference

Shows tables that are in both environments but where the columns are not the same

USE ROLE RL_ARCHITECT;

WITH qa AS (
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    FROM dev_edw_pstg.information_schema.columns
),
prod AS (
    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
    FROM prd_edw_pstg.information_schema.columns
)
SELECT DISTINCT
    QA.TABLE_SCHEMA AS QA_TABLE_SCHEMA,
    QA.TABLE_NAME AS QA_TABLE_NAME,
    QA.COLUMN_NAME AS QA_COLUMN_NAME,
    PROD.TABLE_SCHEMA AS PROD_TABLE_SCHEMA,
    PROD.TABLE_NAME AS PROD_TABLE_NAME,
    PROD.COLUMN_NAME AS PROD_COLUMN_NAME
FROM QA
FULL JOIN PROD
    ON QA.TABLE_SCHEMA = PROD.TABLE_SCHEMA
    AND QA.TABLE_NAME = PROD.TABLE_NAME
    AND QA.COLUMN_NAME = PROD.COLUMN_NAME
WHERE (QA.TABLE_NAME IS NULL OR PROD.TABLE_NAME IS NULL)
    OR (QA.COLUMN_NAME IS NULL OR PROD.COLUMN_NAME IS NULL)
ORDER BY
    QA.TABLE_SCHEMA,
    QA.TABLE_NAME,
    PROD.TABLE_NAME,
    QA.COLUMN_NAME;

Active Users

Any user that is enabled and has successfully logged on in the past 6 months.

USE ROLE securityadmin;
USE WAREHOUSE dba_wh;

SHOW USERS;

SELECT
    "name" AS name,
    "first_name" AS first_name,
    "last_name" AS last_name,
    "last_success_login" AS last_success_login,
    "disabled" AS disabled
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE NOT disabled
    AND last_success_login >= DATEADD(month, -6, last_success_login);

Storage metrics

USE ROLE SYSADMIN;

SELECT
    SM.ID AS TABLE_ID,
    SM.TABLE_NAME,
    SM.TABLE_SCHEMA,
    SM.TABLE_CATALOG,
    T.ROW_COUNT,
    SM.ACTIVE_BYTES,
    SM.TIME_TRAVEL_BYTES,
    SM.FAILSAFE_BYTES,
    SM.RETAINED_FOR_CLONE_BYTES,
    SM.DELETED,
    SM.TABLE_CREATED,
    SM.TABLE_DROPPED,
    SM.SCHEMA_CREATED,
    SM.SCHEMA_DROPPED,
    SM.CATALOG_CREATED,
    SM.CATALOG_DROPPED
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS SM
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.TABLES T
    ON SM.ID = T.TABLE_ID
WHERE SM.TABLE_CATALOG = 'PRD_EDW_PSTG'
    AND NOT SM.DELETED;

-- Aggregate by Schema
USE ROLE SYSADMIN;

WITH TABLE_DATA AS (
    SELECT
        SM.ID AS TABLE_ID,
        SM.TABLE_NAME,
        SM.TABLE_SCHEMA,
        SM.TABLE_CATALOG,
        T.ROW_COUNT,
        SM.ACTIVE_BYTES,
        SM.TIME_TRAVEL_BYTES,
        SM.FAILSAFE_BYTES,
        SM.RETAINED_FOR_CLONE_BYTES,
        SM.DELETED,
        SM.TABLE_CREATED,
        SM.TABLE_DROPPED,
        SM.SCHEMA_CREATED,
        SM.SCHEMA_DROPPED,
        SM.CATALOG_CREATED,
        SM.CATALOG_DROPPED
    FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS SM
    LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.TABLES T
        ON SM.ID = T.TABLE_ID
    WHERE SM.TABLE_CATALOG = 'PRD_EDW_PSTG'
        AND NOT SM.DELETED
)
SELECT
    TABLE_SCHEMA AS SCHEMA,
    COUNT(*) N_TABLES,
    SUM(ROW_COUNT) AS TOTAL_ROWS,
    SUM(ACTIVE_BYTES) AS TOTAL_ACTIVE_BYTES
FROM TABLE_DATA
GROUP BY TABLE_SCHEMA;

Create CLONE table statements

Creates clone table statements for all tables in a schema

SELECT
    'CREATE OR REPLACE TABLE <target_db>.<target_schema>.' ||
    TABLE_NAME ||
    ' CLONE <source_db>.<source_schema>.' ||
    TABLE_NAME || ';'
FROM <source_db>.INFORMATION_SCHEMA.tables
WHERE table_schema = '<source_schema>';

Create GRANT OWNERSHIP statements

Creates grant ownership statements for all tables in a schema

SELECT
    'GRANT OWNERSHIP ON TABLE <database>.<schema>.' ||
    TABLE_NAME ||
    ' TO ROLE <role> COPY CURRENT GRANTS;'
FROM <database>.INFORMATION_SCHEMA.tables
WHERE table_schema = '<schema>';

GarrettMooney/queries.md