[Mar 29, 2024] DEA-C01 Exam Dumps PDF Updated Dump from ExamsTorrent Guaranteed Success [Q20-Q35]

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

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.

NO.21 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.22 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.23 A large table with 200 columns contains two years of historical data. When queried. the table is filtered on a single day Below is the Query Profile:

Using a size 2XL virtual warehouse, this query look over an hour to complete What will improve the query performance the MOST?

increase the size of the virtual warehouse.

Increase the number of clusters in the virtual warehouse

Implement the search optimization service on the table

Add a date column as a cluster key on the table

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

TRUE

FALSE

UPDATE

INSERT

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

NO.26 If external software i.e. TIBCO, exports Data fields enclosed in quotes but inserts a leading space before the opening quotation character for each field, How Snowflake handle it? [Select 2]

Snowflake automatically handles leading spaces by trimming implicitly & removes the quotation marks enclosing each field.

field_optionally_enclosed_by option along with TRIM_IF function in COPY INTO statement can be used to handle this scenario successfully.

Snowflake reads the leading space rather than the opening quotation character as the beginning of the field and the quotation characters are interpreted as string data.
(Correct)

COPY command trims the leading space and removes the quotation marks enclosing each field
1.copy into SFtable
2.from @%SFtable
3.file_format = (type = csv trim_space=true field_optionally_enclosed_by = ‘0x22’);

NO.27 A Data Engineer needs to ingest invoice data in PDF format into Snowflake so that the data can be queried and used in a forecasting solution.
….. recommended way to ingest this data?

Use Snowpipe to ingest the files that land in an external stage into a Snowflake table

Use a COPY INTO command to ingest the PDF files in an external stage into a Snowflake table with a VARIANT column.

Create an external table on the PDF files that are stored in a stage and parse the data nto structured data

Create a Java User-Defined Function (UDF) that leverages Java-based PDF parser libraries to parse PDF data into structured data

NO.28 What kind of Snowflake integration is required when defining an external function in Snowflake?

API integration

HTTP integration

Notification integration

Security integration

NO.29 Streams cannot be created to query change data on which of the following objects? [Select All that Apply]

Standard tables, including shared tables.

Views, including secure views

Directory tables

Query Log Tables

External tables

NO.30 A Data Engineer needs to load JSON output from some software into Snowflake using Snowpipe.
Which recommendations apply to this scenario? (Select THREE)

Load large files (1 GB or larger)

Ensure that data files are 100-250 MB (or larger) in size compressed

Load a single huge array containing multiple records into a single table row

Verify each value of each unique element stores a single native data type (string or number)

Extract semi-structured data elements containing null values into relational columns before loading

Create data files that are less than 100 MB and stage them in cloud storage at a sequence greater than once each minute

NO.31 Find out the odd one out:

1. Bulk Data Load: Loads are always performed in a single transaction.
2. SnowPipe: Loads are combined or split into a single or multiple transactions based on the number and size of the rows in each data file.

1. Bulk Data Load: Requires a user-specified warehouse to execute COPY statements.
2. SnowPipe: Uses Snowflake-supplied compute resources.

1. Bulk Data Load: Billed for the amount of time each virtual warehouse is active.
2. SnowPipe: Billed according to the compute resources used in the Snowpipe ware-house while loading the files.

1. Bulk Data Load: Load history Stored in the metadata of the target table for 365 days.
2. SnowPipe: Load history Stored in the metadata of the pipe for 64 days.

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

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

NO.33 As Data Engineer, you have been asked to access data held in AWS Glacier Deep Archive storage class for Historical Data Analysis, which one is the correct statement to recommend?

You cannot access data held in archival cloud storage classes that requires restoration before it can be retrieved.

Loading data from AWS cloud storage services is supported regardless of the cloud platform that hosts your Snowflake account.

Data can be accessed from External stage using AWS Private link in this case.

We can simply access AWS Glacier Deep Archive storage External Stage data using PUT command.

Upload (i.e. stage) files to your cloud storage account using the tools provided by the cloud storage service.

NO.34 Which column provides information when the stream became stale or may become stale if not con-sumed?

STREAM_STALE_PERIOD

STALE_PERIOD_AFTER

STALE_STREAM_PERIOD

STALE_AFTER

NO.35 You can execute zero, one, or more transactions inside a stored procedure?

TRUE