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

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')

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

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.

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

@% character combination identifies a table stage.

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.

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.

The reason timestamps are earlier than the LOAD_TIME column values which is returned by COPY_HISTORY view (Account Usage) is that CURRENT_TIMESTAMP is evaluated when the load operation is compiled in cloud services rather than when the record is inserted into the table (i.e.

when the transaction for the load operation is committed).

Cross-cloud support only available to accounts hosted on Amazon Web Services currently.

Bulk data load

Load History Stored in the metadata of the target table for 64 days. Available upon completion of the COPY statement as the statement output.

Snowpipe

Load History Stored in the metadata of the pipe for 14 days. Must be requested from Snowflake via a REST endpoint, SQL table function, or ACCOUNT_USAGE view.

Rest are correct statements.