scenario questions

A data engineer observes that a transformation query run on a MEDIUM warehouse spends most of its time in the 'Remote Disk Spilling' phase of the Query Profile. The query joins two large tables and performs a large sort. Memory usage shows the warehouse consistently near its limit. The engineer wants the most direct fix that addresses the root cause. Which action should be taken?

Which component of the Snowflake architecture is responsible for the permanent storage of data and is independent of compute resources?

Which Snowflake feature allows you to access data from a previous point in time to recover from accidental deletions or updates?

Which object acts as the primary container for sharing data with external Snowflake accounts?

A data engineer needs to unload data from a Snowflake table to an external stage that references an Amazon S3 bucket. The engineer wants to ensure that the unloaded files are encrypted using a customer-managed key in AWS KMS. Which COPY INTO <location> parameter should be used to specify the KMS key?

Which THREE factors influence the performance of a Snowflake query? (Choose three)

A provider wants to share data with a consumer using a reader account because the consumer does not have a Snowflake account. Which statement describes the correct capability of a reader account in this scenario?

A provider wants to share data with a consumer but must ensure that the consumer cannot see the underlying base tables, only an aggregated subset. The provider decides to use a secure view. Which statement is true regarding the use of secure views in a share?

What happens when you modify data in a zero-copy cloned table?

A data engineer is using the COPY INTO command to unload data from a Snowflake table to an external stage (Amazon S3). The engineer wants to ensure the unloaded files are encrypted and can be decrypted by the target system. Which TWO statements are true regarding the encryption of unloaded files? (Choose two.)

A data architect needs to ensure that data in Snowflake is encrypted at rest and in transit. Which feature provides the highest level of security by allowing the customer to manage their own encryption keys?

A Snowflake account has a custom role named DATA_ENGINEER. The administrator wants to ensure that DATA_ENGINEER can create databases and warehouses but cannot manage users or roles. Which predefined role should DATA_ENGINEER be granted to achieve this?

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?

Which feature should a provider use if they need to allow a consumer to access data based on the consumer's specific identity or organizational attributes?

A data engineer is optimizing a query that filters on a high-cardinality column in a very large table. The query currently performs a full table scan. The engineer decides to add a clustering key on that column. After clustering, the query performance improves significantly for some queries but remains poor for others that filter on a different low-cardinality column. What is the most likely reason for the inconsistent performance?

A consumer has created a database from a share provided by a partner. The consumer wants to query the shared data but receives an error indicating insufficient privileges. Which role must the consumer's user be granted to access the shared database?

A data engineer notices a large table's clustering depth is very high on the columns used in frequent range filters, and queries are scanning far more micro-partitions than expected. The table receives continuous small inserts throughout the day. Which action best improves pruning while controlling reclustering cost?

A provider wants to share data with a consumer and ensure that the consumer can only read the data but cannot create any objects in the shared database. The provider has already created a share and granted USAGE on the database and schema, and SELECT on the table. What additional step, if any, is required to enforce read-only access?

When querying an External Table, which technique provides the most significant performance improvement for selective queries?

A company is using a Multi-cluster Warehouse with the 'Auto-scale' mode enabled. What is the primary performance benefit of this configuration for a BI dashboard used by 500 concurrent users?

A consumer account has mounted a shared database from a provider. The consumer's role CONSUMER_ROLE has been granted USAGE on the shared database and its schemas. However, when the consumer tries to query a table in the shared database, they receive an error that the table does not exist. What is the most likely cause?

How does Snowflake's micro-partitioning architecture contribute to query performance without requiring user intervention?

A data engineer creates a materialized view on a large sales fact table to improve performance for a frequently run aggregation query. After a few days, users report that the materialized view sometimes returns stale data compared to the base table. The engineer verifies that the base table is being updated continuously via Snowpipe. What is the most likely explanation for the stale results?

Which TWO statements are true regarding the costs associated with Data Sharing in Snowflake?

A data engineer is investigating a slow-running query. The Query Profile shows a high percentage of time spent in the TableScan operator and a large number of partitions scanned. The engineer wants to reduce the number of partitions scanned by using pruning. Which TWO actions should the engineer take? (Choose two.)

A compliance officer needs to confirm which roles have been granted to a specific user across the account, including grants made through role hierarchies. Which Snowflake command should be used to retrieve this information?

A data architect is designing a multi-cluster warehouse to handle unpredictable peak concurrent user sessions. Which scaling property ensures that Snowflake automatically starts additional warehouse clusters to prevent query queuing while maintaining performance?

A data engineer is configuring a Snowpipe to automatically load data from an external stage (Google Cloud Storage) into a Snowflake table. The engineer wants to minimize latency between file arrival and data availability. Which configuration should the engineer use?

A user needs to query data stored in an external stage that points to an Amazon S3 bucket. The user has been granted the necessary privileges on the stage and the storage integration. Which of the following is required to successfully query the data directly from the stage using a SELECT statement?

A data engineer has set up a Snowpipe that continuously loads JSON files from an external stage into a table. The stage references an Amazon S3 bucket with a notification integration. After several days, the engineer notices that new files are not being ingested, even though they exist in the S3 bucket. The Snowpipe is in a RUNNING state and the notification integration is active. What is the MOST likely cause of the missing data?

A Snowflake administrator is configuring a new virtual warehouse for a team of data scientists who will run ad-hoc queries with varying concurrency. The administrator wants to ensure optimal performance and cost-efficiency. Which two configurations should the administrator consider? (Choose two.)

A data engineer runs a long-running aggregation query on a virtual warehouse. Mid-execution, the warehouse is resized from MEDIUM to LARGE. What happens to the query?

A data provider at ACME Corp has created a share named REGIONAL_SALES_SHARE and added a secure view on the SALES table. The provider now wants to grant a specific consumer account, named CONSUMER_ACCT, access to this share. Which command should the provider use to accomplish this?

