SnowPro Core (COF-C03) — Questions 151–225

Privileges granted to a share are enforced at query time against the provider's live data. Revoking SELECT on the table from the share removes the consumer's access instantly, so the next query returns an error such as 'SQL access control error: Insufficient privileges to operate on table'. No re-mounting is required for the revocation to take effect, and previously cached results do not preserve access.

LATERAL FLATTEN is the correct Snowflake table function that expands an array or object into multiple rows, and using it with a lateral join preserves the original row's columns. It accepts an input expression such as PAYLOAD:products and produces one row per array element, which is exactly what this scenario requires.

Result Cache stores the output of a query in the Cloud Services layer for 24 hours (or until the underlying data changes). Since the same query text was rerun and micro-partitions had no changes, Snowflake returned the cached result set without re-executing the query on the virtual warehouse. This is why the second run completed in 1 second.

When a JSON file contains multiple records wrapped in a single array (e.g., [{},{},{}]), setting this option to TRUE instructs Snowflake to remove the brackets and load each object as its own distinct row. This is essential for standardizing data ingestion from many API-based sources.

After creating a database from a share, the consumer must grant privileges on that database to roles that need access. This includes USAGE on the database and schema, and SELECT on the specific tables or views. The consumer controls these grants independently of the provider, so this is the correct action.

The CREATE DATABASE ... CLONE command creates a zero-copy clone of a database. It copies metadata and references the same micro-partitions as the source, so no additional storage is consumed initially. Changes to either the source or the clone create new micro-partitions, ensuring isolation. This meets the requirement for rapid cloning without immediate storage duplication.

When a query is satisfied by the result cache, Snowflake does not activate any warehouse compute. This means the query executes at no cost and with near-zero latency, which is a major advantage for recurring reports and frequently accessed summary data within the Snowflake platform.

The MONITOR USAGE privilege on the account allows a role to view usage information, including queries executed in the account. Granting this privilege to 'ANALYST' enables them to access the QUERY_HISTORY views and monitor all queries, which is necessary for auditing.

Because Snowflake's storage layer is decoupled and supports concurrent access, multiple virtual warehouses or users can access the same data without contention. The architecture ensures that each query is independent and isolated, preventing one query from degrading the performance of another, even when querying the exact same tables.

In a multi-tenant environment like Snowflake, the External ID ensures that even if another user knows your AWS Role ARN, they cannot use their own Snowflake account to access your data. AWS requires the External ID provided by Snowflake to match the one in the IAM trust policy, creating a unique and secure handshake between accounts.

`EXPLAIN` provides the logical execution plan without running the query, showing operators and estimated costs. By inspecting the plan, the engineer can spot steps like Cartesian joins, unnecessary aggregations, or missing filters that cause large intermediate results. This diagnostic step guides targeted optimizations such as adding predicates or rewriting joins. It is a best practice for understanding where the optimizer is spending effort and where data volume explodes.

Increasing this setting raises the threshold for concurrent query execution on a single cluster. This is beneficial for high-concurrency, low-complexity workloads where you want to maximize the utilization of your compute resources, although it may impact the latency of individual queries if they become resource-starved.

The Snowflake query optimizer analyzes table statistics and determines the most efficient join strategy. For a large fact table joined with a small dimension table, broadcasting the small table to all nodes avoids redistributing the large table, reducing network overhead and speeding up the join. This is a core optimization technique in Snowflake's architecture.

VALIDATION_MODE = 'RETURN_ERRORS' validates files against the specified file format and returns any parsing errors without loading data, which directly exposes mismatches such as incorrect field or record delimiters causing NULL VARIANT values. This is the recommended way to test a file format against real files before a production load, and it aligns with diagnosing JSON parsing behavior.

Zero-Copy Cloning creates a new object that points to the same underlying micro-partitions as the source. It is 'zero-copy' because no data is actually duplicated upon creation. Any subsequent changes to the clone result in new micro-partitions, keeping the original data and the clone isolated.

The VALIDATION_MODE parameter instructs Snowflake to parse the files and check for errors without actually loading any data into the table. This is highly efficient for testing file format settings or verifying data quality before committing to a large-scale ingestion process that could consume significant credits.

Because the tables change only nightly, identical query text run repeatedly during the day can be served from the result cache at no compute cost after the first execution. Keeping the SQL text and relevant session parameters consistent allows cache reuse, cutting 39 of 40 daily executions to near-zero credits. This is the most direct cost reduction for a repetitive, read-only report.

Granting USAGE on the database and schema to the share is required so that the consumer can access the schema and see the table. Without these grants, the consumer cannot navigate to the table even if SELECT is granted on the table itself. This is a foundational step for any share.

This is the core architectural advantage of Snowflake. You can increase compute resources during peak reporting hours and suspend them afterwards, all while your data remains securely and durably stored in the cloud object storage layer, independent of the warehouse state.

