[Feb-2024] Free DEA-C01 Exam Dumps to Improve Exam Score [Q18-Q39]

[Feb-2024] Free DEA-C01 Exam Dumps to Improve Exam Score [Q18-Q39]

February 3, 2024 latestexam 0 Comments

5/5 - (1 vote)

[Feb-2024] Free DEA-C01 Exam Dumps to Improve Exam Score

2024 Realistic DEA-C01 Dumps Exam Tips Test Pdf Exam Material

NO.18 While creating External function, Which Database object required with at least ACCOUNTAD-MIN privileges?

STORAGE Integration

SECURITY Integration

API Integration

None of the above required.

NO.19 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)?

SYSTEM$TASK_DEPENDENTS

SYSTEM$TASK_DEPENDENTS_ENABLE

SYSTEM$TASK_DEPENDENTS_RESUME

SYSTEM$TASK_RECURSIVE_ENABLE

Explanation
To recursively resume all tasks in a DAG(A Directed Acyclic Graph (DAG) is a series of tasks com-posed of a single root task and additional tasks, organized by their dependencies.), query the SYS-TEM$TASK_DEPENDENTS_ENABLE function rather than resuming each task individually (us-ing ALTER TASK … RESUME).

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

Explanation
In a typical table, the data is stored in the database; however, in an external table, the data is stored in files in an external stage. External tables store file-level metadata about the data files, such as the filename, a version identifier and related properties. This enables querying data stored in files in an external stage as if it were inside a database. External tables can access data stored in any format supported by COPY INTO <table> statements.
External tables are read-only, therefore no DML operations can be performed on them; however, external tables can be used for query and join operations. Views can be created against external ta-bles.
Querying data stored external to the database is likely to be slower than querying native database tables; however, materialized views based on external tables can improve query performance.
Creating External tables enable user for querying existing data stored in external cloud storage for analysis without first loading it into Snowflake. The source of truth for the data remains in the ex-ternal cloud storage.
Data sets materialized in Snowflake via materialized views are read-only.
This solution is especially beneficial to accounts that have a large amount of data stored in external cloud storage and only want to query a portion of the data; for example, the most recent data. Users can create materialized views on subsets of this data for improved query performance.

NO.22 What are Invalid rules applicable when using stored procedure contains transaction?

A transaction can be inside a stored procedure, or a stored procedure can be inside a transaction.

You cannot start a transaction before calling the stored procedure, then complete the transaction inside the stored procedure.

You cannot start a transaction inside the stored procedure, then complete the transac-tion after returning from the procedure.

If a transaction is started inside a stored procedure and is still active when the stored procedure finishes, then an error occurs, and the transaction is rolled back.

A transaction inside a stored procedure can include a call to another stored procedure that contains a transaction.

All Rules are Applicable.

NO.23 Which two Account usage views can be used for auditing Dynamic data masking purpose?

MASKING POLICIES

POLICY_REFERENCES

DYNAMIC MASKING POLICIES

DYNAMIC POLICY_REFERENCES

NO.24 Search optimization works best to improve the performance of a query when the following condi-tions are true:[Select All that apply]

The table is not clustered.

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.

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.

NO.25 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 sys-tem$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 notifi-cation 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.

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.stalepipe3′,’staleness_check_override’); As an event notification received while a pipe is paused reaches the end of the limited retention pe-riod, Snowflake schedules it to be dropped from the internal metadata. If the pipe is later resumed, Snowpipe processes these older notifications on a best effort basis. Snowflake cannot guarantee that they are processed.
For example, if a pipe is resumed 15 days after it was paused, Snowpipe generally skips any event notifications that were received on the first day the pipe was paused (i.e. that are now more than 14 days old).
If the pipe is resumed 16 days after it was paused, Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused. And so on.

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

SYSTEM$STREAM_HAS_CHANGE_DATA

SYSTEM$STREAM_CDC_DATA

