SnowPro Advanced Architect

Question #: 6
Topic #: 1
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?

A. Unmasked data will be loaded in the new column.
B. Masked data will be loaded into the new column.
C. Unmasked data will be loaded into the new column but only users with the appropriate privileges will be able to see the unmasked data.
D. Unmasked data will be loaded into the new column and no users will be able to see the unmasked data.

Selected Answer: A

Question #: 4
Topic #: 1
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.)

A. COPY INTO tablea FROM @%tablea RETURN_FAILED_ONLY = TRUE;
B. COPY INTO tablea FROM @%tablea;
C. COPY INTO tablea FROM @%tablea FILES = (‘file5.csv’);
D. COPY INTO tablea FROM @%tablea FORCE = TRUE;
E. COPY INTO tablea FROM @%tablea NEW_FILES_ONLY = TRUE;
F. COPY INTO tablea FROM @%tablea MERGE = TRUE;

Selected Answer: BC

Question #: 8
Topic #: 1
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?

A. Create a pipeline to write shared data to a cloud storage location in the target cloud provider.
B. Ensure that all views are persisted, as views cannot be shared across cloud platforms.
C. Setup data replication to the region and cloud platform where the consumer resides.
D. 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.

Selected Answer: A

Question #: 23
Topic #: 1
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?

A. 1. Create a share in the Production account for each database
2. Share access to the QA account as a Consumer
3. The QA account creates a database directly from each share
4. Create clones of those databases on a nightly basis
5. Run tests directly on those cloned databases
B. 1. Create a stage in the Production account
2. Create a stage in the QA account that points to the same external object-storage location
3. Create a task that runs nightly to unload each table in the Production account into the stage
4. Use Snowpipe to populate the QA account
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
D. 1. In the Production account, create an external function that connects into the QA account and returns all the data for one specific table
2. Run the external function as part of a stored procedure that loops through each table in the Production account and populates each table in the QA account

Selected Answer: C

Question #: 11
Topic #: 1
A Data Engineer is designing a near real-time ingestion pipeline for a retail company to ingest event logs into Snowflake to derive insights. A Snowflake Architect is asked to define security best practices to configure access control privileges for the data load for auto-ingest to Snowpipe.
What are the MINIMUM object privileges required for the Snowpipe user to execute Snowpipe?

A. OWNERSHIP on the named pipe, USAGE on the named stage, target database, and schema, and INSERT and SELECT on the target table
B. OWNERSHIP on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table
C. CREATE on the named pipe, USAGE and READ on the named stage, USAGE on the target database and schema, and INSERT end SELECT on the target table
D. USAGE on the named pipe, named stage, target database, and schema, and INSERT and SELECT on the target table

Selected Answer: A

Question #: 9
Topic #: 1
What are some of the characteristics of result set caches? (Choose three.)

A. Time Travel queries can be executed against the result set cache.
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.
D. The data stored in the result cache will contribute to storage costs.
E. The retention period can be reset for a maximum of 31 days.
F. The result set cache is not shared between warehouses.

Selected Answer: BCE

Question #: 100
Topic #: 1
A retail company has over 3000 stores all using the same Point Of Sale (POS) system. The company wants to deliver near real-time sales results to category managers. The stores operate in a variety of time zones and exhibit a dynamic range of transactions each minute, with some stores having higher sales volumes than others.

Sales results are provided in a uniform fashion using data engineered fields that will be calculated in a complex data pipeline. Calculations include exceptions, aggregations, and scoring using external functions interfaced to scoring algorithms. The source data for aggregations has over 100M rows.

Every minute, the POS sends all sales transactions files to a cloud storage location with a naming convention that includes store numbers and timestamps to identify the set of transactions contained in the files. The files are typically less than 10MB in size.

How can the near real-time results be provided to the category managers? (Choose two.)

A. All files should be concatenated before ingestion into Snowflake to avoid micro-ingestion.
B. A Snowpipe should be created and configured with AUTO_INGEST = TRUE. A stream should be created to process INSERTS into a single target table using the stream metadata to inform the store number and timestamps.
C. A STREAM should be created to accumulate the near real-time data and a TASK should be created that runs at a frequency that matches the real-time analytics needs.
D. An external scheduler should examine the contents of the cloud storage location and issue SnowSQL commands to process the data at a frequency that matches the real-time analytics needs.
E. The COPY INTO command with a task scheduled to run every second should be used to achieve the near-real time requirement.

