Streams cannot be created to query change data on which of the following objects? [Select All that Apply]
- A . Standard tables, including shared tables.
- B . Views, including secure views
- C . Directory tables
- D . Query Log Tables
- E . External tables
B,D,E
Explanation:
Streams capture DML changes (INSERT, UPDATE, DELETE) made to the underlying table. You can’t create streams on objects that do not directly store data or do not support DML operations, such as views and external tables.
Query log tables in Snowflake are system-generated views that give you insights into the queries executed. You cannot create streams on these tables because they are system-managed and don’t support direct DML operations.
Tasks may optionally use table streams to provide a convenient way to continuously process new or changed data. A task can transform new or changed rows that a stream surfaces. Each time a task is scheduled to run, it can verify whether a stream contains change data for a table and either consume the change data or skip the current run if no change data exists.
Which System Function can be used by Data engineer to verify whether a stream contains changed data for a table?
- A . SYSTEM$STREAM_HAS_CHANGE_DATA
- B . SYSTEM$STREAM_CDC_DATA
- C . SYSTEM$STREAM_HAS_DATA
- D . SYSTEM$STREAM_DELTA_DATA
A
Explanation:
The SYSTEM$STREAM_HAS_CHANGE_DATA function is used to check if there is any change data available in a specified stream. If the stream has data, the function will return TRUE; otherwise, it will return FALSE. This is useful when combined with tasks to decide whether or not to execute a transformation or processing based on the presence of new change data in the stream.
The Above example indicates that the SF_DATA table is not well-clustered for which of following valid reasons?
- A . Zero (0) constant micro-partitions out of 1156 total micro-partitions.
- B . High average of overlapping micro-partitions.
- C . High average of overlap depth across micro-partitions.
- D . Most of the micro-partitions are grouped at the lower-end of the histogram, with the majority of micro-partitions having an overlap depth between 64 and 128.
- E . ALL of the above
Mark a Data Engineer, looking to implement streams on local views & want to use change tracking metadata for one of its Data Loading use case. Please select the incorrect understanding points of Mark with respect to usage of Streams on Views?
- A . For streams on views, change tracking must be enabled explicitly for the view and underlying tables to add the hidden columns to these tables.
- B . The CDC records returned when querying a stream rely on a combination of the offset stored in the stream and the change tracking metadata stored in the table.
- C . Views with GROUP BY & LIMIT Clause are supported by Snowflake.
- D . As an alternative to streams, Snowflake supports querying change tracking metadata for views using the CHANGES clause for SELECT statements.
- E . Enabling change tracking adds a pair of hidden columns to the table and begins storing change tracking metadata. The values in these hidden CDC data columns provide the input for the stream metadata columns. The columns consume a small amount of storage.
C
Explanation:
A stream object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes, as well as metadata about each change, so that actions can be taken using the changed data. This process is referred to as change data capture (CDC). An individual table stream tracks the changes made to rows in a source table. A table stream (also referred to as simply a “stream”) makes a “change table” available of what changed, at the row level, between two transactional points of time in a table. This allows querying and consuming a sequence of change records in a transactional fashion.
Streams can be created to query change data on the following objects:
・ Standard tables, including shared tables.
・ Views, including secure views
・ Directory tables
・ External tables
When created, a stream logically takes an initial snapshot of every row in the source object (e.g. table, external table, or the underlying tables for a view) by initializing a point in time (called an off-set) as the current transactional version of the object. The change tracking system utilized by the stream then records information about the DML changes after this snapshot was taken. Change records provide the state of a row before and after the change. Change information mirrors the column structure of the tracked source object and includes additional metadata columns that describe each change event.
Note that a stream itself does not contain any table data. A stream only stores an offset for the source object and returns CDC records by leveraging the versioning history for the source object. When the first stream for a table is created, a pair of hidden columns are added to the source table and begin storing change tracking metadata. These columns consume a small amount of storage. The CDC records returned when querying a stream rely on a combination of the offset stored in the stream and the change tracking metadata stored in the table. Note that for streams on views, change tracking must be enabled explicitly for the view and underlying tables to add the hidden columns to these tables.
Streams on views support both local views and views shared using Snowflake Secure Data Sharing, including secure views. Currently, streams cannot track changes in materialized views.
Views with the following operations are not yet supported:
・ GROUP BY clauses
・ QUALIFY clauses
・ Subqueries not in the FROM clause
・ Correlated subqueries
・ LIMIT clauses
Change Tracking:
Change tracking must be enabled in the underlying tables.
Prior to creating a stream on a view, you must enable change tracking on the underlying tables for the view.
Set the CHANGE_TRACKING parameter when creating a view (using CREATE VIEW) or later (using ALTER VIEW).
As an alternative to streams, Snowflake supports querying change tracking metadata for tables or views using the CHANGES clause for SELECT statements. The CHANGES clause enables querying change tracking metadata between two points in time without having to create a stream with an explicit transactional offset.
To advance the offset of a stream to the current table version without consuming the change data in a DML operation, which of the following operations can be done by Data Engineer? [Select 2]
- A . using the CREATE OR REPLACE STREAM syntax, Recreate the STREAM
- B . Insert the current change data into a temporary table. In the INSERT statement, query the stream but include a WHERE clause that filters out all of the change data (e.g. WHERE 0 = 1).
- C . A stream advances the offset only when it is used in a DML transaction, so none of the options works without consuming the change data of table.
- D . Delete the offset using STREAM properties SYSTEM$RESET_OFFSET( <stream_id> )
A, B
Explanation:
When created, a stream logically takes an initial snapshot of every row in the source object (e.g. table, external table, or the underlying tables for a view) by initializing a point in time (called an off-set) as the current transactional version of the object. The change tracking system utilized by the stream then records information about the DML changes after this snapshot was taken. Change records provide the state of a row before and after the change. Change information mirrors the column structure of the tracked source object and includes additional metadata columns that describe each change event.
Note that a stream itself does not contain any table data. A stream only stores an offset for the source object and returns CDC records by leveraging the versioning history for the source object.
A new table version is created whenever a transaction that includes one or more DML statements is committed to the table.
In the transaction history for a table, a stream offset is located between two table versions. Querying a stream returns the changes caused by transactions committed after the offset and at or before the current time.
Multiple queries can independently consume the same change data from a stream without changing the offset. A stream advances the offset only when it is used in a DML transaction. This behavior applies to both explicit and autocommit transactions. (By default, when a DML statement is executed, an autocommit transaction is implicitly started and the transaction is committed at the completion of the statement. This behavior is controlled with the AUTOCOMMIT parameter.) Querying a stream alone does not advance its offset, even within an explicit transaction; the stream contents must be consumed in a DML statement.
To advance the offset of a stream to the current table version without consuming the change data in a DML operation, complete either of the following actions:
・ Recreate the stream (using the CREATE OR REPLACE STREAM syntax).
Insert the current change data into a temporary table. In the INSERT statement, query the stream but include a WHERE clause that filters out all of the change data (e.g. WHERE 0 = 1).
Data Engineer is performing below steps in sequence while working on Stream s1 created on table t1.
Step 1: Begin transaction.
Step 2: Query stream s1 on table t1.
Step 3: Update rows in table t1.
Step 4: Query stream s1.
Step 5: Commit transaction.
Step 6: Begin transaction.
Step 7: Query stream s1.
Mark the Incorrect Operational statements:
- A . For Step 2, The stream returns the change data capture records between the current position to the Transaction 1 start time. If the stream is used in a DML statement, the stream is then locked to avoid changes by concurrent transactions.
- B . For Step 4, Returns the CDC data records by streams with updated rows happened in the Step 3 because Streams works in Repeated committed mode in which statements see any changes made by previous statements executed within the same transaction, even though those changes are not yet committed.
- C . For Step 5, If the stream was consumed in DML statements within the transaction, the stream position advances to the transaction start time.
- D . For Step 7, Results do include table changes committed by Transaction 1.
- E . if Transaction 2 had begun before Transaction 1 was committed, queries to the stream would have returned a snapshot of the stream from the position of the stream to the be-ginning time of Transaction 2 and would not see any changes committed by Transaction 1.
C
Explanation:
Let’s analyze each statement based on the given sequence of operations and Snowflake’s behavior regarding streams:
Streams record the differences between two offsets.
If a row is added and then updated in the cur-rent offset, what will be the value of METADATA$ISUPDATE Columns in this scenario?
- A . TRUE
- B . FALSE
- C . UPDATE
- D . INSERT
B
Explanation:
Stream Columns
A stream stores an offset for the source object and not any actual table columns or data. When queried, a stream accesses and returns the historic data in the same shape as the source object (i.e. the same column names and ordering) with the following additional columns: METADATA$ACTION
Indicates the DML operation (INSERT, DELETE) recorded.
METADATA$ISUPDATE
Indicates whether the operation was part of an UPDATE statement. Updates to rows in the source object are represented as a pair of DELETE and INSERT records in the stream with a metadata column METADATA$ISUPDATE values set to TRUE.
METADATA$ROW_ID
Specifies the unique and immutable ID for the row, which can be used to track changes to specific rows over time.
Note that streams record the differences between two offsets. If a row is added and then updated in the current offset, the delta change is a new row. The METADATA$ISUPDATE row records a FALSE value.
Mark the Incorrect Statements with respect to types of streams supported by Snowflake?
- A . Standard streams cannot retrieve update data for geospatial data.
- B . An append-only stream returns the appended rows only and therefore can be much more performant than a standard stream for extract, load, transform (ELT).
- C . Insert-only Stream supported on external tables only.
- D . An insert-only stream tracks row inserts & Delete ops only
D
Explanation:
Standard Stream:
Supported for streams on tables, directory tables, or views. A standard (i.e. delta) stream tracks all DML changes to the source object, including inserts, updates, and deletes (including table truncates). This stream type performs a join on inserted and deleted rows in the change set to provide the row level delta. As a net effect, for example, a row that is inserted and then deleted between two transactional points of time in a table is removed in the delta (i.e. is not returned when the stream is queried).
Append-only Stream:
Supported for streams on standard tables, directory tables, or views. An append-only stream tracks row inserts only. Update and delete operations (including table truncates) are not recorded. For ex-ample, if 10 rows are inserted into a table and then 5 of those rows are deleted before the offset for an append-only stream is advanced, the stream records 10 rows.
An append-only stream returns the appended rows only and therefore can be much more performant than a standard stream for extract, load, transform (ELT) and similar scenarios that depend exclusively on row inserts. For example, a source table can be truncated immediately after the rows in an append-only stream are consumed, and the record deletions do not contribute to the overhead the next time the stream is queried or consumed. Insert-only Stream:
Supported for streams on external tables only. An insert-only stream tracks row inserts only; they do not record delete operations that remove rows from an inserted set (i.e. no-ops). For example, in-between any two offsets, if File1 is removed from the cloud storage location referenced by the ex-ternal table, and File2 is added, the stream returns records for the rows in File2 only. Unlike when tracking CDC data for standard tables, Snowflake cannot access the historical records for files in cloud storage.
Stuart, a Lead Data Engineer in MACRO Data Company created streams on set of External tables.
He has been asked to extend the data retention period of the stream for 90 days, which parameter he can utilize to enable this extension?
- A . MAX_DATA_EXTENSION_TIME_IN_DAYS
- B . DATA_RETENTION_TIME_IN_DAYS
- C . DATA_EXTENSION_TIME_IN_DAYS
- D . None of the above
D
Explanation:
External tables do not have data retention period applicable.
Good to Understand other Options available.
DATA_RETENTION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Number of days for which Snowflake retains historical data for performing Time Travel actions (SELECT, CLONE, UNDROP) on the object. A value of 0 effectively disables Time Travel for the specified database, schema, or table.
Values:
0 or 1 (for Standard Edition)
0 to 90 (for Enterprise Edition or higher)
Default:
1
MAX_DATA_EXTENSION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale. By default, if the DA-TA_RETENTION_TIME_IN_DAYS setting for a source table is less than 14 days, and a stream has not been consumed, Snowflake temporarily extends this period to the stream’s offset, up to a maximum of 14 days, regardless of the Snowflake Edition for your account. The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter enables you to limit this automatic ex-tension period to control storage costs for data retention or for compliance reasons.
This parameter can be set at the account, database, schema, and table levels. Note that setting the parameter at the account or schema level only affects tables for which the parameter has not already been explicitly set at a lower level (e.g. at the table level by the table owner). A value of 0 effectively disables the automatic extension for the specified database, schema, or table. Values:
0 to 90 (i.e. 90 days) ― a value of 0 disables the automatic extension of the data retention period. To increase the maximum value for tables in your account, Client needs to contact Snowflake Sup-port. Default: 14
Stuart, a Lead Data Engineer in MACRO Data Company created streams on set of External tables.
He has been asked to extend the data retention period of the stream for 90 days, which parameter he can utilize to enable this extension?
- A . MAX_DATA_EXTENSION_TIME_IN_DAYS
- B . DATA_RETENTION_TIME_IN_DAYS
- C . DATA_EXTENSION_TIME_IN_DAYS
- D . None of the above
D
Explanation:
External tables do not have data retention period applicable.
Good to Understand other Options available.
DATA_RETENTION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Number of days for which Snowflake retains historical data for performing Time Travel actions (SELECT, CLONE, UNDROP) on the object. A value of 0 effectively disables Time Travel for the specified database, schema, or table.
Values:
0 or 1 (for Standard Edition)
0 to 90 (for Enterprise Edition or higher)
Default:
1
MAX_DATA_EXTENSION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale. By default, if the DA-TA_RETENTION_TIME_IN_DAYS setting for a source table is less than 14 days, and a stream has not been consumed, Snowflake temporarily extends this period to the stream’s offset, up to a maximum of 14 days, regardless of the Snowflake Edition for your account. The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter enables you to limit this automatic ex-tension period to control storage costs for data retention or for compliance reasons.
This parameter can be set at the account, database, schema, and table levels. Note that setting the parameter at the account or schema level only affects tables for which the parameter has not already been explicitly set at a lower level (e.g. at the table level by the table owner). A value of 0 effectively disables the automatic extension for the specified database, schema, or table. Values:
0 to 90 (i.e. 90 days) ― a value of 0 disables the automatic extension of the data retention period. To increase the maximum value for tables in your account, Client needs to contact Snowflake Sup-port. Default: 14
Stuart, a Lead Data Engineer in MACRO Data Company created streams on set of External tables.
He has been asked to extend the data retention period of the stream for 90 days, which parameter he can utilize to enable this extension?
- A . MAX_DATA_EXTENSION_TIME_IN_DAYS
- B . DATA_RETENTION_TIME_IN_DAYS
- C . DATA_EXTENSION_TIME_IN_DAYS
- D . None of the above
D
Explanation:
External tables do not have data retention period applicable.
Good to Understand other Options available.
DATA_RETENTION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Number of days for which Snowflake retains historical data for performing Time Travel actions (SELECT, CLONE, UNDROP) on the object. A value of 0 effectively disables Time Travel for the specified database, schema, or table.
Values:
0 or 1 (for Standard Edition)
0 to 90 (for Enterprise Edition or higher)
Default:
1
MAX_DATA_EXTENSION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale. By default, if the DA-TA_RETENTION_TIME_IN_DAYS setting for a source table is less than 14 days, and a stream has not been consumed, Snowflake temporarily extends this period to the stream’s offset, up to a maximum of 14 days, regardless of the Snowflake Edition for your account. The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter enables you to limit this automatic ex-tension period to control storage costs for data retention or for compliance reasons.
This parameter can be set at the account, database, schema, and table levels. Note that setting the parameter at the account or schema level only affects tables for which the parameter has not already been explicitly set at a lower level (e.g. at the table level by the table owner). A value of 0 effectively disables the automatic extension for the specified database, schema, or table. Values:
0 to 90 (i.e. 90 days) ― a value of 0 disables the automatic extension of the data retention period. To increase the maximum value for tables in your account, Client needs to contact Snowflake Sup-port. Default: 14
Stuart, a Lead Data Engineer in MACRO Data Company created streams on set of External tables.
He has been asked to extend the data retention period of the stream for 90 days, which parameter he can utilize to enable this extension?
- A . MAX_DATA_EXTENSION_TIME_IN_DAYS
- B . DATA_RETENTION_TIME_IN_DAYS
- C . DATA_EXTENSION_TIME_IN_DAYS
- D . None of the above
D
Explanation:
External tables do not have data retention period applicable.
Good to Understand other Options available.
DATA_RETENTION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Number of days for which Snowflake retains historical data for performing Time Travel actions (SELECT, CLONE, UNDROP) on the object. A value of 0 effectively disables Time Travel for the specified database, schema, or table.
Values:
0 or 1 (for Standard Edition)
0 to 90 (for Enterprise Edition or higher)
Default:
1
MAX_DATA_EXTENSION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale. By default, if the DA-TA_RETENTION_TIME_IN_DAYS setting for a source table is less than 14 days, and a stream has not been consumed, Snowflake temporarily extends this period to the stream’s offset, up to a maximum of 14 days, regardless of the Snowflake Edition for your account. The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter enables you to limit this automatic ex-tension period to control storage costs for data retention or for compliance reasons.
This parameter can be set at the account, database, schema, and table levels. Note that setting the parameter at the account or schema level only affects tables for which the parameter has not already been explicitly set at a lower level (e.g. at the table level by the table owner). A value of 0 effectively disables the automatic extension for the specified database, schema, or table. Values:
0 to 90 (i.e. 90 days) ― a value of 0 disables the automatic extension of the data retention period. To increase the maximum value for tables in your account, Client needs to contact Snowflake Sup-port. Default: 14
Stuart, a Lead Data Engineer in MACRO Data Company created streams on set of External tables.
He has been asked to extend the data retention period of the stream for 90 days, which parameter he can utilize to enable this extension?
- A . MAX_DATA_EXTENSION_TIME_IN_DAYS
- B . DATA_RETENTION_TIME_IN_DAYS
- C . DATA_EXTENSION_TIME_IN_DAYS
- D . None of the above
D
Explanation:
External tables do not have data retention period applicable.
Good to Understand other Options available.
DATA_RETENTION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Number of days for which Snowflake retains historical data for performing Time Travel actions (SELECT, CLONE, UNDROP) on the object. A value of 0 effectively disables Time Travel for the specified database, schema, or table.
Values:
0 or 1 (for Standard Edition)
0 to 90 (for Enterprise Edition or higher)
Default:
1
MAX_DATA_EXTENSION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) ― Can be set for Account » Database » Schema » Table
Description: Maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale. By default, if the DA-TA_RETENTION_TIME_IN_DAYS setting for a source table is less than 14 days, and a stream has not been consumed, Snowflake temporarily extends this period to the stream’s offset, up to a maximum of 14 days, regardless of the Snowflake Edition for your account. The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter enables you to limit this automatic ex-tension period to control storage costs for data retention or for compliance reasons.
This parameter can be set at the account, database, schema, and table levels. Note that setting the parameter at the account or schema level only affects tables for which the parameter has not already been explicitly set at a lower level (e.g. at the table level by the table owner). A value of 0 effectively disables the automatic extension for the specified database, schema, or table. Values:
0 to 90 (i.e. 90 days) ― a value of 0 disables the automatic extension of the data retention period. To increase the maximum value for tables in your account, Client needs to contact Snowflake Sup-port. Default: 14
at(timestamp => (select current_timestamp())); Select the Correct Query Execution Output option below:
- A . Developer missed to create stream on the source table which can further query to capture DML records.
- B . Select query will fail with error: ‘SQL compilation error-Incorrect Keyword "Chang-es()" found’
- C . No Error reported, select command gives Changed records with Metadata columns as change tracking enabled on the Source views & its underlying tables.
- D . Select statement complied but gives erroneous results.
B
Explanation:
Let’s break down the scenario:
Ron wants to capture insert-only changes on the source views. He’s enabled change tracking on the views and their underlying tables. Then, he’s trying to query these changes using the CHANGES() function.
Here’s a breakdown of the given options:
Which column provides information when the stream became stale or may become stale if not consumed?
- A . STREAM_STALE_PERIOD
- B . STALE_PERIOD_AFTER
- C . STALE_STREAM_PERIOD
- D . STALE_AFTER
D
Explanation:
execute SHOW STREAMS command.
Column Name: STALE_AFTER
Timestamp when the stream became stale or may become stale if not consumed. The value is calculated by adding the retention period for the source table (i.e. the larger of the DA-TA_RETENTION_TIME_IN_DAYS or MAX_DATA_EXTENSION_TIME_IN_DAYS parameter setting) to the last time the stream was read.
When created, a stream logically takes an initial snapshot of every row in the source object and the contents of a stream change as DML statements execute on the source table.
A Data Engineer, Sophie Created a view that queries the table and returns the CURRENT_USER and CURRENT_TIMESTAMP values for the query transaction. A Stream has been created on views to capture CDC.
Tony, another user inserted the data e.g.
insert into <table> values (1),(2),(3);
Emily, another user also inserted the data e.g.
insert into <table> values (4),(5),(6);
What will happened when Different user queries the same stream after 1 hour?
- A . All the 6 records would be shown with METADATA$ACTION as ‘INSERT’ out of which 3 records would be displayed with username ‘Tony’ & rest 3 records would be displayed with username ‘Emily’.
- B . All the Six Records would be displayed with CURRENT_USER & CUR-RENT_TIMESTAMP while querying Streams.
- C . All the Six records would be displayed with User ‘Sohpie’ Who is the owner of the View.
- D . User would be displayed with the one who queried during the session, but Recorded timestamp would be of past 1 hour i.e. actual records insertion time.
B
Explanation:
When User queries the stream, the stream returns the username for the user. The stream also returns the current timestamp for the query transaction in each row, NOT the timestamp when each row was inserted.
Which Function would Data engineer used to recursively resume all tasks in Chain of Tasks rather than resuming each task individually (using ALTER TASK … RESUME)?
- A . SYSTEM$TASK_DEPENDENTS
- B . SYSTEM$TASK_DEPENDENTS_ENABLE
- C . SYSTEM$TASK_DEPENDENTS_RESUME
- D . SYSTEM$TASK_RECURSIVE_ENABLE
C
Explanation:
In Snowflake, to recursively resume all tasks in a chain of tasks, the Data Engineer would use the function:
C. SYSTEM$TASK_DEPENDENTS_RESUME
This function will recursively resume a specified task and all of its dependent tasks in a chain. It saves time compared to individually using ALTER TASK … RESUME for each task in the chain.
Steven created the task, what additional privileges required by Steven on the task so that he can suspend or resume the tasks?
- A . Steven is already owner of the task; he can execute the task & suspend/resume the task without any additional privileges.
- B . In addition to the task owner, a Steven Role must have OPERATE privilege on the task so that he can suspend or resume the task.
- C . Steven must have SUSPEND privilege on the task so that he can suspend or resume the task.
- D . Steven needs to have Global Managed RESUME privilege by TASK administrator.
A
Explanation:
For Snowflake tasks:
The owner of the task can suspend, resume, or modify the task.
The role that owns a task can grant privileges on that task to other roles.
Given the choices:
John, Data Engineer, do have technical requirements to refresh the External tables Metadata periodically or in auto mode, which approach John can take to meet this technical specification?
- A . John can use AUTO_REFRESH parameter if the underlying External Cloud host sup-ports this for External tables.
- B . He can create a task that executes an ALTER EXTERNAL TABLE … REFRESH statement every 5 minutes.
- C . External table cannot be scheduled via Snowflake Tasks, 3rd party tools/scripts needs to be used provided by External cloud storage provider.
- D . Snowflake implicitly take care this Infrastructure needs, as underlying warehouse layer internally manage the refresh. No action needed from John.
B
Explanation:
External tables in Snowflake do not automatically reflect changes made to the underlying data in external cloud storage. To see these changes in Snowflake, you need to refresh the external table metadata.
Let’s go through the options:
If you need to connect to Snowflake using a BI tool or technology, which of the following BI tools and technologies are known to provide native connectivity to Snowflake?
- A . SISENSE
- B . SELECT STAR
- C . ALATION
- D . PROTEGRITY
A
Explanation:
SISENSE is BI tools and technologies which is known to provide native connectivity to Snowflake, Rest of the options given are security & governance tools supported by SnowFlake.
Business intelligence (BI) tools enable analyzing, discovering, and reporting on data to help executives and managers make more informed business decisions. A key component of any BI tool is the ability to deliver data visualization through dashboards, charts, and other graphical output. For More details around supported BI Tools in Snowflake Ecosystem, do refer the link below:
https://docs.snowflake.com/en/user-guide/ecosystem-bi
Which of the following security and governance tools/technologies are known to provide native connectivity to Snowflake? [Select 2]
- A . ALTR
- B . Baffle
- C . BIG Squid
- D . Dataiku
- E . Zepl
A, B
Explanation:
Security and governance tools ensure sensitive data maintained by an organization is protected from inappropriate access and tampering, as well as helping organizations to achieve and maintain regulatory compliance. These tools are often used in conjunction with observability solutions/services to provide organizations with visibility into the status, quality, and integrity of their data, including identifying potential issues.
Together, these tools support a wide range of operations, including risk assessment, intrusion detection/monitoring/notification, data masking, data cataloging, data health/quality checks, issue identification/troubleshooting/resolution, and more.
ALTR & Baffle are correct options here.
Which of the following security and governance tools/technologies are known to provide native connectivity to Snowflake? [Select 2]
- A . ALTR
- B . Baffle
- C . BIG Squid
- D . Dataiku
- E . Zepl
A, B
Explanation:
Security and governance tools ensure sensitive data maintained by an organization is protected from inappropriate access and tampering, as well as helping organizations to achieve and maintain regulatory compliance. These tools are often used in conjunction with observability solutions/services to provide organizations with visibility into the status, quality, and integrity of their data, including identifying potential issues.
Together, these tools support a wide range of operations, including risk assessment, intrusion detection/monitoring/notification, data masking, data cataloging, data health/quality checks, issue identification/troubleshooting/resolution, and more.
ALTR & Baffle are correct options here.
Which of the following security and governance tools/technologies are known to provide native connectivity to Snowflake? [Select 2]
- A . ALTR
- B . Baffle
- C . BIG Squid
- D . Dataiku
- E . Zepl
A, B
Explanation:
Security and governance tools ensure sensitive data maintained by an organization is protected from inappropriate access and tampering, as well as helping organizations to achieve and maintain regulatory compliance. These tools are often used in conjunction with observability solutions/services to provide organizations with visibility into the status, quality, and integrity of their data, including identifying potential issues.
Together, these tools support a wide range of operations, including risk assessment, intrusion detection/monitoring/notification, data masking, data cataloging, data health/quality checks, issue identification/troubleshooting/resolution, and more.
ALTR & Baffle are correct options here.
Which of the following security and governance tools/technologies are known to provide native connectivity to Snowflake? [Select 2]
- A . ALTR
- B . Baffle
- C . BIG Squid
- D . Dataiku
- E . Zepl
A, B
Explanation:
Security and governance tools ensure sensitive data maintained by an organization is protected from inappropriate access and tampering, as well as helping organizations to achieve and maintain regulatory compliance. These tools are often used in conjunction with observability solutions/services to provide organizations with visibility into the status, quality, and integrity of their data, including identifying potential issues.
Together, these tools support a wide range of operations, including risk assessment, intrusion detection/monitoring/notification, data masking, data cataloging, data health/quality checks, issue identification/troubleshooting/resolution, and more.
ALTR & Baffle are correct options here.
print(cur.sfqid)
B. When he used the Snowflake Connector for Python to execute a query, he can access the query ID through the pyqueryid attribute in the Cursor object.
C. He needs to query history views to get the queryID as best practices.
D. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
Explanation:
Retrieving the Snowflake Query ID
A query ID identifies each query executed by Snowflake. When you use the Snowflake Connector for Python to execute a query, you can access the query ID through the sfqid attribute in the Cursor object:
print(cur.sfqid)
B. When he used the Snowflake Connector for Python to execute a query, he can access the query ID through the pyqueryid attribute in the Cursor object.
C. He needs to query history views to get the queryID as best practices.
D. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
Explanation:
Retrieving the Snowflake Query ID
A query ID identifies each query executed by Snowflake. When you use the Snowflake Connector for Python to execute a query, you can access the query ID through the sfqid attribute in the Cursor object:
print(cur.sfqid)
B. When he used the Snowflake Connector for Python to execute a query, he can access the query ID through the pyqueryid attribute in the Cursor object.
C. He needs to query history views to get the queryID as best practices.
D. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
Explanation:
Retrieving the Snowflake Query ID
A query ID identifies each query executed by Snowflake. When you use the Snowflake Connector for Python to execute a query, you can access the query ID through the sfqid attribute in the Cursor object:
print(cur.sfqid)
B. When he used the Snowflake Connector for Python to execute a query, he can access the query ID through the pyqueryid attribute in the Cursor object.
C. He needs to query history views to get the queryID as best practices.
D. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
Explanation:
Retrieving the Snowflake Query ID
A query ID identifies each query executed by Snowflake. When you use the Snowflake Connector for Python to execute a query, you can access the query ID through the sfqid attribute in the Cursor object:
print(cur.sfqid)
B. When he used the Snowflake Connector for Python to execute a query, he can access the query ID through the pyqueryid attribute in the Cursor object.
C. He needs to query history views to get the queryID as best practices.
D. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
Explanation:
Retrieving the Snowflake Query ID
A query ID identifies each query executed by Snowflake. When you use the Snowflake Connector for Python to execute a query, you can access the query ID through the sfqid attribute in the Cursor object:
Which connector creates the RECORD_CONTENT and RECORD_METADATA columns in the existing Snowflake table while connecting to Snowflake?
- A . Python Connector
- B . Spark Connector
- C . Node.js connector
- D . Kafka Connector
D
Explanation:
Apache Kafka software uses a publish and subscribe model to write and read streams of records, similar to a message queue or enterprise messaging system. Kafka allows processes to read and write messages asynchronously. A subscriber does not need to be connected directly to a publisher; a publisher can queue a message in Kafka for the subscriber to receive later.
An application publishes messages to a topic, and an application subscribes to a topic to receive those messages. Kafka can process, as well as transmit, messages; however, that is outside the scope of this document. Topics can be divided into partitions to increase scalability.
Kafka Connect is a framework for connecting Kafka with external systems, including databases. A Kafka Connect cluster is a separate cluster from the Kafka cluster. The Kafka Connect cluster sup-ports running and scaling out connectors (components that support reading and/or writing between external systems).
The Kafka connector is designed to run in a Kafka Connect cluster to read data from Kafka topics and write the data into Snowflake tables.
Every Snowflake table loaded by the Kafka connector has a schema consisting of two VARIANT columns:
RECORD_CONTENT. This contains the Kafka message.
RECORD_METADATA. This contains metadata about the message, for example, the topic from which the message was read.
Ryan, a Data Engineer, accidently drop the Share named SF_SHARE which results in immediate access revoke for all the consumers (i.e., accounts who have created a database from that SF_SHARE).
What action he can take to recover the dropped Share?
- A . By Executing UNDROP command he could possibly recover the dropped Share SF_SHARE & its associated Databases for immediate consumer access.
- B . He can recreate a share with the same name as a previous share which does restore the databases created (by any consumers) from the share SF_SHARE.
- C . A dropped share cannot be restored. The share must be created again using the CRE-ATE SHARE command and then configured using GRANT <privilege> … TO SHARE and ALTER SHARE.
- D . Consumer accounts that have created databases from the share will still be able to query these databases as Share is separate securable object & it’s still accessible using time travel feature.
C
Explanation:
You can drop a share at any time using the DROP SHARE command.
Dropping a share instantly invalidates all databases created from the share by consumer accounts.
All queries and other operations performed on these databases will no longer work.
After dropping a share, you can recreate it with the same name;
however, this does not restore any of the databases created from the share by consumer accounts. The recreated share is treated as a new share and all consumer accounts must create a new database from the new share.
For enabling non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks, which two glob-al/account privileges snowflake provide?
- A . CREATE SHARE
- B . IMPORT SHARE
- C . REFERENCE USAGE
- D . OPERATE
A, B
Explanation:
CREATE SHARE
In a provider account, this privilege enables creating and managing shares (for sharing data with
consumer accounts).
IMPORT SHARE
In a consumer account, this privilege enables viewing the inbound shares shared with the account. Also enables creating databases from inbound shares; requires the global CREATE DATABASE privilege.
By default, these privileges are granted only to the ACCOUNTADMIN role, ensuring that only ac-
count administrators can perform these tasks. However, the privileges can be granted to other roles, enabling the tasks to be delegated to other users in the account.
Mark the correct Statements with respect to Secure views & its creation in the SnowFlake Account?
- A . For a secure view, internal optimizations can indirectly expose data & the view definition is visible to other users.
- B . Secure views should not be used for views that are defined solely for query convenience, such as views created to simplify queries for which users do not need to under-stand the underlying data representation.
- C . To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW command.
- D . For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure.
- E . The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner’s Query Profile.
B, D, E
Explanation:
Let’s evaluate each of the statements regarding Secure views in Snowflake:
When using the CURRENT_ROLE and CURRENT_USER functions with secure views that will be shared to other Snowflake accounts, Snowflake returns a NULL value for these functions?
- A . FALSE
- B . TRUE
B
Explanation:
When using the CURRENT_ROLE and CURRENT_USER functions with secure views that will be shared to other Snowflake accounts, Snowflake returns a NULL value for these functions. The reason is that the owner of the data being shared does not typically control the users or roles in the account with which the view is being shared.
Snowflake computes and adds partitions based on the defined partition column expressions when an external table metadata is refreshed.
What are the Correct Statements to configure Partition metadata refresh in case of External Tables?
- A . By default, the metadata is refreshed automatically when the object is created.
- B . The object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.
- C . Metadata refresh is not required as its Managed implicitly by Snowflake.
- D . Partitions of External tables is managed by External Stage Cloud provider.
- E . There is nothing like adding partitions on External tables.
A, B
Explanation:
Snowflake strongly recommend partitioning your external tables, which requires that your underlying data is organized using logical paths that include date, time, country, or similar dimensions in the path.
Partitioning divides your external table data into multiple parts using partition columns.
An external table definition can include multiple partition columns, which impose a multi-
dimensional structure on the external data.
Partitions are stored in the external table metadata.
Benefits of partitioning include improved query performance.
Because the external data is partitioned into separate slices/parts, query response time is faster when processing a small part of the data instead of scanning the entire data set. Based on your individual use cases, you can either:
・ Add new partitions automatically by refreshing an external table that defines an expression for each partition column.
・ Add new partitions manually.
Partition columns are defined when an external table is created, using the CREATE EXTERNAL TABLE … PARTITION BY syntax.
After an external table is created, the method by which partitions are added cannot be changed. Partitions Added Automatically
An external table creator defines partition columns in a new external table as expressions that parse the path and/or filename information stored in the METADATA$FILENAME pseudocolumn.
A partition consists of all data files that match the path and/or filename in the expression for the partition column.
The CREATE EXTERNAL TABLE syntax for adding partitions automatically based on expressions is as follows:
CREATE EXTERNAL TABLE
<table_name>
( <part_col_name> <col_type> AS <part_expr> )
[,…]
[ PARTITION BY ( <part_col_name> [, <part_col_name> … ] ) ] ..
Snowflake computes and adds partitions based on the defined partition column expressions when an external table metadata is refreshed.
By default, the metadata is refreshed automatically when the object is created.
In addition, the object owner can configure the metadata to refresh automatically when new or up-dated data files are available in the external stage.
The owner can alternatively refresh the metadata manually by executing the ALTER EXTERNAL TABLE … REFRESH command.
The metadata for an external table can be refreshed automatically using the event notification ser-vice for your cloud storage service.
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove partitions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?
- A . TRUE
- B . FALSE
A
Explanation:
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove partitions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?
- A . TRUE
- B . FALSE
A
Explanation:
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove partitions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?
- A . TRUE
- B . FALSE
A
Explanation:
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove partitions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?
- A . TRUE
- B . FALSE
A
Explanation:
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove partitions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?
- A . TRUE
- B . FALSE
A
Explanation:
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove partitions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?
- A . TRUE
- B . FALSE
A
Explanation:
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove partitions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?
- A . TRUE
- B . FALSE
A
Explanation:
The CREATE EXTERNAL TABLE syntax for manually added partitions is as follows:
Which Scenario Data engineer decide Materialized views are not useful. Select All that apply.
- A . Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).
- B . Query results contain results that require significant processing.
- C . The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.
- D . The view’s base table change frequently.
A,C,D
Explanation:
Materialized views are designed to store computed results of a query which can then be referenced without recomputing the original expression. This is useful when a query is computationally expensive. However, there are scenarios where using materialized views might not be beneficial. Let’s examine each of the options:
Partition columns optimize query performance by pruning out the data files that do not need to be scanned (i.e. partitioning the external table).
Which pseudocolumn of External table evaluate as an expression that parses the path and/or filename information.
- A . METADATA$ROW_NUMBER
- B . METADATA$COLUMNNAME
- C . METADATA$FILEPATH
- D . METADATA$FILENAME
C
Explanation:
The pseudocolumn of an external table that evaluates as an expression parsing the path and/or filename information is:
C. METADATA$FILEPATH
This pseudocolumn provides the full path (including the filename) of the file from which a particular row originated. This can be especially useful when using partitioning based on directory or file structures in your external stage.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
D
Explanation:
Among the options provided, the incorrect statement (and thus not a limitation) is:
D. A materialized view can query only a single table & Joins, including self-joins, are not supported.
Materialized views in Snowflake can indeed be based on the result of a query that includes JOIN operations on one or more tables.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
D
Explanation:
Among the options provided, the incorrect statement (and thus not a limitation) is:
D. A materialized view can query only a single table & Joins, including self-joins, are not supported.
Materialized views in Snowflake can indeed be based on the result of a query that includes JOIN operations on one or more tables.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
D
Explanation:
Among the options provided, the incorrect statement (and thus not a limitation) is:
D. A materialized view can query only a single table & Joins, including self-joins, are not supported.
Materialized views in Snowflake can indeed be based on the result of a query that includes JOIN operations on one or more tables.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
D
Explanation:
Among the options provided, the incorrect statement (and thus not a limitation) is:
D. A materialized view can query only a single table & Joins, including self-joins, are not supported.
Materialized views in Snowflake can indeed be based on the result of a query that includes JOIN operations on one or more tables.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
D
Explanation:
Among the options provided, the incorrect statement (and thus not a limitation) is:
D. A materialized view can query only a single table & Joins, including self-joins, are not supported.
Materialized views in Snowflake can indeed be based on the result of a query that includes JOIN operations on one or more tables.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
D
Explanation:
Among the options provided, the incorrect statement (and thus not a limitation) is:
D. A materialized view can query only a single table & Joins, including self-joins, are not supported.
Materialized views in Snowflake can indeed be based on the result of a query that includes JOIN operations on one or more tables.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
D
Explanation:
Among the options provided, the incorrect statement (and thus not a limitation) is:
D. A materialized view can query only a single table & Joins, including self-joins, are not supported.
Materialized views in Snowflake can indeed be based on the result of a query that includes JOIN operations on one or more tables.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
D
Explanation:
Among the options provided, the incorrect statement (and thus not a limitation) is:
D. A materialized view can query only a single table & Joins, including self-joins, are not supported.
Materialized views in Snowflake can indeed be based on the result of a query that includes JOIN operations on one or more tables.
group by m.item_id;
Step 3: After 1 hour, he decided to temporarily suspend the use (and maintenance) of the DataReportMV materialized view for cost saving purpose.
alter materialized view DataReportMV suspend;
Please select what Alex is doing wrong here?
- A . A materialized view, DataReportMV does not support Join operations, so Step 2 would be failed & he cannot proceed further.
- B . Materialized view on top of External tables is not supported feature.
- C . Once DataReportMV got suspended, any query on the top of the view will generate error like: Failure during expansion of view ‘DATAREPORTMV’: SQL compilation error: Materialized view DataReportMV is invalid.
- D . There is no command like suspend for temporarily suspension of Materialized views, Step 3 will
give error like invalid Suspend command. - E . Alex is doing everything correct.
B,D
Explanation:
B. As of my last training data, Snowflake does not support creating materialized views on external tables. Materialized views are typically built on native Snowflake tables or views.
D. There’s no "suspend" command for materialized views in Snowflake. Materialized views in Snowflake automatically stay up-to-date as the underlying data changes without the need to manually refresh, so there’s no need for a "suspend" operation.
David, a Lead Data engineer with XYZ company looking out to improve query performance & other benefits while working with Tables, Regular Views, MVs and Cached Results.
Which one of the following does not shows key similarities and differences between tables, regular views, cached query results, and materialized views while choosing any of them by David?
- A . Regular views do not cache data, and therefore cannot improve performance by caching.
- B . As with non-materialized views, a materialized view automatically inherits the privileges of its base table.
- C . Cached Query Results: Used only if data has not changed and if query only uses deterministic functions (e.g. not CURRENT_DATE).
- D . Materialized views are faster than tables because of their “cache” (i.e. the query results for the
view); in addition, if data has changed, they can use their “cache” for data that hasn’t changed and use the base table for any data that has changed. - E . Both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level.
B
Explanation:
Materialized Views, like other database objects (tables, views, UDFs, etc.), are owned by a role and have privileges that can be granted to other roles.
You can grant the following privileges on a materialized view:
SELECT
As with non-materialized views, a materialized view does not automatically inherit the privileges of its base table. You should explicitly grant privileges on the materialized view to the roles that should use that view.
As with non-materialized views, a user who wishes to access a materialized view needs privileges only on the view, not on the underlying object(s) that the view references. Rest is correct.
Melissa, Senior Data Engineer, looking out to optimize query performance for one of the Critical Control Dashboard, she found that most of the searches by the users on the control dashboards are based on Equality search on all the underlying columns mostly.
Which Best techniques she should consider here?
- A . She can go for clustering on underlying tables which can speedup Equality searches.
- B . A materialized view speeds both equality searches and range searches.
- C . The search optimization service would best fit here as it can be applied to all underlying columns & speeds up equality searches. (Correct)
- D . Melissa can create Indexes & Hints on the searchable columns to speed up Equality search.
C
Explanation:
Clustering a table can speed any of the following, as long as they are on the clustering key:
・ Range searches.
・ Equality searches.
However, a table can be clustered on only a single key (which can contain one or more columns or expressions).
The search optimization service speeds equality searches. However, this applies to all the columns of supported types in a table that has search optimization enabled. This is what required here& best fit for purpose.
A materialized view speeds both equality searches and range searches, as well as some sort opera-tions, but only for the subset of rows and columns included in the materialized view.
Search optimization works best to improve the performance of a query when the following conditions are true: [Select All that apply]
- A . The table is not clustered.
- B . The table is frequently queried on columns other than the primary cluster key.
- C . Search Query uses Equality predicates (for example, <column_name> = <constant>) OR Predicates that use IN.
- D . Search Query uses Sort Operations.
A, B, C
Explanation:
Materialized Views works best for search query performance in case of Sort Operations. For Rest of the points Search optimization works best to improve query performance.
Regular views do not cache data, and therefore cannot improve performance by caching?
- A . TRUE
- B . FALSE
A
Explanation:
Regular views do not cache data, and therefore cannot improve performance by caching.
Mark the correct statements about Cache?
- A . Materialized views are more flexible than, but typically slower than, cached results.
- B . Materialized views are faster than tables because of their “cache” (i.e. the query results for the view); in addition, if data has changed, they can use their “cache” for data that hasn’t changed and use the base table for any data that has changed.
- C . For persisted query results of all sizes, the cache expires after 24 hours.
- D . The size of the warehouse cache is determined by the compute resources in the ware-house.
- E . Warehouse cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed.
A, B, C, D, E
Explanation:
How Does Warehouse Caching Impact Queries?
Each warehouse, when running, maintains a cache of table data accessed as queries are processed by the warehouse. This enables improved performance for subsequent queries if they are able to read from the cache instead of from the table(s) in the query. The size of the cache is determined by the compute resources in the warehouse (i.e. the larger the warehouse and, therefore, more compute re-sources in the warehouse), the larger the cache.
This cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed. As the resumed warehouse runs and processes more queries, the cache is rebuilt, and queries that are able to take advantage of the cache will experience improved performance.
Keep this in mind when deciding whether to suspend a warehouse or leave it running. In other words, consider the trade-off between saving credits by suspending a warehouse versus maintaining the cache of data from previous queries to help with performance. Using Persisted Query Results
When a query is executed, the result is persisted (i.e. cached) for a period of time. At the end of the time period, the result is purged from the system.
Snowflake uses persisted query results to avoid re-generating results when nothing has changed (i.e. “retrieval optimization”). In addition, you can use persisted query results to post-process the results (e.g. layering a new query on top of the results already calculated). For persisted query results of all sizes, the cache expires after 24 hours.
Both materialized views and cached query results provide query performance benefits:
Materialized views are more flexible than, but typically slower than, cached results.
Materialized views are faster than tables because of their “cache” (i.e. the query results for the view); in addition, if data has changed, they can use their “cache” for data that hasn’t changed and use the base table for any data that has changed.
Regular views do not cache data, and therefore cannot improve performance by caching.
Marko, a Data Engineer is using Snowpipe for data loading in micro batches for one of the Finance Data workloads. There are set of files he attempted to load into the snowflake table using Snow-pipe. While monitoring he found that there are set of files has multiple issue, He queried the COPY_HISTORY view & checked the STATUS column which indicates whether a particular set of files was loaded, partially loaded, or failed to load.
But he wants to view all errors in the files along with Load status, how he can check all errors?
- A . He can check RETURN_ALL_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason and view all errors in the files.
- B . He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_ERROR_MODE copy option set to RE-TURN_ALL_PIPE_ERRORS.
- C . Marko can look out for FIRST_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason why a file partially loaded or failed for all the files.
- D . He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS.
C
Explanation:
To view details about the errors when loading data into Snowflake, Marko can refer to specific columns in the relevant views. Here’s how each option fares:
Robert, A Data Engineer, found that Pipe become stale as it was paused for longer than the limited retention period for event messages received for the pipe (14 days by default) & also the previous pipe owner transfers the ownership of this pipe to Robert role while the pipe was paused.
How Robert in this case, Resume this stale pipe?
- A . PIPE needs to recreate in this scenario, as pipe already past 14 days of period & stale.
- B . He can apply System function SYSTEM$PIPE_STALE_RESUME with ALTER PIPE statement.
- C . Robert can use SYSTEM$PIPE_FORCE_RESUME function to resume this stale pipe.
- D . select sys-tem$pipe_force_resume (‘mydb.myschema.stalepipe’,’staleness_check_override, ownership_transfer_check_override’);
- E . ALTER PIPES … RESUME statement will resume the pipe.
D
Explanation:
When a pipe is paused, event messages received for the pipe enter a limited retention period. The period is 14 days by default. If a pipe is paused for longer than 14 days, it is considered stale.
To resume a stale pipe, a qualified role must call the SYSTEM$PIPE_FORCE_RESUME function and input the STALENESS_CHECK_OVERRIDE argument. This argument indicates an under-standing that the role is resuming a stale pipe.
For example, resume the stale stalepipe1 pipe in the mydb.myschema database and schema: SELECT SYS-TEM$PIPE_FORCE_RESUME(‘mydb.myschema.stalepipe1′,’staleness_check_override’); While the stale pipe was paused, if ownership of the pipe was transferred to another role, then resuming the pipe requires the additional OWNERSHIP_TRANSFER_CHECK_OVERRIDE argument. For example, resume the stale stalepipe2 pipe in the mydb.myschema database and schema, which transferred to a new role:
SELECT SYS-TEM$PIPE_FORCE_RESUME(‘mydb.myschema.stalepipe1′,’staleness_check_override, own-ership_transfer_check_override’);
How Data Engineer can do Monitoring of Files which are Staged Internally during Continuous data pipelines loading process? [Select all that apply]
- A . She Can Monitor the files using Metadata maintained by Snowflake i.e. file-name,last_modified date etc.
- B . Snowflake retains historical data for COPY INTO commands executed within the previous 14 days.
- C . She can Monitor the status of each COPY INTO <table> command on the History tab page of the classic web interface.
- D . She can use the DATA_LOAD_HISTORY Information Schema view to retrieve the history of data loaded into tables using the COPY INTO command.
- E . She can use the DATA_VALIDATE function to validate the data files She have loaded and can retrieve any errors encountered during the load.
A, B, C, D
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed.
Which option he can choose to remove these files either during data loading or afterwards?
- A . He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- B . Files no longer needed, can be removed using the PURGE=TRUE command.
- C . Files no longer needed, can be removed using the REMOVE command.
- D . Script can be used during data loading & post data loading with DELETE command.
A, C
Explanation:
Let’s evaluate each option:
Snowflake does not provide which of following set of SQL functions to support retrieving information about tasks?
- A . SYSTEM$CURRENT_USER_TASK_NAME
- B . TASK_HISTORY
- C . TASK_DEPENDENTS
- D . TASK_QUERY_HISTORY
- E . SYSTEM$TASK_DEPENDENTS_ENABLE
C
Explanation:
SYSTEM$CURRENT_USER_TASK_NAME
Returns the name of the task currently executing when invoked from the statement or stored procedure defined by the task.
SYSTEM$TASK_DEPENDENTS_ENABLE
Recursively resumes all dependent tasks tied to a specified root task.
TASK_DEPENDENTS
This table function returns the list of child tasks for a given root task in a DAG of tasks.
TASK_HISTORY
This table function can be used to query the history of task usage within a specified date range.
SYSTEM$CLUSTERING_INFORMATION functions returns clustering information, including average clustering depth, for a table based on one or more columns in the table. The function returns a JSON object containing average_overlaps name/value pairs.
Does High average_overlaps indicates well organized Clustering?
- A . YES
- B . NO
B
Explanation:
Higher the avg_overlap indicates poorly organized clustering.
The smaller the average depth, the better clustered the table is with regards to the specified column?
- A . TRUE
- B . FALSE
Data Engineer, ran the below clustering depth analysis function:
select system$clustering_depth(‘TPCH_CUSTOMERS’, ‘(C1, C6)’, ‘C9 = 30’); on TPCH_CUSTOMERS table, will return which of the following?
- A . An error: this function does not accept lists of columns as a third parameter.
- B . An error: this function does not accept predicates (‘C9 = 30’) as parameter.
- C . Calculate the clustering depth for a table using mentioned columns in the table.
- D . Calculate the clustering depth for a table using the clustering key defined for the table.
Mark the Correct Statements:
Statement 1. Snowflake’s zero-copy cloning feature provides a convenient way to quickly take a “snapshot” of any table, schema, or database.
Statement 2. Data Engineer can use zero-copy cloning feature for creating instant backups that do not incur any additional costs (until changes are made to the cloned object).
- A . Statement 1
- B . Statement 2
- C . Both are False.
- D . Statement 1 & 2 are correct.
D
Explanation:
Statement 1 is accurate. Snowflake’s zero-copy cloning feature allows users to create a clone of a table, schema, or database without copying the underlying data, making it an efficient method to produce a "snapshot" of the data.
Statement 2 is also accurate. Since zero-copy cloning doesn’t duplicate the actual data until changes are made to the clone, you don’t incur additional storage costs immediately after cloning. Instead, you start incurring costs only when there’s a divergence between the clone and the original, due to Snowflake’s unique data sharing and metadata tracking capabilities.
Therefore, the correct answer is:
D. Statement 1 & 2 are correct.
Clones can be cloned, with no limitations on the number or iterations of clones that can be created (e.g. you can create a clone of a clone of a clone, and so on), which results in a n-level hierarchy of cloned objects, each with their own portion of shared and independent data storage?
- A . TRUE
- B . FALSE