Databases

• Each database belongs to a single Snowflake Account
• No hard limit of the number of database in a single Snowflake Account
• Each database contains one of more schemas
• Creating a database automatically sets it as the current database in the session
• Creating a database automatically creates two schemas in the database; INFORMATION_SCHEMA and PUBLIC
• Databases can be created from a SHARE
• Databases created from a Share does not create the PUBLIC and INFORMATION_SCHEMA schemas
• Databases created from a Share CANNOT be CLONED

Schemas

• Schemas are logical groupings of database objects
• Transient tables are created by default in Transient schemas
• Since PERMANENT is not a keyword while creating a table, you cannot create permanent tables in transient schemas
• Creating a schema automatically sets it as the current schema in the session
• Managed schemas centralizes privilege management with the schema owner
• In a managed schema, the schema owner manages grants on contained objects but no other privileges such as USAGE, SELECT, DROP etc

Tables

Types of tables

• Permanent
• Temporary
• Transient
• External

Permanent

• Default table type
• Fail safe in all editions is 7 days
• Time travel for Standard edition is 0 or 1
• Time travel for Enterprise edition and above is 0 to 90

Temporary

• Used for storing temporary (ETL), transitory (session) data
• Only exists within the session they were created
• Data is purged completely from the system once the session ends
• Once purged cannot be recovered neither by the user nor Snowflake
• Data storage charges apply for the amount of time the temporary table was in existence
• Can create temporary and non temporary tables with the same name
• If a temporary table has the same name as a non temporary table it will hide the existence of the non temporary table. This is the case either if the temporary table was created before or after the non temporary table
• After creation temporary tables cannot be converted to any other table type

Transient

• Transient tables are similar to permanent table except for they don’t have a Fail safe period
• Transient table are designed for transitory data that is needed beyond a session and can be share by other users
• They do not have the same level of data recovery and protection as provided by permanent tables
• All tables created in a transient schema are transient by definition
• Time travel is 0 or 1 day. Default is 1 day

External

• Query data stored in an external stage
• Are read-only
• External tables can let you access data store in any format that the COPY INTO command support
• External tables can be used for query and join operations and can be used to create views
• Slower than querying table where data is stored natively in snowflake
• Query performance can be improved by creating materialized views based on external tables
• An overhead to manage event notifications for automatic refresh is charged

Views

A view is a named definition of a query

Types of Views

• Standard or Non Materialized
• Materialized

Standard

Usually referred to simply as VIEWS or Non Materialized views. Results of views are created by executing the query at the time the view is referenced in a query.The results are not stored for future use. As a result the performance of views are slower than materialized views. Since there is no storage, there is no additional cost from storage for views. Some uses cases are

• Selecting some or all columns of a table
• Filtering a range of data in a table column
• Joining two or more tables
• Create standard views when
• The results of the underlying tables change often
• Results are not often used
• Queries are not resource intensive so not costly to re run

Materialized

This is an Enterprise Edition and above feature.

Materialized views behave more as a table and results of the view are stored, resulting in storage. Hence additional storage costs are incurred. The benefit of materialized views is that they are fast since the data is stored. This however involves maintenance to get these views fresh, which incur further cost. Some use case are

• Results contain small number of row and columns relative to the base table
• Long processing time such as semi structured data column analysis
• Aggregate functions that take long time to calculate
• Queries are on external tables
• The views base table do not change regularly
• Create materialized views when
• The underlying table data does not change regularly
• The result of the views are used often
• The query consumes a lot of resources

Advantages

• Faster to run for queries that use the same subquery results repeatedly
• Are transparently and automatically maintained by Snowflake
• Data is always current regardless of the amount DML updates made on the base tables

Limitations

• Snowflake does not allow DML queries on a materialized view.
• You cannot create a direct clone of a materialized view, however if you create a clone of the SCHEMA or DATABASE, the materialized view will be cloned in the new SCHEMA or DATABASE.
• TIME TRAVEL features are not supported for materialized views
• Materialized views are NOT monitored by Snowflake

Effects of base table changes

• Columns added to the base table are NOT automatically updated to the materialized view
• Adding columns to base table are also NOT propagated even when the materialized view was created using SELECT *
• If a column was dropped on the base table, the materialized view is suspended, i.e. it cannot be used or maintained. This is the case if the dropped column was not part of the materialized view’s definition. The view has to be recreated
• Dropping the base table automatically suspends the materialized view but does not drop it
• In most cases the materialized view should be dropped too if the base table is dropped