Selected Answer: B

Question #: 99
Topic #: 1
How can the Snowflake context functions be used to help determine whether a user is authorized to see data that has column-level security enforced? (Choose two.)

A. Set masking policy conditions using CURRENT_ROLE targeting the role in use for the current session.
B. Set masking policy conditions using IS_ROLE_IN_SESSION targeting the role in use for the current account.
C. Set masking policy conditions using INVOKER_ROLE targeting the executing role in a SQL statement.
D. Determine if there are OWNERSHIP privileges on the masking policy that would allow the use of any function.
E. Assign the ACCOUNTADMIN role to the user who is executing the object.

Selected Answer: C

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

A. The MERGE command
B. The UPSERT command
C. The CHANGES clause
D. A STREAM object
E. Thee CHANGE_DATA_CAPTURE command

Selected Answer: C

Question #: 74
Topic #: 1
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?

A. Ask the vendor to create a Snowflake account, load the data into Snowflake and create a data share.
B. Create an external stage on Google Cloud Storage and use the external table to load the data into Snowflake.
C. Copy the data from Google Cloud Storage to Azure Blob storage using external tools and load data from Blob storage to Snowflake.
D. Create a Snowflake Account in the Google Cloud Platform (GCP), ingest data into this account and use data replication to move the data from GCP to Azure.

Selected Answer: D

Question #: 68
Topic #: 1
Which technique will efficiently ingest and consume semi-structured data for Snowflake data lake workloads?

A. IDEF1X
B. Schema-on-write
C. Schema-on-read
D. Information schema

Selected Answer: A

Question #: 28
Topic #: 1
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.)

A. Consolidate data in the company’s data lake and use EXTERNAL TABLES.
B. Create a raw database for landing and persisting raw data entering the data pipelines.
C. Create a set of profile-specific databases that aligns data with usage patterns.
D. Create a single star schema in a single database to support all consumers’ requirements.
E. Create a Data Vault as the sole data pipeline endpoint and have all consumers directly access the Vault.

Selected Answer: BC

Question #: 83
Topic #: 1
Based on the architecture in the image, how can the data from DB1 be copied into TBL2? (Choose two.)

A.
B.
C.
D.
E.

Selected Answer: D

Question #: 81
Topic #: 1
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?

A. Connect the applications using the – URL.
Use the Business Critical Snowflake edition.
B. Connect the applications using the – URL.
Use the Virtual Private Snowflake (VPS) edition.
C. Connect the applications using the – URL.
Use the Enterprise Snowflake edition.
D. Connect the applications using the – URL.
Use the Business Critical Snowflake edition.

Selected Answer: B

Question #: 80
Topic #: 1
Which query will identify the specific days and virtual warehouses that would benefit from a multi-cluster warehouse to improve the performance of a particular workload?

A.
B.
C.
D.

Selected Answer: B

Question #: 72
Topic #: 1
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?

A. IOT_timestamp
B. City and DeviceManufacturer
C. DeviceId and CustomerId
D. UniqueId

Selected Answer: D

Question #: 64
Topic #: 1
What considerations need to be taken when using database cloning as a tool for data lifecycle management in a development environment? (Choose two.)

A. Any pipes in the source are not cloned.
B. Any pipes in the source referring to internal stages are not cloned.
C. Any pipes in the source referring to external stages are not cloned.
D. The clone inherits all granted privileges of all child objects in the source object, including the database.
E. The clone inherits all granted privileges of all child objects in the source object, excluding the database.

Selected Answer: C

Question #: 70
Topic #: 1
Which of the following ingestion methods can be used to load near real-time data by using the messaging services provided by a cloud provider?

A. Snowflake Connector for Kafka
B. Snowflake streams
C. Snowpipe
D. Spark

Selected Answer: C

Question #: 16
Topic #: 1
A company wants to deploy its Snowflake accounts inside its corporate network with no visibility on the internet. The company is using a VPN infrastructure and Virtual Desktop Infrastructure (VDI) for its Snowflake users. The company also wants to re-use the login credentials set up for the VDI to eliminate redundancy when managing logins.
What Snowflake functionality should be used to meet these requirements? (Choose two.)

