Snowflake data movement | account and security | semi-structured Flashcards
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
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
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
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
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
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
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
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
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
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
what is the default size of unload file?
a) 100 MB
b) 32 MB
c) 10 MB
d) 16 MB
d) 16 MB
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
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) JSON
b) ORC
c) PARQUET
d) AVRO
e) XML
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
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
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
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
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) 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
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
named stages come in two variables, what are they?
a) internal
b) secure
c) external
d) materialized
e) permanent
a) internal
c) external
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
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
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
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
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
