Free Snowflake SnowPro Advanced Architect ARA-C01 practice questions

10 free Snowflake SnowPro Advanced Architect ARA-C01 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 253 questions. Work through them, then open each answer to check your reasoning.

Get all 253 questions (US$39) · Download these 10 as a PDF

Question 1

What built-in Snowflake features make use of the change tracking metadata for a table? (Choose two.)

  1. The MERGE command
  2. The UPSERT command
  3. The CHANGES clause
  4. A STREAM object
  5. Thee CHANGE_DATA_CAPTURE command
Show answer and explanation

Correct answer: C, D

C. The CHANGES clause D. A STREAM object Snowflake's change tracking metadata is specifically designed to support the CHANGES clause in queries and STREAM objects. The CHANGES clause allows you to query the change tracking metadata directly to identify rows that have been inserted, updated, or deleted. STREAM objects leverage this same metadata to capture DML changes on a table, enabling incremental processing patterns. MERGE uses change detection but not the built-in change tracking metadata feature. UPSERT and CHANGE_DATA_CAPTURE are not standard Snowflake commands that utilize change tracking metadata.

Why the other options are wrong

  • A. MERGE command does not directly use Snowflake's change tracking metadata system.
  • B. UPSERT is not a Snowflake command; MERGE is used for similar operations.
  • E. CHANGE_DATA_CAPTURE is not a native Snowflake command that uses change tracking metadata.

Question 2

When using the Snowflake Connector for Kafka, what data formats are supported for the messages? (Choose two.)

  1. CSV
  2. XML
  3. Avro
  4. JSON
  5. Parquet
Show answer and explanation

Correct answer: C, D

C. Avro D. JSON The Snowflake Connector for Kafka supports Avro and JSON as the primary data formats for incoming messages. These formats are explicitly documented as supported serialization formats because they provide schema information and are widely used in Kafka ecosystems. CSV, XML, and Parquet are not natively supported by the Kafka connector for message deserialization.

Why the other options are wrong

  • A. CSV format is not supported by the Snowflake Connector for Kafka.
  • B. XML format is not supported by the Snowflake Connector for Kafka.
  • E. Parquet format is not supported by the Snowflake Connector for Kafka for message ingestion.

Question 3

At which object type level can the APPLY MASKING POLICY, APPLY ROW ACCESS POLICY and APPLY SESSION POLICY privileges be granted?

  1. Global
  2. Database
  3. Schema
  4. Table
Show answer and explanation

Correct answer: A. Global

The APPLY MASKING POLICY, APPLY ROW ACCESS POLICY, and APPLY SESSION POLICY privileges can only be granted at the global (account) level in Snowflake. These are account-level privileges that control the ability to apply policies to objects throughout the entire Snowflake account. They cannot be scoped to individual databases, schemas, or tables.

Why the other options are wrong

  • B. Database level does not support granting these policy application privileges.
  • C. Schema level does not support granting these policy application privileges.
  • D. Table level does not support granting these policy application privileges; policies are applied via global privileges.

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

  1. COPY INTO tablea FROM @%tablea RETURN_FAILED_ONLY = TRUE;
  2. COPY INTO tablea FROM @%tablea;
  3. COPY INTO tablea FROM @%tablea FILES = ('file5.csv');
  4. COPY INTO tablea FROM @%tablea FORCE = TRUE;
  5. COPY INTO tablea FROM @%tablea NEW_FILES_ONLY = TRUE;
  6. COPY INTO tablea FROM @%tablea MERGE = TRUE;
Show answer and explanation

Correct answer: C, D

C. COPY INTO tablea FROM @%tablea FILES = ('file5.csv'); D. COPY INTO tablea FROM @%tablea FORCE = TRUE; To reload only file5.csv, the Architect should use the FILES parameter to specify the exact file name, which loads only that specific file. Additionally, using FORCE=TRUE will bypass Snowflake's load history and force the file to be reloaded even though it may have been attempted before, allowing the corrected file to be processed. The RETURN_FAILED_ONLY option only returns failed files without loading them. NEW_FILES_ONLY and MERGE parameters do not address the requirement to reload a specific previously-failed file.

Why the other options are wrong

  • A. RETURN_FAILED_ONLY only returns information about failed files without actually loading them.
  • B. Running COPY INTO without restrictions will attempt all files in the stage, not just file5.csv.
  • E. NEW_FILES_ONLY prevents reloading of previously attempted files and will skip file5.csv.
  • F. MERGE is not a valid COPY INTO parameter for this use case.

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?

  1. Migrate the European accounts in the global region and manage shares in a connected graph architecture. Deploy a Data Exchange.
  2. Deploy a Private Data Exchange in combination with data shares for the European accounts.
  3. Deploy to the Snowflake Marketplace making sure that invoker_share() is used in all secure views.
  4. Deploy a Private Data Exchange and use replication to allow European data shares in the Exchange.
Show answer and explanation

Correct answer: D. Deploy a Private Data Exchange and use replication to allow European data shares in the Exchange.

Deploying a Private Data Exchange combined with replication is the recommended best practice for this scenario. A Private Data Exchange allows the business divisions to independently decide what data to share while centralizing management, reducing configuration effort. Replication enables the European divisions (which are on different cloud platforms or regions) to participate by replicating data to the Exchange infrastructure, ensuring all divisions can access shared data regardless of their cloud platform or region. This approach meets the requirements for decentralized decisio-aking, minimal management effort, and cross-region/cross-cloud support.

