Medium COF-C03 practice questions

A data governance officer must produce a report for a compliance audit showing exactly which columns were read by user queries over the past 90 days, including which base table columns were accessed through views. The officer wants an authoritative source that captures column-level read and write lineage. Which Snowflake feature should be used?

A data governance analyst needs to audit which users queried a specific table over the past 18 months. They plan to use the ACCOUNT_USAGE schema in the SNOWFLAKE database. When they query the QUERY_HISTORY view, they discover that records older than one year are missing. What is the reason for this behavior?

A governance analyst must build a report that lists every query executed across the account over the past 180 days, including queries run by users whose accounts have since been dropped. The analyst wants the most complete historical coverage and is willing to accept some data latency. Which data source should the analyst query?

A data governance analyst needs to build a monthly report showing the total average bytes of storage consumed by each individual table across the entire account, including bytes attributable to Time Travel and Fail-safe. They want a single query that requires ACCOUNTADMIN-level visibility across all databases. Which approach should they use?

A Snowflake administrator is organizing objects in a new account. They need to create a network policy that restricts login access, a database to hold analytics tables, and a resource monitor to cap warehouse credit usage. When deciding where each object lives in the Snowflake object hierarchy, which statement correctly classifies these objects?

A new data engineer joins your team and needs to create and manage warehouses, databases, and roles across the account, but company policy strictly forbids granting anyone the ability to view billing information or manage account-level parameters unless they are a designated administrator. Which approach best follows Snowflake's principle of least privilege while meeting these requirements?

A finance operations team wants to track and receive email notifications when the combined monthly credit consumption of a specific set of warehouses, serverless features, and materialized view maintenance for a project exceeds a defined spending limit. They need this to span multiple object types and send alerts without automatically suspending anything. Which Snowflake feature best meets this requirement?

A healthcare analytics company must comply with HIPAA regulations. They require that all data be encrypted using a customer-managed key combined with a Snowflake-managed key, and they need support for private connectivity to isolate traffic from the public internet. Which is the minimum Snowflake edition that satisfies these requirements?

A data engineer runs CREATE DATABASE analytics_dev CLONE analytics_prod; to give the development team a copy of production for testing. Shortly after, the team drops several tables in analytics_dev and adds new columns to others. A manager worries this experimentation could harm the production database. Which statement correctly describes the relationship between the cloned database and its source?

A data engineer runs an identical aggregation query twice within a few minutes. The underlying tables have not changed, and the second execution returns results almost instantly without consuming any virtual warehouse compute credits. Which layer of the Snowflake architecture is responsible for enabling this behavior?

A data engineer manages a 12 TB events table that is frequently queried with filters on an event_date column. Query performance has degraded over time because new data arrives continuously and rows for the same date end up scattered across many micro-partitions. The engineer wants Snowflake to automatically reorganize the table's data so that partitions are better co-located by event_date, improving pruning for date-range queries. Which action should the engineer take?

A data engineering team runs heavy nightly ETL jobs that must never slow down the interactive dashboards used by business analysts during the day. Both workloads query the same production tables. A junior engineer suggests simply increasing the size of the single shared warehouse, but the lead engineer wants to guarantee that the two workloads never compete for compute resources while still reading the exact same underlying data. Which characteristic of the Snowflake architecture enables the recommended approach?

A data provider shares a database containing several large tables with a consumer account using a direct share. The consumer's data engineering team runs frequent analytical queries against the shared tables. During a cost review, the provider's finance team notices no compute charges related to the consumer's query activity on their account, and the consumer asks who pays for the compute used to query the shared data. Which statement correctly describes how compute is billed for querying shared data?

A data engineer runs a bulk COPY INTO command to load 500 CSV files into a table. The load completed, but the engineer suspects some rows were rejected due to data type mismatches. They want to retrieve the specific rows that failed during that most recent load so they can investigate the raw values. Which approach should they use?

A data engineer runs COPY INTO on a table from an internal named stage that already contains 50 files. The load completes successfully. Later that day, the same 50 files plus 10 new files are present in the stage, and the engineer reruns the identical COPY INTO command without any options that alter default behavior. What happens?

A data engineer runs COPY INTO to unload a very large query result to an external S3 stage. They omit the SINGLE option (leaving the default) and do not set MAX_FILE_SIZE. After the unload, they observe many output files rather than one. A downstream partner requires that no individual output file exceed 100 MB. Which action correctly enforces this requirement?

A data engineer used the PUT command to upload three CSV files to their user stage. They now want to load only one specific file, sales_west.csv, into the WEST_SALES table using a predefined file format named CSV_FMT. Which COPY INTO statement correctly loads just that single file from the user stage?

A data engineer is loading Parquet files into a Snowflake table using COPY INTO. The Parquet files contain named columns, but the physical column order in the files does not match the column order defined in the target table. The engineer wants Snowflake to map source fields to table columns based on their names rather than their position. Which COPY INTO option should be used?

A data engineer runs a COPY INTO command to bulk load 50 CSV files from a named internal stage into a target table. Several rows across multiple files contain formatting errors that will cause parsing failures. The business requirement is that all valid rows must be loaded, and only the individual problematic rows should be skipped without aborting the load. Which ON_ERROR option should be specified in the COPY INTO command?

A data engineer is loading a CSV file from a named internal stage into a target table. The source file has columns in the order (id, raw_timestamp, amount), but the target table columns are (amount, event_id, event_time), and the team wants to cast raw_timestamp to a TIMESTAMP and multiply amount by 100 during the load. Which approach lets them accomplish this in a single COPY INTO statement?

A data engineer needs to unload a large fact table from Snowflake to an external S3 stage. Downstream systems require the output to be plain, uncompressed CSV files (no GZIP), and the team wants each file kept under 100 MB to parallelize downstream reads. Which COPY INTO <location> configuration meets these requirements?

A data engineer must unload a large fact table to an external S3 stage. The downstream analytics team requires that the exported files be organized into subfolders by region and load date (for example, region=US/dt=2024-01-15/) so their partition-aware query engine can prune efficiently. Which approach in a single COPY INTO <location> statement produces this folder structure?

A data engineer is preparing to bulk load a large batch of CSV files from a named internal stage into a target table using COPY INTO. Before committing the load, they want to detect any data type mismatches or malformed rows without actually inserting any data into the table. Which approach should they use?

A business intelligence team wants to let non-technical analysts ask questions about sales data in plain English and receive accurate SQL-generated answers, without writing SQL themselves. The team wants to use a managed Snowflake capability that translates natural language questions into SQL against a defined semantic model. Which Snowflake feature should they use?

A data engineer at a legal firm needs to automatically generate concise summaries of thousands of lengthy contract documents already stored as text in a Snowflake table. They want to use a fully managed, serverless capability that requires no infrastructure setup and can be invoked directly within a SQL statement. Which Snowflake feature should they use?