Snowflake DEA-C02 Value Pack (Frequently Bought Together)

You are tasked with implementing a data loading process for a table 'CUSTOMER DATA' in Snowflake. The source data is in Parquet format on Azure Blob Storage and contains personally identifiable information (PII). You must ensure that the data is loaded securely, masked during the loading process, and that only authorized users can access the unmasked data after the load. Assume you have already created a stage pointing to the Azure Blob Storage. Which of the following steps should you take to achieve this?

• A. Load the data directly into a 'VARIANT column. Use a SQL transformation with 'FLATTEN' and masking policies on the extracted columns.
• B. Use a 'COPY command with the 'ENCRYPTION = (TYPE = 'AZURE CSE', KEY = option to encrypt the data during load. Implement role-based access control to restrict access to the table.
• C. Load the data without masking. Implement dynamic data masking policies on the table's PII columns using Snowflake's Enterprise edition features. Use a 'COPY' command with ERROR = CONTINUE
• D. Use a 'COPY command with 'ON ERROR = SKIP FILE'. Use a Task to monitor load failures and trigger alerts.
• E. Use a 'COPY command with the 'TRANSFORM' clause and JavaScript UDFs to mask the PII data during the load process. Implement masking policies on the 'CUSTOMER DATA' table to restrict access to the unmasked data.

You have a Snowflake table 'CUSTOMER DATA with a column 'EMAIL' containing customer email addresses. You need to classify this column as 'PII' using a tag named 'SENSITIVITY' and value 'CONFIDENTIAL'. Also, you want all queries accessing this 'EMAIL' column to be logged, with specific details about who accessed it and when. You already have appropriate roles and privileges to perform the required operations. Which of the following SQL statements, when executed in sequence, will achieve this goal, assuming appropriate logging mechanisms are already configured to read from the Snowflake ACCESS HISTORY view?

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

A data engineering team is building a real-time fraud detection system. They have a large 'TRANSACTIONS table that grows rapidly. They need to calculate the average transaction amount per merchant daily. The following query is used:

This query is run every hour and is performance-critical. Which of the following materialized view definitions would provide the BEST performance improvement, considering the need for near real-time data and minimal latency?

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

A data engineer is tasked with creating an external table that points to a directory in AWS S3 containing CSV files. The files have a header row and are comma-delimited. The engineer executes the following DDL statement:

• A. The statement will fail because the FIELD_DELIMITER property should be 'DELIMITER.
• B. The statement will fail because the 'SKIP HEADER property is invalid and the 'skip_header' parameter should be used in the 'CREATE FILE FORMAT statement.
• C. The statement will fail because the 'LOCATION' is missing the protocol (e.g., 's3:/P).
• D. The statement will fail because external tables cannot be created directly from CSV files without defining a stage.
• E. The statement will succeed if a file format named 'csv_format' with the specified properties already exists.

A data engineer is responsible for a Snowflake data pipeline that ingests data from multiple external sources, transforms it, and loads it into a data warehouse. The engineer needs to implement a notification system to alert them when specific data quality issues occur, such as data duplication exceeding a threshold or a sudden drop in data volume. Which approach offers the MOST flexible and scalable solution for implementing these notifications?

• A. Develop custom SQL scripts to periodically query the data for quality issues and send email notifications using Snowflake's stored procedures and the 'EMAIL' external function.
• B. Create a series of Snowflake Tasks that execute SQL queries to check for data quality issues. If an issue is detected, the task triggers an external function to send a notification to a messaging service (e.g., AWS SNS, Azure Event Grid).
• C. Implement a data quality monitoring tool that integrates with Snowflake via JDBC/ODBC and uses its own rules engine and notification system to detect and alert on data quality issues.
• D. Rely solely on Snowflake's Data Sharing feature to share the data with a data quality team who will manually review the data and report any issues.
• E. Use Snowflake's built-in resource monitors to track data volume and configure alerts based on predefined thresholds. This approach is simple but limited in its ability to detect complex data quality issues.