SPC Missed Flashcards
The VALIDATE table function has which parameter as an input argument for a Snowflake user?
- CURRENT_STATEMENT
- JOB_ID
- UUID_STRING
- LAST_QUERY_ID
JOB_ID
Ownership of any objects owned by the dropped role is transferred to
the role that executes the DROP ROLE command
Which clients does Snowflake support Multi-Factor Authentication (MFA) token caching for?
POJ
ODBC, JDBC, Python
What is the purpose of the Snowflake SPLIT_TO_TABLE function?
To split a string and flatten the results into ROWS (not columns)
Which of the below APIs are NOT Snowpipe REST APIs? (Choose two.)
loadHistoryScan
insertHistoryScan
loadFiles
insertFiles
insertReport
insertHistoryScan
loadFiles
Which Snowflake object helps evaluate virtual warehouse performance impacted by query queuing?
IFO_SCHEMA.WAREHOUSE_LOAD_HISTORY
IFO_SCHEMA.WAREHOUSE_METERING_HISTORY
ACCOUNT_USAGE.QUERY_HISTORY
Account_usage.query_history
How does a scoped URL expire?
The URL expires when the persisted query result period ends (i.e. the results cache expires), which is currently 24 hours.
What consideration should be made when loading data into Snowflake?
The number of data files that are processed in parallel is determined by the virtual warehouse.
Which Snowflake object can be created to be temporary?
- Storage Integration
- User
- Stage
- Role
Stage
Files have been uploaded to a Snowflake internal stage. The files now need to be deleted.
Which SQL command should be used to delete the files?
REMOVE
How is table data compressed in Snowflake?
Each column is compressed as it is stored in a micro-partition.
What is the MINIMUM role required to set the value for the parameter ENABLE_ACCOUNT_DATABASE_REPLICATION
ACCOUNTADMIN
ORGADMIN
ORGADMIN
What are the types of data consumer accounts available in Snowflake? (Choose two.)
. Full Account
. Subscriber account
. Public account
. Shared Account
. Reader Account
Full and Reader accounts
Which [friendly] columns are available in the output of a Snowflake directory table?
FRELMS
File_URL
Relative Path
ETag
Last Modified
MD5
Size
A Snowflake user wants to temporarily bypass a network policy by configuring the user object property MINS_TO_BYPASS_NETWORK_POLICY.
Who can do?
orgadmin
accountadmin
support
Snowflake Support
What are the available Snowflake SCALING MODES for configuring multi-cluster virtual warehouses?
[1 : 10] is a scale
Auto-Scale
Maximized
What are the available Snowflake SCALING POLICIES for multi-cluster virtual warehouses?
Economy
Standard
Which completes: Snowflake tables are…
…logical representations of underlying physical data
…owned by a user
…physical instantiation of data loaded into Snowflake
logical representations of underlying physical data
What is the Snowflake recommended Parquet file size when querying from external tables to optimize the number of parallel scanning operations?
256-512 MB
What is the file size recommendation for COPY operations in Snowflake
100-200 MB
Which function should be used to authorize users to access rows in a base table when using secure views with Secure Data Sharing?
CURRENT_ROLE
CURRENT_ACCOUNT
CURRENT_SESSION
CURRENT_ACCOUNT
Note Secure Data Sharing
What is the purpose of using the OBJECT_CONSTRUCT function with the COPY INTO command?
Convert the rows in a relational table to a single VARIANT column and then unload the rows into a file.
What columns are returned when performing a FLATTEN command on semi-structured data? (Choose two.)
Key, Value
The first user assigned to a new account, ACCOUNTADMIN, should create at least one additional user with which administrative privilege?
USERADMIN
All remaining users should be created by the user(s) with the USERADMIN role
Which of the following commands are not blocking operations? (Choose two.)
- delete
- update
- copy
- merge
- insert
Insert & Copy
These are creating new rows, which would not have locks on them
What is the MOST performant file format for loading data in Snowflake?
CSV (Gzipped)
15TB/hr.
Loading data into fully structured (columnarized) schema is ~10-20%
[FASTER OR SLOWER]
than landing it into a VARIANT.
Faster
What is a feature of column-level security in Snowflake?
- internal tokenization
- external tokenization
- network policies
- row access policies
External Tokenization
Who can access a referenced file through a scoped URL?
ME
Which Snowflake feature can be used to find sensitive data in a table or column?
Data Classification
Which stream type can be used for tracking the records in external tables?
- standard
- insert-only
- external
- append-only
Insert Only
Which types of subqueries does Snowflake support? (Choose two.)
- EXISTS, ANY / ALL, and IN subqueries in WHERE clauses: these subqueries can be correlated only
- EXISTS, ANY / ALL, and IN subqueries in WHERE clauses: these subqueries can be correlated or uncorrelated
- EXISTS, ANY / ALL, and IN subqueries in WHERE clauses: these subqueries can be uncorrelated only
- Uncorrelated scalar subqueries in WHERE clauses
- Uncorrelated scalar subqueries in any place that a value expression can be used
- EXISTS, ANY / ALL, and IN subqueries in WHERE clauses: these subqueries can be correlated or uncorrelated
- Uncorrelated scalar subqueries in any place that a value expression can be used
What can you easily check to see if a large table will benefit from explicitly defining a clustering key?
- clustering depth
- values in a table
- clustering status
- clusterin ration
clustering depth
SELECT SYSTEM$CLUSTERING_DEPTH(‘MY_TABLE’, ‘(col1, col2)’);
When floating-point number columns are unloaded to CSV or JSON files, Snowflake truncates the values to approximately what?
(10,4)
(12,2)
(14,8)
(15,9)
(15,9)
The use of which Snowflake table type will reduce costs when working with ETL workflows?
Permanent
Transient
External
Temporary
Temporary
In Snowflake, the use of federated authentication enables which Single Sign-On (SSO) workflow activities? (Choose two.)
Performing role authentication
Authorizing users
Logging into Snowflake
Initiating user sessions
Logging out of Snowflake
Logging In and Out, (and timeout)
Which are the additional columns that the streams create? (Choose three.)
METADATA$IS_DELETED
METADATA$ACTION
METADATA$ISUPDATE
METADATA$COLUMN_ID
METADATA$ISREAD
METADATA$ROW_ID
METADATA$ACTION
METADATA$ISUPDATE
METADATA$ROW_ID
A user has a standard multi-cluster warehouse auto-scaling policy in place.
Which condition will trigger a cluster to shut-down?
When after 5-6 consecutive checks the system determines that the load on the most-loaded cluster could be redistributed.
When after 5-6 consecutive checks the system determines that the load on the least-loaded cluster could be redistributed.
When after 2-3 consecutive checks the system determines that the load on the most-loaded cluster could be redistributed.
When after 2-3 consecutive checks the system determines that the load on the least-loaded cluster could be redistributed.
When after 2-3 consecutive checks the system determines that the load on the least-loaded cluster could be redistributed.
Which Snowflake object returns a set of rows instead of a single, scalar value, and can be accessed in the FROM clause of a query?
There is no object in Snowflake with the ability to return a set of rows
UDTF
UDF
Stored procedure
UDTF
What are the primary authentication methods that Snowflake supports for securing REST API interactions? (Choose two.)
Multi-Factor Authentication (MFA)
Federated authentication
Username and password authentication
Key pair authentication
OAuth
Username and password authentication
OAuth
Which data types can be used in a Snowflake table that holds semi-structured data? (Choose two.)
TEXT
VARIANT
ARRAY
VARCHAR
BINARY
VARIANT
ARRAY
From what stage can a Snowflake user omit the FROM clause while loading data into a table?
The internal named stage
The user stage
The external named stage
The table stage
The table stage
How a Snowpipe charges calculated?
Number of Pipes in account
Total storage bucket size
Per-second/per-core granularity
Per-second/per Warehouse size
Per-second/per-core granularity
Will data cached in a warehouse be lost when the warehouse is resized?
Possibly, if the warehouse is resized to a smaller size and the cache no longer fits.
No, because the size of the cache is independent from the warehouse size.
Yes, because the new compute resource will no longer have access to the cache encryption key.
Yes, because the compute resource is replaced in its entirety with a new compute resource.
Possibly, if the warehouse is resized to a smaller size and the cache no longer fits.
Which functions can be used to share unstructured data through a secure view? (Choose two.)
BUILD_SCOPED_FILE_URL
GET_ABSOLUTE_PATH
GET_PRESIGNED_URL
BUILD_STAGE_FILE_URL
GET_RELATIVE_PATH
BUILD_SCOPED_FILE_URL
GET_PRESIGNED_URL
The MAXIMUM size for a serverless task run is equivalent to what size virtual warehouse?
Medium
4X-Large
2X-Large
Large
2X-Large
Which data types in Snowflake are synonymous for FLOAT? (Choose two.)
REAL
DECIMAL
NUMERIC
DOUBLE
NUMBER
REAL
DOUBLE
How does a Snowflake user extract the URL of a directory table on an external stage for further transformation?
Use the DESCRIBE STAGE command.
Use the GET_ABSOLUTE_PATH function.
Use the SHOW STAGES command.
Use the GET_STAGE_LOCATION function.
Use the GET_STAGE_LOCATION function.
Which of the following commands are valid options for the VALIDATION_MODE parameter within the Snowflake COPY_INTO command? (Choose two.)
RETURN_ERROR_SUM
RETURN_ALL_ERRORS
RETURN_FIRST_n_ERRORS
RETURN_n_ROWS
RETURN_ALL_ERRORS
RETURN_n_ROWS
Snowflake best practice recommends that which role be used to enforce a network policy on a Snowflake account?
ACCOUNTADMIN
USERADMIN
SYSADMIN
SECURITYADMIN
SECURITYADMIN
Which function will provide the proxy information needed to protect Snowsight?
SYSTEM$ALLOWLIST
SYSTEM$GET_TAG
SYSTEM$AUTHORIZE_PRIVATELINK
SYSTEM$GET_PRIVATELINK
SYSTEM$ALLOWLIST
hich statements are NOT correct about micro-partitions in Snowflake? (Choose two.)
Contiguous units of storage.
50 and 500MB of uncompressed data.
Organized in a columnar way.
Non-contiguous units of storage.
50 and 500MB of compressed data.
Non-contiguous units of storage.
50 and 500MB of compressed data.
Which privileges are required for a user to restore an object? (Choose two.)
OWNERSHIP
MODIFY
CREATE
UPDATE
UNDROP
OWNERSHIP
CREATE
Which property helps us control the credits consumed by a multi-cluster warehouse?
Auto scale (AUTO_SCALE)
Maximum Credits (MAX_CREDITS)
Maximum Clusters (MAX_CLUSTERS)
Scaling policy (SCALING_POLICY)
Scaling policy (SCALING_POLICY)
The Snowflake Cloud Data Platform is described as having which of the following architectures?
Shared-disk
Serverless query engine
Shared-nothing
Multi-cluster shared data
Multi-cluster shared data
The property MINS_TO_BYPASS_NETWORK_POLICY is set at which level?
Account
Organization
User
Role
User
Which of the following describes external functions in Snowflake?
Snowflake.
They contain their own SQL code.
They can return multiple rows for each row received.
They are a type of User-defined Function (UDF).
They are a type of User-defined Function (UDF).
When unloading the data for file format type specified (TYPE = ‘CSV’), SQL NULL can be converted to string ‘null’ using which file format option?
SKIP_BYTE_ORDER_MARK
EMPTY_FIELD_AS_NULL
ESCAPE_UNENCLOSED_FIELD
NULL_IF
NULL_IF
Which REST API can be used with unstructured data?
loadHistoryScan
GET /api/files/
insertFiles
insertReport
GET /api/files/
When a database is cloned, which objects in the clone inherit all granted privileges from the source object? (Choose two.)
Internal named stages
Tables
Account
Database
Schemas
Tables
Schemas
A Snowflake user is trying to load a 125 GB file using SnowSQL. The file continues to load for almost an entire day.
What will happen at the 24-hour mark?
The file will stop loading and all data up to that point will be committed.
The file’s number of allowable hours to load can be programmatically controlled to load easily into Snowflake.
The file loading could be aborted without any portion of the file being committed.
The file will continue to load until all contents are loaded.
The file loading could be aborted without any portion of the file being committed.
What SQL command would be used to view all roles that were granted to USER1?
show grants to user USER1;
show grants on user USER1;
describe user USER1;
show grants user USER1;
show grants to user USER1;
What is the most granular object that the Time Travel retention period can be defined on?
Schema
Table
Account
Database
Table
Which are the metadata columns for staged files? (Choose two.)
METADATA$FILE_SIZE
METADATA$FILE_ROW_ID
METADATA$FILE_ROW_NUMBER
METADATA$FILENAME
METADATA$FILEFORMAT
METADATA$FILE_ROW_NUMBER
METADATA$FILENAME
Users with the ACCOUNTADMIN role can execute which of the following commands on existing users?
Can SHOW users DESCRIBE a given user, or ALTER or DROP a user
Can SHOW users, INDEX a given user, or ALTER or DELETE a user
Can DEFINE users, DESCRIBE a given user, or ALTER or DELETE a user
Can SHOW users, DEFINE a given user or ALTER, DROP, or MODIFY a user
Can SHOW users DESCRIBE a given user, or ALTER or DROP a user
Which validation option is the only one that supports the COPY INTO command?
RETURN_ROWS
RETURN__ROWS
RETURN_ALL_ERRORS
RETURN_ERRORS
RETURN_ROWS (UNLOADING)
RETURN_ALL_ERRORS (LOADING)
Which query contains a Snowflake hosted file URL in a directory table for a stage named bronzestage?
select metadata$filename from @bronzestage;
select * from directory(@bronzestage);
list @bronzestage;
select * from table(information_schema.stage_directory_file_registration_history( stage_name=>’bronzestage’));
select * from directory(@bronzestage);
What are the correct settings for column and element names, regardless of which notation is used while accessing elements in a JSON object?
Both the column name and the element name are case-insensitive.
Both the column name and the element name are case-sensitive.
The column name is case-insensitive and the element name is case-sensitive.
The column name is case-sensitive and the element names are case-insensitive.
The column name is case-insensitive and the element name is case-sensitive.
Data storage for individual tables can be monitored using which commands and/or objects? (Choose two.)
SHOW TABLES;
SHOW STORAGE BY TABLE;
Information Schema -> TABLE_FUNCTION
Information Schema -> TABLE_STORAGE_METRICS
Information Schema -> TABLE_HISTORY
SHOW TABLES;
Information Schema -> TABLE_STORAGE_METRICS
By definition, a secure view is exposed only to users with what privilege?
REFERENCES
OWNERSHIP
IMPORT SHARE
USAGE
OWNERSHIP
Which privilege is required to use the search optimization service in Snowflake?
GRANT SEARCH OPTIMIZATION ON SCHEMA TO ROLE
GRANT ADD SEARCH OPTIMIZATION ON DATABASE TO ROLE
GRANT SEARCH OPTIMIZATION ON DATABASE TO ROLE
GRANT ADD SEARCH OPTIMIZATION ON SCHEMA TO ROLE
GRANT ADD SEARCH OPTIMIZATION ON SCHEMA TO ROLE
