Snowflake data movement | account and security | semi-structured Flashcards by Billie Hurley

which type of the object key is only used for decryption?

a) none of these
b) active key
c) destroyed key
d) retired key

d) retired key

How well did you know this?

Not at all

Perfectly

what is the best option to automatically delete the files from stage after it got loaded using COPY INTO command?

a) setting PURGE =TRUE in COPY INTO command
b) cloud provider deletes the file automatically
c) delete manually using cloud provide UI delete option as soon as file is loaded
d) setting DELETE = TRUE in COPY INTO command

a) setting PURGE =TRUE in COPY INTO command

How well did you know this?

Not at all

Perfectly

which security feature is supported in snowflake

a) multi-factor authentication
b) role based access control
c) all of these
d) tri-secret secure encryption

c) all of these

How well did you know this?

Not at all

Perfectly

which ate the CORRECT statements about STREAMS?

a) STREAMS is used to scheduled SQL execution
b) Streams is used to identify and act on changed table records
c) it can not be used with TASKS
d) it is used for change data capture

b) Streams is used to identify and act on changed table records
d) it is used for change data capture

How well did you know this?

Not at all

Perfectly

which role is not a snowflake system-defined role assigned to a new account?

a) CUSTOM
b) PUBLIC
c) SYSADMIN
d) ACCOUNTADMIN
e) SECURITYADMIN

a) CUSTOM

How well did you know this?

Not at all

Perfectly

two of the key attributes that distinguish semi-structure data from structured data are:

a) lack of fixed schema
b) nested data structure
c) fixed schema

a) lack of fixed schema

b) nested data structure

How well did you know this?

Not at all

Perfectly

which parameter does help in loading files whose metadata has expired?

a) Set LAST_MODIFIED_DATE to more than 64 days
b) set LOAD_UNCERTAIN_FILES to TRUE
c) set LOAD_EXPIRED_FILES to TRUE
d) set LAST_MODIFIED_DATE to with in 64 days

b) set LOAD_UNCERTAIN_FILES to TRUE

How well did you know this?

Not at all

Perfectly

dynamic data masking is supported by:

a) VPS
b) business critical
c) enterprise edition
d) standard editions

a) VPS
b) business critical
c) enterprise edition

How well did you know this?

Not at all

Perfectly

external stages require customers to have an account with a cloud storage service provider. which of the following are available currently or have been announced by snowflake as under development?

a) GCP buckets
b) MS Azure Blob
c) BOX
d) DROPBOX
e) AWS S3

a) GCP buckets
b) MS Azure Blob
e) AWS S3

How well did you know this?

Not at all

Perfectly

if a DBA_ROLE creates a database. later the DBA_ROLE was dropped. who will own the database now which was created by DBA_ROLE?

a) DBA_ROLE can’t be dropped as it is the owner of the database
b) database will be dropped
c) role who dropped the DBA_ROLE
d) no one can access

c) role who dropped the DBA_ROLE

How well did you know this?

Not at all

Perfectly

what is the default size of unload file?

a) 100 MB
b) 32 MB
c) 10 MB
d) 16 MB

d) 16 MB

How well did you know this?

Not at all

Perfectly

how can you create a “super-user” or “super-role” in snowflake who can bypass all the authorization checks?

a) ACCOUNTADMIN role is same as super role
b) contact snowflake personnel to create a super role or super user for your account
c) CREATE ROLE SUPER_ROLE;
d) there is no concept of SUPER_ROLE or SUPER_USER in snowflake

d) there is no concept of SUPER_ROLE or SUPER_USER in snowflake

How well did you know this?

Not at all

Perfectly

what data structure types can be ingested into a VARIANT column in a snowflake table?

a) JSON
b) ORC
c) PARQUET
d) AVRO
e) XML

How well did you know this?

Not at all

Perfectly

how many files can each server in a cluster can process in parallel?

a) 4 files
b) depends on how large is the file. if the file is 32 MB in size, the server can process 4 such files in parallel
c) 16 files
d) 1 file
e) 8 files

e) 8 files

How well did you know this?

Not at all

