Snowflake - Container Hierarchy

#Snowflake

User -> Account -> Database -> Schema -> Table

Guidance Questions

  1. How does [Database] -> [Schema] -> [Table] fit with [Account]?

    1. [User] -> [Account] -> [Database] -> [Schema] -> [Table]
    2. Accounts belong to a single user, databases belong to a single account, schemas belong to a single database, tables belong to a single database.
    3. Source

  2. What are the four types of Snowflake tables and which ones have fail-safe storage?

    1. Types of tables:
      1. Permanent Table.
        1. The default type of table when creating tables.
        2. Visible to all users with appropriate permissions.
        3. All data is stored in permanent tables.
        4. 7-day Fail safe retention period.
      2. Temporary Table.
        1. Lasts single session.
        2. Only visible to the user in the session.
        3. Data not recoverable once session ends.
        4. Contribute towards storage bill while existing.
        5. No fail safe storage.
        6. Used for storing transitory data, like ETL state, etc.
      3. Transient Table.
        1. Like permanent tables, without fail safe.
        2. Cheaper that permanent tables, due to no fail safe storage.
        3. Need to be explicitly dropped.
      4. External Table
        1. Stored externally, but read as if stored in Snowflake.
        2. Let's you store file metadata in Snowflake, like:
          1. Filenames.
          2. Version identifiers.
          3. Row and Column numbers.
        3. Can access any data stored in a format accessible by COPY INTO <table>.
        4. Read Only, you can't use DML on these tables.
        5. Can use them for DDL, like SELECT and JOIN, queries are slower than with internal tables.
        6. Can create Views against external tables, materialized views remove query speed penalty.
        7. No fail safe storage.
        8. Source for external tables.
      5. Source for internal tables.

  3. What are the differences between standard and secure views?
    0. View definition - A View is a subset of a database generated by a query which has been named and stored as a persistent database object.
    1. Views are created using the CREATE VIEW statement.
    2. Views can be queried, joined like database tables.
    3. Non-secure views are visible to all users with database access.

    1. There are 3 types of views:
      1. Standard Views:
        1. Non-Materialized - They are not stored as permanent objects, only as queries.
      2. Secure Views:
        1. Can be materialized or not.
        2. Only query is only visible to authorized users.
        3. Security focused optimizer, not as efficient a regular view.
        4. Source
      3. Materialized Views:
        1. Materialized: When created they turn the query result into a separate persistent data object.
        2. Function like data marts, AKA they are specific subsets of datasets, which makes them more efficient for querying.
        3. Incur storage costs just like a table.
        4. Have additional restrictions.
        5. Source

  4. What are the different types of stages?

    1. External stages, hosted outside of Snowflake in buckets like:
      1. Amazon S3
      2. Google Cloud Storage
      3. Microsoft Azure
    2. Internal Stages, hosted inside of Snowflake:
      1. Table Stage:
        1. Automatically created for each table.
        2. Not visible in webUI.
        3. Stores files owned by one or more users but loaded into a single table.
        4. Not a separate database object.
        5. Only the table owner can interact with the staged files.
      2. User Stage:
        1. Automatically created for each user.
        2. Not visible in webUI.
        3. Cannot be altered or dropped.
        4. Stores files managed by single user.
      3. Named Stage:
        1. A database object created in a schema.
        2. Can store files owned by many users and loaded into many tables.
        3. CRUD access controlled by security access control privileges.
        4. Are created using the CREATE STAGE command.
        5. Definable via webUI.
        6. Viewable in WebUI by users.
        7. Can be internal or external.
        8. Named stages can be hosted on any and all of the supported platform at the same time.
        9. Source
    3. Source

  5. What are the command-line commands that moves filed into stages? What command moves files from stages into tables? From tables back out of stages?

    1. Put can be use by SnowSQL and Snowflake CLI to move files into Stages.
      1. Source
    2. COPY INTO <table> moves files from stages into tables.
      1. Data stored in archival storage can't be loaded this way.
      2. Source
    3. COPY INTO <location> unloads files from tables and puts them into any kind of stage.
      1. Can be downloaded into internal stages using the GET command.
      2. Source

  6. Disregarding replication functionality, can a database span multiple accounts? Can a schema span multiple accounts? Can an account span multiple cloud-as-a-platform providers?

    1. No. A database cannon span multiple accounts.
    2. No. A schema can't span multiple accounts.
    3. No. An account cannot span multiple Cloud providers(AWS,Azure,GCP).
    4. Source

  7. Can a single Snowflake account load data from stages on more than one cloud provider?

    1. Yes with external stages. See the answer to #5.

  8. Can you deduce the cloud platform provider and region of an account based on the account URL?

    1. If it's name is {name}.snowflakecomputing.com it's AWS us-west-2. Otherwise the format is {account}.{region}.{provider}.snowflakecomputing.com
    2. Source

  9. What is a namespace in Snowflake and how does it relate to the container hierarchy?

    1. Database.Schema = Namespace
    2. The namespace is like a folder for tables, making it easier to group similar tables together.
    3. The namespace is inferred from the current database and schema used in the session.
    4. Source

