When choosing between using a dimensional model and a flattened dataset for BI requirements in Snowflake, what considerations impact the final decision? (Select all that apply)

• A.Query performance expectations
• B.User access control requirements
• C.Query execution plan constraints
• D.Data denormalization and redundancy needs

Comment: *

Name: *

Email: *

Verification: *

A Snowflake data warehouse contains a table 'CUSTOMER TRANSACTIONS with columns 'CUSTOMER ID, 'TRANSACTION DATE', 'AMOUNT', and 'PRODUCT CATEGORY'. Analysts frequently run queries that aggregate transaction amounts by product category for specific customer segments. The following query pattern is common:

Which of the following strategies, when implemented together, would BEST optimize the performance of this query pattern, considering both result caching and data access patterns?

• A.Create a view that encapsulates the 'WHERE' clause conditions (filtering by 'CUSTOMER ID and 'TRANSACTION DATE). Enable automatic query rewrite.
• B.create a materialized view that pre-aggregates 'SUM(AMOUNT)' by 'PRODUCT_CATEGORY, 'CUSTOMER_ID , and 'TRANSACTION_DATE. Regularly refresh the materialized view.
• C.Tune the virtual warehouse size to be as small as possible while still meeting performance requirements. Ensure the statistics on the table are up to date.
• D.Cluster the 'CUSTOMER TRANSACTIONS' table by 'CUSTOMER and then 'TRANSACTION DATE. Create a materialized view preaggregating 'SUM(AMOUNT)' by PRODUCT_CATEGORY', and ' TRANSACTION_DATE
• E.Create indexes on 'CUSTOMER and 'TRANSACTION DATE columns of the CUSTOMER TRANSACTIONS table.

Comment: *

Name: *

Email: *

Verification: *

You are tasked with ingesting data from a REST API that provides customer order information in JSON format. The API returns a nested JSON structure with an array of orders, each containing customer details and order items. The data volume is expected to be high. You need to efficiently load this data into Snowflake. Which of the following approaches would be MOST efficient, considering cost and performance, and taking into account potential data quality issues?

• A.Load the JSON data directly into a VARIANT column, then create a view to flatten the data as needed. Use Snowflake's JSON functions for querying and transformation.
• B.Use Snowpipe with a stream and task to continuously ingest the JSON data into a staging table with a VARIANT column. Then, create a separate task to flatten the data into relational tables using SQL transformations after ingestion.
• C.Use an external function to call a Python script that parses the JSON, transforms the data into a flat relational structure, and then uses the Snowflake Python Connector to insert the data directly into relational tables.
• D.Use a third-party ETL tool to extract, transform, and load the data into Snowflake.
• E.Load the data into a temporary stage, manually parse it with SQL statements, and then load it into your target tables. Schedule the task for periodic execution.

Comment: *

Name: *

Email: *

Verification: *

A company ingests sensor data into a Snowflake table named READINGS with columns (VARCHAR), 'reading_time' (TIMESTAMP NTZ), and 'raw_value' (VARCHAR). The 'raw_value' column contains numeric data represented as strings, but sometimes includes non-numeric characters (e.g., '123.45', 'N/A', '500'). You need to calculate the average of the numeric raw_value' readings for each within the last hour, excluding invalid readings. Which of the following Snowflake SQL statements will correctly accomplish this, handling potential conversion errors and filtering for valid data?

• A.SELECT sensor_id, FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1, CURRENT _ TIMESTAMP()) GROUP BY sensor id;
• B.SELECT sensor_id, FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1 , AND TRY_TO IS NOT NULL GROUP BY sensor_id;
• C.SELECT sensor_id, AVG(CASE WHEN THEN ELSE NULL END) FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1, CURRENT TIMESTAMP()) GROUP BY sensor_id;
• D.SELECT sensor_id, raw_value, NULL))) FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1, CURRENT TIMESTAMP()) GROUP BY sensor_id;
• E.SELECT sensor_id, 'N/A'))) FROM SENSOR_READINGS WHERE reading_time DATEADD(hour, -1 , CURRENT TIMESTAMP()) GROUP BY sensor_id;

Comment: *

Name: *

Email: *

Verification: *

When utilizing User-Defined Functions (UDFs) in SQL, which statement accurately describes their usage?

• A.UDFs can't be invoked within Stored Procedures.
• B.They allow custom-defined operations on data, extending SQL functionalities.
• C.UDFs are solely used for administrative tasks within databases.
• D.UDFs are only applicable to numeric data types.

Comment: *

Name: *

Email: *

Verification: *