Data Engineer decided to call the public REST endpoints to load data and retrieve load history reports.Which of the following REST endpoints and a Snowflake Information Schema table function for viewing your load history can be used by her? [Select All that apply]

Information Schema table function: COPY_HISTORY

Account Usage view:COPY_HISTORY

REST endpoints: insertReport or loadHistoryScan

REST endpoints: loadinsertReport or HistoryScan

Information Schema table function: COPY_HISTORY

Account Usage view:COPY_HISTORY

REST endpoints: insertReport or loadHistoryScan

Information Schema table function: LOAD_HISTORY

Mark the incorrect statement when Data Engineer implement Automating Continuous Data Loading Using Cloud Messaging?

Triggering automated Snowpipe data loads using S3 event messages is supported by Snowflake accounts hosted on Cloud Platform like AWS, GCP or AZURE.

Automated Snowpipe uses event notifications to determine when new files arrive in monitored cloud storage and are ready to load.

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.

Notifications identify the cloud storage event and include a list of the file names. They do not include the actual data in the files.

Triggering automated Snowpipe data loads using S3 event messages is supported by Snowflake accounts hosted on Cloud Platform like AWS, GCP or AZURE.

Snowpipe API provides a REST endpoint for defining the list of files to ingest that Informs Snow-flake about the files to be ingested into a table. A successful response from this endpoint means thatSnowflake has recorded the list of files to add to the table. It does not necessarily mean the files have been ingested. What is name of this Endpoint?

REST endpoints --> insertReport

REST endpoints --> loadHistoryScan

The Snowpipe API provides REST endpoints for fetching load reports. One of the Endpoint named insertReport helps to retrieves a report of files submitted via insertFiles end point whose contents were recently ingested into a table. A success response (200) contains information about files that have recently been added to the table. Response Looks like below:

"pipe": "SNOWTESTDB.SFTESTSCHEMA.SFpipe",

"path": "data4859992083898.csv",

"stageLocation": "s3://mybucket/",

"fileSize": 89,10. "timeReceived": "2022-01-31T04:47:41.453Z",11. "lastInsertTime": "2022-01-31T04:48:28.575Z",12. "rowsInserted": 1,13. "rowsParsed": 1,14. "errorsSeen": 0,15. "errorLimit": 1,16. "complete": true,17. "status": "????"18. }19. ]20. }Which one is the correct value of status string data in the Response Body?

Data Engineer try to load data from external stage using Snowpipe & later find out that some Set of Files Not Loaded. To debug the issue, she used COPY_HISTORY function & cross verified that its output indicates a subset of files was not loaded. What is possible reason of arising this situation in both REST API call and Auto-Ingest methods? [Select 2]

External event-driven functionality is used to call the REST APIs, and a backlog of da-ta files already existed in the external stage before the events were configured.

An event notification failure prevented a set of files from getting queued.

External event-driven functionality is used to call the REST APIs, and a backlog of da-ta files already existed in the external stage before the events were configured.

An event notification failure prevented a set of files from getting queued.

Files modified and staged again after 14 days and Snowpipe ignores modified files that are staged again.

A backlog of data files already existed in the external stage do not have any impact on Load failure, as this is well managed by serverless SnowPipe

Data Engineer is looking out to delete staged files automatically/periodically when the data is successfully loaded into tables by the Snowpipe. For achieving the same, which options/command is best suited: [Select 2]

PURGE option can be set as True in the COPY INTO Command embedded in PIPE objects definition.

To remove staged files that no longer needed, periodically REMOVE command can be executed to delete the files.

PURGE option can be set as True in the COPY INTO Command embedded in PIPE objects definition.

To remove staged files that no longer needed, periodically REMOVE command can be executed to delete the files.

To remove staged files that no longer needed, periodically DELETE command can be executed to delete the files.

REMOVE_STAGE_FILES option can be set as True in the COPY INTO Command embedded in PIPE objects definition.

Data Engineer is using existing pipe that automates data loads using event notifications, later he figured out the needs to modify pipe properties. For the same, He decided to recreate the pipe as best practice. He followed the below steps for the same.

Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.Which are the Missing recommended steps while Recreating Pipes for Automated Data Loads?

Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.

Recreate the pipe (using CREATE OR REPLACE PIPE).

Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.Which are the Missing recommended steps while Recreating Pipes for Automated Data Loads?

CREATE OR REPLACE PIPE command will recreate the PIPE successfully.