Snowflake bills for the time a warehouse is running, including idle time before auto-suspension. With AUTO_SUSPEND set to 60 seconds, the warehouse suspends after 60 seconds of inactivity. However, the scenario states it remains idle for 2 minutes before suspending, which implies the auto-suspend setting is not taking effect as expected. Regardless, any time the warehouse is running, credits are consumed. So charges apply for the idle period until suspension.

The STORAGE_AWS_IAM_USER_ARN identifies the Snowflake-owned IAM user that assumes the role, and STORAGE_AWS_EXTERNAL_ID is the unique external ID that must appear in the role's trust policy condition. Placing both in the trust relationship of the AWS IAM role lets Snowflake authenticate to the bucket without embedding long-term AWS keys in Snowflake.

Directly granting privileges to users makes auditing and management extremely difficult. Assigning privileges to roles creates a centralized and reusable set of permissions. When a user changes roles, you simply update their role assignment rather than manually modifying permissions on every individual object in the system.

External tables are read-only and query data in place from the external stage. Snowflake accesses the files using the stage's storage integration and parses them according to the table's file format. The data never leaves the external storage, which is ideal for infrequent queries or when data must remain in its original location.

Auto-suspend stops the warehouse after the specified idle period, eliminating credit consumption while no queries run. Auto-resume is enabled by default and starts the warehouse automatically when a new query arrives, so the team gets both cost savings and immediate query start without manual intervention. This combination directly satisfies the stated requirements.

When FLATTEN expands an array, the VALUE column holds the element itself, which for an array of objects is a VARIANT object. Referencing VALUE and then using the colon path syntax extracts individual attributes from each object. This is how the exploded rows are turned into relational columns.

The Query Acceleration Service (QAS) offloads portions of query processing to shared compute resources, improving performance for queries with large scans and filters. It is beneficial for occasional, complex queries on large datasets, as it can reduce execution time without resizing the warehouse. Enabling QAS is a valid optimization for this scenario.

When a masking policy is attached to a column, every reference to that column in a query is replaced by the policy expression for users who are not authorized to see the raw data. This includes references in the select list, WHERE clause, and ORDER BY clause. Therefore the user sees the masked value and the ordering is computed on the masked representation, not the original name.

Masking policies in Snowflake are column-level security features that can transform data at query time based on the role of the user executing the query. They allow conditional masking, such as showing full data to privileged roles and masked data to others. This precisely matches the administrator's requirement to hide PII from the analyst while allowing access to other columns.

The Query Result Cache persists the results of executed queries in the Cloud Services layer for up to 24 hours. If an identical query is reissued and the underlying data has not changed, Snowflake returns the cached result without provisioning a warehouse, which explains the sub-second response and the absence of a warehouse start.

Loading JSON into a VARIANT column preserves the semi-structured data, and Snowflake's native support for semi-structured data allows extraction using dot notation and array indexing in a view or query. This approach is flexible and avoids complex transformations during load, making it ideal for nested JSON.

LATERAL FLATTEN is a table function that expands nested arrays or objects in a VARIANT column into multiple rows. When used in the FROM clause with a lateral join, it preserves the correlation with the parent row, allowing each element of the array to become a separate row while retaining other columns from the original row. This is the standard method for normalizing semi-structured data.

Zero-copy cloning uses metadata pointers to reference existing micro-partitions. This means no data is physically copied, allowing for the creation of clones in seconds regardless of the size of the source object. It is a fundamental feature for agile development and safe production testing in Snowflake.

Point-lookup queries involve filtering a table to return a small subset of rows based on a specific key value. The Search Optimization Service maintains an index that allows these queries to bypass large-scale scanning, returning results much faster than a standard scan-based query execution would.

Clustering keys ensure that data within a range is grouped into the same or adjacent micro-partitions. This allows the query optimizer to use metadata to prune (skip) micro-partitions that do not contain data within the requested range, significantly reducing the amount of data read from persistent storage.

Because micro-partitions store metadata about the ranges of values within them, the Cloud Services layer can easily prune entire partitions that do not contain data relevant to the query's filters, drastically reducing the amount of data that needs to be scanned and processed by the compute layer.

Result Set Cache is a global cache that persists query results for 24 hours. When a query is repeated and the data is unchanged, Snowflake retrieves the result directly from this cache without starting or utilizing a virtual warehouse, effectively making the query execution free of charge.

Tags are essential for data discovery and governance. They allow administrators to mark tables or columns (e.g., 'PII' = 'True') and then use account-level views to query those tags. This makes it easy to audit sensitive data locations and verify compliance with internal and external policies.

Because storage is decoupled from compute, customers pay for the amount of data stored regardless of whether a virtual warehouse is currently active or what size it is. This allows companies to keep vast amounts of historical data without paying for expensive compute resources constantly.

