D5.7 - OUTLINE SNOWFLAKES CATALOG AND OBJECTS

Question 1

What is a database?

Answer 1

• A logical grouping of schemas. Each database to a single Snowflake account.

Question 2

What is a Schema?

Answer 2

• A schema is a logical grouping of database objects (tables, views, etc.). Each schema belongs to a single database

Question 3

What are the types of tables within Snowflake?

Answer 3

• Permanent Tables (default)
• Temporary Tables
• Transient Tables

Question 4

Permanent Table (Standard Edition)

Answer 4

• Persistence: until explicitly dropped
• Cloning: can be closed to temp, tans, and/or perm
• Time Travel Retention: 0 to 1 Days (default is 1)
• Fail-safe Period: 7 Days

Question 5

Permanent Table (Enterprise Edition and higher)

Answer 5

• Persistence: until explicitly dropped
• Cloning: can be closed to temp, tans, and/or perm
• Time Travel Retention: 0 to 90 Days (default is configurable)
• Fail-safe Period: 7 Days

Question 6

Temporary Table

Answer 6

• Persistence: remainder of the session
• Cloning: can be closed to temp or tans
• Time Travel Retention: 0 to 1 Days (default is 1)
• Fail-safe Period: 0 Days

Question 7

Transient Table

Answer 7

• Persistence: remainder of the session
• Cloning: can be closed to temp or tans
• Time Travel Retention: 0 to 1 Days (default is 1)
• Fail-safe Period: 0 Days

Question 8

What are the types of Views?

Answer 8

• Non-materialized views: the views created when querying objects within a data base, such as when running a query to select all, some rows, or joining data between tables
• Materialized views: views that are stored within the system, which incur costs by taking up space, and behave more like a table
• Both types of views can be secured views, which are useful when considering data protection and privacy, but may impact performance

Question 9

What are they data types within Snowflake?

Answer 9

• String or Binary
• Numeric
• Logical
• Date and Time
• Geospatial
• Semi-structured

Question 10

What are external functions?

Answer 10

• External functions are user-defined functions that are stored and executed outside of Snowflake. External functions make it easier to access external API services.
• This feature eliminates the need to export and reimport data when using third-party services, simplifies data pipelines

Question 11

What is a stored procedure?

Answer 11

• Allows you to write procedural code that executes SQL. In a stored procedure, you can use programmatic constructs to perform bracnhing and looping.
• Created with a CREATE PROCEDURE command and is executed with a CALL command

Question 12

What do stored procedures allow?

Answer 12

• Procedure logic (branching and looping). which SQL does not support
• Error handling
• Dynamically creating a SQL statement and executing it
• Writing code that executes with the privilege’s of the role that owns the procedure, rather with the privilege’s of the role that runs the procedure.
• This allows the stored procedure owner to delegate the power to perform specified operations to users who otherwise could not do so. However, there are limitations on these owner’s rights stored procedures.