You are tasked with loading Parquet files into Snowflake from an AWS S3 bucket. The Parquet files are compressed using Snappy compression and contain a complex nested schem a. Some of the columns contain timestamps with nanosecond precision. You want to create a Snowflake table that preserves the timestamp precision. Which COPY INTO statement options and table definition are MOST appropriate?

• A. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE' VALIDATION_MODE = RETURN_ERRORS;
• B. Table Definition: CREATE TABLE my_table (ts VARCHAR, other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE' = PARSE TIMESTAMP(ts));
• C. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE';
• D. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ, other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ON_ERROR = 'SKIP_FILE';
• E. Table Definition: CREATE TABLE my_table (ts TIMESTAMP NTZ(9), other_col VARCHAR); COPY INTO my_table FROM FILE FORMAT = (TYPE - - pARQUET COMPRESSION = AUTO) ON_ERROR = 'SKIP_FILE';

A healthcare provider stores patient data in Snowflake, including 'PATIENT ID', 'NAME, 'MEDICAL HISTORY , and 'INSURANCE ID. They need to comply with HIPAA regulations. As a data engineer, you need to ensure that PHI (Protected Health Information) is masked appropriately based on user roles. Which of the following steps are NECESSARY to achieve this using Snowflake's data masking features and RBAC? (Select all that apply)

• A. Create custom roles representing different user groups within the organization (e.g., 'DOCTOR, 'NURSE, 'ADMIN') and grant them the necessary privileges to access the data, including 'SELECT on the tables and views containing patient data.
• B. Grant the 'OWNERSHIP privilege on the 'PATIENT table to the 'ACCOUNTADMIN' role, ensuring complete control and management of the data by the administrator.
• C. Apply the created masking policies to the corresponding columns in the patient data tables, ensuring that the masking policies are designed to reveal only the necessary information based on the user's role (e.g., doctors see full medical history, nurses see limited medical history, admins see de-identified data).
• D. Identify the columns containing PHI and create appropriate masking policies for each column (e.g., masking 'NAME, 'MEDICAL HISTORY, INSURANCE_ID).
• E. Enforce multi-factor authentication (MFA) for all users accessing the Snowflake environment to enhance security and prevent unauthorized access to sensitive data.

You have an external table named in Snowflake that points to a set of CSV files in an AWS S3 bucket. The CSV files have a header row, and the data is comma-separated. However, some of the files in the S3 bucket are gzipped. You need to define the external table to correctly read both compressed and uncompressed files. Which of the following SQL statements BEST achieves this?

• A. Option B
• B. Option E
• C. Option D
• D. Option C
• E. Option A

You are using the Snowflake Spark connector to update records in a Snowflake table based on data from a Spark DataFrame. The Snowflake table 'CUSTOMER' has columns 'CUSTOMER ID' (primary key), 'NAME, and 'ADDRESS'. You have a Spark DataFrame with updated 'NAME and 'ADDRESS' values for some customers. To optimize performance and minimize data transfer, which of the following strategies can you combine with a temporary staging table to perform an efficient update?

• A. Broadcast the Spark DataFrame to all executor nodes, then use a UDF to execute the 'UPDATE' statement for each row directly from Spark.
• B. Iterate through each row in the Spark DataFrame and execute an individual 'UPDATE statement against the 'CUSTOMER table in Snowflake. Use the 'CUSTOMER_ID in the 'WHERE clause.
• C. Write the Spark DataFrame to a temporary table in Snowflake. Then, execute an 'UPDATE statement in Snowflake joining the temporary table with the 'CUSTOMER table using the 'CUSTOMER_ID to update the 'NAME and 'ADDRESS' columns. Finally, drop the temporary table.
• D. Write the Spark DataFrame to a temporary table in Snowflake using MERGE. Use the WHEN MATCHED clause for Update the target table based on updates from staging table and finally drop the staging table
• E. Use Spark's foreachPartition to batch update statements and execute on each partition. This will help with efficient data transfer and avoid single row based updates.

You are troubleshooting a slow-running query that joins a large fact table 'SALES DATA' (100 billion rows) with a smaller dimension table 'CUSTOMER DIM' (1 million rows) on 'CUSTOMER ID. Initial analysis shows that the query is spending significant time in the join operation. You suspect the issue lies with the join strategy being used by Snowflake. Which of the following actions are MOST likely to improve query performance and optimize the join?

• A. Ensure that the 'CUSTOMER_ID column in both tables has compatible datatypes and that no implicit type conversions are happening during the join. Also check cardinality of 'CUSTOMER_ID in the SALES DATA table.
• B. Increase the virtual warehouse size and monitor for spillover to local disk. If spilling occurs, further increase the warehouse size.
• C. Analyze the query profile in Snowflake's web UI and identify if a broadcast join is occurring. If so, consider increasing session parameter (within limits) or re-designing the query to avoid the broadcast join.
• D. Convert the query to use a LATERAL FLATTEN function to pre-process the 'CUSTOMER_DIW table before the join.
• E. Ensure both 'SALES DATA' and 'CUSTOMER DIM' are clustered on 'CUSTOMER ID.