| "cluster_by_keys" : "(COL1, COL3)", |

| SYSTEM$CLUSTERING_INFORMATION('SF_DATA', '(COL1, COL3)') |

| "cluster_by_keys" : "(COL1, COL3)", |

| "total_partition_count" : 1156, |

| "total_constant_partition_count" : 0, |

| "average_overlaps" : 117.5484, |

| "average_depth" : 64.0701, |10. | "partition_depth_histogram" : { |11. | "00000" : 0, |12. | "00001" : 0, |13. | "00002" : 3, |14. | "00003" : 3, |15. | "00004" : 4, |16. | "00005" : 6, |17. | "00006" : 3, |18. | "00007" : 5, |19. | "00008" : 10, |20. | "00009" : 5, |21. | "00010" : 7, |22. | "00011" : 6, |23. | "00012" : 8, |24. | "00013" : 8, |25. | "00014" : 9, |26. | "00015" : 8, |27. | "00016" : 6, |28. | "00032" : 98, |29. | "00064" : 269, |30. | "00128" : 698 |31. | } |32. | } |33. +--------------------------------------------------------------+The Above example indicates that the SF_DATA table is not well-clustered for which of following valid reasons?

Zero (0) constant micro-partitions out of 1156 total micro-partitions.

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?

Views with GROUP BY & LIMIT Clause are supported by Snowflake.

For streams on views, change tracking must be enabled explicitly for the view and un-derlying tables to add the hidden columns to these tables.

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.

Views with GROUP BY & LIMIT Clause are supported by Snowflake.

As an alternative to streams, Snowflake supports querying change tracking metadata for views using the CHANGES clause for SELECT statements.

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 stor-age.

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]

using the CREATE OR REPLACE STREAM syntax, Recreate the STREAM

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

using the CREATE OR REPLACE STREAM syntax, Recreate the STREAM

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

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.

Delete the offset using STREAM properties SYSTEM$RESET_OFFSET( <stream_id> )

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:

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.

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.

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.

For Step 5, If the stream was consumed in DML statements within the transaction, the stream position advances to the transaction start time.

For Step 7, Results do include table changes committed by Transaction 1.

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 Transac-tion 1.

Mark the Incorrect Statements with respect to types of streams supported by Snowflake?

An insert-only stream tracks row inserts & Delete ops only

Standard streams cannot retrieve update data for geospatial data.

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

Insert-only Stream supported on external tables only.

An insert-only stream tracks row inserts & Delete ops only

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?

MAX_DATA_EXTENSION_TIME_IN_DAYS

Ron, Snowflake Developer needs to capture change data (insert only) on the source views, for that he follows the below steps:Enable change tracking on the source views & its underlying tables.Inserted the data via Scripts scheduled with the help of Tasks.then simply run the below Select statements.

changes(information => append_only)

at(timestamp => (select current_timestamp()));Select the Correct Query Execution Output option below:

Developer missed to create stream on the source table which can further query to cap-ture DML records.

Select query will fail with error: 'SQL compilation error-Incorrect Keyword "Chang-es()" found'

No Error reported, select command gives Changed records with Metadata columns as change tracking enabled on the Source views & its underlying tables.

Select statement complied but gives erroneous results.

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?

All the Six Records would be displayed with CURRENT_USER & CUR-RENT_TIMESTAMP while querying Streams.

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

All the Six Records would be displayed with CURRENT_USER & CUR-RENT_TIMESTAMP while querying Streams.

All the Six records would be displayed with User 'Sohpie' Who is the owner of the View.

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.

Steven created the task, what additional privileges required by Steven on the task so that he can suspend or resume the tasks?

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.

Steven is already owner of the task; he can execute the task & suspend/resume the task without any additional privileges.

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.

Steven must have SUSPEND privilege on the task so that he can suspend or resume the task.

Steven needs to have Global Managed RESUME privilege by TASK administrator.

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?

John can use AUTO_REFRESH parameter if the underlying External Cloud host sup-ports this for External tables.

