Snowflake DEA-C02 real dump : SnowPro Advanced: Data Engineer (DEA-C02)

You have created an external table in Snowflake that points to a large dataset stored in Azure Blob Storage. The data consists of JSON files, and you've noticed that query performance is slow. Analyzing the query profile, you see that Snowflake is scanning a large number of unnecessary files. Which of the following strategies could you implement to significantly improve query performance against this external table?

• A. Create a materialized view on top of the external table to pre-aggregate the data.
• B. Partition the data in Azure Blob Storage based on a relevant column (e.g., date) and define partitioning metadata in the external table definition using PARTITION BY.
• C. Convert the JSON files to Parquet format and recreate the external table to point to the Parquet files.
• D. Create an internal stage, copy all JSON Files, create and load the target table, and drop external table
• E. Increase the size of the Snowflake virtual warehouse to provide more processing power.

You are designing a data sharing solution in Snowflake where a provider account shares a view with a consumer account. The view is based on a table that undergoes frequent DML operations (inserts, updates, deletes). The consumer account needs to see a consistent snapshot of the data, even during these DML operations. Which of the following strategies, or combination of strategies, would be MOST effective in ensuring data consistency from the consumer's perspective, and what considerations should be made?

• A. Creating a standard view in the provider account and relying on Snowflake's inherent transactional consistency. The consumer account will always see a consistent snapshot of the data as it existed at the beginning of their query execution. No additional configurations are necessary.
• B. Creating a stream on the base table in the provider account and building a view on top of the stream. This way, changes are only reflected when the stream is consumed, allowing for batch processing and controlled updates in the consumer account.
• C. Creating a materialized view in the provider account and sharing that materialized view. This adds compute costs to the provider but ensures a consistent snapshot for the consumer account. The materialized view needs to be refreshed periodically, based on the rate of DML changes.
• D. A and B
• E. Using Snowflake's Time Travel feature by querying the view with a specific 'AT' or 'BEFORE' clause in the consumer account. The provider account needs to inform the consumer account of a specific timestamp that guarantees consistency, adding administrative overhead.

A financial services company is using Snowflake Streams on a table 'TRANSACTIONS' to capture changes for auditing purposes. The 'TRANSACTIONS' table contains sensitive data, and the auditing team requires the stream to only capture changes to specific columns: 'ACCOUNT ID', 'TRANSACTION DATE', and 'TRANSACTION AMOUNT'. Which of the following approaches is the MOST efficient and secure way to achieve this requirement, ensuring minimal performance impact and data exposure?

• A. Create a task that clones the TRANSACTIONS table and a stream on that cloned table, limiting what changes are captured using a WHERE clause on the cloning command.
• B. Create a standard Stream on the 'TRANSACTIONS table and then filter the results in downstream processing to only include the required columns.
• C. Create a Stream on the 'TRANSACTIONS' table and use a masking policy on the stream's output to redact the unnecessary columns.
• D. Create a View that selects only the 'ACCOUNT ID, 'TRANSACTION DATE, and 'TRANSACTION AMOUNT columns and create a Stream on the View.
• E. Create a Stream on the 'TRANSACTIONS' table. Periodically truncate stream and reload all data from TRANSACTION table by applying filter while loading.

You have configured a replication group to replicate a database 'CUSTOMER DATA' from your primary Snowflake account (AWS us- east-I) to your secondary Snowflake account (Azure eastus). After a recent network outage, the replication process stopped. Upon investigation, you find that some tables in the 'CUSTOMER DATA' database in the primary account have been modified (schema changes). You need to resume replication and ensure data consistency in the secondary account, with minimal impact on users querying the secondary database. Which of the following commands or sequence of commands would be the MOST appropriate to refresh the secondary database while minimizing downtime for query users?

• A. Issue a 'ALTER DATABASE REFRESH FROM command. This command automatically handles schema changes and ensures data consistency with minimal downtime.
• B. First, suspend the replication group with 'ALTER REPLICATION GROUP SUSPEND;'. Then, drop the secondary database 'DROP DATABASE . Finally, recreate the secondary database as a replica: 'CREATE DATABASE AS REPLICA OF and resume the replication group 'ALTER REPLICATION GROUP RESUME;'.
• C. Issue a 'ALTER REPLICATION GROUP REFRESH$ command. This will automatically handle schema changes and resume replication.
• D. Suspend the replication group with 'ALTER REPLICATION GROUP SUSPEND;' Then execute the following: 'ALTER DATABASE ENABLE REPLICATION TO ACCOUNT ALTER REPLICATION GROUP RESUME;'
• E. First, pause all running queries on the secondary account. Then, execute a full refresh using 'ALTER DATABASE REFRESH FROM Finally, resume the queries on the secondary account.

You are tasked with building a data pipeline using Snowpark to process sensor data from IoT devices. The data arrives in near real-time as JSON payloads, and you need to transform and load it into a Snowflake table named 'SENSOR DATA'. The transformation logic involves extracting specific fields, converting data types, and filtering out records based on a timestamp. Consider performance optimization for large data volumes. Which of the following approaches, in combination, would be MOST efficient for this scenario?

• A. Using a stored procedure written in Java to parse the JSON data and insert directly into the "SENSOR DATA' table.
• B. Creating an external table pointing to the JSON data in cloud storage and using Snowpark DataFrames to read the external table, apply transformations, and load the result into 'SENSOR DATA'.
• C. Leveraging Snowflake's native JSON parsing functions within a SQL transformation step implemented as a Snowpark DataFrame operation, combined with a Snowpipe for initial data ingestion into a staging table.
• D. Employing Snowpipe to ingest the raw JSON data into a VARIANT column in a staging table, followed by a Snowpark DataFrame operation using 'functions.get' to extract and transform the data, and finally loading into 'SENSOR DATA'
• E. Using a Snowpark Python UDF to parse JSON and perform transformations, loading the result into a temporary table, and then merging into 'SENSOR DATA'.