To share a secure view, the provider must grant USAGE on the database and schema that contain the view, and SELECT on the view itself to the share. The base table's database does not need to be shared because the secure view runs with the owner's rights, and the consumer only needs access to the view. This keeps the base table private.

Spillage to local disk occurs when the warehouse's memory is insufficient to hold intermediate results. Increasing the warehouse size provides more memory per node, which can reduce or eliminate spillage. The MEDIUM warehouse may be undersized for the query's working set. Scaling up to LARGE is a common remedy for memory-intensive operations like large joins and aggregations.

This is correct because for CSV files, the FILE_FORMAT must specify TYPE = 'CSV'. The FIELD_DELIMITER option sets the delimiter to a pipe, and SKIP_HEADER = 1 tells Snowflake to skip the first row (the header). These options together ensure the file is parsed correctly, with the header ignored and fields separated by the pipe character. This is the standard way to handle such CSV files.

The CREATE DATABASE privilege is granted at the account level. SYSADMIN has the authority to grant this privilege to other roles. The correct syntax is GRANT CREATE DATABASE ON ACCOUNT TO ROLE DB_CREATOR; This allows the role to create databases within the account.

Snowflake recommends splitting large files into multiple smaller files (typically 100-250 MB compressed) to enable parallel loading. A single large file cannot be processed in parallel, causing one thread to handle the entire load, leading to high CPU on a single node and longer load times. Splitting the file allows the warehouse to distribute the load across multiple nodes.

This is correct because ON_ERROR = 'CONTINUE' instructs COPY INTO to skip errors and load all valid rows, while capturing rejected records in the load metadata. The rejected rows can be queried using the VALIDATION_MODE or by inspecting the COPY_HISTORY view. This allows the load to proceed without failing entirely, which is exactly what the engineer needs to continue loading valid rows and later analyze the rejected ones.

Without clustering, Snowflake relies on natural micro-partition pruning based on metadata. For a high-cardinality column, the min/max ranges in each micro-partition overlap significantly, so pruning becomes ineffective. The query must scan most or all micro-partitions, leading to poor performance. This is a common issue with unclustered, high-cardinality filter columns.

Setting DISABLED = TRUE on a user prevents that user from authenticating to Snowflake while leaving the account, its grants, and its metadata intact. This is the documented way to suspend access immediately during an investigation or offboarding without losing audit history, and it can be reversed later if needed by setting DISABLED = FALSE.

In Snowflake's hierarchical model, when a role is granted to another, the parent role automatically inherits all privileges assigned to the child role. This allows administrators to manage permissions efficiently by building chains of roles that correspond to the structural requirements of the organization's data access needs.

Splitting into multiple files allows Snowflake to distribute them across threads in the warehouse, enabling parallel loading. The recommended compressed size is 100-250 MB per file. A single COPY INTO command can reference the stage path and will load all files in parallel, maximizing throughput while respecting the CSV's embedded newline constraint.

A Business Profile is a prerequisite for Marketplace participation. It includes details about the company, its contact information, and its data offerings. Snowflake reviews these profiles to ensure providers meet quality and professional standards, which protects the integrity of the Marketplace and provides consumers with confidence in the data they acquire.

Because Parquet is self-describing, Snowflake can use functions like INFER_SCHEMA to automatically determine table structures. This reduces the manual effort required to define table columns and ensures that data types are preserved correctly from the source. It also supports nested structures like arrays and objects much more naturally than flat CSV files.

Time Travel enables users to query data as it existed at any point within the retention period. This allows for the easy recovery of dropped tables or modified rows, providing an essential layer of protection against accidental data loss and supporting organizational data integrity and compliance requirements.

Storing JSON in a VARIANT column allows Snowflake to automatically optimize storage by compressing and storing the JSON in a columnar format internally. Snowflake also extracts frequently accessed paths and stores them as separate micro-partition columns, which can improve query performance for those paths. This approach retains the full JSON object for flexible querying while minimizing storage costs due to Snowflake's automatic optimization. It is the recommended way to store semi-structured data.

A masking policy is a schema-level object that can be applied to a column to dynamically mask its data based on the user's role. You can define a policy that returns '****' for all roles except 'COMPLIANCE_OFFICER'. This precisely meets the requirement of column-level masking for unauthorized users.

Dynamic tables allow users to define the results of a query as a table and specify a 'target lag' for freshness. Snowflake automatically handles the complex refresh logic, including joins and aggregations, making it the most efficient and manageable way to handle continuously updated, complex data transformations.

Dynamic Data Masking policies are the native Snowflake feature for this requirement. Using IS_ROLE_IN_SESSION allows the policy to check the current session's role effectively. This is the standard, scalable approach for enforcing column-level security across an account, ensuring that sensitive data is protected regardless of how it is queried.