He can create a task that executes an ALTER EXTERNAL TABLE ... REFRESH statement every 5 minutes.

John can use AUTO_REFRESH parameter if the underlying External Cloud host sup-ports this for External tables.

He can create a task that executes an ALTER EXTERNAL TABLE ... REFRESH statement every 5 minutes.

External table cannot be scheduled via Snowflake Tasks, 3rd party tools/scripts needs to be used provided by External cloud storage provider.

Snowflake implicitly take care this Infrastructure needs, as underlying warehouse layer internally manage the refresh. No action needed from John.

Mohan, Data engineer works with ALTUSO Company, wants to programmatically check the status of the query. He needs query id to identify each query executed by Snowflake & using Snowflake Connector for Python to execute a query, how he will be able to meet this requirements. Select the best options you will suggest?

He can access the query ID through the sfqid attribute in the Cursor object.

cur.execute("SELECT * FROM snowtable")

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.

He needs to query history views to get the queryID as best practices.

Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.

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

By Executing UNDROP command he could possibly recover the dropped Share SF_SHARE & its associated Databases for immediate consumer access.

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.

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.

Consumer accounts that have created databases from the share will still be able to que-ry these databases as Share is separate securable object & it's still accessible using time travel feature.

Mark the correct Statements with respect to Secure views & its creation in the SnowFlake Account?

Secure views should not be used for views that are defined solely for query conven-ience, such as views created to simplify queries for which users do not need to under-stand the underlying data representation.

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 com-mand.

For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure.

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'sQuery Profile.

For a secure view, internal optimizations can indirectly expose data & the view defini-tion is visible to other users.

Secure views should not be used for views that are defined solely for query conven-ience, such as views created to simplify queries for which users do not need to under-stand the underlying data representation.

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 com-mand.

For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure.

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'sQuery Profile.

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?

By default, the metadata is refreshed automatically when the object is created.

The object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.

By default, the metadata is refreshed automatically when the object is created.

The object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.

Metadata refresh is not required as its Managed implicitly by Snowflake.

Partitions of External tables is managed by External Stage Cloud provider.

There is nothing like adding partitions on External tables.

Which Scenario Data engineer decide Materialized views are not useful. Select All that apply.

The view's base table change frequently.

Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).

Query results contain results that require significant processing.

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.

The view's base table change frequently.

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 materialized views does not support clustering.

A materialized view cannot include HAVING clauses OR ORDER BY clause.

Truncating a materialized view is not supported.

Time Travel is not currently supported on materialized views.

You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW ...CLONE... command.

A materialized view can query only a single table & Joins, including self-joins, are not supported.

A materialized views does not support clustering.

A materialized views cannot be created on Shared Data.

A materialized view cannot include HAVING clauses OR ORDER BY clause.

Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.

Alex, a Data Engineer with one of the Data analytics Organization, created the Materialized view over External tables to improve Data Reporting Experience.Step 1: He created materialized view named DataReportMV

sum(ifnull(quantity * (s.price - m.item_price), 0)) as profit

create or replace materialized view DataReportMV as

select Item_id, Item_price from Items; Step 2: He joined a materialized view with a sales table as

create or replace view Revenue as

select m.item_id, sum(ifnull(s.quantity, 0)) as quantity,

sum(ifnull(quantity * (s.price - m.item_price), 0)) as profit

from DataReportMV as m left outer join sales as s on s.item_id = m.item_id

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 materialized view, DataReportMV does not support Join operations, so Step 2 would be failed & he cannot proceed further.

Materialized view on top of External tables is not supported feature.

Once DataReportMV got suspended , any query on the top of the view will generate er-ror like:Failure during expansion of view 'DATAREPORTMV': SQL compilation error: Material-ized view DataReportMV is invalid.

David, a Lead Data engineer with XYZ company looking out to improve query performance & oth-er 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?

As with non-materialized views, a materialized view automatically inherits the privileges of its base table.

Regular views do not cache data, and therefore cannot improve performance by cach-ing.

As with non-materialized views, a materialized view automatically inherits the privileges of its base table.