SYSTEM$STREAM_HAS_DATA

SYSTEM$STREAM_DELTA_DATA

Explanation
SYSTEM$STREAM_HAS_DATA
Indicates whether a specified stream contains change data capture (CDC) records.

NO.27 A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours.
—will Snowflake use the persisted query results from the result cache provided that the underlying data has not changed^

12 hours

24 hours

14 days

31 days

Explanation
Snowflake uses the result cache to store the results of queries that have been executed recently. The result cache is maintained at the account level and is shared across all sessions and users. The result cache is invalidated when any changes are made to the tables or views referenced by the query. Snowflake also has a retention policy for the result cache, which determines how long the results are kept in the cache before they are purged. The default retention period for the result cache is 24 hours, but it can be changed at the account, user, or session level. However, there is a maximum retention period of 14 days for the result cache, which cannot be exceeded. Therefore, if the underlying data has not changed, Snowflake will use the persisted query results from the result cache for up to 14 days.

NO.28 Michael, a Data Engineer Running a Data query to achieve Union of Data sets coming from Multi-ple data sources, later he figured out that Data processing query is taking more time than expected. He started analyzing the Query performance using query profile interface. He discovered & realized that he used UNION when the UNION ALL semantics was sufficient.
Which Extra Data Processing Operator Michael figured out while doing query profile analysis in this case which helps him to identify this performance bottlenecks?

Aggregate

UNION ALL

Flatten

Join

Filter

Explanation
In SQL, it is possible to combine two sets of data with either UNION or UNION ALL constructs. The difference between them is that UNION ALL simply concatenates inputs, while UNION does the same, but also performs duplicate elimination.
A common mistake is to use UNION when the UNION ALL semantics are sufficient. These que-ries show in Query Profile as a UnionAll operator with an extra Aggregate operator on top (which performs duplicate elimination).
To Know more about Data Processing Operators, please do refer:
https://docs.snowflake.com/en/user-guide/ui-query-profile#operator-types

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

Explanation
The IS_ROLE_IN_SESSION function is used to set up column-level masking using a role in the hierarchy of the current user. Column-level masking is a feature in Snowflake that allows users to apply dynamic data masking policies to specific columns based on the roles of the users who access them. The IS_ROLE_IN_SESSION function takes a role name as an argument and returns true if the role is in the current user’s session, or false otherwise. The function can be used in a masking policy expression to determine whether to mask or unmask a column value based on the role of the user. For example:
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string -> CASE WHEN IS_ROLE_IN_SESSION(‘HR’) THEN val ELSE REGEXP_REPLACE(val, ‘(.).(.@.)’, ‘\1****\2’) END; In this example, the IS_ROLE_IN_SESSION function is used to create a masking policy for an email column.
The masking policy returns the original email value if the user has the HR role in their session, or returns a masked email value with asterisks if not.

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

YES

Explanation
Higher the avg_overlap indicates poorly organized clustering.

NO.31 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.32 A Data Engineer has created table t1 with datatype VARIANT:
create or replace table t1 (cl variant);
The Engineer has loaded the following JSON data set. which has information about 4 laptop models into the table:

The Engineer now wants to query that data set so that results are shown as normal structured data. The result should be 4 rows and 4 columns without the double quotes surrounding the data elements in the JSON data.
The result should be similar to the use case where the data was selected from a normal relational table z2 where t2 has string data type columns model__id. model, manufacturer, and =iccisi_r.an=. and is queried with the SQL clause select * from t2; Which select command will produce the correct results?

NO.33 Data Engineer decided to call the public REST endpoints to load data and retrieve load history re-ports. 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]

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

Explanation
Snowflake provides REST endpoints and an Snowflake Information Schema table function for viewing your load history:
REST endpoints:
insertReport
loadHistoryScan
Information Schema table function:
COPY_HISTORY
Account Usage view:
COPY_HISTORY

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