Terminate the existing pipe (using ALTER PIPE … SET PIPE_EXECUTION_TERMINATE = true) before recreation.

Pause the pipe (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = true) Pre & Post recreation & Resume after recreation (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = false).

Force the pipe to resume (using SYSTEM$PIPE_FORCE_RESUME).

Dominic, a Data Engineer wants to resume the pipe named stalepipe3 which got stale after 14 days.To do the same, he called the SYSTEM$PIPE_FORCE_RESUME function select system$ pipe_force_resume('snowmydb.mysnowschema.stalepipe3','staleness_check_override');Let's say If the pipe is resumed 16 days after it was paused, what will happened to the event notification that were received on the first and second days after the pipe was paused?

Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused.

Pipe maintains Metadata history of files for 64 days, so in this scenarios Snowpipe pro-cessed all the event notifications that were received for 16 days or so.

Once the Pipe got stale, all the events got purged automatically & pipe needs to be rec-reated with modified properties.

All the events get processed from day 1 if the PURGE properties in the PIPE object definition set to be FALSE initially.

Data Engineer wants to analyze query performance & looking out for profiling information, He went to Query/Operator Details also called Profile Overview of Query Profile Interface & searching for statistics attributes around I/O. Which of the following information he can't get from there?

Bytes sent over the wireframe — amount of data sent over the wireframe

Percentage scanned from cache — the percentage of data scanned from the local disk cache.

Bytes written — bytes written (e.g. when loading into a table).

External bytes scanned — bytes read from an external object, e.g. a stage.

Bytes sent over the wireframe — amount of data sent over the wireframe

Bytes read from result — bytes read from the result object.

What are Common Query Problems a Data Engineer can identified using Query Profiler?

"Exploding" Joins i.e Joins resulting due to a "Cartesian product"

Queries Too Large to Fit in Memory

"Exploding" Joins i.e Joins resulting due to a "Cartesian product"

Queries Too Large to Fit in Memory

Jonas, a Lead Performance Engineer,identifed that some of the operation of his query which functionally remove the duplicates from huge data set is spilling the data to remote disk. How can he alleviate spilling to a remote disk for better query performance?

Jonas can recommend using a large warehouse which effectively increase the available memory/local disk space for the operations.

He can Process data in smaller batches to manage workload.

Jonas can recommend using a large warehouse which effectively increase the available memory/local disk space for the operations.

He can Process data in smaller batches to manage workload.

Spilling do not have a profound effect on query performance (especially if remote disk is used for spilling).

Data Sharing can be helpful to improve query performance.

You as Data engineer might want to consider disabling auto-suspend for a warehouse if?

You have a heavy, steady workload for the warehouse.

You require the warehouse to be available with no delay or lag time.

You have a low, fluctuating workload for the warehouse.

You have a heavy, steady workload for the warehouse.

You require the warehouse to be available with delay.

You require the warehouse to be available with no delay or lag time.

Ryan, a Data Engineer, wants to improve the performance of large, complex queries against large data sets. He decided to Scale up underlying warehouse/cluster. What is correct Snowflake consideration while scaling up so that he can achieve better performance results? [Select all that apply]

Resizing can help reduce the queuing that occurs if a warehouse does not have enough compute resources to process all the queries that are submitted concurrently.

Scaling up is not intended for handling concurrency issues; instead, use additional warehouses to handle the workload or use a multi-cluster warehouse (if this feature is available for your account).

Snowflake supports resizing a warehouse at any time, even while running.

Resizing a running warehouse does not impact queries that are already being processed by the warehouse; the additional compute resources, once fully provisioned, are only used for queued and new queries.

Resizing between a 5XL or 6XL warehouse to a 4XL or smaller warehouse results in a brief period during which the customer is charged for both the new warehouse and the old warehouse while the old warehouse is quiesced.

Resizing can help reduce the queuing that occurs if a warehouse does not have enough compute resources to process all the queries that are submitted concurrently.

Scaling up is not intended for handling concurrency issues; instead, use additional warehouses to handle the workload or use a multi-cluster warehouse (if this feature is available for your account).

Snowflake supports resizing a warehouse at any time, even while running.

Resizing a running warehouse does not impact queries that are already being processed by the warehouse; the additional compute resources, once fully provisioned, are only used for queued and new queries.

Resizing between a 5XL or 6XL warehouse to a 4XL or smaller warehouse results in a brief period during which the customer is charged for both the new warehouse and the old warehouse while the old warehouse is quiesced.

