DEA-C01 valid exam blueprint – Prepaway Exam Dumps

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

METADATA$ROW_NUMBER

METADATA$COLUMNNAME

METADATA$FILEPATH

METADATA$FILENAME

NO.22 Snowpipe loads data from files as soon as they are available in a stage. Automated data loads lever-age event notifications for cloud storage to inform Snowpipe of the arrival of new data files to load. Which Cloud hosted platform provides cross cloud support for automated data loading via Snow-pipe?

GCP

AWS

AZURE

None of the Above currently provide cross cloud support for Snowpipe.

NO.23 Mark the Correct Statements:
Statement 1. Enable failover for a primary database to one or more accounts in your organization using an ALTER DATABASE … ENABLE FAILOVER TO ACCOUNTS statement.
Statement 2. Enabling failover for a primary database can be done by Data Engineer either before or after a replica of the primary database has been created in a specified account.

Statement 1

Statement 2

Both are False.

Both are Correct.

NO.24 Which of the following security and governance tools/technologies are known to provide native connectivity to Snowflake? [Select 2]

ALTR

Baffle

BIG Squid

Dataiku

Zepl

NO.25 As a Data Engineer, you have requirement to query most recent data from the Large Dataset that reside in the external cloud storage, how would you design your data pipelines keeping in mind fastest time to delivery?

Data pipelines would be created to first load data into internal stages & then into Per-manent table with SCD Type 2 transformation.

Direct Querying External tables on top of existing data stored in external cloud storage for analysis without first loading it into Snowflake.

Unload data into SnowFlake Internal data storage using PUT command.

Snowpipe can be leveraged with streams to load data in micro batch fashion with CDC streams that capture most recent data only.

External tables with Materialized views can be created in Snowflake.

NO.26 At what isolation level are Snowflake streams?

Snapshot

Repeatable read

Read committed

Read uncommitted

NO.27 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 that Snowflake 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

REST endpoints –> ingestfiles

REST endpoints–> insertfiles

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

NO.29 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 consid-eration 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.

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

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

Explanation
When created, a stream logically takes an initial snapshot of every row in the source object (e.g. ta-ble, 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 rec-ords provide thestate 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. Query-ing 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 execut-ed, an autocommit transaction is implicitly started and the transaction is committed at the comple-tion 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).

NO.31 As Data Engineer, you have requirement to Load set of New Product Files containing Product rele-vant information into the Snowflake internal tables, Later you analyzed that some of the Source files are already loaded in one of the historical batch & for that you have prechecked Metadata col-umn LAST_MODIFIED date for a staged data file & found out that LAST_MODIFIED date is older than 64 days for few files and the initial set of data was loaded into the table more than 64 days earlier, Which one is the best approach to Load Source data files with expired load metadata along with set of files whose metadata might be available to avoid data duplication?

Since the initial set of data for the table (i.e. the first batch after the table was created) was loaded, we can simply use the COPY INTO command to load all the product files with the known load status irrespective of their column LAST_MODIFIED date values.

The COPY command cannot definitively determine whether a file has been loaded al-ready if the LAST_MODIFIED date is older than 64 days and the initial set of data was loaded into the table more than 64 days earlier (and if the file was loaded into the table, that also occurred more than 64 days earlier). In this case, to prevent accidental reload, the command skips the product files by default.

Set the FORCE option to load all files, ignoring load metadata if it exists.

To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true.

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

Statement 1

Statement 2

Both are False.

Statement 1 & 2 are correct.

NO.33 In Which Data Modelling Technique, Data Engineer generally refer the terms Hubs & Satellites?

Data Hub

Data Vault

Star Schema

Snowflake Schema

NO.34 Which are false statements about Star Schema?

Star schemas are denormalized.

The star schema separates business process data into facts, which hold the measurable, quantitative data about a business, and dimensions which are descriptive attributes re-lated to fact data.

Star schema is more flexible in terms of analytical needs compared to Data Vault Mod-elling.

The star schema is an important special case of the snowflake schema and is more effec-tive for handling simpler queries.