A data engineer is optimizing a complex query that joins multiple large tables and includes several aggregations. The Query Profile shows a high number of rows spilled to local disk and remote disk. Which TWO actions are most likely to reduce spilling and improve performance? (Choose two.)

An organization wants to create a 'Data Exchange' but only for its internal departments and a few selected vendors. They want to control who can join and what data is listed. Which Snowflake feature is best suited for this?

A user is assigned the 'SECURITYADMIN' role. Which of the following tasks can this user perform?

A developer is running a COPY INTO command to load 1,000 CSV files. The requirement is that if even one row in any file fails due to a data type mismatch, the entire load operation must stop and no data should be committed to the table. Which ON_ERROR setting is required?

A provider has created a share and granted SELECT on a table to the share. The provider now wants to allow the consumer to query the table but also wants to ensure that the consumer cannot see the table's columns that contain personal data. The provider decides to create a secure view that excludes the sensitive columns and grants SELECT on the view to the share. The provider does NOT grant SELECT on the base table to the share. What will the consumer experience when querying the shared view?

A data engineer is loading data from a set of CSV files stored in an external stage into a Snowflake table. The files have a header row, and the engineer wants to skip the header during loading. The engineer also wants to ensure that any rows with missing values in a NOT NULL column are skipped and logged. Which FILE_FORMAT option should be used to skip the header row?

A governance team is designing a strategy to classify and protect data across many databases in a Snowflake account. They want a scalable approach that applies protection consistently without editing each table definition manually. Which two capabilities should the team use to accomplish this goal? (Choose two.)

A data engineer is using the Snowpipe REST API to ingest data from an external stage. They need to ensure that the pipe does not reprocess files that have already been loaded. Which mechanism does Snowpipe use to track which files have been processed?

A Snowflake user needs to query data that is stored in an external table pointing to Parquet files in an external stage. The user wants to optimize query performance by reducing the amount of data scanned. Which feature should the user leverage?

A Materialized View is created on a base table that experiences high churn (frequent inserts, updates, and deletes). What is the most likely impact of this configuration?

A company requires all data at rest within Snowflake to be encrypted using a customer-provided key. Which feature should they implement?

A data engineer is loading a 5 TB compressed CSV file into a Snowflake table using the COPY INTO command. The file is stored in an external stage pointing to an Amazon S3 bucket. The engineer notices that the load is slower than expected and wants to improve performance. Which of the following actions is most likely to improve the load performance?

A data engineer needs to transform a variant column containing an array of objects into a relational format. Which TWO Snowflake features or functions are required to achieve this?

A data engineer is loading a batch of semi-structured JSON files from an external stage into a VARIANT column. They want the COPY INTO command to skip any file that contains malformed JSON without failing the entire load. Which COPY INTO option should they configure?

How does Snowflake's architecture provide the ability to run multiple concurrent workloads without resource contention?

A data engineer needs to load a CSV file into a Snowflake table. The file has a header row, fields are separated by commas, and string values are enclosed in double quotes. The engineer wants to ensure the header row is skipped and the string values are loaded without quotes. Which FILE_FORMAT option should be used to achieve this?

A consumer wants to access a shared database from a provider. The provider has already created the share and granted the necessary privileges. What must the consumer do to access the shared data?

What is the primary benefit of the Snowflake architecture's decoupling of storage and compute?

Which object type in Snowflake is required to store a compiled masking policy before it can be applied to a table column?

A company is migrating from an on-premises Hadoop cluster to Snowflake. They are concerned about how Snowflake manages the lifecycle of micro-partitions and whether they need to manually 'defragment' the storage over time. How does Snowflake address this?

Which Snowflake feature allows for secure, direct communication between a customer's virtual private cloud (VPC) and the Snowflake service without using the public internet?

A provider shares a secure view named SALES_VIEW with a consumer. The view references a table in the provider's database. The provider later renames the underlying table. What is the impact on the consumer's access to SALES_VIEW?

A query is experiencing performance degradation, and the Query Profile indicates 'Remote Disk Spilling'. Which action is the most direct solution to resolve this specific bottleneck?

A data engineer runs a query that performs a large aggregation over a table with billions of rows. The Query Profile shows that the Aggregate operator is spilling to local disk. The engineer wants to eliminate the spilling and improve performance. Which action is most likely to achieve this?

A data engineer needs to transform semi-structured JSON data stored in a VARIANT column into a relational table with separate columns for each attribute. The JSON contains nested objects and arrays. Which Snowflake feature should the engineer use to flatten the arrays and extract the nested attributes in a single SQL statement?

A data administrator needs to identify which users have failed to log in successfully over the last 30 days due to authentication errors. Which Snowflake object should they query?

When a query is submitted to Snowflake, which component is responsible for retrieving the metadata required to build the query execution plan?

Which property of the FILE_FORMAT object should be adjusted if a CSV file uses a semicolon (;) instead of a comma to separate values?

A provider wants to share a database with a consumer account. After creating the share and granting the necessary privileges, which command must the provider run to make the share available to the consumer account?

A user runs a query that was executed by another user 10 minutes ago, and the underlying data has not changed. The query returns results instantly without using a virtual warehouse. Which Snowflake feature explains this behavior?

A data engineer loads a batch of records into a table and then runs a query that filters on a column with a high-cardinality value. The query profile shows partition pruning eliminated most micro-partitions, but the query still scans many rows within the surviving partitions. The table has never been clustered. Which characteristic of Snowflake micro-partitions best explains why pruning was effective at the partition level yet still left many rows to scan?

A data engineer runs a query that joins a large fact table with a small dimension table. The Query Profile shows a Join operation with an exploding number of rows and significant spilling to remote disk. The engineer notices the join condition uses a function on the join key of the large table. Which action is most likely to improve performance?

