​In Snowflake, while individual views lack an UNDROP command, restoring a dropped database using UNDROP DATABASE reinstates all its contained objects, including views, provided the restoration occurs within the data retention period. We know This is because the UNDROP operation for databases and schemas leverages Snowflake's Time Travel feature, which preserves the state of these objects for a specified duration after deletion.

But why it can undrop the view in db but not individual one?

​In Snowflake, while individual views lack an UNDROP command, restoring a dropped database using UNDROP DATABASE reinstates all its contained objects, including views, provided the restoration occurs within the data retention period. We know This is because the UNDROP operation for databases and schemas leverages Snowflake's Time Travel feature, which preserves the state of these objects for a specified duration after deletion.

But why it can undrop the view in db but not individual one?

• view
• snowflake-cloud-data-platform

Share Improve this question Follow asked Mar 22 at 16:31 abdoulsnabdoulsn 1,17133 gold badges2020 silver badges3838 bronze badges 1

• 2 Because Snowflake haven’t implemented it. I’m not sure what other answer you were expecting? – NickW Commented Mar 22 at 16:50

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

As stated in the documentation, Dropped views cannot be recovered; they must be recreated. view once deleted cannot be undropped because View is more like storing query statements than storing data.

Also, we do not have an undrop for a view. 
https://docs.snowflake/en/sql-reference/sql/undrop#syntax

However, you could use the query_history view to get the SQL of the dropped view.
https://docs.snowflake/en/sql-reference/account-usage
https://docs.snowflake/en/sql-reference/info-schema#list-of-table-functions

You can use the QUERY_ID or QUERY_TEXT as a filter to retrieve the view definition.

select *
from table(information_schema.query_history())
where QUERY_TEXT ilike 'create view <name>%'
order by start_time;

select *
from table(information_schema.query_history())
where QUERY_ID = '<query_ID>'
order by start_time;

DB objects definition(views, UDFs, SP) should be version-controlled to avoid such situation.

Example: Using a Git repository in Snowflake

---

Recovering deleted view definition can be achieved by querying SNOWFLAKE.ACCOUNT_USAGE.VIEWS:

This Account Usage view displays a row for each view in the account

Latency for the view may be up to 90 minutes.

Example:

CREATE VIEW TEST.TEST_VIEW
AS
SELECT 1 AS c;

CREATE OR REPLACE VIEW TEST.TEST_VIEW
AS
SELECT 2 AS c, 3 AS d;

DROP VIEW TEST.TEST_VIEW;

Query:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, *
FROM SNOWFLAKE.ACCOUNT_USAGE.VIEWS
WHERE TABLE_SCHEMA ILIKE 'TEST_DB'
  AND TABLE_NAME ILIKE 'TEST_VIEW'
ORDER BY DELETED DESC;

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	VIEW_DEFINITION
TEST_DB	TEST	TEST_VIEW	CREATE OR REPLACE VIEW TEST.TEST_VIEW AS SELECT 2 AS c, 3 AS d;
TEST_DB	TEST	TEST_VIEW	CREATE VIEW TEST.TEST_VIEW AS SELECT 1 AS c;