Snowflake Online Assessment Test

Consider a database in Snowflake containing a table transactions with the following schema: trans_id (VARCHAR), trans_date (DATE), customer_id (INT), amount (DECIMAL(10,2)), category (VARCHAR). The table has millions of records distributed across various categories and dates. You need to write a SQL query that finds the average transaction amount per category for transactions that occurred in the last 30 days from today but excludes any categories where the total number of transactions in this time period is less than 10.

Assuming today's date in Snowflake is accessed using CURRENT_DATE(), how would you write this query?

In a Snowflake environment, a data engineer is tasked with optimizing a large query that aggregates transaction data across multiple years for trend analysis. The original query is experiencing timeouts and excessive computational load. The table transactions has the following schema: id (INT), user_id (INT), amount (FLOAT), transaction_date (DATE). The engineer decides to utilize Snowflake's clustering feature on transaction_date to improve query performance. Consider the following pseudo code for the optimized query:

Which modification will most effectively optimize the query performance in Snowflake considering its architecture?

A: Replace CLUSTER BY with PARTITION BY in the SELECT statement.
B: Utilize CLUSTER BY in the CREATE TABLE statement instead of in the SELECT statement.
C: Increase the WAREHOUSE_SIZE to a larger instance before running the query.
D: Use ORDER BY at the end of the query to organize the results by transaction_date.
E: Change SUM(amount) to COUNT(amount) to reduce computational overhead.
F: Add an index on transaction_date to speed up query execution.

A data engineering team is tasked with integrating multiple data sources into a Snowflake data warehouse. The primary sources include transactional data from an e-commerce platform, customer data from a CRM system, and product information from an inventory management system. The goal is to optimize query performance for reports that aggregate transactional sales by product category, customer demographics, and sales region. Considering the need for efficient joins and aggregated queries, the team proposes a design using a combination of normalized and denormalized schemas.

The transactional data includes fields such as Transaction_ID, Customer_ID, Product_ID, Quantity, and Sale_Amount. The customer data holds Customer_ID, Name, Age, Gender, and Region. The product information contains Product_ID, Product_Name, Category, and Price.

You are to evaluate the proposed schema:

1. Transactions table (normalized)
2. Customers table (normalized)
3. Products table (normalized)
4. Sales summary table (denormalized, aggregating key metrics by Product Category and Region)

Which schema modification would best optimize the Snowflake query performance for the intended reports?

A: Add a Region column to the Products table.
B: Create an additional denormalized table aggregating customer demographic data by Region.
C: Index Customer_ID in the Transactions table.
D: Implement clustering on the Category column in the Products table.
E: Implement clustering on Customer_ID in the Transactions table.