A security administrator has created a masking policy that replaces the value of a column with a SHA2 hash for users without the role 'HR_ROLE'. The policy is applied to the 'SSN' column of the 'EMPLOYEES' table. A user with the role 'ANALYST_ROLE' queries the table and sees the hashed values. However, when the same user runs a query that includes the 'SSN' column in a WHERE clause, the query returns no results even though matching records exist. What is the most likely cause of this behavior?

A company needs to ensure that data stored in a Snowflake table is encrypted at rest with keys that the company manages and rotates independently. Which Snowflake feature should be configured?

A security administrator needs to ensure that a set of sensitive columns in an existing table are automatically masked for all users except those with the role 'HR_ADMIN'. The masking must be applied without modifying the underlying data. Which Snowflake feature should be used?

A data analyst runs a query that joins a large fact table with a small dimension table. The query is slow, and the Query Profile shows a lot of data movement across the warehouse. Which Snowflake feature is designed to improve performance by automatically broadcasting small tables to all nodes in the warehouse?

Refer to the exhibit. When the COPY INTO command is executed, which field delimiter will Snowflake use to parse the files located in the 'data/' folder of the stage?

Refer to the exhibit. A user with the role 'ANALYST_ROLE' cannot see tables inside the 'sales_db.public' schema despite having 'USAGE' on the database. What is the most likely reason for this access issue?

Refer to the exhibit. A provider has executed these commands to prepare data for a consumer. What is the final mandatory step required before the consumer can access the 'campaign_stats' table?

A data governance team wants to classify data in a table using tags. They create a tag 'PII' and apply it to the 'ssn' column. Later, they need to ensure that only users with the 'PII_READER' role can see the actual values, while all other users see a masked value. They decide to use a tag-based masking policy. Which statement accurately describes how tag-based masking policies work in Snowflake?

A consumer account has created a read-only database named SHARED_SALES from a provider's share and now wants to combine the shared tables with its own local table, LOCAL_REGIONS, in a single query. Which statement about this operation is accurate?

A consumer account has created a database from a share provided by a partner. The consumer wants to ensure that when the provider adds new tables to the share, those tables become available in the consumer's shared database without any manual intervention. Which statement accurately describes the behavior of shared databases in Snowflake?

Refer to the exhibit. A user executes a COPY INTO command and then queries the COPY_HISTORY. Based on the output shown, what most likely happened during the load and what is the current state of the data in the SALES_DATA table?

A provider has shared a database with a consumer. The consumer reports that they can see the shared database but cannot query any tables because they get an error about insufficient privileges. The provider used the following command to create the share: CREATE SHARE MY_SHARE; then added a database and a table to the share. The provider also granted USAGE on the database and SELECT on the table to a role named SHARE_ROLE, and then granted SHARE_ROLE to MY_SHARE. What is the most likely cause of the consumer's issue?

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?

A company wants to share data with a partner without replicating the data. Which Snowflake feature is designed for this purpose?

A Snowflake administrator needs to grant a new analyst the ability to view all tables in the 'SALES' database and query them, but should not be able to modify any data or schema objects. Which sequence of privileges should the administrator grant to meet this requirement with least privilege?

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?

A provider wants to share data with a consumer but needs to ensure that the consumer cannot see the underlying table structure or any data beyond what is explicitly exposed. The provider also wants to prevent the consumer from using the shared data to infer sensitive information. Which Snowflake feature should the provider use?

How does Snowflake's architecture provide high availability and disaster recovery across different geographic locations?

A Python developer wants to upload a Pandas DataFrame to a Snowflake table as efficiently as possible without manually writing files to a local stage. Which function from the Snowflake Connector for Python should be used?

A data engineer is analyzing a slow query that uses a window function with a PARTITION BY clause on a high-cardinality column. The Query Profile shows that the window function is causing significant data shuffling. The engineer wants to reduce the shuffling. Which approach is most likely to improve performance?

Which feature in Snowflake allows you to track and audit all SQL queries executed across the entire account?

How does Snowflake's architecture provide high availability for its internal metadata and security services?

What is the primary role of the Query Optimizer in Snowflake?

What is the primary benefit of Snowflake's 'Multi-Cluster Warehouse' feature when handling highly concurrent workloads?

Which approach is most effective for optimizing an aggregation query that performs a 'GROUP BY' on a high-cardinality column?

A data engineer needs to verify the structure and content of several CSV files staged in an internal Snowflake stage without actually loading the data into the production table or incurring significant compute costs. Which parameter should be used with the COPY INTO command to achieve this specific goal?

Which Snowflake command is used to upload data files from a local file system to an internal stage?

A retail provider shares a secure view that joins a table in its SALES database with a table in its INVENTORY database. A consumer reports that queries against the shared view fail with an authorization error, even though the view itself is granted to the share. The provider confirms both underlying tables exist and the view definition is valid. Which issue most likely explains the failure?

Which component in the Snowflake architecture is responsible for managing the data storage lifecycle, including compaction and file management?

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?

Which function or command should be used to analyze the execution details of a slow-running query in Snowflake?

A data analyst runs a query that filters on a DATE column and returns a small number of rows from a large table. The Query Profile shows a TableScan operator with a high percentage of partitions scanned. The analyst wants to reduce the number of partitions scanned without changing the query result. What should the analyst do?

Which TWO conditions must be met for the Query Acceleration Service (QAS) to boost the performance of a query?

A data engineer has a CSV file on their local machine and wants to load it into a Snowflake table. They do not have access to an external cloud storage bucket and want the simplest path that does not require creating a named internal stage. Which command should they use?

Refer to the exhibit. Which Snowflake security feature is represented by this JSON definition?