While working with Multi Cluster Warehouses, Select the incorrect understanding of Data Engineer about its usage?

Multi-cluster warehouses generally improve query performance, particularly for larger, more complex queries.

Multi-cluster warehouses are designed specifically for handling queuing and perfor-mance issues related to large numbers of concurrent users and/or queries.

Unless you have a specific requirement for running in Maximized mode, multi-cluster warehouses should be configured to run in Auto-scale mode, which enables Snowflake to automatically start and stop clusters as needed.

When choosing the minimum number of clusters for a multi-cluster warehouse keep the default value as 1.

Multi-cluster warehouses generally improve query performance, particularly for larger, more complex queries.

When choosing the maximum number of clusters for a multi-cluster warehouse set its value as large as possible.

Select the incorrect statement while working with warehouses?

Resizing a warehouse will have any immediate impact on statements that are currently being executed by the warehouse.

Compute resources waiting to shut down are considered to be in "quiesce" mode.

Resizing a warehouse to a larger size is useful while loading and unloading significant amounts of data.

Resizing a warehouse will have any immediate impact on statements that are currently being executed by the warehouse.

Resizing a suspended warehouse does not provision any new compute resources for the warehouse.

Ira a Data Engineer with TESLA IT systems, looking out to Compare Traditional Partitioning vs Snowflake micro-partitions for one of the Snowflake Project implementations. Which one of the following is incorrect understanding of Ira about Micro Partitioning?

Snowflake stores metadata about all rows stored in a micro-partition, including number of distinct columns.

All data in Snowflake tables is automatically divided into micro-partitions, which are contiguous units of storage compared to traditional partitioning where specialized DDL required.

All DML operations (e.g. DELETE, UPDATE, MERGE) take advantage of the under-lying micropartition metadata to facilitate and simplify table maintenance.

Snowflake stores metadata about all rows stored in a micro-partition, including number of distinct columns.

The micro-partition metadata maintained by Snowflake enables precise pruning of col-umns in micro-partitions at query run-time, including columns containing semi-structured data.

In Snowflake, as data is inserted/loaded into a table, clustering metadata is collected and recorded for each micro-partition created during the process.

Which one is not the Core benefits of micro-partitioning

Micro-partitions can overlap in their range of values, helps data skewing.

Snowflake micro-partitions are derived automatically they do not need to be explicitly defined upfront or maintained by users.

Enables extremely efficient DML and fine-grained pruning for faster queries.

Micro-partitions can overlap in their range of values, helps data skewing.

Columns are stored independently within micro-partitions, often referred to as colum-nar storage.

Columns are also compressed individually within micro-partitions.

Select the incorrect statements regarding Clustering depth?

A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 1.(Correct)

The clustering depth for a populated table measures the average depth (1 or greater) of the overlapping micro-partitions for specified columns in a table. The smaller the aver-age depth, the better clustered the table is with regards to the specified columns.

It helps Monitoring the clustering "health" of a large table, particularly over time as DML is performed on the table.

Clustering depth can be used for determining whether a large table would benefit from explicitly defining a clustering key.

A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 1.(Correct)

To view/monitor the clustering metadata for a table, Snowflake provides which of the following system functions?

SYSTEM$CLUSTERING_INFORMATION (including clustering depth)

SYSTEM$CLUSTERING_KEY_INFORMATION (including clustering depth)

SYSTEM$CLUSTERING_INFORMATION (including clustering depth)

Which ones are the false statements about Materialized Views?

Materialized views are first-class account objects.

Snowflake does not allow standard DML (e.g. INSERT, UPDATE, DELETE) on ma-terialized views.

Snowflake does not allow users to truncate materialized views.

Materialized views are first-class account objects.

A materialized view can also be used as the data source for a subquery.

Materialized views can be secure views.

Clustering a subset of the materialized views on a table tends to be more cost-effective than clustering the table itself.

Charles, A Lead Data engineer, with ACCOUNTADMIN role wants to configure the time travel for one of the Schema's object. He setup the MIN_DATA_RETENTION_TIME_IN_DAYS pa-rameter with Value 79 at account level but he figured out that DA-TA_RETENTION_TIME_IN_DAYS is already set with value 81 at account level. What would be the effective minimum data retention period for an object?

There is no such MIN_DATA_RETENTION_TIME_IN_DAYS parameter

