COF-C03 Practice Test 9 — 10 Questions

Question 1

A consumer account has mounted a shared database named partner_db. The consumer wants to create a local table that combines data from the shared database with their own data. What must the consumer do to create this new table?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create the table in one of their own databases using CREATE TABLE my_db.public.combined AS SELECT ... FROM partner_db.public.table. Consumers can query shared databases but cannot create objects inside them. To combine shared data with local data, the consumer creates a new table in their own database using CREATE TABLE AS SELECT that reads from the shared database. This keeps ownership and write privileges with the consumer and avoids any attempt to modify the read-only shared database.

Answer

Create the table in the shared database using CREATE TABLE partner_db.public.combined AS SELECT ...

Answer

Request that the provider grant INSERT privileges on the shared schema so the consumer can create a table there.

Answer

Clone the shared database into their account and then create the table inside the clone.

Question 2

A user with the role 'ANALYST' needs to be able to see the definition of a secure view named 'sales_view' in the 'sales_db' database. The view owner has granted SELECT on the view to ANALYST. However, when ANALYST runs SHOW VIEWS, the view definition is not visible. What is the most likely cause?

Accepted Answer

Secure views do not expose their definition to users who do not have the OWNERSHIP privilege on the view.. Secure views intentionally hide their definition from users who do not own the view. Even with SELECT privilege, non-owners cannot see the view's SQL definition. This is a key security feature of secure views.

Answer

The ANALYST role needs the MONITOR privilege on the view to see its definition.

Answer

The ANALYST role lacks the USAGE privilege on the database and schema.

Answer

The view definition is only visible if the user has the SELECT privilege on all underlying tables.

Question 3

Refer to the exhibit. Based on the Query Profile snippet, which optimization strategy would most likely address the high execution time and massive row production?

Accepted Answer

Review the SQL to ensure a valid join predicate exists between the tables.. The exhibit identifies a Cartesian product join, which occurs when a join condition is missing or improperly defined, resulting in every row from one table being combined with every row from another. This leads to exponential data growth and severe performance issues. Correcting the join logic is the only way to prevent the system from generating these massive, unnecessary intermediate datasets.

Answer

Apply a clustering key to the primary keys of both tables.

Answer

Enable the Query Acceleration Service for the warehouse.

Answer

Increase the warehouse size to 4X-Large to handle the volume.

Question 4

A Snowflake administrator needs to ensure that a virtual warehouse automatically suspends after a period of inactivity to save costs. Which parameter should they configure?

Accepted Answer

AUTO_SUSPEND. AUTO_SUSPEND is the parameter that defines the idle time before a virtual warehouse is automatically suspended. Configuring it appropriately ensures that warehouses do not run unnecessarily, reducing credit usage. AUTO_RESUME is complementary but does not suspend the warehouse. The other parameters affect query timeout or warehouse size, not automatic suspension.

Answer

WAREHOUSE_SIZE

Answer

STATEMENT_TIMEOUT_IN_SECONDS

Answer

AUTO_RESUME

Question 5

An administrator needs to grant the role 'ANALYST' the ability to see all queries executed in the account for auditing purposes. Which privilege should be granted to 'ANALYST'?

Accepted Answer

MONITOR USAGE. To allow a role to view all queries executed in the account, the MONITOR USAGE privilege must be granted. This privilege provides access to the ACCOUNT_USAGE schema, including the QUERY_HISTORY view, which contains records of all queries. Other privileges like USAGE on a database or MANAGE GRANTS do not grant account-wide query visibility.

Answer

MANAGE GRANTS

Answer

SELECT on the QUERY_HISTORY view

Answer

USAGE on the database

Question 6

A Snowflake user is designing a table to store semi-structured data from JSON logs. The user wants to query specific fields within the JSON efficiently and also retain the ability to query the entire JSON object. The user also wants to minimize storage costs. Which approach should the user take?

Accepted Answer

Store the entire JSON object in a single VARIANT column.. Storing JSON in a VARIANT column leverages Snowflake's automatic optimization for semi-structured data, including columnar storage and path extraction. This provides efficient querying of specific fields while retaining the full JSON object, and it minimizes storage costs through compression and automatic micro-partition optimization. Other approaches either increase storage costs, reduce flexibility, or do not utilize Snowflake's native optimizations.

