Snowflake data movement | account and security | semi-structured Flashcards

1
Q

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

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

A

d) retired key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

a) setting PURGE =TRUE in COPY INTO command

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

c) all of these

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

a) CUSTOM

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

a) lack of fixed schema

b) nested data structure

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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

A

b) set LOAD_UNCERTAIN_FILES to TRUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

dynamic data masking is supported by:

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

A

a) VPS
b) business critical
c) enterprise edition

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

c) role who dropped the DBA_ROLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

what is the default size of unload file?

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

A

d) 16 MB

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

e) 8 files

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

what are the features of column-level security?

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

A

b) external tokenization

c) dynamic data masking

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

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

A

d) all of these

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

A

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?
1
Not at all
2
3
4
5
Perfectly
19
Q

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

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

A

b) CSV, TSV
c) JSON
e) PARQUET

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

named stages come in two variables, what are they?

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

A

a) internal

c) external

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

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

A

d) 64 days

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

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

A

d) 10 MB - 100MB compressed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

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

A

TRUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

what are the three snowflake stages types?

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

A

a) named
d) user
e) table

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

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

A

FALSE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

which role at least should have MFA configured?

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

A

c) ACCOUNTADMIN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

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

A

TRUE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

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

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

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

A

d) COPY INTO command followed by GET command

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

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

a) all of these

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

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

A

c) TABLE

d) LATERAL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

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()

A

e) SHOW PIPES()

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

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

a) user managed virtual warehouse

c) snowflake managed service

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

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

TRUE or FALSE

A

FALSE

38
Q

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

A

d) using ACCEPT = TRUE parameter with COPY INTO command

39
Q

time travel is available for which tables types?

a) external
b) transient
c) permanent
d) temporary

A

b) transient
c) permanent
d) temporary

40
Q

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

a) roles

41
Q

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

a) internal table stage

c) internal user stage

42
Q

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?

A

strip outer element

43
Q

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

A

b) all of these

44
Q

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

TRUE or FALSE

A

TRUE

45
Q

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

A

d) only copy into command

46
Q

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

a) column_name::

47
Q

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

A

c) only COPY INTO command

48
Q

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

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

49
Q

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

A

c) variant

50
Q

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

TRUE or FALSE

A

FALSE

51
Q

at what frequency does snowflake rotate the object keys?

a) 1 year
b) 30 days
c) 60 days
d) 16 days

A

b) 30 days

52
Q

snowpipe (REST) method only works with internal stages

TRUE or FALSE

A

FALSE

53
Q

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

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

54
Q

which stage is allocated to a user?

a) internal user stage
b) internal tables stage
c) internal named stage
d) external names stage

A

a) internal user stage

55
Q

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

A

b) Set STRIP_OUTER_ARRAY = TRUE

56
Q

what level of privilege do you need to view resource monitor?

a) operate
b) monitor
c) ownership
d) usage

A

b) monitor

57
Q

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

A

d) okta

e) microsoft ADFS

58
Q

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

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

59
Q

what are the security layers that snowflake takes care of?

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

A

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

60
Q

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

A

b) MFA is available for all the users by default

61
Q

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)

A

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)

62
Q

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

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

63
Q

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

A

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

64
Q

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

A

b) FILE FORMAT
c) TABLE
e) STAGE

65
Q

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

A

e) 16 MB compressed

66
Q

what is the column datatype used for ingesting semi-structured data?

a) VARCHAR
b) FLOAT
c) STRING
d) VARIANT
e) NUMBER

A

d) VARIANT

67
Q

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

TRUE or FALSE

A

FALSE

68
Q

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

TRUE or FALSE

A

TRUE

69
Q

snowflake can query the data from external tables

TRUE or FALSE

A

TRUE

70
Q

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>.;

A

b) SHOW GRANTS ON SCHEMA .;

71
Q

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

A

ord policy cannot be changed

72
Q

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

A

e) breaks an array into multiple elements

73
Q

STRIP_NULL_VALUE converts a JSON “null” value to a SQL NULL value

TRUE or FALSE

A

TRUE

74
Q

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

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

75
Q

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

TRUE or FALSE

A

FALSE

76
Q

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

A

b) using SQL command - CREATE USER

77
Q

SNOWPIPE AUTO_INGEST method only works with external stages

TRUE or FALSE

A

TRUE

78
Q

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

TRUE or FALSE

A

FALSE

79
Q

what is the recommendation for file size for parquet files for loading?

a) 1GB
b) 2 GB
c) 16 MB
d) 3 GB

A

a) 1 GB

80
Q

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

YES or NO

A

YES

81
Q

federated authentication is supported by:

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

A

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

82
Q

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

a) PUBLIC ROLE

83
Q

what is the default compression algorithm snowflake apply wile unloading data?

a) BZ2
b) GZIP
c) ZSTD
d) GZIP3
e) NONE

A

b) GZIP

84
Q

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

A

c) PUT COMMAND followed by COPY INTO

85
Q

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

A

b) load small volumes of data incrementally

86
Q

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

A

b) table

c) user

87
Q

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

A

c) all of the above

88
Q

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)

A

c) business critical (of higher)

89
Q

all data transparently and synchronously replicated across minimum 2 availability zones

TRUE or FALSE

A

FALSE

90
Q

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

a) OAuth
b) MFA
e) SSO

91
Q

which objects are securable objects in snowflake?

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

A

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