COF-C03 Practice Test 8 — 10 Questions

Question 1

What is the consequence of applying a Row Access Policy to a table that already contains existing data?

Accepted Answer

The policy is applied immediately to all subsequent queries.. When a Row Access Policy is applied to an existing table, the policy immediately takes effect for all subsequent queries. Any user who runs a query against the table will only see the rows allowed by the policy logic. This is critical for governance because it provides an immediate, retroactive enforcement of security rules without needing to migrate or transform the existing data in the table, ensuring compliance from the moment the policy is activated.

Answer

The data must be reloaded to apply the policy.

Answer

Existing queries will continue to return all rows.

Answer

The table must be dropped and recreated.

Question 2

A data engineer wants to ensure that data loaded into Snowflake is encrypted at rest and in transit. Which Snowflake architecture component handles the encryption and decryption processes automatically without requiring user-managed keys or manual intervention?

Accepted Answer

The Snowflake Cloud Services layer. Snowflake utilizes a multi-layered security architecture where encryption is handled transparently by the cloud service layer. Data is automatically encrypted using AES-256 before being written to persistent storage. This architectural feature is critical because it ensures compliance and data protection without adding operational overhead for the end user, allowing architects to focus on data transformation logic rather than managing complex infrastructure security keys.

Answer

The Virtual Warehouse compute engine

Answer

The Snowflake Stage object configuration

Answer

The Snowflake Database Catalog

Question 3

Refer to the exhibit. User 'jdoe' holds the 'manager' role. Which privileges does 'jdoe' possess regarding roles and data access?

Accepted Answer

jdoe has the privileges of the manager and analyst roles.. Snowflake's role-based access control (RBAC) supports hierarchy. When 'analyst' is granted to 'manager', and 'manager' is granted to 'jdoe', 'jdoe' inherits all privileges assigned to both the 'manager' and 'analyst' roles. This inheritance model allows for complex organizational structures while keeping grant management simple. It is crucial for governance, as it prevents over-privileging by allowing admins to nest permissions logically rather than assigning every individual role directly to users.

Answer

jdoe has the privileges of the manager role only.

Answer

jdoe must explicitly switch to the analyst role to see its data.

Answer

jdoe only has access to objects created by the analyst role.

Question 4

Which function should be used to transform a single row containing a semi-structured VARIANT column with an array of objects into multiple individual rows?

Accepted Answer

FLATTEN. Transforming semi-structured data is a core capability of Snowflake, allowing users to convert nested JSON or arrays into a relational format. The FLATTEN function is specifically designed for this purpose, enabling data analysts to join the parent row with its child elements, which is essential for reporting and traditional SQL analysis.

Answer

PARSE_JSON

Answer

OBJECT_CONSTRUCT

Answer

STRIP_NULL_VALUE

Question 5

A user wants to check for potential errors in a set of staged files without actually loading the data or consuming significant warehouse credits. Which approach should they use?

Accepted Answer

Execute the COPY command with the VALIDATION_MODE = 'RETURN_ERRORS' parameter specified.. The VALIDATION_MODE parameter in the COPY command is a powerful tool for data quality assurance. It allows Snowflake to parse files and identify errors without writing data to the table, which helps prevent failed loads and saves time by catching formatting issues before the actual ingestion occurs.

Answer

Run the COPY command with the ON_ERROR = 'ABORT_STATEMENT' and PURGE = 'FALSE' options.

Answer

Query the files directly using the SELECT * FROM @stage syntax with a LIMIT 100 clause.

Answer

Use the ANALYZE_STAGE function to generate a report on the health and formatting of the staged files.

Question 6

A developer is performing a large data load using the COPY INTO command. The load is taking longer than expected. Which action should be taken to optimize this load?

Accepted Answer

Split large files into smaller, equal-sized chunks.. Optimizing bulk data loads involves ensuring the data is split into appropriately sized files to maximize parallelism during the ingestion process. Snowflake’s COPY command can leverage multiple warehouse nodes if the input files are partitioned effectively. By ensuring that the files are roughly 100MB to 250MB each, the load process can be distributed across the available nodes in the virtual warehouse, leading to significant reductions in the overall time required to complete the load.

Answer

Reduce the size of the virtual warehouse.

Answer

Change the file format to JSON.

Answer

Disable auto-clustering on the target table.

Question 7

A data engineer notices that a query on a large table is consistently slow despite the table being clustered. The query filters on a column that is not part of the clustering key. What is the most efficient way to improve performance for this query?

Accepted Answer

Redefine the clustering key to include the filter column.. Improving performance requires minimizing the amount of data scanned during query execution. Since the existing clustering key does not align with the query filter, Snowflake must scan more micro-partitions than necessary. By redefining or adding a clustering key that aligns with the frequently used filter column, the query optimizer can prune unnecessary partitions effectively. This process reduces I/O overhead and significantly speeds up query execution, demonstrating the vital role of data layout in performance.

Answer

Increase the virtual warehouse size.

Answer

Create a materialized view on the column.

Answer

Convert the table to a temporary table.

Question 8

Which technique is recommended to improve the performance of a query that must frequently filter data based on values within a VARIANT column containing JSON data?

Accepted Answer

Materialize frequently used JSON keys into separate relational columns.. Querying semi-structured data is efficient in Snowflake, but performance can be further enhanced by creating 'functional' elements. Since Snowflake micro-partitions store VARIANT data in a columnar fashion, extracting common fields into their own relational columns allows the engine to use standard pruning and statistics more effectively.

Answer

Convert the JSON into a single large string and use LIKE operators.

Answer

Create a separate table for every key-value pair in the JSON.

Answer

Disable the use of the result cache for all JSON-based queries.

Question 9

A user wants to create a table that automatically stays up-to-date with a complex transformation from a source table. The transformation involves multiple joins and aggregations. Which Snowflake object is best suited for this, assuming the user prioritizes ease of management and low latency?

Accepted Answer

Dynamic Table. Dynamic Tables represent a shift toward declarative data engineering in Snowflake. Unlike Materialized Views, which have strict limitations on joins and functions, or Tasks/Streams, which require manual orchestration, Dynamic Tables automatically manage the refresh process based on a target lag, simplifying the management of complex data pipelines.

Answer

Materialized View

Answer

Standard View

Answer

External Table

Question 10

A Snowflake administrator is configuring a new external stage to load data from an Amazon S3 bucket. The storage integration already exists and is named MY_S3_INT. The administrator needs to reference this integration when creating the stage. Which SQL statement correctly creates the stage with the necessary parameters?

Accepted Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' STORAGE_INTEGRATION=MY_S3_INT;. To create an external stage that uses a storage integration, the correct syntax includes the STORAGE_INTEGRATION parameter followed by the integration name as an identifier (unquoted). This allows Snowflake to assume the IAM role defined in the integration to access the S3 bucket securely, without embedding credentials.

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' INTEGRATION=MY_S3_INT;

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' STORAGE_INTEGRATION='MY_S3_INT';

Answer

CREATE STAGE my_stage URL='s3://mybucket/path/' CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...');