NO.35 To troubleshoot data load failure in one of your Copy Statement, Data Engineer have Executed a COPY statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS with reference to the set of files he had attempted to load. Which below function can facilitate analysis of the problematic records on top of the Results produced? [Select 2]

RESULT_SCAN

LAST_QUERY_ID

Rejected_record

LOAD_ERROR

NO.36 Within a Snowflake account permissions have been defined with custom roles and role hierarchies.
To set up column-level masking using a role in the hierarchy of the current user, what command would be used?

CORRECT_ROLE

IKVOKER_ROLE

IS_RCLE_IN_SESSION

IS_GRANTED_TO_INVOKER_ROLE

NO.37 Which are the Cloud Platforms that Support Calling an External Function?

GCP

AWS & AZURE

AWS only

AWS,GCP,AZURE

NO.38 A Data Engineer wants to centralize grant management to maximize security. A user needs ownership on a table m a new schema However, this user should not have the ability to make grant decisions What is the correct way to do this?

Grant ownership to the user on the table

Revoke grant decisions from the user on the table

Revoke grant decisions from the user on the schema.

Add the with managed access parameter on the schema

NO.39 A Data Engineer enables a result cache at the session level with the following command:
ALTER SESSION SET USE CACHED RESULT = TRUE;
The Engineer then runs the following select query twice without delay:

The underlying table does not change between executions
What are the results of both runs?

The first and second run returned the same results because sample is deterministic

The first and second run returned the same results, because the specific SEEDvalue was provided.

The first and second run returned different results because the query is evaluated each time it is run.

The first and second run returned differentresults because the query uses *instead of an explicit column list

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

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’s Query Profile.

Explanation
Why Should I Use Secure Views?
For a non-secure view, internal optimizations can indirectly expose data.
Some of the internal optimizations for views require access to the underlying data in the base tables for the view. This access might allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions, or other programmatic methods. Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.
For a non-secure view, the view definition is visible to other users.
By default, the query expression used to create a standard view, also known as the view definition or text, is visible to users in various commands and interfaces.
For security or privacy reasons, you might not wish to expose the underlying tables or internal struc-tural details for a view. With secure views, the view definition and details are visible only to author-ized users (i.e.
users who are granted the role that owns the view).
When Should I Use a Secure View?
Views should be defined as secure when they are specifically designated for data privacy (i.e. to limit access to sensitive data that should not be exposed to all users of the underlying table(s)).
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 understand the underlying data representation. Secure views can execute more slowly than non-secure views.
Secure views are defined using the SECURE keyword with the standard DDL for views:
To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MA-TERIALIZED VIEW command.
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.
The definition of a secure view is only exposed to authorized users (i.e. users who have been grant-ed the role that owns the view). If an unauthorized user uses any of the following commands or in-terfaces, the view definition is not displayed:
SHOW VIEWS and SHOW MATERIALIZED VIEWS commands.
GET_DDL utility function.
VIEWS Information Schema view.
VIEWS Account Usage view.
For non-materialized views, the IS_SECURE column in the Information Schema and Account Us-age 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’s Query Profile.

NO.41 A Data Engineer is trying to load the following rows from a CSV file into a table in Snowflake with the following structure:

….engineer is using the following COPY INTO statement:

However, the following error is received.

Which file format option should be used to resolve the error and successfully load all the data into the table?

ESC&PE_UNENGLO9ED_FIELD = ‘\’

ERROR_ON_COLUMN_COUKT_MISMATCH = FALSE

FIELD_DELIMITER = “,”

FIELD OPTIONALLY ENCLOSED BY = ” “

NO.42 Data Engineer is looking out to delete staged files automatically/periodically when the data is suc-cessfully 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.

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.

NO.43 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?

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.

NO.44 Jonas, a Lead Performance Engineer,identifed that some of the operation of his query which func-tionally 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.

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.

NO.45 Which Role that is dedicated to user and role management only?

ORGADMIN

SECURITYADMIN

USERADMIN

SYSADMIN

PUBLIC

Prepaway Exam Dumps

Tag: DEA-C01 valid exam blueprint

Free DEA-C01 Exam Files Downloaded Instantly 100% Dumps & Practice Exam [Q21-Q45]

Tag Archives: DEA-C01 valid exam blueprint