Datacamp Snowflake Course

Question 1

In snowflake, an _ is created to reference the folder directory on cloud (like S3 bucket).

Answer 1

external stage

Question 2

A table can be created pointing to the stage, that allows Snowflake to query data from cloud directory (mapped to the stage).
How to provide Snowflake permission to access cloud directory?

Assume required role with permission policy is already created in cloud (AWS).

Answer 2

1. You need to create Storage Integration in Snowflake:

CREATE STORAGE INTEGRATION my_s3_integration
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<your-account-id>:role/<your-role-name>'
  STORAGE_ALLOWED_LOCATIONS = ('s3://your-bucket/path/');

2. Copy the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID

DESC INTEGRATION my_s3_integration;

3. Update trust policy by editing sts:ExternalId

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::<snowflake-account-id>:user/<snowflake-user>"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "<external-id-from-desc>"
        }
      }
    }
  ]
}

AWS_EXTERNAL_ID property from external stage

Question 3

create an external STAGE in Snowflake using STORAGE INTEGRATION.

Answer 3

CREATE OR REPLACE STAGE my_s3_stage
  URL = 's3://your-bucket/path/'
  STORAGE_INTEGRATION = my_s3_integration
  FILE_FORMAT = (TYPE = CSV);

Question 4

Create an external table from external stage in Snowflake

Answer 4

CREATE OR REPLACE EXTERNAL TABLE my_ext_table (
  col1 STRING,
  col2 STRING
)
WITH LOCATION = @my_s3_stage
FILE_FORMAT = (TYPE = CSV);

Question 5

Provide correct order of steps for loading data into Snowflake from a cloud provider.
* a) Create the stage.
* b) Refresh the stage.
* c) Create storage integration.
* d) Load data into table from stage
* e) Reference the stage in table creation

Answer 5

1. c)
2. a)
3. b)
4. e)
5. d)

Question 6

Two benefits of using Storage Integration:

Answer 6

1. No need to pass AWS keys
2. IAM role usage is trackable in AWS

Question 7

_ role has complete access to Snowflake account.

Answer 7

ACCOUNTADMIN

manages account-level tasks like billing.

Question 8

_ role has privileges to create warehouses and databases (and other objects) in an account.

Answer 8

SYSADMIN

Question 9

_ role is dedicated to user and role management

Answer 9

USERADMIN

Can create users and roles in the account

Question 10

How to assign a user the privilege to manage objects owned by the CUSTOM_ADMIN_HR role?

Answer 10

While creating the user, set the ‘Default Role’ field to CUSTOM_ADMIN_HR from the dropdown.

Question 11

You created a new warehouse that you want users with SYSADMIN role to be allowed to use. How will you achieve it?

Answer 11

1. Click on warehouse. Under Privileges section, click to add privilege (+Privilege)
2. Set role = SYSADMIN and Privilege = Usage
3. Click on Grant Privilege.

Question 12

Convert ‘80’ into number in Snowflake SQL.

Answer 12

1. cast('80' as int)
2. ‘80’::int

Question 13

Convert order_timestamp
(2015-01-01 11:38:36.000 -0800) to date.

Answer 13

cast(order_timestamp as date)

Question 14

Capitalize each word of string in Snowflake SQL

Answer 14

INITCAP()