AWS AI Fundamentals > SnowPro Core > Flashcards

SnowPro Core Flashcards

(277 cards)

Study These Flashcards
1
Q

How many values can a scalar function return per invocation?

A

1

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

As a Snowflake Analyst I see that a query is taking unusually long and consuming too many credits. Which system function would I use to stop this query?

A

SYSTEM$CANCEL_QUERY

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

Estimation functions allow us to perform a calculation quicker but with less accuracy. True or false?

A

True

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

Which function is not an estimation function? APPROX_PERCENTILE(), HLL(), APPROX_TOP_K(), FACTORIAL()

A

FACTORIAL()

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

Table sampling is a way to read a random subset of rows from a table. True or false?

A

True

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

As a data engineer I want to create a file URL that is only valid for 24hours. Which file function would I use? BUILD_URL, BUILD_STAGE_FILE_URL, BUILD_SCOPED_FILE_URL, BUILD24_URL?

A

BUILD_SCOPED_FILE_URL

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

As a data engineer I would like to generate a pre-signed URL for a file stored in an external Stage. Which privilege does this role require on the Stage? USAGE, READ, SELECT, DROP

A

USAGE. Sounds weird but you need USAGE on the file to be able to generate an insecure download link to the file.

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

On which object is Directory Table enabled?

A

STAGE. You create directory tables whaich are essentailly metadata tables.

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

Which type of file URL can you not download with the File Support REST API?

A

Pre-signed. File Support API is meant to be secure. Pre-signed is by nature, unsecure.

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

Which result from the APPROXIMATE_SIMILARITY estimation function would indicate two sets of rows have no overlap?

A

0.0. Zero indicates no overlap, 1 indicates identical.

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

What do Snowflake call the files which are stored in cloud storage and are used to segment data loaded into Snowflake?

A

Micro-partitions.

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

What is the size range for uncompressed micro-partitions?

A

50-500MB. This is the typical uncompressed micro-partition size after data is imported into Snowflake.

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

Which layer in the multi-cluster shared data architecture maintains micro-partition metadata?

A

Services Layer. Metadata storage is not stored in the standard Storage Layer. It is maintained in the Services Layer and often involves serverless compute.

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

An INSERT statement can add micro-partitions to a table without locking any existing micro-partitions. True or false?

A

True. Adding new data to a table just adds new micro-partitions, not affecting the existing partitions.

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

What order is the life cycle of table data?

A

Active Table Data -> Time travel -> Fail Safe.

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

What is the maximum number of days the property DATA_RETENTION_TIME_IN_DAYS can have?

A
  1. The maximum amount of days for Time travel is 90 days if it has been specified, and only in Enterprise Editions of Snowflake.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

The property DATA_RETENTION_TIME_IN_DAYS can be set on both tables and databases. True or false?

A

True. DATA_RETENTION_TIME_IN_DAYS can be set on objects from an Account level, down.

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

Fail-safe is a non-configurable period of how many days?

A

7 days.

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

What is the maximum number of shares a data provider can create?

A

No limit.

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

What do data consumers create from a SHARE object?

A

Database. They must first create a database from the share.

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

A data engineer is running some transformation jobs using a M virtual warehouse. The virtual warehouse seems to be suspending between the jobs, making subsequent jobs take longer. What could be the issue?

A

The Virtual Warehouse AUTO_SUSPEND value is set too low. Resuming the data warehouse can take time which may add to the query execution of subsequent queries.

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

What’s the maximum number of days a user could set the table property DATA_RETENTION_TIME_IN_DAYS to for a transient table on a business critical edition Snowflake account?

A

1 day. Temporary or transient objects have a Time Travel period of 1 day maximum, irrespecitive of account type.

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

Materialized views contribute to both compute and storage costs. True or false?

A

True. A materilaised view is a stored results of a view producing query.

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

What are the rules when referencing elements in a VARIANT object, related to case sensitivity?

A

