63 free Snowflake SnowPro Advanced Architect ARA-C01 practice questions from the CertStash pack of 253, each with the correct answer and a full explanation. The rule is the same for every exam: ten free on the product page, 63 free here, all 253 in the pack, and each step includes the one before it.
Get all 253 questions (US$39) · Download the first 10 as a PDF
Question 1
What built-in Snowflake features make use of the change tracking metadata for a table? (Choose two.)
Show answer and explanation
Correct answer: C, D. The CHANGES clause; A STREAM object
Snowflake's change tracking metadata is used by STREAM objects, which capture row-level changes (inserts, updates, deletes) for downstream consumption. The CHANGES clause is also a built-in feature that directly queries the change tracking metadata to retrieve historical changes to a table. Both of these features read the table's change tracking metadata to provide change data capture natively in Snowflake, without external tools.
Why the other options are wrong
- A. The MERGE command changes table data but does not read the change tracking metadata; only streams and the CHANGES clause do.
- B. UPSERT is not a native Snowflake command; the MERGE command is used for similar functionality.
- E. CHANGE_DATA_CAPTURE is not a built-in Snowflake command; change tracking is accessed through STREAM objects and the CHANGES clause.
Question 2
When using the Snowflake Connector for Kafka, what data formats are supported for the messages? (Choose two.)
Show answer and explanation
Correct answer: C, D. Avro; JSON
The Snowflake Connector for Kafka supports Avro and JSON as the primary message data formats. The connector provides converters for these formats and loads each message into a VARIANT column (RECORD_CONTENT), with message metadata in RECORD_METADATA; JSON messages do not need to carry a schema.
Why the other options are wrong
- A. CSV is not supported by the Kafka connector as a message format.
- B. XML is not supported by the Kafka connector for message serialization.
- E. Parquet is not a supported format for the Snowflake Kafka connector; it is primarily used for file-based data storage.
Question 3
At which object type level can the APPLY MASKING POLICY, APPLY ROW ACCESS POLICY and APPLY SESSION POLICY privileges be granted?
Show answer and explanation
Correct answer: A. Global
The APPLY MASKING POLICY, APPLY ROW ACCESS POLICY, and APPLY SESSION POLICY privileges are granted at the global (account) level only. These are account-wide privileges that control a user's ability to apply policies to any objects within the account, rather than being scoped to specific databases, schemas, or tables.
Why the other options are wrong
- B. Database level is not where these policy privileges are granted; they operate at the account level.
- C. Schema level is not the correct scope for these policy-related privileges.
- D. Table level is not where these privileges can be granted; policy application authority is determined at the global level.
Question 4
An Architect uses COPY INTO with the ON_ERROR=SKIP_FILE option to bulk load CSV files into a table called TABLEA, using its table stage. One file named file5.csv fails to load. The Architect fixes the file and re-loads it to the stage with the exact same file name it had previously. Which commands should the Architect use to load only file5.csv file from the stage? (Choose two.)
Show answer and explanation
Correct answer: B, C. COPY INTO tablea FROM @%tablea;; COPY INTO tablea FROM @%tablea FILES = ('file5.csv');
To load only file5.csv after it has been fixed and re-uploaded with the same filename, the Architect should use the FILES parameter to explicitly specify which file to load, or use a basic COPY INTO command without the FORCE parameter. Option C explicitly targets file5.csv using the FILES clause. Option B also works because load metadata only skips files that loaded successfully; file5.csv failed and was re-uploaded with new contents, so a plain COPY INTO loads it while skipping the files already loaded.
Why the other options are wrong
- A. RETURN_FAILED_ONLY only controls whether the statement output lists just the files that failed to load; it does not select which files are loaded.
- D. FORCE = TRUE would reload all previously loaded files, not just the fixed file5.csv.
- E. NEW_FILES_ONLY is not a valid COPY INTO parameter; skipping files that were already loaded is the default behavior.
- F. MERGE = TRUE is not a standard COPY INTO parameter; MERGE functionality is used with different syntax.
Question 5
A large manufacturing company runs a dozen individual Snowflake accounts across its business divisions. The company wants to increase the level of data sharing to support supply chain optimizations and increase its purchasing leverage with multiple vendors. The company’s Snowflake Architects need to design a solution that would allow the business divisions to decide what to share, while minimizing the level of effort spent on configuration and management. Most of the company divisions use Snowflake accounts in the same cloud deployments with a few exceptions for European-based divisions. According to Snowflake recommended best practice, how should these requirements be met?
Show answer and explanation
Correct answer: D. Deploy a Private Data Exchange and use replication to allow European data shares in the Exchange.
For a company whose accounts are mostly in the same cloud deployment, with a few European accounts in other deployments, and that wants decentralized sharing decisions with minimal management overhead, a Private Data Exchange combined with replication is the recommended approach. This allows each division to manage their own shared data through the Private Exchange while replication ensures that European accounts (in different cloud deployments) can participate by having data replicated to their respective regions and cloud providers.
Why the other options are wrong
- A. Migrating the European accounts and managing point-to-point shares among a dozen accounts adds the configuration and management effort the company wants to minimize, whereas replication lets those accounts join a Private Data Exchange from where they already are.
- B. Plain data shares cannot reach accounts in other deployments, so the European divisions also need replication to take part in the Exchange.
- C. The Snowflake Marketplace is designed for external commercial sharing, not internal multi-account orchestration, and invoker_share() is not the appropriate function for this use case.
Question 6
A user has the appropriate privilege to see unmasked data in a column. If the user loads this column data into another column that does not have a masking policy, what will occur?
Show answer and explanation
Correct answer: A. Unmasked data will be loaded in the new column.
When a user with appropriate privileges to see unmasked data loads data from a masked column into a new unmasked column, the unmasked data is transferred as-is. The masking policy applies only to the original column where it is defined; the policy does not follow the data to the new column. The user's privilege to see unmasked data means they access the actual unmasked values during the load operation.
Why the other options are wrong
- B. Masked data will not be loaded; the user's privilege to see unmasked data means they access and load the actual values.
- C. The new column has no masking policy, so all data visibility is determined by standard object privileges, not by masking policies.
- D. Unmasked data is loaded into the new column and visibility is controlled by standard table and column privileges, not by masking restrictions.
Question 7
How can an Architect enable optimal clustering to enhance performance for different access paths on a given table?
Show answer and explanation
Correct answer: B. Create multiple materialized views with different cluster keys.
To optimize clustering for different access paths on a single table, an Architect should create multiple materialized views, each with different clustering keys tailored to specific access patterns. This approach allows each view to be optimized for its particular query patterns without modifying the base table structure.
Why the other options are wrong
- A. A table cannot have multiple clustering keys; only one clustering key can be defined per table.
- C. Super projections are a concept from other database systems, not a Snowflake feature, and Snowflake does not derive clustering from projections.
- D. A single clustering key containing every column used in the access paths would not optimize the different access paths and can degrade pruning for specific query patterns.
Question 8
Company A would like to share data in Snowflake with Company B. Company B is not on the same cloud platform as Company A. What is required to allow data sharing between these two companies?
Show answer and explanation
Correct answer: C. Setup data replication to the region and cloud platform where the consumer resides.
When companies on different cloud platforms need to share data in Snowflake, replication must be set up to replicate the shared data to the target cloud platform and region where the consumer resides. Snowflake's direct data sharing works only within the same region and cloud platform; sharing across regions or clouds requires replication as an intermediary mechanism.
Why the other options are wrong
- A. Writing shared data out to cloud storage is not required; Snowflake shares data across cloud platforms by replicating it to the consumer's region and cloud platform.
- B. Views can be shared across cloud platforms when used with replication; persistence is not a requirement.
- D. Snowflake supports sharing across cloud platforms by replicating the shared database to the consumer's region and cloud platform, so the two companies do not need to use the same provider.
Question 9
What are some of the characteristics of result set caches? (Choose three.)
Show answer and explanation
Correct answer: B, C, E. Snowflake persists the data results for 24 hours; Each time persisted results for a query are used, a 24-hour retention period is reset; The retention period can be reset for a maximum of 31 days.
Snowflake's result set cache persists query results for 24 hours, and each time the cached results are reused, the 24-hour retention period resets, ensuring frequently accessed results remain cached. The cached data does not contribute to storage costs, distinguishing it from data stored in tables. Time Travel cannot be executed against the result set cache as it only stores final query results. Resetting the retention period in this way can extend retention up to a maximum of 31 days from when the query was first executed. The result set cache is shared across all warehouses in the account, allowing any warehouse to benefit from cached results.
Why the other options are wrong
- A. Time Travel queries cannot be executed against result set cache; it only stores final query results without historical versioning.
- D. Result set cache data does not contribute to storage costs; it is separate from persistent data storage billing.
- F. The result set cache is shared across all warehouses in the account, not isolated to individual warehouses.
Question 10
Which organization-related tasks can be performed by the ORGADMIN role? (Choose three.)
Show answer and explanation
Correct answer: B, C, F. Creating an account; Viewing a list of organization accounts; Enabling the replication of a database
The ORGADMIN system role manages the accounts that belong to an organization. It creates new accounts with CREATE ACCOUNT, lists every account in the organization with SHOW ACCOUNTS, and enables replication for accounts by calling SYSTEM$GLOBAL_ACCOUNT_SET_PARAMETER to set ENABLE_ACCOUNT_DATABASE_REPLICATION, which must be in place before databases can be replicated between those accounts. These three tasks are the core organization-level duties that Snowflake assigns to ORGADMIN.
Why the other options are wrong
- A. The organization name cannot be changed by ORGADMIN with a SQL command; changing it requires a request to Snowflake Support.
- D. Renaming an account changes that single account's identifier and URL, which is an account lifecycle change rather than one of the organization-wide duties of creating, listing and enabling replication for accounts.
- E. DROP ACCOUNT only marks an account as dropped for a grace period of 3 to 90 days, and Snowflake itself performs the permanent deletion when that period ends, so ORGADMIN does not directly delete the account.
What people say about the full ARA-C01 pack 5.0 from 2
Passed on the first attempt.
Question 11
A retail company has 2000+ stores spread across the country. Store Managers report that they are having trouble running key reports related to inventory management, sales targets, payroll, and staffing during business hours. The Managers report that performance is poor and time-outs occur frequently. Currently all reports share the same Snowflake virtual warehouse. How should this situation be addressed? (Choose two.)
Show answer and explanation
Correct answer: B, C. Configure a dedicated virtual warehouse for the Store Manager team; Configure the virtual warehouse to be multi-clustered.
To address poor performance and timeouts for store managers sharing a warehouse, a dedicated virtual warehouse for the Store Manager team (B) isolates their workload from other users and prevents resource contention. Additionally, configuring the warehouse as multi-clustered (C) automatically scales out or in by adding or removing clusters based on query demand, allowing multiple concurrent queries to run in parallel without queueing. This combination provides both isolation and elasticity. Using BI tools adds complexity without addressing the core resource issue. Resizing to 4-XL helps a single query run faster but doesn't improve concurrency. Deferring reports to off-hours doesn't meet business requirements.
Why the other options are wrong
- A. The timeouts come from many reports competing for one shared warehouse, and an in-memory BI layer neither adds warehouse capacity nor isolates the Store Managers' queries.
- D. Sizing to 4-XL mainly speeds up individual queries and does not add clusters to absorb many concurrent queries.
- E. Deferring reports to off-hours conflicts with the managers' need to run these reports during business hours.
Question 12
An Architect needs to load a CSV file from an external stage to a Snowflake target table. Initial investigation shows that more than 100 new CSV files can be created during the week on certain days at different times. The files are well formatted, partitioned, and within the recommended sizes, however file modifications do occur on existing files. The modifications happen when new data rows get inserted into the existing files (with no file name change). The company wants fresh data uploaded daily into a target table, with no duplicates. What solution will MINIMIZE Snowflake credit usage and management overhead?
Show answer and explanation
Correct answer: D. Use an external table, and a scheduled task using the ALTER EXTERNAL TABLE ... REFRESH command. Then use streams and tasks to load new records into the target table.
Snowpipe and COPY track files by name and load metadata, so rows appended to an existing file without a name change are either skipped or cause the whole file to be reloaded, creating gaps or duplicates. An external table registers file metadata and, when ALTER EXTERNAL TABLE ... REFRESH runs, picks up both new and modified files. An insert-only stream on the external table then records the rows of new and rewritten files as inserts, and a task can load them into the target table once a day. This needs no continuous ingestion and no custom notification handling, which keeps credit usage and management overhead low; because an insert-only stream records every row of a rewritten file as an insert, the task's load should match on a key so rows already in the target table are not duplicated.
Why the other options are wrong
- A. Snowpipe does not reload a file whose name is unchanged after modification, so appended rows would be missed regardless of the notification events.
- B. Snowpipe skips files that were already loaded under the same name, so modified files would not deliver their new rows to the stream.
- C. Calling insertFiles through the REST API still relies on Snowpipe load metadata, which ignores rows appended to an already loaded file name.
Question 13
An Architect is designing a file ingestion recovery solution. The project will use an internal named stage for file storage. Currently, in the case of an ingestion failure, the Operations team must manually download the failed file and check for errors. Which downloading method should the Architect recommend that requires the LEAST amount of operational overhead?
Show answer and explanation
Correct answer: B. Use the GET command in SnowSQL to retrieve the file.
The GET command in SnowSQL requires the least operational overhead for retrieving files from internal named stages. It is a simple, native SQL command that can be executed directly without requiring additional connectors, API integrations, or third-party tools. SnowSQL is already available to operations teams and requires minimal setup.
Why the other options are wrong
- A. Using the Python Connector requires additional software installation, dependencies, and code development, increasing operational overhead.
- C. The GET command cannot be run from Snowsight worksheets; it must be run from SnowSQL or a Snowflake driver.
- D. The Snowflake SQL API does not support the GET command, so it cannot simply download a file from an internal stage and would require custom client code, adding the overhead the stem asks to avoid.
Question 14
A healthcare company wants to share data with a medical institute. The institute is running a Standard edition of Snowflake; the healthcare company is running a Business Critical edition. How can this data be shared?
Show answer and explanation
Correct answer: D. Set the share_restriction parameter on the shared object to false.
By default, a Business Critical account cannot share data with a lower-edition account. An ACCOUNTADMIN can override this by setting SHARE_RESTRICTIONS to FALSE on the share, which allows the Standard edition consumer to be added.
Why the other options are wrong
- A. A provider cannot change another account's Snowflake edition.
- B. Sharing from Business Critical to a lower edition is blocked by default, not allowed.
- C. Snowflake support does not execute shares on a customer's behalf; the provider controls this setting.
Question 15
A company has an inbound share set up with eight tables and five secure views. The company plans to make the share part of its production data pipelines. Which actions can the company take with the inbound share? (Choose two.)
Show answer and explanation
Correct answer: C, E. Create a table from the shared database; Create a table stream on the shared table.
With an inbound share, the consuming account can create its own table from the shared data (for example, CREATE TABLE ... AS SELECT from the shared database into a local database) and can create streams on shared tables once the provider has enabled change tracking. Creating a table copies the data into the consumer account for independent use, while streams on shared tables enable change data capture for production data pipelines. These actions preserve the original shared data while enabling independent consumption and processing.
Why the other options are wrong
- A. Objects in a shared database are read-only and cannot be cloned; the consumer must copy the data into a table in its own database instead.
- B. Grant permissions are managed by the share provider, not the consumer; consumers cannot modify share permissions.
- D. Creating new views inside a shared database is not permitted; consumers can only use objects explicitly shared by the provider.
Question 16
What does a Snowflake Architect need to consider when implementing a Snowflake Connector for Kafka?
Show answer and explanation
Correct answer: A. Every Kafka message is in JSON or Avro format.
The Snowflake Connector for Kafka passes each Kafka message to Snowflake in JSON or Avro format, so an Architect must confirm that producers write messages in one of those two formats before the connector can load them. The connector writes each message into a table with a RECORD_CONTENT column holding the message and a RECORD_METADATA column holding details such as the topic, partition and offset, both of type VARIANT. Messages in other formats cannot be parsed by the connector's JSON or Avro converters, which makes message format a key design consideration.
Why the other options are wrong
- B. The default Kafka topic retention period is 7 days (168 hours), not 14 days.
- C. The Kafka connector does not use basic username and password authentication; it authenticates to Snowflake with key pair authentication.
- D. The connector creates one pipe for each partition of a topic, not one pipe per topic, so the per-topic description of the pipes is inaccurate.
Question 17
A healthcare company is deploying a Snowflake account that may include Personal Health Information (PHI). The company must ensure compliance with all relevant privacy standards. Which best practice recommendations will meet data protection and compliance requirements? (Choose three.)
Show answer and explanation
Correct answer: A, B, D. Use, at minimum, the Business Critical edition of Snowflake; Create Dynamic Data Masking policies and apply them to columns that contain PHI; Use the External Tokenization feature to obfuscate sensitive data.
Business Critical edition provides enhanced security features required for PHI compliance (Option A). Dynamic Data Masking policies enable column-level protection of PHI columns (Option B), enforcing access policies at query time. External Tokenization (Option D) allows sensitive data to be replaced with tokens, adding an additional layer of protection for highly sensitive health information. Internal Tokenization is not a standard Snowflake feature for this purpose. Rewriting queries based on role is inefficient and not a best practice. Avoiding all data sharing is impractical in healthcare environments where partner organizations legitimately need access.
Why the other options are wrong
- C. Internal Tokenization is not a recognized Snowflake feature for data obfuscation.
- E. Rewriting queries protects PHI only in the queries that were rewritten, so any other query or tool can still project the columns, unlike a masking policy that is enforced on the column itself.
- F. Avoiding all partner data sharing is not feasible for healthcare organizations and is not a practical compliance recommendation.
Question 18
When using the COPY INTO [table] command with the CSV file format, how does the MATCH_BY_COLUMN_NAME parameter behave?
Show answer and explanation
Correct answer: A. It expects a header to be present in the CSV file, which is matched to a case-sensitive table column name.
When MATCH_BY_COLUMN_NAME is used with CSV file format, Snowflake expects a header row in the CSV file. The column names in this header are matched against table column names in a case-sensitive manner. This allows flexible column ordering in the file, as Snowflake uses the header names rather than positional order to determine which CSV column maps to which table column. The parameter is not ignored, does not cause errors, and does not produce warnings about unmatched columns.
Why the other options are wrong
- B. MATCH_BY_COLUMN_NAME is not ignored; it actively controls column mapping behavior.
- C. The command does not return an error when a valid CSV header is present.
- D. The command does not return unmatched column warnings; table columns with no matching file column are loaded as NULL.
Question 19
A company is using Snowflake in Azure in the Netherlands. The company analyst team also has data in JSON format that is stored in an Amazon S3 bucket in the AWS Singapore region that the team wants to analyze. The Architect has been given the following requirements: 1. Provide access to frequently changing data 2. Keep egress costs to a minimum 3. Maintain low latency How can these requirements be met with the LEAST amount of operational overhead?
Show answer and explanation
Correct answer: C. Copy the data between providers from S3 to Azure Blob storage to collocate, then use Snowpipe for data ingestion.
Copying data from AWS S3 Singapore to Azure Blob storage in the Netherlands collocates the data with the Snowflake account, minimizing egress costs and latency while supporting frequently changing data through Snowpipe ingestion. This approach balances all requirements: low operational overhead compared to custom pipelines, minimal cross-region egress charges, and low-latency queries against local data.
Why the other options are wrong
- A. A materialized view stores its results in Snowflake, but it must be refreshed from the files in the remote S3 bucket in AWS Singapore, so every change to the frequently changing data is read cross-cloud from the Azure Netherlands account and incurs egress costs.
- B. An external table on the S3 bucket in AWS Singapore is read cross-cloud from the Azure Netherlands account on every copy, and the transient tables hold only the data as of the last copy, so frequently changing data goes stale between reruns.
- D. An external table reads the Blob files at query time, which Snowflake documents as slower than querying data loaded into native tables, so it fails the low-latency requirement that loading with Snowpipe meets.
Question 20
A company has a Snowflake environment running in AWS us-west-2 (Oregon). The company needs to share data privately with a customer who is running their Snowflake environment in Azure East US 2 (Virginia). What is the recommended sequence of operations that must be followed to meet this requirement?
Show answer and explanation
Correct answer: C. 1. Create a new Snowflake account in Azure East US 2 (Virginia) 2. Set up replication between AWS us-west-2 (Oregon) and Azure East US 2 (Virginia) for the database objects to be shared 3. Create a share and add the database privileges to the share 4. Alter the share and add the customer's Snowflake account to the share
When sharing data between different cloud providers (AWS to Azure) and different regions, Snowflake requires database replication to establish the necessary infrastructure in the target region and cloud. The sequence is: first create a Snowflake account in the target cloud and region, then set up replication to synchronize the database objects, and finally create a share with privileges and add the customer's account. This ensures data locality compliance and enables proper cross-cloud sharing.
Why the other options are wrong
- A. Marketplace listings are not the appropriate mechanism for private direct sharing between specific accounts.
- B. Omitting the replication step fails to establish the database in the target cloud and region, so the share cannot work.
- D. Reader accounts cannot be created in a different cloud provider from the source account; they must reside in the same cloud environment.
Question 21
Instead of updating a particular row, a user forgot to put in the where clause and accidentally updated the entire table, named customer. The variable error_update_query_id is set to query_id for the mistaken update statement. How can the current version of the table customer be preserved into the table CUSTOMER_ERROR, and the table CUSTOMER be restored to the version that was in place prior to the update?
Show answer and explanation
Correct answer: B. CREATE TABLE CUSTOMER_ERROR CLONE CUSTOMER BEFORE(STATEMENT => $error_update_query_id) ALTER TABLE CUSTOMER SWAP WITH CUSTOMER_ERROR;
CREATE TABLE CUSTOMER_ERROR CLONE CUSTOMER BEFORE(STATEMENT => $error_update_query_id) uses Time Travel to create a zero-copy clone of CUSTOMER as it existed just before the mistaken update. ALTER TABLE CUSTOMER SWAP WITH CUSTOMER_ERROR then exchanges the two tables in a single metadata operation. After the swap, CUSTOMER holds the restored pre-update data and CUSTOMER_ERROR holds the current, incorrectly updated version, which meets both requirements without copying any data.
Why the other options are wrong
- A. CREATE TABLE ... AS requires a SELECT query, so CREATE TABLE CUSTOMER_ERROR AS CUSTOMER BEFORE(...) is not valid syntax.
- C. CREATE TABLE ... LIKE copies only the column definitions without data and does not accept a Time Travel clause, so no data is restored.
- D. This only copies the pre-update data into CUSTOMER_ERROR and never restores CUSTOMER, leaving the erroneous version in the original table.
Question 22
Which Snowflake objects can be used in a data share? (Choose two.)
Show answer and explanation
Correct answer: B, D. Secure view; External table
External tables and secure views can be included in Snowflake data shares. External tables allow consumers to query data stored in cloud storage without replicating it, and secure views provide controlled access to underlying data while maintaining security properties. Both object types are on Snowflake's list of objects that can be added to a share.
Why the other options are wrong
- A. By default a share accepts only secure objects, so a standard view cannot be added to it; a secure view is the view type designed for sharing.
- C. Stored procedures cannot be added to a data share; shareable objects include tables, external tables, secure views and secure UDFs, but not stored procedures.
- E. Streams cannot be shared in data shares; they are change tracking objects used within individual accounts.
Question 23
What Snowflake features should be leveraged when modeling using Data Vault?
Show answer and explanation
Correct answer: A. Snowflake’s support of multi-table inserts into the data model’s Data Vault tables
Snowflake's support of multi-table inserts into Data Vault tables is the key feature to leverage for Data Vault modeling. Multi-table inserts allow efficient loading of hub, link, and satellite tables simultaneously in a single operation, which optimizes the insert performance for complex Data Vault loads.
Why the other options are wrong
- B. Data Vault does not require pre-partitioning; Snowflake's architecture handles data access performance through its columnar format and clustering.
- C. Scaling up a warehouse makes each individual load faster, but running many new source loads in parallel is handled by scaling out with a multi-cluster warehouse, so this is not the Snowflake feature to leverage for Data Vault loading.
- D. Joining on long hash-key values is not faster than joining on integer keys, and hashing keys is a Data Vault modeling technique rather than a Snowflake feature to leverage.
Question 24
A company has several sites in different regions from which the company wants to ingest data. Which of the following will enable this type of data ingestion?
Show answer and explanation
Correct answer: D. The company should use a storage integration for the external stage.
Storage integration is the appropriate mechanism for ingesting data from multiple regional sites. A storage integration creates secure, authenticated connections to external cloud storage without requiring credentials in code, allowing data to be staged from various regional sources and loaded into a single Snowflake account. This is more efficient than replication or multiple accounts and doesn't require reader accounts for basic ingestion.
Why the other options are wrong
- A. A single Snowflake account can ingest data from multiple cloud regions without requiring accounts in each region.
- B. Replication is for synchronizing data between accounts, not for the initial multi-region ingestion pattern.
- C. Reader accounts are for sharing data outbound, not for enabling inbound data ingestion from multiple sites.
Question 25
A Snowflake Architect is designing a multiple-account design strategy. This strategy will be MOST cost-effective with which scenarios? (Choose two.)
Show answer and explanation
Correct answer: B, D. The company needs to share data between two databases, where one must support Payment Card Industry Data Security Standard (PCI DSS) compliance but the other one does not; The company security policy mandates the use of different Active Directory instances for the development, test, and production environments.
Multiple accounts are most cost-effective when compliance requirements mandate separation (option B), as this eliminates unnecessary compliance costs by housing PCI DSS data separately from non-regulated data. Additionally, different Active Directory instances per environment (option D) necessitate separate accounts since authentication mechanisms are account-level settings. These scenarios require architectural separation for security and compliance rather than optional isolation.
Why the other options are wrong
- A. Cloning works only within a single account, so a database cannot be cloned from an AWS account to an Azure account; moving it would need replication, and this scenario gives no cost reason to split accounts.
- C. Role-based access control can be implemented within a single account using Snowflake's native RBAC features without needing multiple accounts.
- E. A network policy can be assigned to specific users within one account, so this requirement does not call for multiple accounts.
Question 26
An Architect on a new project has been asked to design an architecture that meets Snowflake security, compliance, and governance requirements as follows: 1. Use Tri-Secret Secure in Snowflake 2. Share some information stored in a view with another Snowflake customer 3. Hide portions of sensitive information from some columns 4. Use zero-copy cloning to refresh the non-production environment from the production environment To meet these requirements, which design elements must be implemented? (Choose three.)
Show answer and explanation
Correct answer: B, C, E. Use the Business Critical edition of Snowflake; Create a secure view; Use Dynamic Data Masking.
Meeting these four requirements requires specific design elements. Tri-Secret Secure requires Business Critical edition or higher, so Business Critical must be selected. To share information with another Snowflake customer via a view, a secure view must be created to ensure the view definition and underlying logic remain hidden from the recipient. Dynamic Data Masking is the feature that hides portions of sensitive information from columns, allowing column-level access control based on user roles. Zero-copy cloning works on any edition and doesn't require a specific design element. Row access policies provide row-level security but are not required here since the masking requirement is column-level. Materialized views are not a security or governance mechanism.
Why the other options are wrong
- A. Row access policies provide row-level security, not column-level data masking for sensitive information.
- D. Enterprise edition does not include Tri-Secret Secure; Business Critical edition is required.
- F. Materialized views are a performance optimization tool, not a security or governance control.
Question 27
A media company needs a data pipeline that will ingest customer review data into a Snowflake table, and apply some transformations. The company also needs to use Amazon Comprehend to do sentiment analysis and make the de-identified final data set available publicly for advertising companies who use different cloud providers in different regions. The data pipeline needs to run continuously and efficiently as new records arrive in the object storage leveraging event notifications. Also, the operational complexity, maintenance of the infrastructure, including platform upgrades and security, and the development effort should be minimal. Which design will meet these requirements?
Show answer and explanation
Correct answer: B. Ingest the data using Snowpipe and use streams and tasks to orchestrate transformations. Create an external function to do model inference with Amazon Comprehend and write the final records to a Snowflake table. Then create a listing in the Snowflake Marketplace to make the data available to other companies.
Snowpipe provides event-driven continuous ingestion with minimal operational overhead. Streams and tasks orchestrate transformations natively within Snowflake. External functions integrate Amazon Comprehend for sentiment analysis without requiring separate infrastructure management. Snowflake Marketplace enables secure data sharing across cloud providers and regions. Together, these choices meet the requirements for minimal complexity and operational effort.
Why the other options are wrong
- A. COPY INTO does not load files in response to event notifications, and exporting the data to Amazon S3 and loading it back adds an unload and reload cycle that an external function calling Amazon Comprehend avoids.
- C. Using Amazon EMR and PySpark introduces significant operational complexity, infrastructure management, and development effort contrary to stated requirements.
- D. Exporting to S3 and re-importing data adds latency, complexity, and operational overhead compared to native external functions.
Question 28
An Architect has been asked to clone schema STAGING as it looked one week ago, Tuesday June 1st at 8:00 AM, to recover some objects. The STAGING schema has 50 days of retention. The Architect runs the following statement: CREATE SCHEMA STAGING_CLONE CLONE STAGING at (timestamp => '2021-06-01 08:00:00'); The Architect receives the following error: Time travel data is not available for schema STAGING. The requested time is either beyond the allowed time travel period or before the object creation time. The Architect then checks the schema history and sees the following: • CREATED_ON 2021-06-02 23:00:00 | NAME STAGING | DROPPED_ON NULL • CREATED_ON 2021-05-01 10:00:00 | NAME STAGING | DROPPED_ON 2021-06-02 23:00:00 How can cloning the STAGING schema be achieved?
Show answer and explanation
Correct answer: C. Rename the STAGING schema and perform an UNDROP to retrieve the previous STAGING schema version, then run the CLONE statement.
The schema history shows two versions of STAGING. The original was created on 2021-05-01 10:00:00 and dropped on 2021-06-02 23:00:00, and the current one was created at that same moment. The requested time, 2021-06-01 08:00:00, falls within the life of the dropped version, so the current schema has no Time Travel data for it. Renaming the current STAGING frees the name, UNDROP then restores the original STAGING, and the CLONE statement at the requested timestamp succeeds because that time is within its 50-day retention.
Why the other options are wrong
- A. UNDROP fails while a schema named STAGING already exists, so the current STAGING must be renamed before the dropped version can be restored.
- B. The statement still targets the current STAGING schema, created on 2021-06-02, so 2021-05-01 10:00:00 is also before its creation time and fails the same way; it would also clone the wrong point in time.
- D. Cloning is achievable through proper sequencing of undrop and rename operations to access the correct historical version.
Question 29
An Architect needs to allow a user to create a database from an inbound share. To meet this requirement, the user’s role must have which privileges? (Choose two.)
Show answer and explanation
Correct answer: A, C. IMPORT SHARE;; CREATE DATABASE;
To allow a user to create a database from an inbound share, the user's role must have two specific privileges. The CREATE DATABASE privilege is required to create the database object. The IMPORT SHARE privilege is required to import and consume shared data from another account. These two privileges together enable the complete workflow of accepting a shared object and materializing it as a database. IMPORT PRIVILEGES and IMPORT DATABASE are not valid Snowflake privilege names. CREATE SHARE is for outbound sharing, not for creating databases from inbound shares.
Why the other options are wrong
- B. IMPORT PRIVILEGES is not a valid Snowflake system privilege.
- D. CREATE SHARE is used to create outbound shares, not to consume inbound shares.
- E. IMPORT DATABASE is not a valid Snowflake system privilege; the correct privilege is IMPORT SHARE.
Question 30
A Snowflake Architect is setting up database replication to support a disaster recovery plan. The primary database has external tables. How should the database be replicated?
Show answer and explanation
Correct answer: B. Move the external tables to a database that is not replicated, then replicate the primary database.
External tables in Snowflake reference data in cloud storage and are region-specific. Snowflake database replication does not support external tables at all, so they cannot be carried into the secondary database in any region. The correct approach is to move external tables to a separate non-replicated database, then replicate only the internal tables. The external tables remain accessible separately in their original region.
Why the other options are wrong
- A. Cloning the database first does not help, because database replication does not support external tables in any region.
- C. Replicating in the same region defeats the purpose of disaster recovery across geographic regions.
- D. Sharing creates read-only access; it does not replicate the database for disaster recovery purposes.
Question 31
A table for IOT devices that measures water usage is created. The table quickly becomes large and contains more than 2 billion rows. The general query patterns for the table are: 1. DeviceId, IOT_timestamp and CustomerId are frequently used in the filter predicate for the select statement 2. The columns City and DeviceManufacturer are often retrieved 3. There is often a count on UniqueId Which field(s) should be used for the clustering key?
Show answer and explanation
Correct answer: C. DeviceId and CustomerId
A clustering key should be built from the columns used in filter predicates, because clustering co-locates rows with similar values in the same micro-partitions so that filters can prune the rest. Query pattern 1 names DeviceId, IOT_timestamp and CustomerId as the filter columns, and of the options only DeviceId and CustomerId covers two of them, while IOT_timestamp alone covers one. City and DeviceManufacturer are only retrieved and UniqueId is only counted, so clustering on them would not help queries find rows. For a table of more than 2 billion rows, clustering on DeviceId and CustomerId gives the most pruning for the common query pattern.
Why the other options are wrong
- A. IOT_timestamp alone is insufficient; it is only one of the filter columns and leaves out DeviceId and CustomerId, which are also used in the filter predicates.
- B. City and DeviceManufacturer are retrieved columns but are not used in filter predicates; clustering keys must be based on filtering conditions, not projection columns.
- D. UniqueId is used for counting but is not a filter predicate; it should not be the clustering key as it provides no optimization for the main query access patterns.
Question 32
A user can change object parameters using which of the following roles?
Show answer and explanation
Correct answer: C. ACCOUNTADMIN, USER with PRIVILEGE
Snowflake parameters follow a hierarchy of account, session and object levels. Object parameters, such as DATA_RETENTION_TIME_IN_DAYS or MAX_CONCURRENCY_LEVEL, can be set at the account level by the ACCOUNTADMIN role, which establishes the default that every object inherits. They can then be overridden on an individual object by any user whose role holds the required privilege on that object, typically OWNERSHIP or MODIFY. These are the two documented routes for changing object parameters: ACCOUNTADMIN, or a user with the appropriate privilege on the object.
Why the other options are wrong
- A. SECURITYADMIN has no inherent right to modify object parameters; parameter changes require ACCOUNTADMIN or the required privilege on the object.
- B. Neither SYSADMIN nor SECURITYADMIN is granted a general right to change object parameters; that comes from ACCOUNTADMIN or from holding the required privilege on the object.
- D. SECURITYADMIN does not have parameter modification rights; only ACCOUNTADMIN or a user with the required privilege on the object can change parameters.
Question 33
A group of Data Analysts have been granted the role ANALYST_ROLE. They need a Snowflake database where they can create and modify tables, views, and other objects to load with their own data. The Analysts should not have the ability to give other Snowflake users outside of their role access to this data. How should these requirements be met?
Show answer and explanation
Correct answer: C. Make every schema in the database a MANAGED ACCESS schema, owned by SYSADMIN, and grant create privileges on each schema to the ANALYST_ROLE for each type of object that needs to be created.
MANAGED ACCESS schemas provide the necessary security by placing schema ownership and privilege management under SYSADMIN control. This prevents analysts from granting access to other users while still allowing them to create and modify objects within the schemas. By granting only CREATE privileges on specific object types, analysts can build objects but cannot share them with others, meeting both requirements: data autonomy and access isolation.
Why the other options are wrong
- A. As owner of the database, ANALYST_ROLE would also own the schemas and objects it creates there, and an object's owner can grant access to it without MANAGE GRANTS, so the analysts could still share the data with other roles.
- B. With CREATE SCHEMA on the database, ANALYST_ROLE would own the schemas it creates and the objects in them, so it could still grant other roles access to that data.
- D. Granting ANALYST_ROLE database ownership conflicts with the requirement that analysts should not be able to give access to other users.
Question 34
The Data Engineering team at a large manufacturing company needs to engineer data coming from many sources to support a wide variety of use cases and data consumer requirements which include: 1. Finance and Vendor Management team members who require reporting and visualization 2. Data Science team members who require access to raw data for ML model development 3. Sales team members who require engineered and protected data for data monetization What Snowflake data modeling approaches will meet these requirements? (Choose two.)
Show answer and explanation
Correct answer: B, C. Create a raw database for landing and persisting raw data entering the data pipelines; Create a set of profile-specific databases that aligns data with usage patterns.
To meet the diverse requirements of finance, data science, and sales teams, a multi-layered approach is necessary. Creating a raw database for landing and persisting raw data entering the pipeline provides a foundation where all raw data is stored in its original form, supporting data science needs and providing a single source of truth. Creating profile-specific databases that align with usage patterns allows the organization to structure data according to how different consumer groups use it: finance gets reporting schemas, data science accesses raw repositories, and sales gets protected datasets for monetization. A single star schema cannot accommodate all these different requirements. External tables alone don't address the modeling strategy. A Data Vault as the sole endpoint would create a bottleneck rather than supporting diverse consumption patterns.
Why the other options are wrong
- A. External tables are a storage mechanism, not a data modeling approach that addresses multiple consumer requirements.
- D. A single star schema cannot simultaneously support raw data access for ML, financial reporting, and data monetization with different protection levels.
- E. Using Data Vault as the sole endpoint creates a single point of access that cannot flexibly support the different transformation and protection needs of multiple consumer types.
Question 35
An Architect needs to grant a group of ORDER_ADMIN users the ability to clean old data in an ORDERS table (deleting all records older than 5 years), without granting any privileges on the table. The group’s manager (ORDER_MANAGER) has full DELETE privileges on the table. How can the ORDER_ADMIN role be enabled to perform this data cleanup, without needing the DELETE privilege held by the ORDER_MANAGER role?
Show answer and explanation
Correct answer: C. Create a stored procedure that runs with owner’s rights, including the appropriate "> 5 years" business logic, and grant USAGE on this procedure to ORDER_ADMIN. The ORDER_MANAGER role owns the procedure.
A stored procedure that runs with owner's rights executes using the privileges of the role that owns the procedure, not the caller. By having ORDER_MANAGER (which holds DELETE privileges) own a procedure containing the deletion logic with the 5-year filter, the ORDER_ADMIN role can execute the procedure and perform the cleanup without directly holding DELETE privilege. The procedure executes with ORDER_MANAGER's rights, bypassing the need for ORDER_ADMIN to have DELETE on the table.
Why the other options are wrong
- A. A procedure with caller's rights executes using the privileges of the user calling it, so ORDER_ADMIN would still need DELETE privilege to execute the deletion.
- B. A procedure runs with either caller's rights or owner's rights, as set by its EXECUTE AS clause (in CREATE PROCEDURE or ALTER PROCEDURE); the caller cannot choose the mode at execution time.
- D. This scenario is entirely possible in Snowflake through owner's rights procedures, which enable privilege elevation for specific business logic without granting broader table privileges.
Question 36
Assuming all Snowflake accounts are using an Enterprise edition or higher, in which development and testing scenarios would copying of data be required, and zero-copy cloning not be suitable? (Choose two.)
Show answer and explanation
Correct answer: A, C. Developers create their own datasets to work against transformed versions of the live data; Data is in a production Snowflake account that needs to be provided to Developers in a separate development/testing Snowflake account in the same cloud region.
Zero-copy cloning creates a metadata-only copy that shares underlying data with the source and works only within a single account, making it unsuitable when a physically separate copy of the data is required. Option A requires developers to work with transformed versions of live data, and those transformations must be applied to a separate copy to avoid affecting the source. Option C involves cross-account data transfer to a separate development account; zero-copy cloning cannot span across different Snowflake accounts, necessitating actual data copying. Options B, D, and E all support zero-copy cloning: B can use clones with dynamic data masking applied at query time without copying data, D involves cloning within the same account for independent development work, and E can use clones within the same production account for pre-production testing without data duplication.
Why the other options are wrong
- B. Dynamic data masking policies can be applied to zero-copy clones without requiring actual data copies, allowing masked views of the same underlying data.
- D. Developers can create independent zero-copy clones of a standard test database within the same account, each maintaining their own metadata layer while sharing the source data.
- E. Zero-copy cloning within the same production account provides isolated pre-production environments that share data with the source, meeting security and scale requirements without copying.
Question 37
A company's Architect needs to find an efficient way to get data from an external partner, who is also a Snowflake user. The current solution is based on daily JSON extracts that are placed on an FTP server and uploaded to Snowflake manually. The files are changed several times each month, and the ingestion process needs to be adapted to accommodate these changes. What would be the MOST efficient solution?
Show answer and explanation
Correct answer: A. Ask the partner to create a share and add the company's account.
Snowflake Data Sharing is the most efficient solution for data exchange between two Snowflake users. It eliminates manual file transfers, automatically handles schema changes, and provides real-time access to the partner's data without requiring data movement or duplication. The partner simply creates a share and grants access to the company's account, making it far superior to managing file extracts and ingestion pipelines.
Why the other options are wrong
- B. Data lake export and schema-on-read still require file management and manual ingestion processes and do not use the direct sharing capabilities between Snowflake accounts.
- C. Restricting file changes does not solve the underlying inefficiency of manual daily extracts and still requires ongoing process adaptation.
- D. A reader account is created and paid for by the provider for consumers who have no Snowflake account of their own; the company already has one, so it can consume the partner's share directly.
Question 38
A group of marketing and advertising companies want to share their data with one another for joint analysis, while adhering to strict levels of privacy. How should an Architect design their data sharing solution?
Show answer and explanation
Correct answer: A. Use Data Clean Rooms to connect natively to share data with each other.
Snowflake Data Clean Rooms let several parties bring their data together for joint analysis while each party keeps control of its raw data. Each collaborator defines which templates and queries can run against its data, and only approved, aggregated results are returned, so no company sees the other companies' underlying records. This is the design built for privacy-sensitive collaboration such as audience overlap and campaign measurement between marketing and advertising companies.
Why the other options are wrong
- B. A direct share gives the consumer read access to the shared objects, so it does not enforce the strict privacy controls that joint analysis between these companies needs.
- C. Marketplace listings distribute data products to consumers and do not restrict how the receiving company can query the shared records.
- D. A Data Exchange is a private hub for publishing and discovering data, and members who get access can still query the raw shared data.
Question 39
How can the Snowpipe REST API be used to keep a log of data load history?
Show answer and explanation
Correct answer: D. Call loadHistoryScan every 10 minutes for a 15-minute time range.
The loadHistoryScan endpoint should be called every 10 minutes querying a 15-minute time range to maintain an accurate and efficient log of data load history using the Snowpipe REST API. This frequency and window size strike a balance between capturing all load events and avoiding redundant API calls and data processing.
Why the other options are wrong
- A. Calling insertReport every 20 minutes is too infrequent, because insertReport retains events for a maximum of 10 minutes, so loads between calls would be missed.
- B. loadHistoryScan is not designed to be called every minute; it causes excessive API usage and the maximum time range parameter is not the appropriate approach.
- C. insertReport does not accept a time range; it returns recent events from a beginMark position, so it cannot be called for a 10-minute time range.
Question 40
An Architect is designing a Snowflake implementation that will share anonymized data from cell phone towers. The data will be shared with companies that want to understand consumer spending patterns close to major retail centers. Because all Personal Identifiable Information (PII) must be anonymized, the design must include the highest degree of separation of duties. The design must support a full development cycle with separation between environments. How can these requirements be met? (Choose three.)
Show answer and explanation
Correct answer: A, B, C. Implement a git-enabled workflow with a CI/CD deployment model; Implement a separate Snowflake account for PII data; Implement a Snowflake design that has separate accounts for each environment.
The highest degree of separation of duties comes from account boundaries, because roles, users, policies and administrators in one Snowflake account have no reach into another. Placing PII in its own account isolates raw identifiable data from the anonymized data that is shared, and using separate accounts for development, test and production keeps each environment fully isolated through the development cycle. A git-enabled workflow with CI/CD then promotes the same versioned code through those accounts automatically, which removes manual steps, enforces review and gives a repeatable, auditable deployment path.
Why the other options are wrong
- D. Manually deployed scripts introduce human error and inconsistent releases, which undermines a controlled development cycle across environments.
- E. Masking policies hide PII inside one account but do not provide separation of environments or the account-level isolation the design requires.
- F. A single account with separate roles still shares one administrative boundary, so it cannot deliver the highest degree of separation of duties.
Question 41
A Snowflake Architect is designing a multi-tenant application strategy for an organization in the Snowflake Data Cloud and is considering using an Account Per Tenant strategy. Which requirements will be addressed with this approach? (Choose two.)
Show answer and explanation
Correct answer: B, D. Security and Role-Based Access Control (RBAC) policies must be simple to configure; Tenant data shape may be unique per tenant.
An Account Per Tenant strategy provides strong isolation where each tenant has their own dedicated Snowflake account. This naturally simplifies RBAC and security policies since each account is completely separate with its own role hierarchy and access controls (Option B). It also accommodates unique data shapes per tenant because each account can be configured independently without constraints from other tenants (Option D). The strategy does not reduce objects per tenant, optimize compute costs, or optimize storage costs; it typically increases costs due to per-account overhead.
Why the other options are wrong
- A. Each tenant account needs its own full set of databases, schemas, roles, warehouses and users, so Account Per Tenant multiplies objects rather than reducing them.
- C. Account Per Tenant increases compute costs due to separate compute infrastructure per account rather than optimizing it.
- E. Each tenant's data lives in a separate account, so common data and objects are stored again in every account instead of once, which works against optimizing storage costs.
Question 42
When loading data into a table that captures the load time in a column with a default value of either CURRENT_TIME() or CURRENT_TIMESTAMP() what will occur?
Show answer and explanation
Correct answer: D. All rows loaded using a specific COPY statement will have the same timestamp value.
When loading data into a table using a COPY statement where a column has a default value of CURRENT_TIME() or CURRENT_TIMESTAMP(), all rows loaded within a single COPY statement execution receive the same timestamp value. This is by design: the timestamp is evaluated once for the COPY operation and applied uniformly to all rows in that batch. This ensures data consistency and allows tracking of when entire batches were loaded. The timestamp does not vary based on when individual rows were inserted, read from the source, or created in the source system.
Why the other options are wrong
- A. Rows do not have varying timestamps within a single COPY statement; they all receive the same value.
- B. Timestamps are not based on when rows were read from the source; the function is evaluated once per COPY.
- C. Timestamps are not based on when rows were created in the source system; they reflect when data entered Snowflake.
Question 43
How can a Snowflake Architect allow unauthorized users to perform joins on protected columns in a SQL query?
Show answer and explanation
Correct answer: C. Use external tokenization.
External tokenization replaces sensitive values with tokens before or as data lands in Snowflake, and an external function detokenizes them at query time only for authorized roles. Because tokenization produces a consistent token for a given value, users who are not authorized to see the real data can still group and join on the tokenized columns, matching records across tables without ever revealing the protected values. This preserves the analytical usefulness of the column while keeping the clear-text data hidden from those users.
Why the other options are wrong
- A. Secure views hide the view definition and control which columns are exposed, but they give unauthorized users no way to join on protected values without revealing them.
- B. Secure functions protect the function definition and internals, not the ability to join on protected column values.
- D. A cloud IAM role governs access to cloud services such as storage, not how Snowflake users can join on protected columns.
Question 44
An Architect is integrating an application that needs to read and write data to Snowflake without installing any additional software on the application server. How can this requirement be met?
Show answer and explanation
Correct answer: C. Use the Snowflake SQL REST API.
The Snowflake SQL REST API enables applications to read and write data to Snowflake using standard HTTP requests without installing any client software or drivers. This is a pure REST-based approach that allows seamless integration from any application environment capable of making HTTP calls.
Why the other options are wrong
- A. SnowSQL is a command-line tool that requires installation on the application server.
- B. Snowpipe REST API is designed specifically for data ingestion (write-only), not for general read/write operations.
- D. ODBC driver requires installation of software on the application server, violating the no-additional-software requirement.
Question 45
Which statements describe characteristics of the use of materialized views in Snowflake? (Choose two.)
Show answer and explanation
Correct answer: B, D. They cannot include nested subqueries; They can support MIN and MAX aggregates.
Materialized views in Snowflake are maintained automatically by a background service, so their definitions are restricted to operations that Snowflake can keep up to date. A materialized view can query only a single table, and it cannot include nested subqueries, joins, window functions, HAVING, ORDER BY or LIMIT. Supported aggregate functions include MIN and MAX, along with others such as SUM, COUNT and AVG. Context functions such as CURRENT_TIME() are not allowed because they return different values over time and would make the stored results non-deterministic.
Why the other options are wrong
- A. Snowflake does not support an ORDER BY clause in a materialized view definition.
- C. Context functions like CURRENT_TIME() cannot be used in materialized views as they are non-deterministic.
- E. A materialized view can query only a single table, so it supports neither inner joins nor outer joins.
Question 46
An Architect needs to meet a company requirement to ingest files from the company’s AWS storage accounts into the company's Snowflake Google Cloud Platform (GCP) account. How can the ingestion of these files into the company's Snowflake account be initiated? (Choose two.)
Show answer and explanation
Correct answer: A, C. Configure the client application to call the Snowpipe REST endpoint when new files have arrived in Amazon S3 storage; Create an AWS Lambda function to call the Snowpipe REST endpoint when new files have arrived in Amazon S3 storage.
Snowpipe exposes a REST endpoint (insertFiles) that any client can call with a list of staged file names, and the call works regardless of which cloud platform hosts the Snowflake account. A client application can call this endpoint whenever new files land in the Amazon S3 bucket referenced by an external stage, and an AWS Lambda function triggered by S3 object creation events can make the same call automatically. In both cases the GCP-hosted Snowflake account loads the files from S3 through the stage, so either a client application or a Lambda function calling the Snowpipe REST endpoint initiates the ingestion.
Why the other options are wrong
- B. Snowflake cannot load files held in archival storage classes such as S3 Glacier that require restoration before they can be read.
- D. Configuring SNS by itself does not set up ingestion: the pipe must be created with AUTO_INGEST = TRUE and AWS_SNS_TOPIC so that Snowflake subscribes its own queue to the topic, whereas A and C start each load directly by calling the Snowpipe REST endpoint.
- E. COPY INTO cannot read files stored in S3 Glacier because archival storage classes must be restored before Snowflake can load them.
Question 47
A table, T1, has a table stream defined, named S1. The data retention time on the table is set for 10 days. Stream S1 has not been consumed for 10 consecutive days. What consideration needs to be made if an Architect wants to implement an ELT process on the stream?
Show answer and explanation
Correct answer: C. An ETL process can access stream S1 after the 11th day because Snowflake automatically puts a temporary extension on the stream offset for 14 additional days.
When a table's data retention period is shorter than 14 days and a stream on it has not been consumed, Snowflake temporarily extends the retention period for the stream's offset so that the stream does not become stale. The length of this extension is governed by the MAX_DATA_EXTENSION_TIME_IN_DAYS parameter, which defaults to 14 days in every edition. With T1 set to 10 days of retention, stream S1 is therefore still readable on the 11th day, and an ELT process can consume its change records as long as it does so before the extended period runs out.
Why the other options are wrong
- A. Snowflake automatically extends retention for an unconsumed stream up to MAX_DATA_EXTENSION_TIME_IN_DAYS, so S1 is not stale on the 11th day.
- B. The default extension is governed by MAX_DATA_EXTENSION_TIME_IN_DAYS, which is 14 days, not 7.
- D. Streams depend on the table's retention period, because they read change records from Time Travel data and go stale once that data is no longer available.
Question 48
Which data models can be used when modeling tables in a Snowflake environment? (Choose three.)
Show answer and explanation
Correct answer: B, D, F. Dimensional/Kimball; Inmon/3NF; Data vault
Snowflake supports three primary data modeling approaches: Dimensional/Kimball modeling (option B) for analytical data warehouses, Inmon/3NF (option D) for normalized enterprise data warehouses, and Data Vault (option F) for flexible and auditable historical data models. These are established methodologies specifically designed for relational database implementations including Snowflake.
Why the other options are wrong
- A. Graph models are specialized for relationship-heavy data and are not a standard data modeling approach in Snowflake.
- C. Data lake is an architectural pattern, not a data modeling methodology for table design.
- E. Bayesian hierarchical models are statistical methodologies, not data modeling approaches for structuring tables.
Question 49
An Architect has chosen to separate their Snowflake Production and QA environments using two separate Snowflake accounts. The QA account is intended to run and test changes on data and database objects before pushing those changes to the Production account. It is a requirement that all database objects and data in the QA account need to be an exact copy of the database objects, including privileges and data in the Production account on at least a nightly basis. Which is the LEAST complex approach to use to populate the QA account with the Production account’s data and database objects on a nightly basis?
Show answer and explanation
Correct answer: C. 1. Enable replication for each database in the Production account 2. Create replica databases in the QA account 3. Create clones of the replica databases on a nightly basis 4. Run tests directly on those cloned databases
Replication from the Production account to the QA account, using a replication group that also includes roles, provides an exact copy of all database objects and data including privileges. Cloning the replicated databases nightly creates isolated testing environments while maintaining full fidelity. This approach is simpler than share-based solutions and more efficient than unload/load patterns.
Why the other options are wrong
- A. Databases created from shares cannot be cloned, and shares do not carry the Production account's privileges, so this approach cannot produce the required nightly copies.
- B. Unloading tables to a stage and reloading them with Snowpipe copies only table data, not the other database objects or their privileges, so it cannot produce the exact copy the requirement demands.
- D. An external function is a scalar function that calls a remote service through an API proxy, so looping it over tables cannot recreate the database objects or privileges needed for an exact copy of the Production account.
Question 50
Which steps are recommended best practices for prioritizing cluster keys in Snowflake? (Choose two.)
Show answer and explanation
Correct answer: A, D. Choose columns that are frequently used in join predicates; Choose cluster columns that are most actively used in selective filters.
Snowflake's guidance for choosing a clustering key is to prioritize the columns most actively used in selective filters, because clustering on them lets the optimizer prune the most micro-partitions. If there is room for another column, the next priority is columns frequently used in join predicates, since clustering on the join key helps prune the micro-partitions scanned on that side of the join. Both choices target the predicates that decide how much data a query reads.
Why the other options are wrong
- B. Very low cardinality columns (such as a boolean) give little pruning benefit; Snowflake recommends columns with enough distinct values for effective pruning but few enough for rows to be grouped into the same micro-partitions.
- C. TIMESTAMP columns with nanosecond precision create excessive cardinality and are unsuitable for cluster keys; clustering works best with moderate cardinality.
- E. Snowflake's recommended order for choosing clustering key columns is columns used in selective filters first, then columns used in join predicates; columns used in GROUP BY clauses are not part of that recommended order.
Question 51
What transformations are supported in the below SQL statement? (Choose three.) CREATE PIPE ... AS COPY ... FROM (...)
Show answer and explanation
Correct answer: B, C, D. Columns can be reordered; Columns can be omitted; Type casts are supported.
A pipe wraps a single COPY INTO <table> statement, and that COPY can load from a SELECT over the staged files using the simple transformations COPY supports. Columns can be reordered by listing positional references such as $3 and $1 in any order, columns can be omitted by leaving them out of the SELECT, and values can be cast to other data types with :: or CAST. COPY transformations do not support filtering rows with a WHERE clause, joining to other tables, or aggregations, so more complex logic belongs in streams and tasks after the load.
Why the other options are wrong
- A. WHERE clause filtering is not supported in COPY transformations.
- E. Joins with other tables are not supported in COPY transformations.
- F. ON_ERROR is a copy option rather than a transformation, and a pipe's COPY statement does not support ON_ERROR = ABORT_STATEMENT.
Question 52
A company is storing large numbers of small JSON files (ranging from 1-4 bytes) that are received from IoT devices and sent to a cloud provider. In any given hour, 100,000 files are added to the cloud provider. What is the MOST cost-effective way to bring this data into a Snowflake table?
Show answer and explanation
Correct answer: B. A pipe
For 100,000 small files per hour, a Snowflake Pipe is the most cost-effective solution. Pipes use Snowflake's serverless compute model, which is billed as a fixed credit amount per GB of data loaded rather than for a warehouse that must run while files arrive. They automatically detect new files in cloud storage and load them without manual intervention, reducing operational overhead. Pipes are optimized for continuous, high-frequency file ingestion and handle the metadata tracking efficiently. External tables would not ingest the data into native Snowflake tables. Streams track changes but do not load data. COPY commands run at regular intervals need a user-managed warehouse and are less efficient than automated pipe processing.
Why the other options are wrong
- A. External tables only reference data in cloud storage and do not ingest it into Snowflake, missing the requirement to bring data into a table.
- C. Streams track data changes but do not load data from cloud storage; they are used after data is already in Snowflake.
- D. Scheduled COPY commands run on a user-managed warehouse that is billed for every second it runs, whereas a pipe loads each new file with serverless compute billed per GB of data loaded.
Question 53
An Architect would like to save quarter-end financial results for the previous six years. Which Snowflake feature can the Architect use to accomplish this?
Show answer and explanation
Correct answer: D. Zero-copy cloning
Zero-copy cloning allows the Architect to create multiple independent clones of tables or schemas without duplicating the underlying data, making it cost-effective for preserving historical snapshots. The Architect can clone the quarter-end financial results at the end of each quarter for six years, retaining every previous version. A zero-copy clone shares the source's micro-partitions, so it adds storage only for data that later changes in the source or the clone.
Why the other options are wrong
- A. Search optimization service is for query performance improvement, not data retention.
- B. Materialized views are for pre-computing query results, not for archiving historical snapshots.
- C. Time Travel allows access to historical data within retention periods but is not a persistent storage solution for multi-year retention.
- E. Secure views are for controlling data access, not for saving historical results.
Question 54
Consider the following COPY command which is loading data with CSV format into a Snowflake table from an internal stage through a data transformation query. This command results in the following error: SQL compilation error: invalid parameter 'validation_mode' Assuming the syntax is correct, what is the cause of this error?
Show answer and explanation
Correct answer: C. The VALIDATION_MODE parameter does not support COPY statements that transform data during a load.
The VALIDATION_MODE parameter in Snowflake COPY statements cannot be used when data transformation occurs during the load operation. In this command, the FROM clause contains a SELECT statement that transforms data (selecting specific columns t.$1, t.$2, t.$6, t.$7 from the staged file), which means data transformation is happening during the load. VALIDATION_MODE validates the staged files and returns the results without loading any data, but it does not support scenarios where transformation queries are applied during the COPY operation.
Why the other options are wrong
- A. VALIDATION_MODE supports both internal and external stages, so stage type is not the issue here.
- B. VALIDATION_MODE is compatible with CSV file formats; the CSV format itself is not the restriction.
- D. The value 'return_all_errors' is a valid option for VALIDATION_MODE and would not cause a compilation error on its own.
Question 55
A company’s client application supports multiple authentication methods, and is using Okta. What is the best practice recommendation for the order of priority when applications authenticate to Snowflake?
Show answer and explanation
Correct answer: A. 1. OAuth (either Snowflake OAuth or External OAuth) 2. External browser 3. Okta native authentication 4. Key Pair Authentication, mostly used for service account users 5. Password
The recommended authentication priority is: 1) OAuth (Snowflake or External OAuth), 2) External browser/SSO, 3) Okta native authentication, 4) Key Pair Authentication for service accounts, and 5) Password. This hierarchy prioritizes OAuth as the most secure modern standard, followed by browser-based SSO, then specialized authentication methods, with password as the least secure fallback. This ordering balances security, user experience, and operational requirements.
Why the other options are wrong
- B. Incorrectly prioritizes external browser over OAuth and places key pair authentication too high for development-only use.
- C. Places Okta native authentication first, which is less secure than OAuth and not aligned with modern best practices.
- D. Begins with password authentication, which is the least secure method and should never be prioritized over OAuth and SSO.
Question 56
A company is designing high availability and disaster recovery plans and needs to maximize redundancy and minimize recovery time objectives for their critical application processes. Cost is not a concern as long as the solution is the best available. The plan so far consists of the following steps: 1. Deployment of Snowflake accounts on two different cloud providers. 2. Selection of cloud provider regions that are geographically far apart. 3. The Snowflake deployment will replicate the databases and account data between both cloud provider accounts. 4. Implementation of Snowflake client redirect. What is the MOST cost-effective way to provide the HIGHEST uptime and LEAST application disruption if there is a service event?
Show answer and explanation
Correct answer: A. Connect the applications using the <organization_name>-<connection_name> URL. Use the Business Critical Snowflake edition.
Client redirect works through a connection object, and applications connect with its connection URL in the form <organization_name>-<connection_name>. That hostname names no specific account, so when a service event occurs an administrator runs ALTER CONNECTION ... PRIMARY in the secondary account and every client using the connection URL is sent to that account without any change to connection strings. Client redirect, account failover and database failover all require Business Critical Edition or higher, so Business Critical is the lowest edition that delivers the whole plan. Virtual Private Snowflake costs more without adding anything this plan needs, which makes Business Critical the most cost-effective choice.
Why the other options are wrong
- B. The account URL ties the applications to one specific account so they would not follow a redirect, and Virtual Private Snowflake costs more without adding anything this plan needs.
- C. Enterprise edition does not support client redirect or account failover, both of which require Business Critical Edition or higher.
- D. The account URL points to one specific account, so the applications would not follow a redirect to the secondary account after a failover.
Question 57
A Snowflake Architect is designing an application and tenancy strategy for an organization where strong legal isolation rules as well as multi-tenancy are requirements. Which approach will meet these requirements if Role-Based Access Policies (RBAC) is a viable option for isolating tenants?
Show answer and explanation
Correct answer: B. Create an object for each tenant strategy if row level security is viable for isolating tenants.
When RBAC is viable for tenant isolation, creating separate database objects for each tenant and granting each tenant's role access only to its own objects keeps tenants isolated while enabling multitenancy. This approach leverages RBAC where it is effective rather than requiring account-level separation.
Why the other options are wrong
- A. An account per tenant is the pattern for when RBAC cannot isolate tenants, and the stem states RBAC is viable, so tenants can be isolated with their own objects and roles inside one account.
- C. This ties the object per tenant strategy to row level security not being viable, which does not match the scenario, where access policies are viable for isolating tenants.
- D. Multi-tenant tables without row-level security cannot provide the strong legal isolation required by the organization.
Question 58
Why might a Snowflake Architect use a star schema model rather than a 3NF model when designing a data architecture to run in Snowflake? (Choose two.)
Show answer and explanation
Correct answer: D, E. The BI tool needs a data model that allows users to summarize facts across different dimensions, or to drill down from the summaries; The Architect wants to present a simple flattened single view of the data to a particular group of end users.
A star schema is chosen for analytical workloads because it enables BI tools to aggregate facts across multiple dimensions and drill down into detailed views (option D). Star schemas also present a simple, flattened denormalized view that is intuitive for end users and faster for queries without requiring them to navigate complex join logic (option E). These are the key reasons for choosing dimensional modeling over normalized approaches in Snowflake.
Why the other options are wrong
- A. Snowflake handles 3NF joins efficiently; join complexity is not a limitation.
- B. Star schemas increase data duplication through denormalization, the opposite of removing duplication.
- C. Landing zones receive raw data and are independent of schema model choice; this is not a reason to use star schema.
Question 59
What integration object should be used to place restrictions on where data may be exported?
Show answer and explanation
Correct answer: C. Storage integration
Storage integration objects in Snowflake control access to external cloud storage locations and enforce restrictions on where data may be exported. They manage credentials and define allowed storage paths, enabling organizations to restrict data exfiltration to approved locations.
Why the other options are wrong
- A. Stage integration is not a valid Snowflake object type; stages are separate objects used for data loading.
- B. Security integration handles authentication mechanisms like OAuth and SAML, not data export restrictions.
- D. API integration manages connections to external APIs but does not restrict data export locations.
Question 60
Is it possible for a data provider account with a Snowflake Business Critical edition to share data with an Enterprise edition data consumer account?
Show answer and explanation
Correct answer: D. If a user in the provider account with a share owning role which also has OVERRIDE SHARE RESTRICTIONS privilege SHARE_RESTRICTIONS set to False when adding an Enterprise consumer account, it can import the share.
Snowflake enforces share restrictions between editions. Business Critical accounts have stricter sharing requirements than Enterprise accounts. To share from Business Critical to Enterprise, a user with a share-owning role must explicitly set SHARE_RESTRICTIONS to False and possess the OVERRIDE SHARE RESTRICTIONS privilege. This combination of conditions allows the Enterprise consumer account to import the shared data despite the edition mismatch.
Why the other options are wrong
- A. Business Critical accounts can share with Enterprise consumers if proper restrictions are overridden.
- B. Simply having CREATE or ALTER SHARE authority is insufficient; SHARE_RESTRICTIONS must be explicitly disabled with appropriate privileges.
- C. Setting SHARE_RESTRICTIONS to False alone is not enough; the user must also have the OVERRIDE SHARE RESTRICTIONS privilege.
Question 61
A company has an external vendor who puts data into Google Cloud Storage. The company's Snowflake account is set up in Azure. What would be the MOST efficient way to load data from the vendor into Snowflake?
Show answer and explanation
Correct answer: B. Create an external stage on Google Cloud Storage and use the external table to load the data into Snowflake.
Creating an external stage pointing to Google Cloud Storage and using external tables is the most efficient method for loading data from GCS into an Azure-hosted Snowflake account. This approach leverages Snowflake's ability to read from cloud storage across different cloud providers, avoiding data duplication and unnecessary account creation or replication overhead.
Why the other options are wrong
- A. A share requires the vendor to buy a Snowflake account and load the files into it first, whereas an external stage lets the company's account read the files directly from the vendor's Google Cloud Storage bucket.
- C. Snowflake on Azure can already read Google Cloud Storage through an external stage, so copying the files to Azure Blob storage with external tools adds a transfer step and tooling that the direct approach avoids.
- D. A second Snowflake account on GCP plus replication to Azure adds an account to run and a replication step, when the Azure account can read the vendor's Google Cloud Storage directly through an external stage.
Question 62
Data is being imported and stored as JSON in a VARIANT column. Query performance was fine, but most recently, poor query performance has been reported. What could be causing this?
Show answer and explanation
Correct answer: A. There were JSON nulls in the recent data imports.
When semi-structured data is loaded into a VARIANT column, Snowflake extracts frequently occurring elements into a columnar form so queries can read them like regular columns. Snowflake documents that elements containing even a single JSON null value are not extracted into a column. Once recent imports introduced JSON nulls, those elements stopped being stored in columnar form, and queries on them must scan and parse the entire JSON structure in every row, which explains why query performance degraded after previously being fine.
Why the other options are wrong
- B. JSON object keys are accessed by name, and changing their order has no effect on how Snowflake extracts or queries the elements.
- C. Elements that are simply missing from some records are still represented in columnar form, so fewer fields do not stop extraction.
- D. Values that remain strings keep a consistent data type regardless of length, so varying string lengths do not prevent columnar extraction.
Question 63
Files arrive in an external stage every 10 seconds from a proprietary system. The files range in size from 500 K to 3 MB. The data must be accessible by dashboards as soon as it arrives. How can a Snowflake Architect meet this requirement with the LEAST amount of coding? (Choose two.)
Show answer and explanation
Correct answer: A, E. Use Snowpipe with auto-ingest; Use a combination of a task and a stream.
To meet the requirement of making data accessible immediately upon arrival with minimal coding, Snowpipe with auto-ingest is the optimal solution. Snowpipe automatically detects files arriving in the external stage and loads them with minimal latency, and auto-ingest mode responds to cloud notifications, eliminating the need for polling logic. A combination of a task and a stream also minimizes coding while providing near-real-time loading, because the stream captures changes and the task orchestrates the load on a schedule. A COPY command with a task has no stream to signal new data, so the task runs its COPY on every scheduled run whether or not new files have arrived. Neither a manual COPY INTO nor a materialized view on an external table provides real-time loading into a Snowflake table.
Why the other options are wrong
- B. A task running COPY has no stream to signal new data, so it runs on every scheduled interval whether or not new files have arrived, unlike Snowpipe auto-ingest, which loads files as their event notifications arrive.
- C. A materialized view on an external table leaves the files in external storage and depends on external table metadata refreshes, so it does not load new files into a Snowflake table.
- D. Manual COPY INTO commands do not meet the real-time requirement and require manual triggering.
That was 63 of 253.
The full Snowflake SnowPro Advanced Architect ARA-C01 pack has all 253 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.
Not ready yet? Get the first 108 free as one PDF by email. Get the Half Pack →