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?

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

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.

Select the Incorrect statement about External Functions in SnowFlake?

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

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.

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.

Elon, a Data Engineer, needs to Split Semi-structured Elements from the Source files and load them as an array into Separate Columns.Source File:

| {"mac_address": {"host1": "197.128.1.1","host2": "197.168.0.1"}}, |

| {"mac_address": {"host1": "197.168.2.1","host2": "197.168.3.1"}} |

+----------------------------------------------------------------------+ Output: Splitting the Machine Address as below.

Which are the two ways to access elements in a JSON object?

Use dot notation to traverse a path in a JSON object: <column>:< level1_element>.<level2_element>.<level3_element>.

use bracket notation to traverse the path in an object: <column>['< level1_element>']['<level2_element>'].

Use SemiColon notation to traverse a path in a JSON object: <column>:< level1_element>;<level2_element>;<level3_element>.

use Curly bracket notation to traverse the path in an object: <column>{'< level1_element>'}{'<level2_element>'}.

Use dot notation to traverse a path in a JSON object: <column>:< level1_element>.<level2_element>.<level3_element>.

use bracket notation to traverse the path in an object: <column>['< level1_element>']['<level2_element>'].

Jeff, a Data Engineer, accessing elements in JSON object in its 3 data loading scripts, he unknow-ingly use the upper case while accessing the elements. e.g.Script 1 --> fruits:apple.sweet Script 2 --> FRUITS:apple.sweet Script 3 --> FRUITS:Apple.Sweet Which are the correct statements?

Script 1 & Script 2 traverse path twill be treated same, but Script 2 will not.

Script 1,2,3 traverse path will treat in same way.

Script 1 & 3 traverse path will be treated in same way.

Script 2&3 traverse path will be same.

Which are false statements about Star Schema?

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

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.

Which of the following System keeps the following characteristics?

Flat architecture d. Highly agile

It will keep in it all the raw data.

Generally, the users of it is data scientists and data developers.

Flat architecture d. Highly agile

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.

In one of your created Schema, you have been required to create Internal Stages, what are the Incorrect considerations you can noticed from the below options? [Select All that Apply]

User stages can be altered or dropped just like Table Stage.

Table stage type is designed to store files that are staged and managed by one or more users but only loaded into a single table.

A named internal stage type can store files that are staged and managed by one or more users and loaded into one or more tables.

A table stage is available for each table created in Snowflake.

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?

External tables with Materialized views can be created in Snowflake.

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.

Mark the incorrect statement in case Data engineer using the COPY INTO <table> command to load data from files into Snowflake tables?

For Data loading of files with semi-structured file formats (JSON, Avro, etc.), the only supported character set is UTF-16.

For loading data from all semi-structured supported file formats (JSON, Avro, etc.), as well as unloading data, UTF-8 is the only supported character set.

For Local environment, Files are first copied ("staged") to an internal (Snowflake) stage, then loaded into a table.

UTF-32 & UTF-16 both encoding character sets supported for loading data from de-limited files (CSV, TSV, etc.)

Let us say you have List of 50 Source files, which needs to be loaded into Snowflake internal stage. All these Source system files are already Brotli-compressed files. Which statement is correct with respect to Compression of Staged Files?

Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotlicompressed files, you must explicitly specify the compression method that was used.

Even though Source files are already compressed, Snowflake do apply default gzip2 Compression to optimize the storage cost.

Snowflake automatically detect Brotli Compression, will skip further compression of all 50 files.

Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotlicompressed files, you must explicitly specify the compression method that was used.

When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.

For the most efficient and cost-effective Data load experience, Data Engineer needs to inconsider-ate which of the following considerations?

Split larger files into a greater number of smaller files, maximize the processing over-head for each file.(Correct)

Enabling the STRIP_OUTER_ARRAY file format option for the COPY INTO <ta-ble> command to remove the outer array structure and load the records into separate table rows.

Amazon Kinesis Firehose can be convenient way to aggregate and batch data files which also allows defining both the desired file size, called the buffer size, and the wait interval after which a new file is sent, called the buffer interval.

When preparing your delimited text (CSV) files for loading, the number of columns in each row should be consistent.