Cached Query Results: Used only if data has not changed and if query only uses de-terministic functions (e.g. not CURRENT_DATE).

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.

Both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level.

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?

The search optimization service would best fit here as it can be applied to all underlying columns & speeds up equality searches.(Correct)

She can go for clustering on underlying tables which can speedup Equality searches.

A materialized view speeds both equality searches and range searches.

The search optimization service would best fit here as it can be applied to all underlying columns & speeds up equality searches.(Correct)

Melissa can create Indexes & Hints on the searchable columns to speed up Equality search.

Search optimization works best to improve the performance of a query when the following conditions are true:[Select All that apply]

The table is frequently queried on columns other than the primary cluster key.

Search Query uses Equality predicates (for example, <column_name> = <constant>) OR Predicates that use IN.

The table is frequently queried on columns other than the primary cluster key.

Search Query uses Equality predicates (for example, <column_name> = <constant>) OR Predicates that use IN.

Search Query uses Sort Operations.

Mark the correct statements about Cache?

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.

For persisted query results of all sizes, the cache expires after 24 hours.

The size of the warehouse cache is determined by the compute resources in the ware-house.

Warehouse cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed.

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.

For persisted query results of all sizes, the cache expires after 24 hours.

The size of the warehouse cache is determined by the compute resources in the ware-house.

Warehouse cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed.

Marko, a Data Engineer is using Snowpipe for data loading in micro batches for one of the FinanceData 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?

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.

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.

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.

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.

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.

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?

select sys-tem$pipe_force_resume('mydb.myschema.stalepipe','staleness_check_override, ownership_transfer_check_override');

PIPE needs to recreate in this scenario, as pipe already past 14 days of period & stale.

He can apply System function SYSTEM$PIPE_STALE_RESUME with ALTER PIPE statement.

Robert can use SYSTEM$PIPE_FORCE_RESUME function to resume this stale pipe.

select sys-tem$pipe_force_resume('mydb.myschema.stalepipe','staleness_check_override, ownership_transfer_check_override');

ALTER PIPES ... RESUME statement will resume the pipe.

How Data Engineer can do Monitoring of Files which are Staged Internally during Continuous data pipelines loading process? [Select all that apply]

She Can Monitor the files using Metadata maintained by Snowflake i.e. file-name,last_modified date etc.

Snowflake retains historical data for COPY INTO commands executed within the pre-vious 14 days.

She can Monitor the status of each COPY INTO <table> command on the History tab page of the classic web interface.

She Can Monitor the files using Metadata maintained by Snowflake i.e. file-name,last_modified date etc.

Snowflake retains historical data for COPY INTO commands executed within the pre-vious 14 days.

She can Monitor the status of each COPY INTO <table> command on the History tab page of the classic web interface.

She can use the DATA_LOAD_HISTORY Information Schema view to retrieve the history of data loaded into tables using the COPY INTO command.

She can use the DATA_VALIDATE function to validate the data files She have loaded and can retrieve any errors encountered during the load.

To help manage STAGE storage costs, Data engineer recommended to monitor stage files and remove 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?

He can choose to remove stage files during data loading (using the COPY INTO <table> command).

Files no longer needed, can be removed using the PURGE=TRUE command.

He can choose to remove stage files during data loading (using the COPY INTO <table> command).

Files no longer needed, can be removed using the PURGE=TRUE command.

Files no longer needed, can be removed using the REMOVE command.

Script can be used during data loading & post data loading with DELETE command.

Pascal, a Data Engineer, have requirement to retrieve the 10 most recent executions of a specified task (completed, still running, or scheduled in the future) scheduled within the last hour, which of the following is the correct SQL Code ?

scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),

from table(information_schema.task_history(

scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),

task_name=>'MYTASK') WHERE query_id IS NOT NULL);

from table(information_schema.task_history(

scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),

Snowflake DEA-C01 Practice Test 1 - Free Online Exam Prep & Questions

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

Snowflake DEA-C01 Practice Test 1

Question

Snowflake DEA-C01 Practice Tests

Practice Tests