Snowflake User Table Creation History

snowflake_user_table_creates.txt

// anything older than 20 days ago
SET cutoff = current_date() - 20;

SELECT h.query_text,
       h.database_name,
       h.user_name,
       h.role_name,
       t.table_name,
       u.login_name
  FROM snowflake.account_usage.query_history h 
  LEFT JOIN snowflake.account_usage.users u
    ON u.name = h.user_name 
   AND u.disabled = FALSE 
   AND u.deleted_on IS NULL
  JOIN user_stage.information_schema.tables t
    ON t.created = h.start_time
 WHERE execution_status = 'SUCCESS' 
   AND start_time < $cutoff
   AND query_type LIKE '%CREATE_TABLE%'
   AND CONTAINS(UPPER(h.query_text), UPPER(t.table_name))
   AND t.created < $cutoff;