if the "null" values in your files indicate missing values and have no other special mean-ing, Snowflake recommend setting the file format option STRIP_NULL_VALUES to TRUE when loading the semi-structured data file.

The COPY command supports several options for loading data files from a stage i.e.

By pathII. Specifying a list of specific files to load.III. Using pattern matching to identify specific files by pattern.IV. Organize files into logical paths that reflect a scheduling pattern.Of the aforesaid options for identifying/specifying data files to load from a stage, which option in general is the fastest & best considerate?

As Data Engineer, you have requirement to Load set of New Product Files containing Product relevant 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?

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

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 theLAST_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.

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]

COPY command trims the leading space and removes the quotation marks enclosing each field

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

file_format = (type = csv trim_space=true field_optionally_enclosed_by = '0x22');

Data Engineer Loading File named snowdata.tsv in the /datadir directory from his local machine to Snowflake stage and try to prefix the file with a folder named tablestage, please mark the correct command which helps him to load the files data into snowflake internal Table stage?

put file://c:\datadir\snowdata.tsv @%tablestage;

put file://c:\datadir\snowdata.tsv @~/tablestage;

put file://c:\datadir\snowdata.tsv @%tablestage;

put file://c:\datadir\snowdata.tsv @tablestage;

put file:///datadir/snowdata.tsv @%tablestage;

Mark the Correct Statements for the VALIDATION_MODE option used by Data Engineer for Da-ta loading operations in his/her COPY INTO <table> command:

VALIDATION_MODE instructs the COPY command to validate the data files instead of loading them into the specified table; i.e., the COPY command tests the files for er-rors but does not load them.

VALIDATION_MODE option supported these values:RETURN_n_ROWS,RETURN_ERRORS,RETURN_ALL_ERRORS

VALIDATION_MODE does not support COPY statements that transform data during a load. If the parameter is specified, the COPY statement returns an error.

VALIDATION_MODE instructs the COPY command to validate the data files instead of loading them into the specified table; i.e., the COPY command tests the files for er-rors but does not load them.

VALIDATION_MODE option supported these values:RETURN_n_ROWS,RETURN_ERRORS,RETURN_ALL_ERRORS

VALIDATION_MODE does not support COPY statements that transform data during a load. If the parameter is specified, the COPY statement returns an error.

VALIDATION_MODE only support Data loading operation i.e., do not work while da-ta unloading.

As part of Table Designing, Data Engineer added a timestamp column that inserts the current timestamp as the default value as records are loaded into a table. The intent is to capture the time when each record was loaded into the table; however, the timestamps are earlier than the LOAD_TIME column values returned by COPY_HISTORY view (Account Usage). What could be reason of this issue?

The reason is, CURRENT_TIMESTAMP is evaluated when the load operation is com-piled in cloud services rather than when the record is inserted into the table (i.e. when the transaction for the load operation is committed).

LOAD_TIME column values returned by COPY_HISTORY view (Account Usage) gives the same time as returned by CURRENT_TIMESTAMP.

CURRENT_TIMESTAMP values might be different due to query gets executed in warehouse located in different region.

It might be possible that Cloud Provider hosted on Snowflake belongs to region having server time zone lagging Cluster time zone of warehouse where queries get processed & committed.

The reason is, CURRENT_TIMESTAMP is evaluated when the load operation is com-piled in cloud services rather than when the record is inserted into the table (i.e. when the transaction for the load operation is committed).

Snowpipe loads data from files as soon as they are available in a stage. Automated data loads leverage 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 Snowpipe?

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

Find out the odd one out:

Bulk Data Load: Loads are always performed in a single transaction.

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.

Bulk Data Load: Requires a user-specified warehouse to execute COPY statements.

SnowPipe: Uses Snowflake-supplied compute resources.

Bulk Data Load: Billed for the amount of time each virtual warehouse is active.

SnowPipe: Billed according to the compute resources used in the Snowpipe ware-house while loading the files.

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

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

Comment (0)

Snowflake DEA-C01 Practice Test 2

Question

Snowflake DEA-C01 Practice Tests

Practice Tests