COF-R02 Sample Questions & Answers

Snowflake Data Clean Rooms provide a secure environment where multiple parties can collaborate on sensitive data without sharing the raw data itself. Each party maintains control over their data, and they can run joint queries with predefined restrictions to protect privacy. This directly addresses the requirement for privacy-preserving analysis without data movement. While Secure Data Sharing with policies is a component, the 'Clean Room' concept holistically addresses this specific multi-party collaboration use case.

Hybrid Tables, the core of Snowflake's Unistore workload, are designed for Hybrid Transactional/Analytical Processing (HTAP). They use a row-based storage engine for fast, single-row operations (transactional) while integrating with Snowflake's existing columnar engine for fast analytical scans. This allows a single table to efficiently serve both types of queries.

The IS_ROLE_IN_SESSION() function is specifically designed to be used within masking and row access policies. It checks if the specified role (e.g., 'ANALYST') is in the hierarchy of the current active primary or secondary roles for the user executing the query. This allows for conditional logic within the policy body. CURRENT_ROLE() only returns the primary active role, and CURRENT_USER() returns the user name, not their roles.

Streamlit in Snowflake allows users to build, deploy, and share interactive data applications using Python, all within Snowflake's secure environment. It is specifically designed for rapidly creating data-centric web apps without needing front-end web development skills. Snowsight is for dashboards, and the Python Connector is a library for connecting to Snowflake from an external Python application, but it doesn't provide the application framework or hosting.

The RESULT_SCAN command is used to retrieve results from a previous query. When Snowflake can reuse the results of a previously executed query from the result cache, it does so without engaging a virtual warehouse for computation. This is a key performance and cost-saving feature. The local disk cache is used for data, not query results, and if a warehouse were used, the query profile would show compute activity.

This solution correctly applies the best optimization feature for each distinct workload. 1) A clustering key on (ORDER_DATE, STATUS) will improve pruning for the analytics team's range queries. 2) The Search Optimization Service is specifically designed to accelerate low-latency, selective point-lookup queries, perfect for the fraud team's needs on a high-cardinality column like CUSTOMER_ID. 3) A materialized view will pre-compute the sales aggregates, providing the fastest possible performance for the BI team's dashboards. This multi-faceted approach is superior to using a single, less-targeted optimization method.

The Snowflake Marketplace natively supports multiple pricing models for paid listings, including 'Per Month' (subscription) and 'Per Query'. By selecting the 'Per Query' model when creating the listing, Snowflake automatically handles the tracking of queries against the shared objects and the subsequent billing, allowing providers to implement usage-based pricing.

The COPY INTO FROM (SELECT ...) syntax allows for data transformation during the load. To mask data on ingest, you can apply a function (which could be a standalone UDF or a function used in a masking policy) to the staged data columns within this SELECT statement. This transforms the data before it is written to the target table, ensuring the sensitive data never lands in plain text.

To process a file from a stage, a scoped URL is generated to provide temporary, secure access to that specific file for the external service.

The External Function is the Snowflake object that acts as a proxy to the remote HTTP service (e.g., an AWS Lambda function) that performs the OCR.

The API Integration object stores the necessary information, such as the API provider and security credentials, to establish a secure and authenticated connection to the remote endpoint.

The SCALING_POLICY parameter controls how quickly a multi-cluster warehouse scales down. The default is 'STANDARD', which prioritizes conserving credits but may be slower to shut down clusters. Changing the policy to 'ECONOMY' will cause clusters to shut down more aggressively after they finish executing queries, which is the desired behavior for reducing costs when query loads decrease.