A data engineer is loading a 4 GB CSV file from an external stage into a Snowflake table using COPY INTO. The file is compressed with gzip and has a header row. The engineer notices the load is taking longer than expected. Which action is MOST likely to improve performance?

A data engineer is analyzing a slow query and notices that the Query Profile shows a high percentage of time spent in 'TableScan' with many partitions scanned but few rows returned. Which action is most likely to improve performance?

Which feature allows Snowflake to automatically utilize results from previous queries without consuming compute resources?

Which of the following describes the behavior of Snowflake's 'Query Profile' when encountering a join that produces a large Cartesian product?

A consumer has created a database from a share. They want to grant their 'ANALYST' role the ability to query the tables within this shared database. Which privilege should they grant to the role?

Refer to the exhibit. If a user with the 'ANALYST' role queries a table protected by this policy, what will they see?

What is the most efficient way to perform a bulk load of data into Snowflake from a local file system?

An organization wants to simplify its data pipeline by automatically updating target tables whenever new data arrives in the source tables, without manually managing tasks or streams. Which Snowflake feature is designed for this architectural pattern?

A Snowflake administrator needs to grant USAGE on a virtual warehouse named ANALYTICS_WH to a role named ANALYST_ROLE. Which SQL command accomplishes this?

A data architect is designing a multi-cluster warehouse to handle unpredictable bursts in query volume. Which architectural feature ensures that the warehouse automatically scales out to maintain performance without manual intervention?

A provider wants to share a dynamic table with a consumer. The dynamic table is defined on a base table that is not shared. What must the provider ensure for the consumer to query the dynamic table?

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?

When sharing data via the Snowflake Marketplace, what architectural component ensures that the consumer sees the most up-to-date data without the provider having to re-send files?

A Snowflake administrator is configuring a new virtual warehouse for a data science team. The team requires the ability to run multiple concurrent queries without queuing, and they want to minimize credit consumption during idle periods. The administrator needs to configure the warehouse with appropriate settings. Which two actions should the administrator take? (Choose two.)

What is the primary benefit of using a Search Optimization Service (SOS) in Snowflake?

A consumer has created a database from a share provided by a partner. The consumer wants to allow a specific role, ANALYST_ROLE, to query the shared data. Which privilege must the consumer grant to ANALYST_ROLE on the shared database?

A data engineer is configuring Snowpipe to automatically ingest files as they arrive in an external S3 stage. They must ensure the pipe loads only files matching a specific path prefix and that duplicate notifications for the same file do not cause duplicate rows. Which two configurations should they apply? (Choose two.)

A data engineer runs a query that joins a large fact table to a small dimension table. The Query Profile shows a Cartesian join with massive intermediate row counts. The join condition in the SQL is `ON fact.dim_id = dim.id`. The dimension table has a primary key on `id` and the fact table has a foreign key referencing it, but neither constraint is enforced. Which action will most reliably eliminate the Cartesian join and produce the expected result?

A data engineer is optimizing a complex query that joins multiple large tables and applies several aggregations. The Query Profile shows significant time spent in the Join and Aggregate operators, and the warehouse is sized appropriately. Which TWO actions should the engineer take to improve performance? (Choose two.)

A data engineer is building a transformation pipeline that processes semi-structured JSON data. The pipeline needs to extract values from nested objects and arrays and output a relational table. The engineer wants to minimize manual coding and ensure the transformation is maintainable. Which Snowflake feature should the engineer use?

A data scientist wants to create a copy of a 100 TB production table to test a new transformation logic. How does Snowflake’s Zero-copy Cloning architecture handle this request?

A provider is preparing to share a secure view that references a table in another database. The provider must ensure the consumer can query the view but cannot access the base table. Which two actions must the provider take? (Choose two.)

A data engineer is optimizing a transformation pipeline and wants to reduce compute cost and improve performance for queries that repeatedly scan the same large table with different filters. Which TWO Snowflake features or techniques directly support this goal? (Choose two.)

Refer to the exhibit. What happens if a user with the 'ANALYST' role queries the 'ssn' column protected by this policy?

A provider wants to ensure that a Reader Account they created does not exceed a budget of 50 credits per month. What is the most effective way to implement this control?

A data engineer must continuously load Parquet files arriving in an external Azure stage into a Snowflake table. The files have no consistent naming pattern and arrive at unpredictable intervals. The engineer wants Snowflake to detect and load new files automatically without running COPY on a schedule. Which Snowflake feature should be configured?

What is the primary benefit of the Snowflake Query Result Cache compared to a standard warehouse cache?

A security administrator needs to ensure that all data loaded into Snowflake is encrypted using a customer-managed key. Which feature should be configured?

A provider has created a share and added a table. The consumer reports that they can see the shared database and schema but cannot see the table. The provider confirms the table was added with ALTER SHARE ... ADD TABLE. Which additional privilege must the provider grant to the share so the consumer can query the table?

A governance team is implementing data classification in Snowflake and wants to use tags to drive both discovery and enforcement. Which TWO capabilities are provided by Snowflake tags in this context? (Choose two.)

A data engineer is configuring a Snowpipe to automatically ingest files as they arrive in an external stage. They need to set up event notifications from the cloud provider to Snowflake. Which two components are required to enable this automated ingestion? (Choose two.)

An organization wants to restrict access to Snowflake based on the source IP address of the client application. Which object should the administrator configure to enforce this network-level security?

A security team at a healthcare company must guarantee that query results returned from a table named PATIENT_RECORDS are filtered based on the department of the user executing the query, without requiring any changes to existing SQL statements. The policy must evaluate a mapping table that lists each user and their department. Which Snowflake object should be created to meet this requirement?

A company's security team wants to ensure that when a user with the role PII_ANALYST queries a table, only rows where the region column equals 'US' are returned, but they do not want to create separate copies of the table for each region. Which Snowflake feature should they implement?

