SnowPro Core Certification COF-C02 Exam Questions and Answers PDF

Snowflake provides mechanisms to automatically load data from files in external stages (like Amazon S3 or Azure Blob Storage).

The correct methods include:

Snowpipe (B):

Snowpipe is Snowflake’s continuous data ingestion service, which automatically detects and loads new files from external stages into Snowflake tables.

It provides near real-time data loading with low-latency ingestion.

Example:

CREATE PIPE my_pipe

AUTO_INGEST = TRUE

AS COPY INTO my_table FROM @my_stage FILE_FORMAT = (TYPE = 'CSV');

Task (C):

Tasks can be scheduled to run at specific intervals or triggered in response to events.

You can create a task to call a stored procedure, which, in turn, uses Snowpipe or COPY INTO to load data.

Example:

CREATE TASK my_task

SCHEDULE = '5 MINUTE'

AS

CALL load_data_procedure();

Why Other Options Are Incorrect:

A. Stream: Streams track changes in a table for change data capture (CDC) but do not directly load data from files.

D. Directory table: Snowflake does not have a "directory table" object for managing external file ingestion.

E. User-Defined Function (UDF): UDFs are used for custom logic within SQL queries, not for data loading.

References:

Snowpipe Overview

Tasks Overview

Snowflake's Secure Data Sharing enables sharing of data objects (e.g., tables and secure views) with other Snowflake accounts without copying or transferring the data.

Secure views are sharable because they enforce access control and prevent unauthorized access to underlying data.

Why Other Options Are Incorrect:

B. Materialized view: Materialized views cannot be shared via Secure Data Sharing.

C. External function: External functions are not supported for Secure Data Sharing.

D. User-Defined Function (UDF): UDFs cannot be shared.

References:

Secure Data Sharing

When creating a file format to load JSON data into Snowflake, the STRIP_OUTER_ARRAY option is used to remove outer brackets ([ ]) from a JSON array.

Key Use Case: This is particularly helpful when the JSON data contains a top-level array, and you want to treat its elements as separate records for loading into Snowflake.

Example:

sql

CopyEdit

CREATE FILE FORMAT my_json_format

TYPE = JSON

STRIP_OUTER_ARRAY = TRUE;

Why Other Options Are Incorrect:

A. STRIP_NULL_VALUES: Removes NULL values, not brackets.

B. REPLACE_INVALID_CHARACTERS: Replaces invalid characters but does not remove brackets.

C. TRIM_SPACE: Removes leading and trailing spaces, unrelated to JSON brackets.

References:

File Formats for JSON Data

In Snowflake, Secure User-Defined Functions (UDFs) can be shared with other accounts using Snowflake's data sharing feature. This allows different Snowflake accounts to securely execute the UDFs without having direct access to the underlying data the functions operate on, ensuring privacy and security. The sharing is facilitated through shares created in Snowflake, which can contain Secure UDFs along with other database objects like tables and views.References: Snowflake Documentation on Data Sharing and Secure UDFs