Quiz:

  1. Which of these are Snowflake table types?
    1. Permanent.
    2. External.
    3. Temporary.
    4. Transient.
  2. Which of the following are Snowflake view types?
    1. Secure.
    2. Standard.
    3. Materialised.
  3. What are the three Snowflake Stage types?
    1. User.
    2. Table.
    3. Named.
  4. Named stages come in two varieties, what are they?
    1. Internal.
    2. External.
  5. What type of view is most like a table?
    1. Materialised.
  6. Which type of view has na extra layer of protection to hide the SQL code from unauthorised viewing?
    1. Secure.
  7. In a Snowflake account named MX43210, you need to set a user's default namespace to a database called MYDB and the PUBLIC schema. Which of the following commands would you use?
    1. set default_namespace = mydb.public.
  8. Which statements are true about the Snowflake container hierarchy? Select all that apply.
    1. Accounts contain databases which contain schemas.
    2. Schemas contain tables as well as views.
  9. In the Snowflake container hierarchy, what container is represented as a URL (for example: https://HJ54364.snowflakecomputing.com)
    1. Account
  10. Fail-Safe is a seven-day history of data and is automatically available on which table types?
    1. Permanent.
  11. Each Snowflake account comes with two shared databases. One is a set of sample data and the other contains Account Usage information. Check all true statements about these shared databases.
    1. SNOWFLAKE contains a scheme called ACCOUNT_USAGE.
    2. SNOWFLAKE_SAMPLE_DATA contains several schemas from TPC(tpc.org)
    3. ACCOUNT_USAGE is a schema filled with secure views.
  12. Which of the following statements are true about the Fail-safe?
    1. Only a Snowflake employee can recover data from Fail-safe storage.
    2. The data stored as part of Fail-safe is part of storage costs charged to customers.
  13. Time travel is available for which table types?
    1. Permanent.
    2. Temporary.
    3. Transient.
  14. Which types of stages are automatically available in snowflake and do not need to be created or configured?
    1. User.
    2. Table.
  15. Which table type disappears after the close of the session and therefore has no Fail-safe and no Time Travel options after the close of the session?
    1. Temporary.
  16. You set up a Snowflake account, choosing AWS as your cloud platform provider. What stages can you use to load data files?
    1. USER
    2. TABLE
    3. NAMED INTERNAL
    4. NAMED EXTERNAL - using S3 Buckets
    5. NAMED EXTERNAL - using Azure BLOB Storage
    6. NAMED EXTERNAL - using GCS/GCP Buckets
  17. Which of the following object types are stored within schemas?
    1. Stages.
    2. File Formats
    3. Sequences
    4. Stored Procedures
    5. User Defined Functions