A. Set up replication to allow users to connect from outside the company VPN.
B. Provision a unique company Tri-Secret Secure key.
C. Use private connectivity from a cloud provider.
D. Set up SSO for federated authentication.
E. Use a proxy Snowflake account outside the VPN, enabling client redirect for user logins.

Selected Answer: CD

Question #: 24
Topic #: 1
A user can change object parameters using which of the following roles?

A. ACCOUNTADMIN, SECURITYADMIN
B. SYSADMIN, SECURITYADMIN
C. ACCOUNTADMIN, USER with PRIVILEGE
D. SECURITYADMIN, USER with PRIVILEGE

Selected Answer: C

Question #: 45
Topic #: 1
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.)

A. Clone a table from a share.
B. Grant modify permissions on the share.
C. Create a table from the shared database.
D. Create additional views inside the shared database.
E. Create a table stream on the shared table.

Selected Answer: CE

Question #: 26
Topic #: 1
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?

A. Create accounts for each tenant in the Snowflake organization.
B. Create an object for each tenant strategy if row level security is viable for isolating tenants.
C. Create an object for each tenant strategy if row level security is not viable for isolating tenants.
D. Create a multi-tenant table strategy if row level security is not viable for isolating tenants.

Selected Answer: C

Question #: 92
Topic #: 1
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?

A. There were JSON nulls in the recent data imports.
B. The order of the keys in the JSON was changed.
C. The recent data imports contained fewer fields than usual.
D. There were variations in string lengths for the JSON values in the recent data imports.

Selected Answer: A

Question #: 32
Topic #: 1
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.)

A. Use Snowpipe with auto-ingest.
B. Use a COPY command with a task.
C. Use a materialized view on an external table.
D. Use the COPY INTO command.
E. Use a combination of a task and a stream.

Selected Answer: AE

Question #: 98
Topic #: 1
When using the COPY INTO [table] command with the CSV file format, how does the MATCH_BY_COLUMN_NAME parameter behave?

A. It expects a header to be present in the CSV file, which is matched to a case-sensitive table column name.
B. The parameter will be ignored.
C. The command will return an error.
D. The command will return a warning stating that the file has unmatched columns.

Selected Answer: A

Question #: 61
Topic #: 1
A company has a Snowflake account named ACCOUNTA in AWS us-east-1 region. The company stores its marketing data in a Snowflake database named MARKET_DB. One of the company’s business partners has an account named PARTNERB in Azure East US 2 region. For marketing purposes the company has agreed to share the database MARKET_DB with the partner account.
Which of the following steps MUST be performed for the account PARTNERB to consume data from the MARKET_DB database?

A. Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA create a share of database MARKET_DB, create a new database out of this share locally in AWS us-east-1 region, and replicate this new database to AZABC123 account. Then set up data sharing to the PARTNERB account.
B. From account ACCOUNTA create a share of database MARKET_DB, and create a new database out of this share locally in AWS us-east-1 region. Then make this database the provider and share it with the PARTNERB account.
C. Create a new account (called AZABC123) in Azure East US 2 region. From account ACCOUNTA replicate the database MARKET_DB to AZABC123 and from this account set up the data sharing to the PARTNERB account.
D. Create a share of database MARKET_DB, and create a new database out of this share locally in AWS us-east-1 region. Then replicate this database to the partner’s account PARTNERB.

Selected Answer: D

Question #: 40
Topic #: 1
How is the change of local time due to daylight savings time handled in Snowflake tasks? (Choose two.)

A. A task scheduled in a UTC-based schedule will have no issues with the time changes.
B. Task schedules can be designed to follow specified or local time zones to accommodate the time changes.
C. A task will move to a suspended state during the daylight savings time change.
D. A frequent task execution schedule like minutes may not cause a problem, but will affect the task history.
E. A task schedule will follow only the specified time and will fail to handle lost or duplicated hours.

Selected Answer: AE

Question #: 62
Topic #: 1
What does a Snowflake Architect need to consider when implementing a Snowflake Connector for Kafka?

A. Every Kafka message is in JSON or Avro format.
B. The default retention time for Kafka topics is 14 days.
C. The Kafka connector supports key pair authentication, OAUTH, and basic authentication (for example, username and password).
D. The Kafka connector will create one table and one pipe to ingest data for each topic. If the connector cannot create the table or the pipe it will result in an exception.

Selected Answer: D