An error: this function does not accept lists of columns as a third parameter.

An error: this function does not accept predicates (‘C9 = 30’) as parameter.

Calculate the clustering depth for a table using mentioned columns in the table.

Calculate the clustering depth for a table using the clustering key defined for the table.

NO.35 A Data Engineer executes a complex query and wants to make use of Snowflake s query results caching capabilities to reuse the results.
Which conditions must be met? (Select THREE).

The results must be reused within 72 hours.

The query must be executed using the same virtual warehouse.

The USED_CACHED_RESULT parameter must be included in the query.

The table structure contributing to the query result cannot have changed

The new query must have the same syntax as the previously executed query.

The micro-partitions cannot have changed due to changes to other data in the table

Explanation
Snowflake’s query results caching capabilities allow users to reuse the results of previously executed queries without re-executing them. For this to happen, the following conditions must be met:
The results must be reused within 24 hours (not 72 hours), which is the default time-to-live (TTL) for cached results.
The query must be executed using any virtual warehouse (not necessarily the same one), as long as it is in the same region and account as the original query.
The USED_CACHED_RESULT parameter does not need to be included in the query, as it is enabled by default at the account level. However, it can be disabled or overridden at the session or statement level.
The table structure contributing to the query result cannot have changed, such as adding or dropping columns, changing data types, or altering constraints.
The new query must have the same syntax as the previously executed query, including whitespace and case sensitivity.
The micro-partitions cannot have changed due to changes to other data in the table, such as inserting, updating, deleting, or merging rows.

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

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.

Explanation
The STATUS column indicates whether a particular set of files was loaded, partially loaded, or failed to load.
The FIRST_ERROR_MESSAGE column provides a reason when an attempt partial-ly loaded or failed.
Note that if a set of files has multiple issues, the FIRST_ERROR_MESSAGE column only indi-cates the first error encountered. To view all errors in the files, execute a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS. The VALIDATION_MODE copy option instructs a COPY statement to validate the data to be loaded and return results based on the validation option specified. No data is loaded when this copy option is specified.

NO.37 Which property can be used with ALTER USER command to temporarily disable MFA for the user so that they can log in?

HOURS_TO_BYPASS_MFA

SECS_TO_BYPASS_MFA

MINS_TO_SKIP_MFA

MINS_TO_BYPASS_MFA

Explanation
You can use the following properties for the ALTER USER command to perform these tasks:
MINS_TO_BYPASS_MFA
Specifies the number of minutes to temporarily disable MFA for the user so that they can log in. Af-ter the time passes, MFA is enforced and the user cannot log in without the temporary token gener-ated by the Duo Mobile application.

NO.38 Select the Incorrect statement about External Functions in SnowFlake?

An external function is a type of UDF.

An external function does not contain its own code; instead, the external function calls code that is stored and executed outside Snowflake.

Inside Snowflake, the external function is stored as a API Integration object.

Inside Snowflake, the external function is stored as a database object that contains in-formation that Snowflake uses to call the remote service.

NO.39 Changing the retention period for your account or individual objects changes the value for all lower-level objects that do not have a retention period explicitly set?

TRUE

FALSE

Loading …

Powerful DEA-C01 PDF Dumps for DEA-C01 Questions: https://www.latestcram.com/DEA-C01-exam-cram-questions.html

Related Certifications

C-THR91-1902 (1)
C-THR92-2211 (1)
C-THR86-2205 (1)
C_ARSOR_2302 (1)
C-HCADM-01 (1)
C-S4CSC-2202 (1)
C-TADM-22 (1)
C-S4CSV-2208 (1)
C-THR96-2205 (1)
C-S4CPR-2302 (1)

Free Cram & Latest Exams Dumps

[Feb-2024] Free DEA-C01 Exam Dumps to Improve Exam Score [Q18-Q39]

leave a comment Cancel reply

Related Certifications

Recent Posts

Archives

Categories