Answer

Store the JSON as a string in a VARCHAR column.

Answer

Store the JSON in an external stage and query it using external tables.

Answer

Shred the JSON into separate relational columns for each field.

Question 7

A Snowflake administrator configures a storage integration named EXT_S3_INT so that an external stage can read Parquet files from an Amazon S3 bucket. After creating the integration, the administrator runs DESCRIBE INTEGRATION EXT_S3_INT and copies the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID values. What must the administrator do with these two values to allow Snowflake to access the bucket?

Accepted Answer

Attach them to an AWS IAM role trust policy that permits the Snowflake-generated IAM user to assume the role.. Storage integrations let Snowflake assume an AWS IAM role instead of storing AWS credentials. The IAM user ARN is the principal that assumes the role, and the external ID is a condition that prevents the confused-deputy problem. Both must be configured in the role's trust policy in AWS, after which the integration can be referenced by an external stage to read the S3 data.

Answer

Register them with an AWS KMS key policy so Snowflake can decrypt the Parquet files at rest.

Answer

Store them as a secret in Snowflake and reference the secret from the external stage definition.

Answer

Add them to the S3 bucket policy as principal and condition values in the bucket's resource statement.

Question 8

A Snowflake administrator is configuring a new virtual warehouse to support a data science team that runs occasional, complex queries on large datasets. The team requires fast performance and minimal latency. Which TWO warehouse configuration settings should the administrator consider to optimize performance for this workload? (Choose two.)

Accepted Answer

Enable the Query Acceleration Service (QAS) for the warehouse.. For occasional complex queries on large datasets, increasing warehouse size provides more compute power for faster processing. Additionally, enabling the Query Acceleration Service (QAS) can offload eligible query parts to shared resources, further improving performance. Multi-cluster warehouses target concurrency, not single-query speed, and AUTO_SUSPEND settings affect cost, not performance.

Answer

Configure the warehouse to use a larger number of clusters with scaling policy set to ECONOMY.

Answer

Enable multi-cluster warehouse with a minimum cluster count of 2.

Answer

Set AUTO_SUSPEND to a low value (e.g., 60 seconds) to minimize idle costs.

Question 9

A data engineer is loading streaming events through Snowpipe. The ingest rate spikes unpredictably, causing queued files to wait several minutes before loading, while the target table is queried heavily by BI users. The engineer wants Snowpipe processing to scale independently of the BI virtual warehouse so that neither workload interferes with the other. Which Snowflake feature should the engineer leverage to achieve this?

Accepted Answer

Rely on Snowflake-managed compute that Snowpipe uses serverlessly to load files without consuming user warehouse resources.. Snowpipe performs continuous data ingestion using compute resources managed by Snowflake rather than a user-created virtual warehouse. Because its compute is separate from the warehouse serving BI queries, ingest and query workloads scale independently, eliminating the interference described. This architecture is what allows Snowpipe to load files promptly even when users are actively querying the target tables.

Answer

Enable multi-cluster scaling on the BI virtual warehouse so both workloads share its compute.

Answer

Create a dedicated virtual warehouse and configure Snowpipe to run all its COPY statements on that warehouse.

Answer

Increase the size of the existing BI virtual warehouse so it can absorb the additional Snowpipe load.

Question 10

A data engineering team is loading a 500 GB CSV file into a Snowflake table using the COPY command. They notice that the load is taking longer than expected and the warehouse is showing high CPU utilization. Which of the following is the MOST likely cause for the slow performance?

Accepted Answer

The file is too large for a single COPY command and should be split into smaller files.. For optimal load performance, Snowflake recommends splitting large data files into multiple smaller files (100-250 MB compressed) to allow parallel loading across the warehouse nodes. A single large file cannot be parallelized, leading to slower loads and potential resource contention. Increasing warehouse size or tweaking other parameters does not address the fundamental limitation of loading a single large file.

Answer

The COPY command is using a single thread by default and should be configured for multi-threading.

Answer

The CSV file contains too many columns, causing parsing overhead.

Answer

The warehouse size is too small and should be increased to handle the file size.