A data analyst runs a dashboard query that aggregates sales by region for the current month. The query has run successfully many times today, but the analyst notices it is returning results in under a second even though the underlying table is very large. The analyst has not changed the query or the data. Which Snowflake feature is most likely responsible for the fast response?

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?

A data engineer needs to transform semi-structured data stored in a VARIANT column. The engineer wants to extract a scalar value from a JSON object and use it in a relational query. Which Snowflake feature should the engineer use?

An administrator needs to implement a data governance strategy that includes tracking sensitive data and masking PII. Which TWO Snowflake features should be used to achieve this?

When using the Snowflake Connector for Python, which method is most efficient for uploading and loading large local CSV files into a Snowflake table?

A data architect needs to ensure that a specific set of resource-intensive queries always have dedicated compute resources and do not compete with other workloads. Which Snowflake feature should be used?

A data analyst wants to explore semi-structured JSON data stored in a Snowflake table column. They need to extract specific fields and flatten nested arrays. Which Snowflake feature is best suited for this task?

A data engineer is designing a pipeline for a high-frequency stream of small JSON files arriving every minute in an S3 bucket. Why would Snowpipe be preferred over a scheduled COPY INTO command running on a dedicated virtual warehouse?

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?

A data administrator wants to ensure that all data access is audited. Where can they find a list of all tables accessed by a specific user?

A provider uses a reader account to share data with a client that does not have its own Snowflake account. The client now reports that it cannot see newly added tables that the provider granted to the share. The provider confirms the new tables were added to the share with SELECT grants. Which statement explains why the client cannot see the new tables?

A data engineer notices that a query performing a large aggregation is spilling to remote disk. The warehouse is a 2XL multi-cluster warehouse with maximum clusters set to 4. The engineer wants to reduce spilling and improve performance without increasing the warehouse size. Which action should the engineer take?

What is the primary function of the Snowflake metadata store during the query optimization process?

A provider account named PROVIDER_ACCT has created a share named PARTNER_SHARE and granted SELECT on a secure view to it. The provider now needs to make this share available to a specific consumer account named CONSUMER_ACCT. Which single command should the provider execute to accomplish this?

Which Snowflake feature allows a user to download data from a Snowflake table into a local folder on their computer using the SnowSQL command-line interface?

Which TWO of the following statements accurately describe the function of the Snowflake Cloud Services layer?

A provider runs CREATE SHARE sales_share; then GRANT USAGE ON DATABASE sales_db TO SHARE sales_share; GRANT USAGE ON SCHEMA sales_db.public TO SHARE sales_share; GRANT SELECT ON TABLE sales_db.public.orders TO SHARE sales_share; ALTER SHARE sales_share ADD ACCOUNT = consumer_acct; A consumer in consumer_acct queries the shared database and receives results. Six months later, the provider executes REVOKE SELECT ON TABLE sales_db.public.orders FROM SHARE sales_share; What is the immediate effect for the consumer?

A data engineer has a large table SALES_RAW with a VARIANT column PAYLOAD that stores semi-structured JSON. The engineer needs to flatten an array of product objects inside PAYLOAD into separate rows, keeping all other columns intact. Which Snowflake construct should be used in the SELECT statement to achieve this?

A Snowflake analyst runs a query that aggregates sales by region over the last 30 days. The query takes 40 seconds on an X-Small warehouse. The analyst then reruns the exact same query 10 minutes later without any data changes. It completes in 1 second. Which Snowflake feature explains this behavior?

When loading data into Snowflake using the COPY INTO command, what is the impact of using the 'STRIP_OUTER_ARRAY = TRUE' file format option for JSON files?

A consumer has mounted a share from a provider and wants to grant a role in their account the ability to query the shared data. What must the consumer do?

A data architect is designing a solution that requires zero-copy cloning of a production database for testing purposes. The clone must be created quickly and should not duplicate storage until changes are made. Which Snowflake feature should they use?

What is the primary function of the Snowflake 'Result Cache'?

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'?

What happens to the performance of a warehouse when multiple users query the same data simultaneously?

Refer to the exhibit. This JSON policy is part of the setup for a Snowflake Storage Integration. What is the architectural purpose of the 'sts:ExternalId' condition in this cross-account IAM trust relationship?

A data engineer is optimizing a complex query that joins five large tables and includes multiple aggregations. The Query Profile shows significant time spent in the Join and Aggregate nodes, and the engineer wants to reduce the amount of data processed. Which TWO techniques are most appropriate for improving performance in this scenario? (Choose two.)

Refer to the exhibit. What is the impact of changing the MAX_CONCURRENCY_LEVEL parameter on this warehouse?

A data engineer runs a query that joins a large fact table with a small dimension table. The query takes 12 minutes. The engineer notices that the small dimension table is broadcast to all compute nodes, and the fact table is redistributed across nodes. Which Snowflake feature is primarily responsible for this behavior, and what is its main benefit in this scenario?

A data engineer is troubleshooting a COPY INTO command that loads JSON files from a named external stage and is seeing unexpected NULL values in several VARIANT columns. Which TWO actions should the engineer take to diagnose how the JSON is being parsed? (Choose two.)

Which feature of Snowflake allows for the rapid creation of a near-zero-copy clone of a database, schema, or table without consuming additional storage?

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?

A finance analyst runs a monthly report that aggregates 18 months of sales data. The report executes 40 times per day, and each run currently takes 4 minutes on a medium warehouse. The underlying tables are loaded once nightly. Which approach most effectively reduces compute cost for this workload?

A provider is preparing to share data with a consumer using a direct share. The provider wants to ensure that the consumer can query a specific table and also see any future columns added to that table without additional grants. Which two actions must the provider take? (Choose two.)

