Snowflake DEA-C01 Exam Practice Test Instant Access

Question 1

A Data Engineer is working on a continuous data pipeline which receives data from Amazon Kinesis Firehose and loads the data into a staging table which will later be used in the data transformation process The average file size is 300-500 MB.

The Engineer needs to ensure that Snowpipe is performant while minimizing costs.

How can this be achieved?

AIncrease the size of the virtual warehouse used by Snowpipe.

BSplit the files before loading them and set the SIZE_LIMIT option to 250 MB.

CChange the file compression size and increase the frequency of the Snowpipe loads

DDecrease the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose

Answer : B

This option is the best way to ensure that Snowpipe is performant while minimizing costs. By splitting the files before loading them, the Data Engineer can reduce the size of each file and increase the parallelism of loading. By setting the SIZE_LIMIT option to 250 MB, the Data Engineer can specify the maximum file size that can be loaded by Snowpipe, which can prevent performance degradation or errors due to large files. The other options are not optimal because:

Increasing the size of the virtual warehouse used by Snowpipe will increase the performance but also increase the costs, as larger warehouses consume more credits per hour.

Changing the file compression size and increasing the frequency of the Snowpipe loads will not have much impact on performance or costs, as Snowpipe already supports various compression formats and automatically loads files as soon as they are detected in the stage.

Decreasing the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose will not affect Snowpipe performance or costs, as Snowpipe does not depend on Kinesis Firehose buffer size but rather on its own SIZE_LIMIT option.

Question 2

A CSV file around 1 TB in size is generated daily on an on-premise server A corresponding table. Internal stage, and file format have already been created in Snowflake to facilitate the data loading process

How can the process of bringing the CSV file into Snowflake be automated using the LEAST amount of operational overhead?

ACreate a task in Snowflake that executes once a day and runs a copy into statement that references the internal stage The internal stage will read the files directly
from the on-premise server and copy the newest file into the table from the on-premise server to the Snowflake table

BOn the on-premise server schedule a SQL file to run using SnowSQL that executes a PUT to push a specific file to the internal stage Create a task that executes once a
day m Snowflake and runs a OOPY WTO statement that references the internal stage Schedule the task to start after the file lands in the internal stage

COn the on-premise server schedule a SQL file to run using SnowSQL that executes a PUT to push a specific file to the internal stage. Create a pipe that runs a copy
into statement that references the internal stage Snowpipe auto-ingest will automatically load the file from the internal stage when the new file lands in the internal
stage.

DOn the on premise server schedule a Python file that uses the Snowpark Python library. The Python script will read the CSV data into a DataFrame and generate an
insert into statement that will directly load into the table The script will bypass the need to move a file into an internal stage

Answer : C

This option is the best way to automate the process of bringing the CSV file into Snowflake with the least amount of operational overhead. SnowSQL is a command-line tool that can be used to execute SQL statements and scripts on Snowflake. By scheduling a SQL file that executes a PUT command, the CSV file can be pushed from the on-premise server to the internal stage in Snowflake. Then, by creating a pipe that runs a COPY INTO statement that references the internal stage, Snowpipe can automatically load the file from the internal stage into the table when it detects a new file in the stage. This way, there is no need to manually start or monitor a virtual warehouse or task.

Question 3

A Data Engineer is building a set of reporting tables to analyze consumer requests by region for each of the Data Exchange offerings annually, as well as click-through rates for each listing

Which views are needed MINIMALLY as data sources?

ASNOWFLAKE - DATA_SHARING _USAGE - LISTING_EVENTS_BAILY

BSNOWFLAKE.DATA_ SHARING _USAGE.LISTING_CONSOKE>TION_DAILY

CSNOWFLAKE. DATA_SHARING_USAGE. LISTING_TELEMETRY_DAILy

DSNOWFLAKE.ACCOUNT_USAGE.DATA _TRANSFER_HISTORY

Answer : B

The SNOWFLAKE.DATASHARING _USAGE.LISTING_CONSOKE>TION_DAILY view provides information about consumer requests by region for each of the Data Exchange offerings annually, as well as click-through rates for each listing. This view is the minimal data source needed for building the reporting tables. The other views are not relevant for this use case.

Question 4

Which query will show a list of the 20 most recent executions of a specified task kttask, that have been scheduled within the last hour that have ended or are still running's.

AOption A

BOption B

COption C

DOption D

Answer : B

Question 5

What is a characteristic of the operations of streams in Snowflake?

AWhenever a stream is queried, the offset is automatically advanced.

BWhen a stream is used to update a target table the offset is advanced to the current time.

CQuerying a stream returns all change records and table rows from the current offset to the current time.

DEach committed and uncommitted transaction on the source table automatically puts a change record in the stream.

Answer : C

A stream is a Snowflake object that records the history of changes made to a table. A stream has an offset, which is a point in time that marks the beginning of the change records to be returned by the stream. Querying a stream returns all change records and table rows from the current offset to the current time. The offset is not automatically advanced by querying the stream, but it can be manually advanced by using the ALTER STREAM command. When a stream is used to update a target table, the offset is advanced to the current time only if the ON UPDATE clause is specified in the stream definition. Each committed transaction on the source table automatically puts a change record in the stream, but uncommitted transactions do not.

Question 6

A Data Engineer defines the following masking policy:

....

must be applied to the full_name column in the customer table:

Which query will apply the masking policy on the full_name column?

AALTER TABLE customer MODIFY COLUMN full_name Set MASKING POLICY name_policy;

BALTER TABLE customer MODIFY COLUMN full_nam ADD MASKING POLICY name_poiicy;

CALTER TABLE customer MODIFY COLUMN first_nane SET MASKING POLICY name_policy; lasT_name SET MASKING POLICY name_pclicy;

DALTER TABLE customer MODIFY COLUMN first_name ADD MASKING POLICY name_policy,

Answer : A

The query that will apply the masking policy on the full_name column is ALTER TABLE customer MODIFY COLUMN full_name SET MASKING POLICY name_policy;. This query will modify the full_name column and associate it with the name_policy masking policy, which will mask the first and last names of the customers with asterisks. The other options are incorrect because they do not follow the correct syntax for applying a masking policy on a column. Option B is incorrect because it uses ADD instead of SET, which is not a valid keyword for modifying a column. Option C is incorrect because it tries to apply the masking policy on two columns, first_name and last_name, which are not part of the table structure. Option D is incorrect because it uses commas instead of dots to separate the database, schema, and table names

Question 7

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?

AESC&PE_UNENGLO9ED_FIELD = '\\'

BERROR_ON_COLUMN_COUKT_MISMATCH = FALSE

CFIELD_DELIMITER = ','

DFIELD OPTIONALLY ENCLOSED BY = ' '

Answer : D

The file format option that should be used to resolve the error and successfully load all the data into the table is FIELD_OPTIONALLY_ENCLOSED_BY = '''. This option specifies that fields in the file may be enclosed by double quotes, which allows for fields that contain commas or newlines within them. For example, in row 3 of the file, there is a field that contains a comma within double quotes: ''Smith Jr., John''. Without specifying this option, Snowflake will treat this field as two separate fields and cause an error due to column count mismatch. By specifying this option, Snowflake will treat this field as one field and load it correctly into the table.

Snowflake DEA-C01 SnowPro Advanced: Data Engineer Certification Exam Practice Test