Snowflake DEA-C02 Value Pack (Frequently Bought Together)

You have a table 'SALES DATA' in your production environment. You want to create a development environment using cloning, but only want to include data up to a specific point in time to minimize storage costs and potential exposure of recent, sensitive data'. You know there were significant changes to the 'SALES DATA' table structure on '2024-01-15'. Your goal is to create a clone that only includes the structure as of '2024-01-14'. Which Snowflake command is MOST appropriate for this scenario?

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

You have data residing in AWS S3 in Parquet format, which is updated daily with new columns being added occasionally. The data is rarely accessed, but when it is, it needs to be queried using SQL within Snowflake. You want to minimize storage costs within Snowflake while ensuring the data can be queried without requiring manual table schema updates every time a new column is added to the S3 data'. Which approach is MOST suitable?

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

You are using Snowpipe to continuously load JSON data from an Azure Blob Storage container into a Snowflake table. The data contains nested JSON structures. You observe that some records are not being loaded into the table, and the 'VALIDATION MODE shows 'PARSE ERROR' for these records. Examine the following COPY INTO statement and the relevant error message from 'VALIDATION MODE', and identify the most likely cause of the problem. COPY INTO my_table FROM FILE FORMAT = (TYPE = JSON STRIP OUTER ARRAY = TRUE) ON ERROR = CONTINUE; Error Message (from VALIDATION MODE): 'JSON document is not well formed: invalid character at position 12345'

• A. The JSON data contains invalid characters or formatting errors at position 12345, as indicated in the error message. Cleanse the source data to ensure it is well-formed JSON before loading.
• B. The file format definition is missing a 'NULL IF' parameter which is causing Snowflake to attempt to load string values that should be NULL.
• C. The 'STRIP OUTER ARRAY' parameter is causing the issue because the incoming JSON data is not wrapped in an array. Remove the 'STRIP OUTER ARRAY' parameter from the COPY INTO statement.
• D. Snowpipe is encountering rate limiting issues with Azure Blob Storage. Implement retry logic in your Snowpipe configuration.
• E. The Snowflake table schema does not match the structure of the JSON data. Verify that the column names and data types in the table are compatible with the JSON fields.

You are tasked with designing a data pipeline to load data from an Azure Blob Storage container into Snowflake using an external stage. The data is in CSV format, compressed using GZIP. The container contains millions of small CSV files. To optimize the data loading process and minimize cost, which of the following strategies would you implement, considering both stage configuration and COPY INTO options? Choose TWO that apply.

• A. Use the 'MATCH BY COLUMN NAME = CASE INSENSITIVE option with a copy transformation in the 'COPY INTO' statement to ensure that the column order in the CSV files doesn't affect the data load.
• B. Use the 'VALIDATION MODE = RETURN ERRORS option in the 'COPY INTO' statement to identify and correct any data quality issues during the load. This ensures that only clean data is loaded into Snowflake.
• C. Leverage Snowflake's Snowpipe with a REST API endpoint to trigger data loads whenever new files are available in the Azure Blob Storage container.
• D. Create a pipe object with 'AUTO INGEST = TRUE to automatically ingest new files as they are added to the Azure Blob Storage container. This ensures near real-time data ingestion.
• E. Consolidate the small CSV files in the Azure Blob Storage container into larger files before loading them into Snowflake. This reduces the overhead of processing numerous small files.

You are the provider of a data product on the Snowflake Marketplace. You need to grant a trial access to a potential consumer You want to provide limited access for 7 days to specific tables in your database. Which of the following steps are REQUIRED to accomplish this?
(Select all that apply)

• A. Grant OWNERSHIP on the specific tables to the consumer's account temporarily.
• B. Create a new role, grant USAGE privilege on the database and SELECT privilege on the specific tables to this role, and then grant this role to the trial consumer.
• C. Contact Snowflake support to enable trial access for the consumer's account.
• D. Create a new share specifically for the trial consumer, granting USAGE privilege on the database and SELECT privilege on the specific tables.
• E. Monitor the consumer's query history to ensure they are only accessing the allowed tables.