A data analyst needs to create a view named 'DAILY SALES SUMMARY that provides a daily summary of sales data from the 'SALES TRANSACTIONS' table. The 'SALES TRANSACTIONS' table contains columns like 'TRANSACTION , 'TRANSACTION DATE, 'PRODUCT ID', 'SALES_AMOUNT, and 'CUSTOMER ID. They want to optimize query performance for frequently run reports that use this summary Which of the following approaches provides the BEST performance for the reports while minimizing maintenance overhead?
Correct Answer: C
Materialized views pre-compute and store the results of the query, providing the fastest query performance for read-heavy workloads. Snowflake automatically manages the refresh of materialized views, reducing maintenance overhead. Regular and secure views re-execute the query each time they are accessed. Manually refreshing a table adds significant overhead.
Question 77
You are preparing to load a large dataset from Parquet files stored in an Azure Blob Storage container into Snowflake using Snowsight. The dataset contains personally identifiable information (PII) and you need to ensure that only authorized users can access this data,. You want to use Snowflake's data masking policies to protect the PII. Which of the following options represents the correct sequence of steps and considerations for achieving this, specifically using Snowsight for the loading and initial policy application phases?
Correct Answer: E
Option E is the most efficient and recommended approach. First, you need to set up the connection to Azure Blob Storage (the external stage) and define the file format for Parquet files. Then, you create the target table with the correct schema to receive the data. The critical step is to define the masking policies before loading the data. This ensures that the masking policies are in place when the data is loaded into the target table. Finally, load the data using Snowsight. Applying masking policies after loading the data (as in option B) is less efficient and leaves a window of opportunity where PII is exposed. Defining policies and then referring to them (as in option C) isn't the typical workflow. Option A is more complex and introduces a VARCHAR staging table, which isn't optimal for Parquet data.
Question 78
You have a table 'PRODUCTS' with a 'PRICE' column stored as VARCHAR. Some values in this column are valid numerical strings (e.g., '12.99'), while others contain invalid characters (e.g., '12.99USD', 'N/A'). You need to calculate the average price of all valid products. Which of the following approaches ensures that you only consider valid numeric values and handles potential errors effectively? Select all that apply.
Correct Answer: B,D
Option B is correct because it uses a regular expression to explicitly check if the PRICE column contains only valid numeric characters (digits and an optional decimal point) before attempting to cast it to a DECIMAL. 'REGEXP LIKE' ensures that non-numeric values are filtered out, preventing errors during casting, and then 'CAST is used since it is validated by the regular expression check. Option D is also correct because it uses 'CASE statement with 'IS DECIMAL' function that is a user defined function (UDF) for checking if a string can be converted to decimal, and if True, it casts the PRICE to DECIMAL; otherwise, it assigns NULL. The AVG function automatically ignores NULL values, ensuring that only valid numeric values are considered. Option A won't work as DECIMAL' function is not standard, so you will need to create user defined function. 'TO_NUMBER and 'TO_DECIMAL' throws error if it cannot parse which is not ideal. Option E, throws error if column PRICE cannot be converted, where as 'TRY TO DECIMAL' will not throw the error and return NULL.
Question 79
What critical role do user-defined functions (UDFs) play in SQL for advanced data analysis?
Correct Answer: C
UDFs extend SQL functionalities by enabling customized operations on data, going beyond standard SQL capabilities.
Question 80
When employing different data models (e.g., dimensional, Data Vault) in Snowflake, how does Data Vault modeling address complexities in data integration and changes?
Correct Answer: B
Data Vault models simplify data integration and adapt well to changes, providing advantages in handling complexities and evolutions in data structures.