Perfectly

what is the best practice after creating a custom role in a snowflake account?

a) Grant ownership to the SYSADMIN role or a child role under SYSADMIN
b) Grant PUBLIC to the role so all database objects owned by PUBLIC will be available to the new role
c) Grant ownership of the role to itself so a member of the role can control access to the role
d) Grant the role to the USERADMIN role so users can be added to the role

a) Grant ownership to the SYSADMIN role or a child role under SYSADMIN

How well did you know this?

Not at all

Perfectly

what are the features of column-level security?

a) internal tokenization
b) external tokenization
c) dynamic data masking
d) column masking

b) external tokenization

c) dynamic data masking

How well did you know this?

Not at all

Perfectly

select the best option:

a) internal table stage is referenced as @%
b) internal user stage is referenced using @~
c) internal named stage is referenced as @
d) all of these
e) none of these are correct

d) all of these

How well did you know this?

Not at all

Perfectly

what actions can be performed when an error is encountered while loading data from a file?

a) skip file
b) continue loading the file
c) abort the load operation
d) skip file when the number of errors in the file is equal to 10

How well did you know this?

Not at all

Perfectly

what all file formats are supported by snowflake for unloading data from snowflake?

a) AVRO
b) CSV, TSV
c) JSON
d) XML
e) PARQUET

b) CSV, TSV
c) JSON
e) PARQUET

How well did you know this?

Not at all

Perfectly

named stages come in two variables, what are they?

a) internal
b) secure
c) external
d) materialized
e) permanent

a) internal

c) external

How well did you know this?

Not at all

Perfectly

snowflake maintains detailed metadata for each table into which data is loaded. for how long snowflake keeps the metadata active?

a) 24 hours
b) 31 days
c) 32 days
d) 64 days
e) 16 days

d) 64 days

How well did you know this?

Not at all

Perfectly

what is the best recommended size of file for bulk loading?

a) 50 MB - 500 MB compressed
b) 16 MB compressed
c) 10 MB - 100MB uncompressed
d) 10 MB - 100MB compressed

d) 10 MB - 100MB compressed

How well did you know this?

Not at all

Perfectly

when unloading data into multiple files, use the MAX_FILE_SIZE copy options to specify the maximum size of each file created

TRUE or FALSE

TRUE

How well did you know this?

Not at all

Perfectly

what are the three snowflake stages types?

a) named
b) materialized
c) secure
d) user
e) table
f) permanent

a) named
d) user
e) table

How well did you know this?

Not at all

Perfectly

snowflake supports transforming data while loading it into a table using copy command. what all options you have? a) casts b) column reordering c) column omission d) string truncation e) join

a) casts b) column reordering c) column omission d) string truncation

John has setup process to load specific set of files using both Bulk and Snowpipe. this best practice to avoid any missed loading either by bulk loading or snowpipe TRUE or FALSE

FALSE

which role at least should have MFA configured? a) SYSADMIN b) PUBLIC c) ACCOUNTADMIN d) SECURITYADMIN

c) ACCOUNTADMIN

which are the correct statements about TASKS? a) can not be trigged manually b) it can be used with STREAMS c) TASKS is used to scheduled SQL execution d) TASKA is used to identify and act on changed table records e) it is used for change data capture

a) can not be trigged manually b) it can be used with STREAMS c) TASKS is used to scheduled SQL execution

AWS private link is an AWS service for creating private VPC endpoints that allow direct, secure connectivity between AWS VPCs without traversing the public internet TRUE or FALSE

TRUE

a user's default role is a) the role a user is set to each time the user logs in to the system b) the user's default role is always PUBLIC c) the user's default role changes each time the user logs in to the system d) the name used to log in to the webUI

a) the role a user is set to each time the user logs in to the system

what all commands will you use to unload data from your snowflake table to your local file system? a) only GET command b) COPY INTO followed by PUT command c) Only COPY INTO command d) COPY INTO command followed by GET command

d) COPY INTO command followed by GET command

which key concepts should be considered when loading data into snowflake? a) all of these b) stage object c) transformation and error validation d) file format

a) all of these

