ExamGecko
Login
Home / Snowflake / DEA-C01 / List of questions
Ask Question

Snowflake DEA-C01 Practice Test - Questions Answers, Page 8

Add to Whishlist

List of questions

Question 71

Report Export Collapse

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)
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.
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.
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.
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.
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.
Suggested answer: A
Explanation:

Split larger files into a greater number of smaller files to distribute the load among the compute resources in an active warehouse. This would minimize the processing overhead rather than maximize it.

Rest is recommended Data loading considerations.

asked 23/09/2024
I Haq
40 questions

Question 72

Report Export Collapse

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?
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?
I
I
II
II
III
III
IV
IV
Suggested answer: B
Explanation:

Of the above options for identifying/specifying data files to load from a stage, providing a discrete list of files is generally the fastest; however, the FILES parameter supports a maximum of 1,000 files, meaning a COPY command executed with the FILES parameter can only load up to 1,000 files.

For example:

copy into load1 from @%load1/Snow1/ files=('mydata1.csv', 'mydata2.csv', 'mydata3.csv')

asked 23/09/2024
Eric Hebert
41 questions

Question 73

Report Export Collapse

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?

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.
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.
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.
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.
To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true.
Suggested answer: D
Explanation:

To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true. The copy option references load metadata, if available, to avoid data duplication, but also at-tempts to load files with expired load metadata.

Alternatively, set the FORCE option to load all files, ignoring load metadata if it exists. Note that this option reloads files, potentially duplicating data in a table.

Please refer the Example as mentioned in the link below:

https://docs.snowflake.com/en/user-guide/data-load-considerations-load.html#loading-older-files

asked 23/09/2024
Natalia Novikova
48 questions

Question 74

Report Export Collapse

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.
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.
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)
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
COPY command trims the leading space and removes the quotation marks enclosing each field
copy into SFtable
copy into SFtable
from @%SFtable
from @%SFtable
file_format = (type = csv trim_space=true field_optionally_enclosed_by = '0x22');
file_format = (type = csv trim_space=true field_optionally_enclosed_by = '0x22');
Suggested answer: D
Explanation:

If your external software exports fields enclosed in quotes but inserts a leading space before the opening quotation character for each field, Snowflake reads the leading space rather than the opening quotation character as the beginning of the field. The quotation characters are interpreted as string data.

Use the TRIM_SPACE file format option to remove undesirable spaces during the data load.

asked 23/09/2024
Web Administrator
45 questions

Question 75

Report Export Collapse

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://c:\datadir\snowdata.tsv @tablestage;
put file://c:\datadir\snowdata.tsv @tablestage;
put file:///datadir/snowdata.tsv @%tablestage;
put file:///datadir/snowdata.tsv @%tablestage;
Suggested answer: B
Explanation:

Execute PUT to upload (stage) local data files into an internal stage.

@% character combination identifies a table stage.

asked 23/09/2024
Asif Ibrahim
52 questions

Question 76

Report Export Collapse

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 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 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 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.
VALIDATION_MODE only support Data loading operation i.e., do not work while da-ta unloading.
Suggested answer: A, B, C
Explanation:

All the Statements are correct except the statement saying VALIDATION_MODE only support Data loading operation.

VALIDATION_MODE can be used with COPY INTO <location> command as well i.e for data unloading operation.

VALIDATION_MODE = RETURN_ROWS can be used at the time of Data unloading.

This option instructs the COPY command to return the results of the query in the SQL statement instead of unloading the results to the specified cloud storage location. The only supported validation option is RETURN_ROWS. This option returns all rows produced by the query.

When you have validated the query, you can remove the VALIDATION_MODE to perform the unload operation.

asked 23/09/2024
Avinash Jindal
40 questions

Question 77

Report Export Collapse

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
RESULT_SCAN
LAST_QUERY_ID
LAST_QUERY_ID
Rejected_record
Rejected_record
LOAD_ERROR
LOAD_ERROR
Suggested answer: A, B
Explanation:

LAST_QUERY_ID() Function

Returns the ID of a specified query in the current session. If no query is specified, the most recently executed query is returned.

RESULT_SCAN() Function

Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table.

The following example validates a set of files (SFfile.csv.gz) that contain errors. To facilitate analy-sis of the errors, a COPY INTO <location> statement then unloads the problematic records into a text file so they could be analyzed and fixed in the original data files. The statement queries the RESULT_SCAN table.

1. #copy into Snowtable

2. from @SFstage/SFfile.csv.gz

3. validation_mode=return_all_errors;

4. #set qid=last_query_id();

5. #copy into @SFstage/errors/load_errors.txt from (select rejected_record from table( result_scan($qid))); Note: Other options are not valid functions.

asked 23/09/2024
SAI CHARAN TANGELLA
40 questions

Question 78

Report Export Collapse

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?

Become a Premium Member for full access
  Unlock Premium Member

Question 79

Report Export Collapse

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?

Become a Premium Member for full access
  Unlock Premium Member

Question 80

Report Export Collapse

Find out the odd one out:

Become a Premium Member for full access
  Unlock Premium Member
Total 130 questions
Go to page: of 13
Search
Open VPLUS File
Convert VPLUS to PDF

Related questions

Snowflake web interface can be used to create users with no passwords or remove passwords from existing users?
Select the correct usage statements with regards to SQL UDF?
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).
You as Data engineer might want to consider disabling auto-suspend for a warehouse if?
The COPY command supports several options for loading data files from a stage i.e.
Clones can be cloned, with no limitations on the number or iterations of clones that can be created (e.g. you can create a clone of a clone of a clone, and so on), which results in a n-level hierarchy of cloned objects, each with their own portion of shared and independent data storage?
Streams record the differences between two offsets. If a row is added and then updated in the current offset, what will be the value of METADATA^^SUPDATE Columns in this scenario?
Select the Incorrect statement about External Functions in SnowFlake?
For enabling non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks, which two glob-al/account privileges snowflake provide?
Which are false statements about Star Schema?