In efforts to recover the dropped child tables within schema named SCV_SCHEMA by Data Engi-neer, She found that DATA_RETENTION_TIME_IN_DAYS parameter set with value 45 days at Schema level & the data retention period for child tables explicitly set at 85 days. What will happen when she will try to run undrop table command on Child tables to recover them on the 50th day as-suming SCV_SCHEMA is already dropped on 45th day?

When a schema is already dropped, the data retention period for child tables, if explicit-ly set to be different from the retention of the schema, is not honoured. So UNDROP command will fail to run on 50th day for Child tables recovery.

To honor the data retention period for child tables, She will ab able to recover the child tables on 50th day as DATA_RETENTION_TIME_IN_DAYS is explicitly set with higher retention value.

When a schema is already dropped, the data retention period for child tables, if explicit-ly set to be different from the retention of the schema, is not honoured. So UNDROP command will fail to run on 50th day for Child tables recovery.

Child tables can be recovered using Fail-Safe SQL commands.

Data Engineer needs to first recover the Schema & then Child tables will automatically be recovered irrespective of Retention Inheritance.

To support Time Travel, Which of the following SQL extensions/parameters/commands have been implemented?

AT | BEFORE clause which can be specified in the CREATE … CLONE commands.

OFFSET (time difference in seconds from the present time)

STATEMENT (identifier for statement, e.g. query ID)

UNDROP command for tables, schemas, and databases.

AT | BEFORE clause which can be specified in the CREATE … CLONE commands.

ONSET (time difference in seconds from the present time)

OFFSET (time difference in seconds from the present time)

STATEMENT_ID (identifier for statement, e.g. query ID)

STATEMENT (identifier for statement, e.g. query ID)

UNDROP command for tables, schemas, and databases.

The following CREATE DATABASE command creates a clone of a database snowmy_db i.e.Create database pods_db clone snowmy_db before (statement => '7e5d0cb9-005e-94e6-b058-k8f5b37c5725'); What are possible reason of failing cloning operation for this database?

Time Travel Statement query time is beyond the retention time of few current child (e.g., a table) of the Database entity.

Time Travel Statement query time is at or before the point in time when the object was created.

Time Travel Statement query time is beyond the retention time of few current child (e.g., a table) of the Database entity.

Time Travel Statement query time is at or before the point in time when the object was created.

CREATE DATABASE query fails due to compilation error as it do not support state-ment keyword.

SQL Compilation error: "Incorrect Syntax 'before' while creating database"

Which of the following statements is/are incorrect regarding Fail-safe data recovery?

Historical data in transient tables can be recovered by Snowflake due to Operation fail-ure after the Time Travel retention period ends using Fail-safe.

Data stored in temporary tables is not recoverable after the table is dropped as they do not have fail-safe.

Historical data in transient tables can be recovered by Snowflake due to Operation fail-ure after the Time Travel retention period ends using Fail-safe.

Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.

Short-lived tables (i.e. <1 day), such as ETL work tables, can be defined as transient to eliminate Fail-safe costs.

If downtime and the time required to reload lost data are factors, permanent tables, even with their added Fail-safe costs, may offer a better overall solution than transient tables.

Select the Correct statements with regard to using Federated authentication/SSO?

Snowflake supports using MFA in conjunction with SSO to provide additional levels of security.

Snowflake supports multiple audience values (i.e. Audience or Audience Restriction Fields) in the SAML 2.0 assertion from the identity provider to Snowflake.

Snowflake supports SSO with Private Connectivity to the Snowflake Service for Snow-flake accounts on Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform.

Snowflake supports using SSO with organizations, and you can use the corresponding URL in the SAML2 security integration.

Snowflake supports using MFA in conjunction with SSO to provide additional levels of security.

Snowflake supports multiple audience values (i.e. Audience or Audience Restriction Fields) in the SAML 2.0 assertion from the identity provider to Snowflake.

Snowflake supports SSO with Private Connectivity to the Snowflake Service for Snow-flake accounts on Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform.

Snowflake supports using SSO with organizations, and you can use the corresponding URL in the SAML2 security integration.

Jackie, a Data engineer advised to his data team members about one of the Role highlighting fol-lows points:

Avoid Using the <?> Role for Automated Scripts

Avoid Using the <?> Role to Create Objects Which System defined or Custom Role She is mentioning?

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

Data engineer designed the data pipelines using Snowpipe to load data files into Snowflake tables, what will happen in case few files with same name but modified data are queued for reloading?

Become a Premium Member for full access

Unlock Premium Member

Snowflake DEA-C01 Practice Test 3

Question

Snowflake DEA-C01 Practice Tests

Practice Tests