Which statement best describes the 'Snowflake Data Cloud' architecture's approach to scalability?

A Snowflake account has a virtual warehouse that is configured with AUTO_SUSPEND = 60 seconds and AUTO_RESUME = TRUE. A user runs a query that takes 5 minutes to complete. After the query finishes, the warehouse remains idle for 2 minutes and then suspends. During the idle period, what charges apply?

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?

When designing a role-based access control (RBAC) model, which THREE of the following are recommended best practices?

A data analyst wants to query data stored in an external stage (Amazon S3) without loading it into a Snowflake table. The analyst creates an external table pointing to the stage. Which statement accurately describes how the data is accessed?

An administrator is configuring a virtual warehouse for a data science team that runs unpredictable, long-running training queries. The team wants the warehouse to shut down automatically when idle to save credits, but they also want queries to start immediately when a new request arrives without waiting for a manual resume. Which configuration should the administrator apply?

A data engineer is writing a transformation that reads a VARIANT column containing nested arrays of objects and needs to produce one output row per array element. Which TWO Snowflake features or functions should be used to accomplish this? (Choose two.)

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.)

A company has a table named customer_orders that contains a column storing the customer's full name. A masking policy has been applied to that column. The policy uses CURRENT_ROLE() to compare the executing role against a list of roles allowed to see the raw value. A user with a role that is not in the allowed list runs a query that includes the column in an ORDER BY clause. What does the user see?

A Snowflake administrator needs to provide a data analyst with the ability to read data from a specific table but prevent the analyst from seeing any personally identifiable information (PII) columns. The administrator decides to use a masking policy. Which statement accurately describes the behavior of a masking policy in Snowflake?

A Snowflake user runs a SELECT statement against a large fact table. The query returns results in under a second, and the query profile shows that no warehouse was started. Which Snowflake feature most likely served the result?

A data engineer is loading JSON data from an external stage into a Snowflake table using COPY INTO with a JSON file format. The JSON records contain nested arrays and objects. The engineer wants to load specific elements into separate columns. Which approach should the engineer use?

A data engineer needs to transform semi-structured JSON data stored in a VARIANT column into a relational table. The JSON contains nested arrays and objects. Which Snowflake feature should be used to flatten the arrays into separate rows while preserving the parent-child relationship?

Which Snowflake feature allows for the creation of a 'zero-copy' clone of a database or table?

What is the role of the 'Search Optimization Service' in Snowflake?

What is the benefit of using clustering keys for a table that is queried using range filters?

Which THREE of the following are benefits of Snowflake's micro-partitioning architecture?

Which Snowflake feature allows a user to retrieve the results of a query that was executed 10 minutes ago without consuming additional virtual warehouse credits?

What is the primary purpose of a 'Tag' in Snowflake from a data governance perspective?

Which TWO of the following statements accurately describe the functionality of Snowflake's separation of storage and compute architecture?

A Snowflake provider wants to share a secure view with a consumer. The view is defined on a table in a different database than the one containing the view, and the provider must ensure the consumer cannot access the underlying base table directly. Which action should the provider take?

A Snowflake user executes a complex query that joins a large fact table with several dimension tables. The query takes longer than expected. The user notices that the query plan shows a significant amount of data being spilled to local disk. The warehouse size is currently MEDIUM. What is the most likely cause of the spillage, and what is the recommended action?

A user needs to load data from a CSV file stored in an external stage into a Snowflake table. The CSV file has a header row and uses a pipe (|) as the field delimiter. The user wants to ensure the header row is skipped and the pipe delimiter is recognized. Which FILE_FORMAT options should be specified in the COPY INTO command?

A user with the role 'SYSADMIN' wants to grant the privilege to create databases to a custom role 'DB_CREATOR'. Which command should the SYSADMIN execute?

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?

A data engineer is loading data from a local file system into a Snowflake table using the PUT command to an internal stage, followed by COPY INTO. The engineer notices that some rows are rejected due to data type mismatches. The engineer wants to capture the rejected records and continue loading valid rows. Which COPY INTO option should be used to achieve this?

A user runs a query that filters on a column with a very high cardinality, such as a timestamp with millisecond precision. The table is extremely large and is not clustered. What is the most likely impact on query performance?

An administrator discovers that a former employee's user account still exists and is still granted the ANALYST_ROLE. The administrator needs to immediately prevent the account from authenticating while preserving the account and its historical query metadata for an ongoing audit. Which action should the administrator take?

Which of the following describes the correct order of precedence for role inheritance in Snowflake?

A data engineer needs to load a 4.2 GB uncompressed CSV file from an internal stage into a Snowflake table. The file cannot be split because the CSV has embedded newlines within quoted fields. The engineer wants to maximize load performance. What should the engineer do?

To publish a data listing on the Snowflake Marketplace and make it available to all Snowflake customers, which TWO requirements must the provider fulfill?

When loading semi-structured data like Parquet into a Snowflake table, what is a primary advantage of using Parquet over CSV for the ingestion process?

Which of the following describes the purpose of 'Time Travel' from a data governance perspective?

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?

A data steward needs to ensure that a column containing email addresses is masked for all users except those with the role 'COMPLIANCE_OFFICER'. The masking should show a fixed string '****' for unauthorized users. Which Snowflake feature should be used?

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?

An organization requires that specific sensitive columns in a table be masked for all users except those in the 'DATA_STEWARD' role. Which mechanism should the architect implement to enforce this policy efficiently?

A user with the role DATA_ANALYST has been granted the USAGE privilege on a database and schema, but when they try to query a table in that schema, they receive an error that the table does not exist. The table exists and is owned by the role DATA_ENGINEER. What is the most likely cause of this issue?

Refer to the exhibit. What is the primary advantage of using the VARIANT data type in this scenario?