which are the different ways a user can get permissions to a database object? a) grant the user permissions to the object b) the user granted a role that inherits another role with permissions to the object c) grant the role with permissions on the database objects to the user d) the user creates the objects and the current role becomes the owner of the object

b) the user granted a role that inherits another role with permissions to the object c) grant the role with permissions on the database objects to the user d) the user creates the objects and the current role becomes the owner of the object

the FLATTEN command will parse nested entities into separate rows. one version of the FLATTEN command uses a join and the other uses an object keyword. select two words that represent the options used with the FLATTEN command a) SIDEWAYS b) CONVERT c) TABLE d) LATERAL

c) TABLE | d) LATERAL

which command will help you get the lists of pipes for which you have access privileges? a) LIST PIPE b) SHOW PIPE c) SELECT PIPE() d) LIST PIPES e) SHOW PIPES()

e) SHOW PIPES()

the compute resource used by snowflake for data loading jobs can be provided by: a) user managed virtual warehouse b) hardware provisioned by user directly from cloud providers c) snowflake managed service d) all of these

a) user managed virtual warehouse | c) snowflake managed service

the VARIANT data type imposes a 10 MB to 100MB (compressed) size limit on individual rows TRUE or FALSE

FALSE

how can you ignore metadata and load the already loaded staged file into table? a) using LOAD = FORCE parameter with COPY INTO command b) Using INSERT = TRUE parameter with COPY INTO command c) using FORCE = YES parameter with COPY INTO command d) using ACCEPT = TRUE parameter with COPY INTO command e) using FORCE = TRUE parameter with COPY INTO command

d) using ACCEPT = TRUE parameter with COPY INTO command

time travel is available for which tables types? a) external b) transient c) permanent d) temporary

b) transient c) permanent d) temporary

Permissions on a database objects such as database or tables are granted to: a) roles b) users c) schemas d) users or roles e) virtual warehouses

a) roles

stages which do not support file formats are: a) internal table stage b) external named stage c) internal user stage d) internal named stage

a) internal table stage | c) internal user stage

we loaded an XML file that included a header entity called . in order to bypass this entity and treat each EMPLOYEE object as a separate record, what FILE_FORMAT property did we change?

strip outer element

snowflake provides native support semi-structured data. select true option about snowflake native support for semi-structure data a) automatic conversion of data to optimize internal storage format b) all of these c) flexible-schema data types for loading semi-structures data without transformation d) database optimization for fast and efficient SQL querying

b) all of these

FORCE option is used to load all files, ignoring load metadata if it exists TRUE or FALSE

TRUE

what all commands will you use to load data from external stage system to snowflake table? a) only out command b) copy into followed by put command c) put command followed by copy into d) only copy into command

d) only copy into command

semi-structured data types van be cast using what method? a) column_name:: b) column_name CAST TO c) () column_name d) column_name as e) column_NAME as_

a) column_name::

what all commands will you use to unload data from your snowflake table to external stages? a) COPY INTO command followed by GET command b) only GET command c) only COPY INTO command d) COPY INTO followed by PUT command

c) only COPY INTO command

Semi Structured data can be accessed: a) in a permanent table using the variant data type b) in files in an external stage c) in files on an AWS EC2 server d) in files in an on-perm file server e) in files in a internal stage

a) in a permanent table using the variant data type b) in files in an external stage e) in files in a internal stage

when key: value pairs are required from a semi-structured record and the data is not cast, what is the data type returned? a) binary b) varchar c) variant d) string e) the system automatically cast the value to the correct data type based on the structure

c) variant

snowflake black list some certain IPs by default to ensure that customer is getting highest level of network security TRUE or FALSE

FALSE

at what frequency does snowflake rotate the object keys? a) 1 year b) 30 days c) 60 days d) 16 days

b) 30 days

snowpipe (REST) method only works with internal stages TRUE or FALSE

FALSE

which all commands COPY INTO support while unloading from snowflake tables to internal or external stages? a) JOIN b) Column omission c) String Truncation d) Column reordering e) Casts

a) JOIN b) Column omission c) String Truncation d) Column reordering e) Casts

