View DDL history of CREATE VIEW statement in Snowflake

Question

We have some changes done to the existing view and now looking to return to the previous version. Is it any way we can get a history of DDL for the regular view in Snowflake?

Answer 1

If you created the last version of the view in the last 365 days, you should be able to get the query that created it by querying the SNOWFLAKE database. You can write something like this to find the last year's changes to MY_VIEW_NAME:

select   * 
from     "SNOWFLAKE"."ACCOUNT_USAGE"."QUERY_HISTORY"
where    QUERY_TEXT ilike '%create%view%MY\_VIEW\_NAME%'
order by END_TIME desc limit 100;

Remember to escape the underscores in the ilike statement with backslashes (underscores are single-character wildcards in the ilike function).

Answer 2

Recently I was looking for above query answer and came across this thread. I had issue running above query and refined it as follows:

    select *
    from table(information_schema.query_history())
    where    QUERY_TEXT ilike '%create%view%viewname%'
    order by END_TIME desc limit 5;