Pass the Snowflake SnowPro Core Certification COF-C02 Questions and answers with CertsForce

Snowflake supports semi-structured data types that can store complex data structures within a single column. TheVARIANTdata type can hold structured and semi-structured data formats such as JSON, Avro, ORC, Parquet, and XML. TheARRAYdata type is also used for semi-structured data and can store an ordered list of elements. These data types enable users to work with semi-structured data directly in Snowflake without needing to flatten the data into a relational schema first.

SnowSQL, Snowflake's command-line client, can be used to diagnose and troubleshoot network connections. SnowSQL provides various commands and options to test connectivity, configure network settings, and troubleshoot issues related to network connections between the client and Snowflake.

Snowflake Documentation: SnowSQL

Snowflake’s Time Travel feature allows users to access historical data within a specific period. This feature supports the restoration of various objects, including databases and schemas, to their previous states. Time Travel can be used for recovering dropped objects, undoing accidental changes, or analyzing data changes over time. However, it does not support user or role objects like Users and Roles, or compute resources like Virtual Warehouses.

To convert semi-structured data into rows and columns in Snowflake, theFLATTENfunction is utilized.

FLATTEN Function: This function takes semi-structured data (e.g., JSON) and transforms it into a relational table format by breaking down nested structures into individual rows. This process is essential for querying and analyzing semi-structured data using standard SQL operations.

Example Usage:

SELECT

f.value:attribute1 AS attribute1,

f.value:attribute2 AS attribute2

FROM

my_table,

LATERAL FLATTEN(input => my_table.semi_structured_column) f;

Snowflake Documentation on FLATTEN

In Snowflake, the system-defined, read-only view that displays information on column lineage, which specifies how data flows from source to target in a SQL write operation, isACCESS_HISTORY. This view is instrumental in auditing and analyzing data access patterns, as it provides detailed insights into how and from where the data is being accessed and manipulated within Snowflake.

Reference to Snowflake documentation on ACCESS_HISTORY:

Using Access History to Audit Data Access

Masking policies in Snowflake are created and applied at the column level. These policies are used to obfuscate sensitive data by masking the values in a specific column. Masking policies can enforce data privacy and protection measures by dynamically masking the data based on the role of the user querying the data.

Snowflake Documentation: Dynamic Data Masking

Snowflake supports structured data in standard tables and semi-structured data in VARIANT, OBJECT, and ARRAY columns.

Users can load and query both using standard SQL functions, without schema constraints for semi-structured formats.

To improve the performance of compute-intensive queries, increasing the warehouse size (e.g., from Medium to Large or X-Large) provides more compute resources (CPU/memory), allowing Snowflake to process queries faster. This is the most direct and effective method for performance optimization.

Users must self-enroll for MFA by logging into the web UI and scanning a QR code with an authenticator app. Administrators cannot do this on their behalf.

The COPY_HISTORY table function in Snowflake is used to track data loading activities, including both manual COPY INTO <title> commands and automated data loading through Snowpipe. This function provides visibility into load operations, making it valuable for monitoring data ingestion, ensuring data integrity, and troubleshooting loading processes.

In Snowflake, network policies define the allowed IP address ranges from which users can connect to Snowflake, enhancing security by restricting access based on network location. The creation and management of network policies require sufficient privileges. Specifically, a user with theSECURITYADMINrole or any role with higher privileges, such asACCOUNTADMIN, can create network policies. Additionally, a custom role can be granted theCREATE NETWORK POLICYprivilege, enabling users assigned to that role to also create network policies. This approach allows for flexible and secure management of network access to Snowflake.

The ACCESS_HISTORY view in Snowflake provides detailed information about queries executed in the account, including metadata such as the time of execution, the user, and the SQL text of the queries. This view is available in the Snowflake Enterprise edition and higher editions. The Standard edition does not include this feature.

Snowflake Documentation: Access History

Snowflake Editions: Snowflake Pricing