which stage is allocated to a user? a) internal user stage b) internal tables stage c) internal named stage d) external names stage

a) internal user stage

how can you remove the outer array structure of JSON data? a) set STRIP_OUTER_BRACKET = TRUE b) Set STRIP_OUTER_ARRAY = TRUE c) Set STRIP_OUTER_ARRAY = 1 d) set STRIP_INNER_ARRAY = TRUE

b) Set STRIP_OUTER_ARRAY = TRUE

what level of privilege do you need to view resource monitor? a) operate b) monitor c) ownership d) usage

b) monitor

which vendors do support snowflake natively for federated authenticated and SSO? a) microsoft azure active directory b) onelogin c) google g suite d) okta e) microsoft ADFS

d) okta | e) microsoft ADFS

snowflake provides standard and powerful features that ensure the highest levels of security for your account and users if used properly which are the true statements about snowflake security? a) federated authentication in snowflake is compliant with SAML 2.0 b) Tri-secret requires that customers manage their own keys c) snowflake supports user-based access controls

a) federated authentication in snowflake is compliant with SAML 2.0 b) Tri-secret requires that customers manage their own keys

what are the security layers that snowflake takes care of? a) data protection b) authentication c) access d) authorization e) infrastructure

a) data protection b) authentication c) access d) authorization

MFA is automatically enabled for all the users. please select the true statement. a) MFA is only enabled for ACCOUNTADMIN by default. ACCOUTADMIN can enable it for others users. b) MFA is available for all the users by default

b) MFA is available for all the users by default

what is the best recommended size of data file in case of SNOWPIPE continuous loading? a) 1GB compressed b) if file taking more than a minute, then split the files into more files c) same as of bulk loading (10 MB - 100MB uncompressed) d) same as of bulk loading (10 MB - 100 MB compressed)

b) if file taking more than a minute, then split the files into more files d) same as of bulk loading (10 MB - 100 MB compressed)

which of the following industry compliance standards has snowflake been audited and certified for? a) PCI DSS b) FedRamp c) SOC1 d) SOC Type 2 e) HIPAA f) Cloud GBDQ

a) PCI DSS b) FedRamp c) SOC1 d) SOC Type 2 e) HIPAA

how can you validate the COPY INTO command before actually loading data into the table? a) simply count the loaded records and match with satge b) USE parameter VALIDATION_MODE = RETURN_ _ROWS|RETURN_ERROS | RETURN_ALL_ERRORS c) use parameter VALIDATION_COPY = TRUE | RETURN__ROWS|RETURN_ERRORS|RETURN_ALL_ERRORS with COPY INTO command d) none of these

b) USE parameter VALIDATION_MODE = RETURN_ _ROWS|RETURN_ERROS | RETURN_ALL_ERRORS

which three objects did we explicitly refer to using the COPY INTO command in the lesson on using external stages? a) SCHEMA b) FILE FORMAT c) TABLE d) DATABASE e) STAGE f) VIEW

b) FILE FORMAT c) TABLE e) STAGE

what is the data size limit while loading data into VARIANT column? a) 10 MB - 100 MB compressed b) 10 MB - 100 MB uncompressed c) 1 GB compressed d) 16 MB uncompressed e) 16 MB compressed

e) 16 MB compressed

what is the column datatype used for ingesting semi-structured data? a) VARCHAR b) FLOAT c) STRING d) VARIANT e) NUMBER

d) VARIANT

all files stored in stages (for data loading/unloading) automatically encrypted (using either AES 256 standard or 512 strong encryption) TRUE or FALSE

FALSE

snowflake has a default file format if none is explicitly set or declared in SQL syntax TRUE or FALSE

TRUE

snowflake can query the data from external tables TRUE or FALSE

TRUE

which command will help you to create current permissions on a schema? a) SHOW ALL PREIVILEGE ON SCHEMA .; b) SHOW GRANTS ON SCHEMA .; c) SHOW ROLES ON SCHEMA .; d) SHOW GRANTS OF SCHEMA ,database_name>.;