A platform team is evaluating Snowflake's Time Travel feature for a production database. They need to understand which capabilities Time Travel provides for recovering from accidental data changes and for querying historical data. (Choose two.)

A provider wants to share a database with a consumer but must prevent the consumer from seeing the database's table and schema names in its own account. The provider also wants the consumer's queries to be isolated from the provider's own warehouse usage. Which approach satisfies both requirements?

Which Snowflake architecture feature enables seamless data sharing between two different Snowflake accounts without the need to copy or move data?

A user runs a query that filters on a column with a high cardinality and the table is not clustered. The query scans a large number of micro-partitions. Which action would most directly reduce the number of micro-partitions scanned?

Which TWO statements are true regarding the behavior and management of External Tables in Snowflake?

A provider has created a share and added a table. The provider now wants to revoke access for a specific consumer account without affecting other consumers. Which command should the provider use?

A data engineer needs to transform a JSON column stored in a VARIANT type into a relational table. The JSON contains a top-level array of objects, each with keys `id`, `name`, and `tags`, where `tags` is itself an array of strings. The engineer wants each object to become a row, with the `tags` array flattened into a separate column containing one tag per row. Which combination of Snowflake functions will produce one row per tag while preserving `id` and `name`?

A Snowflake data provider creates a Reader Account for a consumer who does not have a Snowflake account. Who is responsible for the compute costs incurred by the queries executed within this Reader Account?

Which type of Snowflake stage is automatically created for every user and cannot be dropped or altered?

A developer needs to flatten a VARIANT column named payload that contains a nested JSON array of order line items into individual rows, preserving the parent order attributes alongside each line item. Which Snowflake construct accomplishes this in a single SELECT statement?

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?

A provider's account is named PROVIDER_ACCT and it has created a share named PARTNER_SHARE that already contains a secure view. The provider now runs: ALTER SHARE PARTNER_SHARE ADD ACCOUNTS = CONSUMER_ACCT; What is the effect of this command in the provider's environment?

A provider wants to share data with a consumer but needs the shared data to reflect changes in the provider's source tables in near real time. The provider also wants to avoid granting the consumer access to the underlying base tables. Which approach best meets these requirements?

Which Snowflake feature provides an 'always-on' mechanism to allow users to instantly query the state of data as it existed at any point in the past within a defined retention period?

A query that previously ran in 5 seconds now takes 2 minutes. The Query Profile shows that most of the time is spent in 'Remote Disk I/O'. What is the most likely cause for this performance degradation?

A provider is preparing to share data with a consumer via a direct share. The provider wants to ensure that the consumer can access the data but cannot see the underlying table structure or any other objects in the database. Which two actions should the provider take? (Choose two.)

A data analyst needs to unload the results of a query from a Snowflake table to a local machine. The analyst wants to use the Snowflake web interface (Snowsight) to download the data as a CSV file. Which of the following is the correct approach?

A query is failing with the error 'Can\'t compile the query as it is too large'. Which action is most likely to resolve this issue while maintaining the query's logical intent?

A data provider wants to share a subset of data with a specific consumer while ensuring the consumer cannot see any other tables in the same database. What is the most secure and efficient method to achieve this?

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?

A data engineer needs to transform a table by unpivoting columns Q1, Q2, Q3, Q4 into rows with a quarter label and sales amount. Which Snowflake SQL construct is designed for this task?

What is the purpose of the PURGE = TRUE option in a COPY INTO command?

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?

Which TWO of the following tasks are performed by the Snowflake Cloud Services layer?

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?

What is the primary function of the 'SECURITYADMIN' role in Snowflake's RBAC model?

A provider wants to share data with a consumer account, but the consumer's account is in a different Snowflake region. The provider's data is in the US West (Oregon) region, and the consumer is in the EU (Frankfurt) region. The provider needs the consumer to access the data with low latency. Which Snowflake feature should the provider use?

A developer is using a Stream on a table to capture changes. If the developer executes a DML statement that consumes the data from the Stream within a transaction, what happens to the Stream's offset after the transaction commits?

Refer to the exhibit. Given the state described in the JSON, what happens when a user executes the query?

A provider shares a database with a consumer using a direct share. The consumer reports that queries against the shared database fail with an error indicating the database does not exist, even though the share was successfully created and granted to the consumer account. The provider confirmed that the share contains the necessary tables and that the consumer account has been added to the share. What is the most likely cause of the issue?

A company is using an external S3 stage to load data daily. They want Snowflake to automatically delete the source files from the S3 bucket only after they have been successfully loaded into the table. Which COPY INTO option should they enable?

How does Snowflake's architecture handle the storage and querying of semi-structured data like JSON to optimize performance?

An analyst runs the same dashboard query every morning at 08:00. The query reads from tables that are loaded by an ELT job finishing at 07:30. The analyst complains that the first run takes 40 seconds while subsequent identical runs during the day return in under a second. The data in the tables does not change between the first run and the later runs. Which mechanism explains the speedup?

What is the primary role of the 'ORGANIZATIONADMIN' role in Snowflake?

Refer to the exhibit. Based on the JSON configuration for this Snowflake listing, who will be able to discover and access this data?

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

When considering the implementation of a clustering key on a large table, which TWO scenarios indicate that clustering will provide the most significant performance benefit?

Which component of the Snowflake architecture is responsible for managing data integrity, query optimization, and transaction management?

A data engineer is using the COPY INTO command to load data from an external stage into a Snowflake table. The engineer wants to ensure that the load operation does not fail if some files in the stage have already been loaded previously. The engineer also wants to avoid reloading files that have already been processed. Which COPY INTO option should be used to achieve this?

A provider wants to share data with a consumer using a Direct Share. Which two statements accurately describe the consumer's experience after the share is mounted? (Choose two.)