Why the other options are wrong

  • A. Migrating to a global region would require operational changes and does not address cross-cloud deployment differences.
  • B. Private Data Exchange alone without replication does not effectively support European accounts on different cloud platforms.
  • C. Snowflake Marketplace is for public data sharing and does not meet the requirement for internal divisional control and privacy.

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?

  1. Unmasked data will be loaded in the new column.
  2. Masked data will be loaded into the new column.
  3. Unmasked data will be loaded into the new column but only users with the appropriate privileges will be able to see the unmasked data.
  4. Unmasked data will be loaded into the new column and no users will be able to see the unmasked data.
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 that data into another column without a masking policy, the unmasked data will be loaded. The masking policy is applied at query time based on the column where the data resides and the user's privileges. Once the unmasked data is physically copied to the new column, the masking policy on the original column no longer applies. The new column has no masking policy, so all users see the data as it was loaded, unmasked.

Why the other options are wrong

  • B. Masking policies do not follow data between columns; once loaded to an unmasked column, data appears unmasked.
  • C. Data loaded into an unmasked column is unmasked for all users, not restricted by privileges.
  • D. Data loaded into an unmasked column is visible to all users who can query that column.

Question 7

How can an Architect enable optimal clustering to enhance performance for different access paths on a given table?

  1. Create multiple clustering keys for a table.
  2. Create multiple materialized views with different cluster keys.
  3. Create super projections that will automatically create clustering.
  4. Create a clustering key that contains all columns used in the access paths.
Show answer and explanation

Correct answer: B. Create multiple materialized views with different cluster keys.

Creating multiple materialized views with different cluster keys is the optimal approach for supporting different access paths on a table. Each materialized view can have its own clustering key optimized for different query patterns, allowing queries against each view to benefit from their respective clustering. A single clustering key on the table can only optimize for one access pattern, and super projections and multiple clustering keys on a single table are not Snowflake features.

Why the other options are wrong

  • A. Snowflake tables support only one clustering key, not multiple clustering keys on a single table.
  • C. Super projections are not a Snowflake feature; this appears to be a Vertica concept.
  • D. Creating a single clustering key with all columns would not provide optimal clustering for different access paths and could degrade performance.

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?

  1. Create a pipeline to write shared data to a cloud storage location in the target cloud provider.
  2. Ensure that all views are persisted, as views cannot be shared across cloud platforms.
  3. Setup data replication to the region and cloud platform where the consumer resides.
  4. Company A and Company B must agree to use a single cloud platform: Data sharing is only possible if the companies share the same cloud provider.
Show answer and explanation

Correct answer: C. Setup data replication to the region and cloud platform where the consumer resides.

When companies are on different cloud platforms, Snowflake's native data sharing cannot be used directly. To share data across cloud platforms, the provider must set up data replication to replicate the shared databases and objects to the region and cloud platform where the consumer resides. Once replicated to the consumer's cloud platform, standard data sharing can then occur between the accounts on the same cloud. This is the supported method for cross-cloud data sharing scenarios.

Why the other options are wrong

  • A. Writing to cloud storage requires manual pipeline management and is not the Snowflake-recommended approach.
  • B. Views can be shared across accounts on the same cloud platform; the limitation is cloud platform, not view persistence.
  • D. While direct sharing requires the same cloud platform, replication enables cros-latform sharing, so this is not an absolute requirement.

Question 9

What are some of the characteristics of result set caches? (Choose three.)

  1. Time Travel queries can be executed against the result set cache.
  2. Snowflake persists the data results for 24 hours.
  3. Each time persisted results for a query are used, a 24-hour retention period is reset.
  4. The data stored in the result cache will contribute to storage costs.
  5. The retention period can be reset for a maximum of 31 days.
  6. The result set cache is not shared between warehouses.
Show answer and explanation

Correct answer: B, C, E

B. Snowflake persists the data results for 24 hours. C. Each time persisted results for a query are used, a 24-hour retention period is reset. E. 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 is automatically reset, ensuring frequently used query results remain available. The cached data does not contribute to storage costs, as it is managed separately from the data warehouse storage. Time Travel cannot be executed against the result set cache as it only stores final results. The retention period is fixed at 24 hours and cannot be extended to 31 days. The result set cache is shared across all warehouses in the same account, allowing different warehouses to benefit from cached results of the same queries.

Why the other options are wrong

  • A. Time Travel operates on historical table versions stored in Time Travel retention, not on result set cache data.
  • D. Result set cache data is not counted toward storage billing as it is ephemeral and managed separately.

Question 10

Which organization-related tasks can be performed by the ORGADMIN role? (Choose three.)

  1. Changing the name of the organization
  2. Creating an account
  3. Viewing a list of organization accounts
  4. Changing the name of an account
  5. Deleting an account
  6. Enabling the replication of a database
Show answer and explanation

Correct answer: B, C, D

B. Creating an account C. Viewing a list of organization accounts D. Changing the name of an account The ORGADMIN role can create new accounts, view the list of existing organization accounts, and change account names. These are core organizational management tasks within the organization hierarchy. Changing the organization name itself requires a higher privilege level typically reserved for ORGADMIN or equivalent system-level access in ways distinct from standard account management. Database replication requires specific role privileges and is not a standard ORGADMIN organization-level task. Deleting accounts is restricted and typically requires specific approval workflows beyond standard ORGADMIN privileges.

Why the other options are wrong

  • A. Changing the organization name requires special administrative permissions beyond standard ORGADMIN duties.
  • E. Account deletion is a sensitive operation that requires elevated privileges beyond standard ORGADMIN scope.
  • F. Database replication enablement requires specific database and account-level privileges, not organization-level permissions.

That was 10 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.

Get the full pack