b) SHOW GRANTS ON SCHEMA .;

when setting the password policy, what options are available? a) Prevent the reuse of a previous password b) set the require length for a password c) all of the above d) snowflake password policy cannot be changed e) force the use of a special character in a password

ord policy cannot be changed

when using a LATERAL FLATTEN in a semi-structured data query, what does the LATERAL do? a) modifies the data in an array to a variant data type so it will work with the FLATTEN command b) causes the FLATTEN command to be recursive and drill down into all levels of the records c) works as a self join to join the current record key/ value pairs to an array in the same record d) causes the FLATTEN command to read an array in reverse order e) breaks an array into multiple elements

e) breaks an array into multiple elements

STRIP_NULL_VALUE converts a JSON "null" value to a SQL NULL value TRUE or FALSE

TRUE

you set up a snowflake account, choosing AWS as your cloud platform provider. what stages can you use to load data files? a) NAMED EXTERNAL - using GCS/GCP buckets b) NAMED INTERNAL c) NAMED EXTERNAL - using s3 buckets d) NAMED EXTERNAL - using Azure BLOB storage e) TABLE f) USER

a) NAMED EXTERNAL - using GCS/GCP buckets b) NAMED INTERNAL c) NAMED EXTERNAL - using s3 buckets d) NAMED EXTERNAL - using Azure BLOB storage e) TABLE f) USER

semi-structured data must be transformed prior to loading the data into a snowflake table TRUE or FALSE

FALSE

How can you create a user in snowflake without password? a) using salesforce web interface b) using SQL command - CREATE USER c) user creation without password is not possible

b) using SQL command - CREATE USER

SNOWPIPE AUTO_INGEST method only works with external stages TRUE or FALSE

TRUE

a user cannot view the result set from a query that another user expected expect ACCOUNTADMIN TRUE or FALSE

FALSE

what is the recommendation for file size for parquet files for loading? a) 1GB b) 2 GB c) 16 MB d) 3 GB

a) 1 GB

if an account has federated authentication enabled. can snowflake admins still maintain user id and passwords in snowflake YES or NO

YES

federated authentication is supported by: a) VPS b) Enterprise edition c) business critical d) standard edition

a) VPS b) Enterprise edition c) business critical d) standard edition

which role is inherited to every other role in the account? a) PUBLIC ROLE b) PREFERED ROLE c) CONTROL ROLE d) DEFAULT ROLE e) STANDARD ROLE

a) PUBLIC ROLE

what is the default compression algorithm snowflake apply wile unloading data? a) BZ2 b) GZIP c) ZSTD d) GZIP3 e) NONE

b) GZIP

what all commands will you use to load data from your local file system to snowflake table? a) COPY INTO followed by PUT command b) only PUT command c) PUT COMMAND followed by COPY INTO d) only COPY INTO command

c) PUT COMMAND followed by COPY INTO

loading using SNOWPIPE is best useful for: a) load large files with size of 500 MB b) load small volumes of data incrementally c) none of these d) data files which takes at least 30 to 60 minutes

b) load small volumes of data incrementally

which types of stages are automatically available in snowflake and do not need to be created or configured? a) named external b) table c) named internal c) user

b) table | c) user

which approach would result in improved performance through linear scaling of data ingestion workload? a) consider the practice of splitting input file batch within the recommended range of 10MB to 100MB b) consider the practice of organizing data by granular path c) all of the above d) resize virtual warehouse

c) all of the above

which snowflake edition does allow for customer-managed keys encryption a) only VPS b) standard (or higher) c) business critical (of higher) d) enterprise (or higher)

c) business critical (of higher)

all data transparently and synchronously replicated across minimum 2 availability zones TRUE or FALSE

FALSE

snowflake supports many methods of authentication. which are the supported authenticated methods in all snowflake editions? a) OAuth b) MFA c) only MFA is supported by all the snowflake editions d) only MFA and SSO are supported by all the snowflake editions e) SSO

a) OAuth b) MFA e) SSO

which objects are securable objects in snowflake? a) database b) table c) warehouse d) file format

a) database b) table c) warehouse d) file format