A company wants to monetize its data by making it available to other Snowflake customers through a centralized, public platform. Which Snowflake feature should they use?

A company wants to enforce that all data in a specific schema is protected by a data classification tag before it can be queried by analysts. The security team has created a tag named DATA_CLASS and a masking policy associated with that tag. Analysts report they can still see raw values in some columns. What is the most likely cause?

A data engineer needs to transform semi-structured data stored in a VARIANT column that contains an array of JSON objects into a relational table with one row per object. The engineer wants to use a Snowflake function that can expand the array into multiple rows. Which function should be used?

A developer is building a Change Data Capture (CDC) pipeline using Snowflake. Which TWO features are required to ensure that only new or modified data is processed and that the processing logic runs automatically whenever data arrives?

A governance team needs to implement data classification and access control for a new table containing sensitive data. They want to (1) tag columns with a sensitivity level, and (2) enforce that only users with a specific role can see the unmasked data. Which two Snowflake features should they use together to achieve these goals? (Choose two.)

For optimal parallel loading performance using a Snowflake virtual warehouse, what is the generally recommended compressed file size range for data files in a stage?

Which component is responsible for orchestrating the lifecycle of a virtual warehouse, including start and stop operations?

A data engineer is tuning a query that filters on a VARCHAR column `status` with values such as 'ACTIVE', 'INACTIVE', and 'PENDING'. The table is very large and the query currently performs a full table scan. The engineer wants to reduce the amount of data scanned by using a search optimization service. Which action should the engineer take?

Refer to the exhibit. Based on the query profile metadata provided, which Snowflake feature most likely contributed to the high efficiency of this query?

A Snowflake user needs to query data stored in an external cloud storage location (e.g., Amazon S3) without loading it into Snowflake tables. They want to minimize data movement and cost. Which Snowflake feature should they use?

A data governance lead is configuring tag-based masking so that columns tagged with a PII classification are automatically protected. The lead creates a tag named PII_CLASSIFICATION and a masking policy, then applies the tag to several columns. Later, an analyst queries a tagged column and sees unmasked values. The masking policy was attached to the tag using ALTER TAG ... SET MASKING POLICY. What is the most likely cause?

Which administrative role should be used to manage the lifecycle of warehouses and databases, while strictly avoiding the management of users and roles?

A data engineer is configuring a Snowflake external stage that points to an Amazon S3 bucket. The bucket is in the same region as the Snowflake account. The engineer wants to avoid embedding long-lived AWS credentials in the stage definition and instead use a secure, temporary credential mechanism. Which authentication method should be used for the external stage?

An administrator needs to restrict access to sensitive PII data. Which TWO of the following are valid approaches to implement governance in Snowflake?

Refer to the exhibit. Why were zero credits consumed for the query execution?

When a virtual warehouse spills data to local disk, what does this indicate about the query and resource allocation?

A data steward needs to review the history of changes made to a table, including which columns were added or dropped and when, for an audit that covers the past 60 days. The table is in a database that has a data retention period of 90 days. Which Snowflake feature should the steward use to retrieve this information?

A data engineer is configuring a Snowflake storage integration to allow Snowflake to access an external S3 bucket. The engineer needs to ensure that the integration has the necessary permissions to read and write data. Which two actions must the engineer perform? (Choose two.)

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

A data engineer needs to ensure that sensitive PII columns are masked for all users except for a specific group of HR analysts. Which Snowflake feature is the most efficient and scalable solution to implement this requirement?

A data engineer runs a query that joins a large fact table to a small dimension table, but the Query Profile shows a Cartesian join instead of the intended inner join. The join predicate in the SQL is `ON fact.dim_id = dim.id`. Which action will most reliably correct the plan while preserving the query's result?

A consumer has mounted a shared database named 'SALES_DATA_SHARED'. They need to add a new column to one of the tables in this database to store local annotations. Which statement best describes the outcome if they attempt this?

Which of the following describes the purpose of 'Object Tagging' in Snowflake?

Refer to the exhibit. Based on the configuration provided for the ANALYTICS_WH, how will Snowflake manage clusters when multiple users start submitting queries at the same time?

An administrator wants to ensure that a specific role can only access Snowflake from the corporate office IP range. Which tool should they use?

A provider has a share containing a secure view that references a table in the same database. The provider wants the share to stop being visible to a specific consumer account but keep the share and its grants intact for other consumers. Which action accomplishes this?

What is the role of the Cloud Services layer within the Snowflake architecture when a user submits a query?

A security administrator for a Snowflake account needs to grant the role FINANCE_ANALYST to a user named Priya. The administrator also wants Priya to be able to grant FINANCE_ANALYST to other users in the future. Which SQL statement should the administrator execute?

Snowflake's optimizer uses various techniques to improve query performance dynamically. Which TWO of the following are examples of Adaptive Query Optimization?

A data provider at a healthcare analytics company wants to share live patient-readmission metrics with a partner hospital. The partner must query the data with low latency and the provider must retain full ownership and control of the underlying tables. The provider creates a share and grants USAGE on a secure view to the share. Which action must the provider perform next so the partner account can mount and query the shared data?

A data engineer is working with a table that contains a VARIANT column storing arrays of JSON objects. The engineer needs to produce a report that lists each object's attributes in separate rows. Which Snowflake function should the engineer use to transform the array into multiple rows?

A data engineer loads JSON files from an external Azure stage into a table with a single VARIANT column. The JSON documents are newline-delimited, and each line is a separate object. Which file format type and option should be specified to correctly parse one JSON object per line?

A data engineer is tuning a query that joins a 900 million row fact table to a 2 million row dimension table. The dimension table is fully contained in the fact table's join-key range, and the join key is not the clustering key of either table. The engineer wants to eliminate the shuffle of the fact table across warehouse nodes. Which approach best achieves this?