Column names are case insensitive but elements inside the JSON are case sensitive

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What is the recommended COMPRESSED file size when loading data into Snowflake?
100-250mb
26
Once a data provider creates a share object, what two operations must they perform next to ensure a data consumer can read the shared data?
Grant privileges on the database objects to the share, and add consumer account to the share.
27
Why would you use a materialised view?
Materialised viewsd can make complex queries that are commonly executed, readily available. They can also be created on top of external tables to imporve query performance. Materialised views, unlike tables automatically update as the data in the underlying base table changes. This is a serverless compute cost.
28
When securely sharing data, data providers can share data with a data consumer in a different cloud region or cloud provider without using replication. True or false?
False. You can not securely share data across different cloud providers or cloud regions. This is because the consumer is accessing the same underlying storage from the provider (which is residing on a cloud service and region). The way you get around this is by gettting the consumer to create a Snowflake account with the same provider and region. Organisations can have accounts from mulitple regions.
29
Which SQL commands would show the contents of a stage?
LS @, LIST@
30
Which requirements must be met to reuse the results cache?
The new query matches the cached query exactly. The underlying table data must not have changed. The role executing the query must have the required privileges on the objects.
31
What is a selective point lookup?
A query looking for a sigle record or a small number of highly targeted conditions (WHERE = ; or anything with an IN clause).
32
Which symbols are used when referencing a table stage via SQL?
@%. TABLE Stages are referenced by @%. User stages use @~, and Named stages use only @
33
What is the query to remove a virtual warehouse?
DROP WAREHOUSE ;
34
If the query profile indicates queries are regularly spilling to remote storage what are the recommended solution?
Process data in smaller batches, and/or increase the size of the Data Warehouse.
35
Which Snowflake object would be ideal to schedule an execution to refresh a secondary database for replication?
Task. Scheduled reloads are called tasks in Snowflake.
36
On which version of Snowflake is Secure Data Sharing not available?
Virtual Private Snowflake (VPS). This is supposed to be a private secure platform so it doesn't support shares.
37
The following command is permitted when interacting with a Virtual Warehouse: ALTER WAREHOUSE DE_VW SET WAREHOUSE_SIZE=LARGE. True or false?
True. You can change the size of a warehouse via SQL commands. This is an example of vertical scaling.
38
As a data engineer I want to create a file URL that is only valid for 24hours. Which file function would I use?
BUILD_SCOPED_FILE_URL. Scoped file shares are for 24Hrs only.
39
What does it mean to overload a User Defined Function (UDF)?
Thge ability for mulitple UDFs to exist with the same name within the same schema, as long as their signatures are different.
40
Automatic clustering improves performance on queries that include what?
Automatic clustering in Snowflake assists filters that filter, JOIN or sort on specific columns. Clustering keys are columns wiwth similar data. Using clustering keys, Snowflake can exclude certain micro-partitions from the query (pruning).
41
What is the default value for the table property DATA_RETENTION_TIME_IN_DAYS for an account on the enterprise edition of Snowflake?
1. The maximum it can be set to is 90, but the default is 1 day (for all editions).
42
Snowflake partitions files loaded into Snowflake automatically. True or false?
True. Snowflake analysis anc transforms imported data into its proprietary columnar optimised data sotrage format.
43
Which properties are not available to a file format object of type CSV?
STRIP_NULL_VALUES, STRIP_OUTER_ARRAY. These specific parameters are only available for JSON type file formats.
44
Which SQL extensions did Snowflake add for use with the Time Travel feature?
UNDROP, BEFORE, AT. These SQL commands are all used in Time travel functions specifically.
45
Virtual warehouses are billed on a per second basis. What is the minimum number of seconds a virtual warehouse is billed for?
60 seconds. The minimum up time a warehouse can be is 60 seconds. This is because the warehouses are charged for the first 60s and then by the second after that.
46
Users can SSH (Secure Shell) into the compute instances which make up a Virtual Warehouse. True or false?
False. The instances that run the compute are abstracted away from the user in Snowflake.
47
Which system-defined role is it recommended to assign custom roles to?
SYSADMIN. Snowflake recommends to create custom roles as child roles to the SYSADMIN role. This way systems administrators inherit all the grants to the custom roles, which will help to administer the objects involved.
48
User defined functions can NOT be executed in the context of another statement. For example, in a SELECT statement. True or false?
False. UDF can be used inside SELECT statements.
49
What is the function of a row access policy?
Restricting which rows are returned in a query.
50
Which function can be used to automatically detect a schema definition?
INFER_SCHEMA(). This function auto-detects and returns the structure (columns) of semi-strucutred data.
51
If the PURGE copy option were set to TRUE what would happen to data files that are successfully loaded into Snowflake from an external stage?
They would be removed from the external stage.
52
What is the purpose of the TYPE option when creating a file format object?
It specifies the file type to be imported so Snowflake can parse it correctly.
53
In which state is a Virtual Warehouse billable?
Started.
54
What do you configure a SHARE object with?
Privileges on the object. Account identifier of the consumer.
55
Time Travel is not possible on transient tables. True or false?
False. Time travel can be used on temporary and transient objects for up to 1 day.
56
The ACCOUNTADMIN can perform the same functions as the SECURITYADMIN, SYSADMIN & ORGAMIN. True or false?
False. ORGADMIN is higher level than ACCOUNTADMIN.
57
Fill in the blank. Filters should be used as _________ as possible.
Early. Filters should be used as early as possible to allow for exclusion, pruning and optimised query performance.
58
Which layer of the Multi-cluster Shared Data Architecture handles user authentication?
"Services" Layer
59
The account usage share contains a database called SNOWFLAKE. The views in this database are used to provide fine-grained metrics at the account and object level. What is the maximum number of months data in these views available for?
Data in the ACCOUNT_USAGE schema is available for the past 12 months.
60
What level of cardinality is it recommended a column have when selected in a query that makes use of a GROUP BY?
Low cardinality. This means low uniqueness. This means that Snowflake will not have to calculate and store separate values for the potentially many distinct groupings during processing. This will cause in-memory increase if the column cardinality is high.
61
Network policies currently support which type of IP address?
IPv4 only.
62
A Snowflake user creates a clone of a 'TABLE_A' and names it 'TABLE_B'. When new records are inserted into 'TABLE A' what is expected to happen to 'TABLE B'?
The new records do not show at all in TABLE_B. to refresh the data in the clone requires a task that uses a REFRESH command. The clone is a completely different and independent object.
63
What does each database created in an account automatically include?
Information Schema
64
The following query can make use of the results cache: SELECT NAME, EMP_ID, CURRENT_TIME() FROM EMPLOYEE. True of false?
False. This has a reference to the current time which means the resulting query conditions are not identical.
65
Which layers are in Snowflake’s Multi-cluster Shared Data Architecture?
Storage layer, Compute layer, Services layer.
66
Which system function is called to retrieve clustering metadata for a column or columns?
system$clustering_information. This is an environment variable that we can call to give use metadata clustering information.
67
How many geographic regions in a cloud platform can a Snowflake account be deployed into?
1. Accounts are single region. Organisations can have multiple regions.
68
When copying files from a table to an internal stage using the COPY INTO command, the results are split into multiple files. Which copy option would I use to ensure only one file is produced?
SINGLE. This paramater is a boolean that allows the file to be saved as a single file.
69
What are the two scaling policies that can be defined when creating a multi-cluster warehouse? Choose two correct values.
Economy, Standard. Standard scales up when there are queries queued, and scales down when less servers can complete the left over work. Economy only scales up when it is calculated that it would require at least 6 minutes from another server to complete the work.
70
What is the function of the AUTO_SUSPEND property of a Virtual Warehouse?
Suspends the warehouse after a set amount of time of inactivity.
71
What is the ecapsulated <> section of the following Snowflake account URL referred to as: snowflakecomputing.com?
Account Identifier. The parts that make up the account identifier are (in order): The account locator, the account region and the account cloud platform.
72
Which semi-structured data formats do Snowflake natively store and query?
XML, JSON, PARQUET, AVRO and ORC
73
What is the function of the INITIALLY_SUSPENDED property of a Virtual Warehouse?
It creates a warehouse in a suspended state.
74
Databases are not required to have a unique identifier. True or false?
False. Database objects are required to have a unique identifier within an account.
75
How many days is the query history maintained in the history tab of the classic UI?
14.
76
Which object does an external function make use of to hold security related information?
API integration.
77
Which cloud platforms can a Snowflake account be hosted in?
Google Cloud, AWS and Microsoft.
78
Which table type does have the fail-safe feature?
Permanent.
79
Which configuration of min_cluster_count & max_cluster_count would put a muti-cluster warehouse in maximized mode?
Min and Max cluster count at the same value.
80
Which languages can be used to develop a User Defined Function (UDF)?
Python, Javascript, Java and SQL.
81
Data loading typically requires the use of a XXL Virtual Warehouse. True or false?
False.
82
What is the purpose of the VALIDATE function?
Allows the user to view all errors encountered during a previous COPY INTO execution.
83
Fail-safe is available for external tables. True or false?
False.
84
What is the maximum number of Time Travel days for a table of the permanent type?
90.
85
What is the process of eliminating unrequired micro-partitions called during a query?
Pruning.
86
Which types of notation are used to traverse semi-structured data natively in Snowflake?
Dot notation, bracket notation.
87
In Snowflake, if periodic rekeying is enabled for encrypted data, after how many months does the system automatically rotate the encryption keys?
12 months. Enryption key rotation is automatically performed every 12 months.
88
How many accounts can a data consumer share a shared database with?
0. Data consumers can not re-share shared data.
89
Which symbols are used when referencing a user stage via SQL?
@~
90
Which symbols are used when referencing a user stage via SQL?
False. Micro-partitions are immutable. But they can be reorganised, which essentially replaces them.
91
When setting DATA_RETENTION_TIME_IN_DAYS to 0 on a table, Time Travel is effectively disabled for that table. True or false?
True. DATA_RETENTION_TIME_IN_DAYS=0, switches time travel off.
92
What is the default compression format used when unloading files from Snowflake using the GET command?
GZIP.
93
Which result from the APPROXIMATE_SIMILARITY estimation function would indicate two sets of rows overlap significantly but are not identical?
A number close to 1. 1 is identical, 0 is no similarities.
94
What is the SnowSQL CLI tool used for?
Connectinf to and issuing SQL command to Snowflake through the command line.
95
How many database objects can a data provider add to a SHARE object?
Unlimited.
96
You can execute commands as the ACCOUNTADMIN using the SnowSQL CLI tool. True or False?
True.
97
What is the definition of Tri-secret secure?
A composite encryption key to encrypt data files made up of a user provider key and a Snowflake key.
98
A schema cannot be cloned. True or false?
False. All database objects can be cloned.
99
What is the recommended maximum number of columns (or expressions) defined per clustering key?
3 or 4 is the recommended maximum number of columns for a clustering key.
100
What is the behaviour of the COPY INTO command when the ON_ERROR option is set to CONTINUE?
Load the file even when errors are found.
101
Cloning a table copies the underlying data effectively doubling storage costs. True or false?
False. A clone is merely a reference to the initial data micro-partitions (until the data changes).
102
Every securable object is owned by a single role. True or false?
True. At least a single role.
103
External tables can be cloned. True or false?External tables can be cloned. True or false?
False. External tables exist outside of Snowflake and so can not be cloned.
104
What attributes make Snowflake a SaaS solution?
No hardware management. Pay for what you use pricing plan.
105
Snowflake uses the following access control schemes: role based access control (RBAC) and _________.
Discretionary Acces Control (DAC).
106
Which type of view bypasses some query optimizations to improve data security?
Secure Views. Secure views are data reduction views for security purposes. Users can not access the underlyinmg tables. Snowflake has made the decision to not include Secure views in query optimisation to ensure that queries do not expose the restricted data.
107
The COPY INTO
statement does NOT require a virtual warehouse to run. True or false?
False. Data importation into Snowflake requires a warehouse.
108
Natural clustering is determined by the order in which data is loaded. True or false?
True. Table data is partitioned and stored in the order it was loaded.
109
What happens to historical data when the retention period for an object ends?
It goes into fail safe.
110
A company’s security audit requires generating a report listing all Snowflake logins (e.g., date and user) within the last 90 days.
SELECT EVENT_TIMESTAMP, USER_NAME FROM ACCOUNT_USAGE.LOGIN_HISTORY;
111
Which view in SNOWFLAKE.ACCOUNT_USAGE shows which IP address a user connected to Snowflake?
LOGIN_HISTORY.
112
Which Snowflake feature can be used to find sensitive data in a table or column?
Data Classification
113
Which functions can be used to identify the data type stored in a VARIANT column?
IS_DATE_VALUE, IS_NULL_VALUE.
114
How are privileges inherited in a role hierarchy in Snowflake?
Parents inherit privileges from their children. Confusing but Snowflake wording for this is Privileges are inherited by any roles above 'that' role in the hierarchy.
115
What is the recommended Snowflake data type to store semi-structured data like JSON?
VARIANT.
116
Which of the following Snowflake features provide continuous data protection automatically?
Time travel and Fail-Safe.
117
What is the maximum row size in Snowflake?
16 MB.
118
A Snowflake user wants to optimize performance for a query that queries only a small number of rows in a table. The rows require significant processing. The data in the table does not change frequently. What should the user do?
Creaet a materialised view. This is given away by the keywords, significant processing, small number of rows and does not change frequently. Materialised views are store the result of complex queries, so the storage cost here would be small. Also materialised views use serverless compute for data refresh. The fact that it doesn't change often means this cost is reduced.
119
What is the advantage of using a reader account?
It can be used by a client that doen't have a Snowflake Account.
120
Which stages are created by default, with no need to use the CREATE STAGE command?
Table Stages and User Stages.
121
Which columns are available in the output of a Snowflake directory table?
RELATIVE_PATH, LAST_MODIFIED.
122
What type of function returns one value for each invocation?
Scalar.
123
Which Snowflake object will consume credits during automatic background maintenance?
Materialised View consume serverless compute to constantly refresh the data based onchanges in the source table.
124
A Snowflake user needs to import a JSON file larger than 16 MB. What file format option could be used?
strip_outer_array=True. allows snowflake to process each element in the array as a separate row.
125
Which Snowflake table objects can be shared with other accounts?
Permanent tables and External tables. Transient or temporary tables can not be shared.
126
Which Snowflake table objects can be shared with other accounts?
Stream. A stream in Snowflake is a lightweight object that tracks row-level changes (inserts, updates, and deletes) made to a table, a view, or another supported object. It's essentially a Change Data Capture (CDC) mechanism that records the metadata of these changes.
127
What is the recommended approach for unloading data to a cloud storage location from Snowflake?
Unload the data directly to the cloud storage location.
128
Which file format will keep floating-point numbers from being truncated when data is unloaded?
Parquet. Parquet have specific data type for floating point numbers. Other file types store floats as text.
129
What type of columns does Snowflake recommend to be used as clustering keys?
Columns that are used in JOINS and selective filters.
130
What does the client redirect feature in Snowflake enable?
A redirect of client connection to Snowflake accounts in different regions for business continuity.
131
If a Small Warehouse is made up of 2 servers/cluster, how many servers/cluster make up a Medium Warehouse?
4. Warehouse size doubles with every increase in size name.
132
What authentication method does the Kafka connector use within Snowflake?
Key Pair. Key pair is an authentication method. Connections and service processes do not use normal auth methods. They also do not require anything authorisation protocol like OAuth.
133
Query results are stored in the Result Cache for how long after they are last accessed, assuming no data changes have occurred?
24 Hours. Results cache is stored for 24 hours after last accessing.
134
What feature of Snowflake Continuous Data Protection can be used for maintenance of historical data?
Time travel.
135
What information is included in the display in the Query Profile?
Graphical representation of the query plan. Details and statistics for the overall query.
136
What are the key characteristics of ACСOUNT_USAGE views?
Records dropped objects. Data Latency of 45 mins to 3 hours.
137
Which Snowflake object can be created to be temporary?
Stages, tables and views are temporary objects that are session based.
138
Why would a Snowflake user load JSON data into a VARIANT column instead of a string column?
A VARIANT column can be used to create a data hierarchy, whereas string columns can not. Also you can use dot notation to extract information from VARIANT columns.
139
The first user assigned to a new account, ACCOUNTADMIN, should create at least one additional user with which administrative privilege?
USERADMIN. Snowflake recommends creating a USERADMIN to manage user, role and privileges.
140
Which command can be used to delete staged files from a Snowflake stage when the files are no longer needed?
REMOVE. REMOVE is used on staged files, not DROP.
141
What happens to the objects in a reader account when the DROP MANAGED ACCOUNT command is executed?
The objects are dropped immediately.
142
When should you consider disabling auto-suspend for a Virtual Warehouse?
When compute must be available with no lag time. When managing a steady workload.
143
Which common query problems can the Query Profile help a user identify and troubleshoot?
When there are exploding JOINS. Where there is a UNION with ALL.
144
Which command is used to take away staged files from a Snowflake stage after a successful data ingestion?
REMOVE.
145
How does Snowflake enable OAuth?
By configuring a security integration.
146
What commands can be used to see what files are stored in a stage?
LS and LIST.
147
A Snowflake user wants to temporarily bypass a network policy by configuring the user object property MINS_TO_BYPASS_NETWORK_POLICY. What should they do?
Contact Snowflake Support.
148
Which clients does Snowflake support Multi-Factor Authentication (MFA) token caching for?
Python and ODBC connectors.
149
What is the purpose of a Query Profile?
To profile a particular query to understand the mechanics of the query, it's behaviour and performance.
150
What strategies can be used to optimize the performance of a virtual warehouse?
Increase the warehouse size. Reduce queuing.
151
What is the Snowflake multi-clustering feature for virtual warehouses used for?
To improve concurrency for users and queries.
152
Which table type has a Fail-safe period of 7 days?
Permanent tables only.
153
What are the available Snowflake scaling modes for configuring multi-cluster virtual warehouses?
Maximised, Auto-scale.
154
What is the MOST performant file format for loading data in Snowflake?
CSV (Gzipped)
155
hat columns are returned when performing a FLATTEN command on semi-structured data?
KEY and VALUE. This is because Flatten just parses out the first level of the semi-structured data (JSON), which is essentailly a dictionary.
156
Who can create network policies within Snowflake?
SECURITYADMIN or higher. Anyone with CREATE NETWORK POLICY privilege.
157
Which loop type iterates until a condition is true?
REPEAT iterates until a condition = TRUE.
158
Which system-defined Snowflake role has permission to rename an account and specify whether the original URL can be used to access the renamed account?
OORGADMIN is the only account with privileges to administrate Snowflake accoutns within an org. The URL part of this question is what informs us that this is a question about Snowflake accounts, not user accounts.
159
Which Snowflake features can be enabled by calling the SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER function by a user with the ORGADMIN role?
Account level database replication. Client redirect.
160
What triggers the automated maintenance of a table's clustering key after it has been defined?
Snowflake determination that the table will benefit from maintennance.
161
During periods of warehouse contention, which parameter controls the maximum length of time a warehouse will hold a query for processing?
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS.
162
What is the Snowflake recommended Parquet file size when querying from external tables to optimize the number of parallel scanning operations?
256-512 MB.
163
How should a virtual warehouse be configured if a user wants to ensure that additional multi-clusters are resumed with the shortest delay possible?
Use the standard warehouse scaaling policy.
164
Which of the following terms best describes Snowflake's database architecture?
Multi-cluster shared data.
165
What is the default access of a securable object until other access is granted?
No access.
166
What is the purpose of collecting statistics on data in Snowflake?
To enable efficient pruning based on query filters.
167
What function can be used with the recursive argument to return a list of distinct key names in all nested elements in an object?
FLATTEN.
168
What issues can be identified and troubleshooted using the Query Profile?
Queries too large to fit into memory. Cartesian products.
169
Snowflake users can create a resource monitor at which levels?
Account leve. Virtual Warehouse level.
170
Which Snowflake governance feature allows users to assign metadata labels to improve data governance and database access control?
Obect tagging.
171
Which command is used to generate a zero-copy "snapshot" of any table, schema, or database?
CLONE.
172
Which stream type can be used for tracking the records in external tables?
Insert-only.
173
What actions can be performed by a consumer account on a shared database?
Selecting the shared data. Joining the data to another table.
174
A user wants to unload data from a relational table into a CSV file in an external stage. The table must be named exactly as specified by the user. Which file format option MUST be used to do this?
Single. SINGLE+TRUE is a file format option that ensures the data is written to a single file. file_extension paramter allows for selection of the output file type, but by itself, does not guarantee that the file will be a single file. The trick in the answer here is that the file must be named exactly. If a secondary file is generated, it will have a different name.
175
What role should be used when creating a new user?
USERADMIN
176
Snowflake will return an error when a user attempts to share which object?
Standard Views. Standard views are not designed for sharing as they are just a reference to a query that generates a result.
177
What value provides information about disk usage for operations where intermediate results do not fit in memory in a Query Profile?
Spilling. Spilling is when the warehouse runs out of RAM and spills over to local disk. When local disk is exceeded, spillage can go to remote disk. Both of these spillages impact query performance.
178
Which common query issues can be identified by the Query Profile?
JOIN explosion and inefficient query pruning.
179
A virtual warehouse initially suffers from poor performance as a result of queries from multiple concurrent processes that are queuing. Over time, the problem resolved. What action can be taken to prevent this from happening again?
Change the mutli-cluster settings to add additonal clusters. The key here is the words 'concurrent' and queuing. Problems with queuing can be resolved by multi-cluster warehouses.
180
Which Snowflake object does not consume any storage costs?
Secure views are logical representaton of the underlying data and don't incur any addition storage costs.
181
Which pages are included in the Monitoring area of Snowsight?
Copy history, Query history.
182
What action can a user take to address query concurrency issues?
Add additional clusters to the warehouse.
183
What aspect of an executed query is represented by the remote disk I/O statistic of the Query Profile in Snowflake?
Time spent reading and writing data from and to remote storage when the data being accessed does not fit into either the virtual warehouse memory or the local disk
184
Which Snowflake feature records changes made to a table so actions can be taken using that change data capture?
Stream. A stream is table data CDC.
185
What is the MINIMUM role required to set the value for the parameter ENABLE_ACCOUNT_DATABASE_REPLICATION?
ORGADMIN.
186
How can a 5 GB table be downloaded into a single file MOST efficiently?
Set SINGLE=TRUE.
187
How does Snowflake store a table's underlying data?
Columnar file formation, Micro-partitions.
188
Which table function is used to perform additional processing on the results of a previously-run query?
RESULT_SCAN. Often run with RESULT_SCAN().
189
Snowflake provides two mechanisms to reduce data storage costs for short-lived tables. These mechanisms are
Transient and Temporary tables.
190
A Snowflake query took 40 minutes to run. The results indicate that ‘Bytes spilled to local storage’ was a large number. What is the issue and how can it be resolved?
Increase the size of the warehouse.
191
What does an integration between Snowflake and Microsoft Private Link or AWS PrivateLink support?
A secure, direct connection to Snowflake that does not use the internet.
192
A developer is granted ownership of a table that has a masking policy. The developer’s role is not able to see the masked data. Will the developer be able to modify the table to read the masked data?
No, because ownership of a table does not include the ability to change masking policies
193
Which function should be used to authorize users to access rows in a base table when using secure views with Secure Data Sharing?
CURRENT_ACCOUNT() function should be used to authorize users to access rows in a base table when using secure views with Secure Data Sharing.
194
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.
195
Convert the rows in a relational table to a single VARIANT column and then unload the rows into a file.
Use a multi-cluster warehouse.
196
Why is a federated environment used for user authentication in Snowflake?
To separate user atuhtentication from user access.
197
If a virtual warehouse runs for 61 seconds, shuts down, and then restarts and runs for 30 seconds, for how many seconds is it billed?
121. Billed by the second after the first minute.
198
Which statistic displayed in a Query Profile is specific to external functions?
Total invocations.
199
A A role is created and owns 2 tables. This role is then dropped. Who will now own the two tables?
The role that dropped the 'owner' role.
200
By default, which Snowflake role is required to create a share?
ACCOUNTADMIN.
201
What transformations are supported in a CREATE PIPE ... AS COPY `¦ FROM (`¦) statement?
Columns can be omitted. Columns can be reordered.
202
Which REST API can be used with unstructured data?
GET api/files/
203
How does Snowflake Fail-safe protect data in a permanent table?
Fail-safe makes data available for 7 days, recoverable only by Snowflake Support.
204
What does a table with a clustering depth of 1 mean in Snowflake?
Tha table has no overlapping micro-partitions. Clustering depth of 1 indicates optimal clustering meaning each micro-partition is well organised without overlap.
205
How does a Snowflake user extract the URL of a directory table on an external stage for further transformation?
Use the GET_STAGE_LOCATION function.
206
In which layer of its architecture does Snowflake store its metadata statistics?
Cloud Service Layer
207
What is the MINIMUM permission needed to access a file URL from an external stage?
USAGE.
208
What privilege does a user need in order to receive or request data from the Snowflake Marketplace?
IMPORT SHARE
209
Which types of subqueries does Snowflake support?
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 uncorrellated.
210
How are network policies defined in Snowflake?
They are a set of rules that control access to Snowflake accounts by specifying the IP addresses or ranges of IP addresses that are allowed to connect to Snowflake.
211
What parameter controls if the Virtual Warehouse starts immediately after the CREATE WAREHOUSE statement?
INITIALLY_SUSPENDED=TRUE;
212
Which privilege grants the ability to set a column-level security masking policy on a table or view column?
APPLY
213
How can a data provider share their Snowflake data?
Shares, Data Marketplace
214
Which ACCOUNT_USAGE view will identify long-running queries?
QUERY_HISTORY
215
What is the maximum total Continuous Data Protection (CDP) charges incurred for a temporary table?
Temporary tables are only held in Fail Safe for 24 hours.
216
How do Snowflake data providers share data that resides in different databases?
Secure views.
217
What setting in Snowsight determines the databases, tables, and other objects that can be seen and the actions that can be performed on them?
Roles, RBAC. The reason that this answer is not row or column based data masking/security is that the requirements are too broad to be able to be satisfied by Row or Column level data security in isolation.
218
Which objects together comprise a namespace in Snowflake?
Database, Schema.
219
Which feature is only available in the Enterprise or higher editions of Snowflake?
Column level security.
220
Which property needs to be added to the ALTER WAREHOUSE command to verify the additional compute resources for a virtual warehouse have been fully provisioned?
WAIT_FOR_COMPLETION. This property ensures that all resources are completely deployed before processing occurs in the warehouse.
221
Which Snowflake URL type allows users or applications to download or access files directly from Snowflake stage without authentication?
Pre-signed URL.
222
How does Snowflake handle the data retention period for a table if a stream has not been consumed?
The data retention period is temporarily extended to the stream’s offset.
223
A Snowflake user needs to share unstructured data from an internal stage to a reporting tool that does not have Snowflake access. Which file function should be used?
GET_PRESIGNED_URL.
224
Which file format option should be used when unloading data into a stage to create a CSV or a JSON file?
FILE_EXTENSION.
225
Which view will show the MOST recent information about table-level storage utilization?
The TABLE_STORAGE_METRICS view in the INFORMATION_SCHEMA. INFORMATION_SCHEMA does not have any lag.
226
What versions of Snowflake should be used to manage compliance with Personal Identifiable Information (PII) requirements?
Virtual Private Snowflake, Business Critical Edition.
227
Snowflake best practice recommends that which role be used to enforce a network policy on a Snowflake account?
SECURITYADMIN
228
Which command should be used to load data from a file, located in an external stage, into a table in Snowflake?
COPY INTO
229
Which data types in Snowflake are synonymous for FLOAT?
DOUBLE, REAL.
230
What data types are not allowed as clustering keys?
Not allowed data types for clustering keys: GEOGRAPHY, VARIANT, OBJECT, ARRAY.
231
What type of account can be used to share data with a consumer who does not have a Snowflake account?
Reader account.
232
What type of account can be used to share data with a consumer who does not have a Snowflake account?
More control over object-level access for different user groups
233
What does Snowflake recommend when configuring the auto-suspend parameter for a virtual warehouse?
Enable auto-suspend to a low value to minimize credit consumption during inactivity.
234
A user is loading JSON documents composed of a huge array containing multiple records into Snowflake. The user enables the STRIP_OUTER_ARRAY file format option. What does the STRIP_OUTER_ARRAY file format do?
It removes the outer array structure and loads the records into separate table rows.
235
Which views are included in the DATA_SHARING_USAGE schema?
MONETIZED_USEAGE DAILY, LISTING_TELEMETRY_DAILY
236
What are the responsibilities of Snowflake's Cloud Service layer?
Resource management, Authentication, Query parsing and optimisation.
237
What Snowflake features are recommended to restrict unauthorized users from accessing Personal Identifiable Information (PII)?
Secure views and Dynamic Data Masking.
238
A user has semi-structured data to load into Snowflake but is not sure what types of operations will need to be performed on the data. Based on this situation, what type of column does Snowflake recommend be used?
VARIANT
238
Which functions can be used to share unstructured data through a secure view?
BUILD_SCOPED_FILE_URL, GET_PRESIGNED_URL
239
What is the default behavior of internal stages in Snowflake?
Each user and table are automatically allocated an internal stage.
240
How many resource monitors can be assigned at the account level?
1. Only one can be assigned at the account level.
241
The Snowflake Cloud Data Platform is described as having which of the following architectures?
Mutli-cluster shared data.
242
What does Snowflake recommend a user do if they need to connect to Snowflake with a tool or technology that is not listed in Snowflake’s partner ecosystem?
Connect through Snowflake’s JDBC or ODBC drivers.
243
Which data formats are supported by Snowflake when unloading semi-structured data?
Binary file in Parquet, Newline Delimited in JSON.
244
Which governance features is supported by all Snowflake editions?
Object tags, OBJECT_DEPENDENCIES view.
245
When loading data into Snowflake via Snowpipe what is the compressed file size recommendation?
100-250 MB.
246
Which task is supported by the use of Access History in Snowflake?
Compliance auditing.
247
In the Data Exchange, who can get or request data from the listings?
ACCOUNTADMIN, andyone with IMPORT SHARE privilege.
248
Which command line flags can be used to log into a Snowflake account using SnowSQL?
-a (account name),-d (database).
249
Which type of loop requires a BREAK statement to stop executing?
LOOP. Unlike other loop types, it doesn’t have a natural stopping condition, so a BREAK is needed to exit the loop based on a specific condition.
250
Which Snowflake data types can be used to build nested hierarchical data?
VARIANT, OBJECT ARRAY.
251
What SQL command would be used to view all roles that were granted to USER1?
SHOW GRANTS TO USER .
252
A tabular User-Defined Function (UDF) is defined by specifying a return clause that contains which keyword?
TABLE
253
What is the only supported character set for loading and unloading data from all supported file formats?
UTF-8
254
When unloading the data for file format type specified (TYPE = 'CSV'), SQL NULL can be converted to string ‘null’ using which file format option?
NULL_IF
255
By definition, a secure view is exposed only to users with what privilege?
OWNERSHIP
256
What happens to foreign key constraints when a table is cloned to another database?
The cloned table will reference the primary key in the source table.
257
When floating-point number columns are unloaded to CSV or JSON files, Snowflake truncates the values to approximately what?
(15,9) meaning 15 digits of precision and 9 digits after the decimal point.
258
When sharing data in Snowflake, what privileges does a Provider need to grant along with a share?
USAGE on the database a schema, and SELECT on the tables.
259
At what level can the ALLOW_CLIENT_MFA_CACHING parameter be set?
Account.
260
To use the OVERWRITE option on INSERT, which privilege must be granted to the role?
DELETE
261
In a SPLIT_PART function, what will the returned value be if the partNumber is out of range?
An empty string.
262
Which privileges are required for a user to restore an object?
CREATE, OWNERSHIP. Undrop is not a privilege. Ownership grants full permissions. You would also need create to create an object AS a time travel object.
263
What does Snowflake attempt to do if any of the compute resources for a virtual warehouse fail to provision during start-up?
Repair the failed resources.
264
What is used to diagnose and troubleshoot network connections to Snowflake?
SnowCD is a tool designed to help identify and resolve network connectivity issues.
265
Which function will provide the proxy information needed to protect Snowsight?
SYSTEM$ALLOWLIST
266
When should a multi-cluster virtual warehouse be used in Snowflake?
When queuing is delaying query execution on the warehouse
267
How does the ACCESS_HISTORY view enhance overall data governance pertaining to read and write operations?
Provides a unified picture of what data was accessed and when it was accessed. It also shows how the accessed data was moved from the source to the target objects
268
What is the MINIMUM configurable idle timeout value for a session policy in Snowflake?
5 minutes.
269
Which element in the Query Profile interface shows the relationship between the nodes in the execution of a query?
Operator Tree.
270
What storage cost is completely eliminated when a Snowflake table is defined as transient?
Fail Safe.
271
What is the minimum Snowflake Edition that supports secure storage of Protected Health Information (PHI) data?
Business Criticcal Edition.
272
What entity is responsible for hosting and sharing data in Snowflake?
Data provider
273
Which VALIDATION_MODE value will return the errors across the files specified in a COPY command, including files that were partially loaded during an earlier load?
RETURN_ALL_ERRORS
274
The MAXIMUM size for a serverless task run is equivalent to what size virtual warehouse?
2X Large
275
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 import could be aborted without any part of the file being saved.
276
AWS AI Fundamentals
flashcards
Decks in class (2)
# Cards
Brainscape helps you reach your goals faster, through stronger study habits.
© 2026 Bold Learning Solutions. Terms and Conditions