In Snowflake, having USAGE on a database and schema allows you to see the schema and potentially list objects, but to query a table, you need the SELECT privilege on that table. Without SELECT, the table appears as if it does not exist when queried. The error message 'table does not exist' is often misleading; it can also mean the user lacks privileges. Granting SELECT on the table to DATA_ANALYST would resolve the issue.

By storing data in the VARIANT format, users can navigate nested JSON hierarchies using Snowflake's dot-notation and colon-notation syntax directly within SQL queries. This allows for powerful analytical operations on semi-structured data without requiring it to be transformed into a flat relational structure first.

Time Travel enables recovery of dropped objects within the defined retention period. A dropped table can be restored with UNDROP TABLE, and historical versions can be cloned to create a new object reflecting an earlier state. This capability is a primary reason Time Travel is used for accidental deletion recovery in production databases.

Sharing a secure view hides the underlying table and schema names, since the consumer sees only the view. When the consumer creates a database from the share, it uses its own warehouse for queries, so the provider's warehouse is not consumed. This combination satisfies both the concealment and isolation requirements.

Secure Data Sharing is the standard Snowflake feature for sharing data between accounts. It uses the underlying metadata layer to grant access to specific objects in the provider account to one or more consumer accounts, ensuring data is never copied and always remains under the control of the provider.

Clustering keys reorganize the micro-partitions so that data with similar values is stored together. When a query filters on the clustered column, the optimizer can use the clustering metadata to prune micro-partitions that do not contain the filter value. This directly reduces the number of micro-partitions scanned, improving performance for high-cardinality columns.

To keep the metadata of an external table in sync with the actual files in the cloud storage, it must be refreshed. Snowflake allows users to trigger this manually using the ALTER EXTERNAL TABLE... REFRESH command or automatically by integrating with cloud event services like AWS SQS or Azure Event Grid.

The ALTER SHARE ... REMOVE ACCOUNT command is the correct way to revoke a consumer's access to a share. It removes the specified account from the share, immediately terminating their ability to access the shared data. This action does not affect other consumers who are still added to the share, making it the precise method for this scenario.

`LATERAL FLATTEN` is designed to explode an array into multiple rows. When applied to `json_col:tags`, it produces one row per element of the tags array. The outer query can still reference `json_col:id` and `json_col:name` because the lateral join preserves the original row context. This yields exactly one row per tag with the corresponding id and name, which matches the requirement.

Reader accounts are managed by the provider, who is responsible for all credit consumption generated by the consumer's activity. The provider can set up resource monitors to control and limit the amount of credits the Reader Account can use. This ensures the provider can share data without facing unexpected or unmanaged costs.

Every user in Snowflake has a personal User Stage identified by the '@~' symbol. It is the most convenient place for individual users to upload files for testing or personal use. Because it is managed by the system, it cannot be dropped, and permissions cannot be granted to other users to access its contents.

LATERAL FLATTEN is the native Snowflake table function that expands a VARIANT array or object into one row per element, and using it as a lateral join preserves the parent row's columns. This directly satisfies the requirement to produce one row per line item while retaining order-level attributes, all within a single SELECT statement.

Secure views are designed to hide the view 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 security feature to prevent exposure of underlying data or logic.

Adding an account to a share only authorizes that consumer account and places a share object in its 'inbound' area; nothing is readable until a consumer-side role with CREATE DATABASE runs CREATE DATABASE ... FROM SHARE. Until that database is created, the consumer sees only an available share, not tables. This is exactly how data sharing provisioning works.

A secure view queries the base tables at runtime, so consumers always see current data without any refresh process. Because the view is secure, its definition is hidden and consumers cannot infer underlying table structures. Granting SELECT on the view to the share provides access without exposing the base tables, satisfying both requirements.

Time Travel allows users to access historical data versioning by utilizing Snowflake's micro-partition metadata. This feature is integrated natively into the platform, providing the ability to perform point-in-time recovery and analysis without the need for additional administration or storage management by the user.

Snowflake warehouses use local SSDs to cache data from micro-partitions. When a warehouse is resumed after being suspended, or if it has not queried this data recently, it must fetch the data from remote cloud storage. This 'cold' cache scenario results in significant Remote Disk I/O and slower performance.

To prevent the consumer from seeing any other objects, the provider must ensure that the share contains only the secure view. If other objects are added to the share, the consumer could potentially access them. By limiting the share to the secure view, the provider controls exactly what the consumer can see, aligning with the principle of least privilege.

In Snowsight, after running a query, the results grid provides a 'Download results' button that allows downloading the result set as a CSV file directly to the local machine. This is a straightforward and supported method for small to medium result sets, and it requires no additional staging or commands.

Simplifying the query by breaking it into smaller, manageable chunks or using temporary tables to store intermediate results reduces the complexity the compiler must handle at once. This often resolves 'query too large' errors while keeping the logic identical for the final output.