SnowPro Data Engineer DEA-C02: Sharing and Replication

Topic snapshot

Sample questions

These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.

Question 1

Topic: Sharing, Replication, and Cross-Platform Delivery

A data engineering team is deciding how to expose curated tables to another Snowflake account.

Consumer account: ANALYTICS_DEV
Cloud/region: same as provider account
Access needed: read-only on curated tables
Provider updates: must be visible without copying data
Independent writes in consumer: no
DR/failover objective: none

Based on the exhibit, what is the best next step?

Options:

• A. Publish a listing with auto-fulfillment.

• B. Replicate the database to the consumer account.

• C. Create a reader account for the consumer team.

• D. Create a direct share for the curated tables.

Best answer: D

Explanation: This is a collaboration requirement, not a replication or failover requirement. Because the consumer already has a Snowflake account and only needs read-only access to current provider-managed data in the same region, a direct share is the best fit.

Secure Data Sharing is the Snowflake pattern for collaboration when consumers need read-only access to live provider data. The exhibit explicitly rules out the usual reasons to copy data: there is no disaster recovery goal, no locality requirement, and no need for independent writes in the consumer account. Because the consumer account already exists and is in the same cloud and region, the provider can create a direct share, grant the curated objects to that share, and let the consumer query the latest data without storage duplication.

• Use sharing for centralized, read-only collaboration.
• Use replication when data must be copied for locality, business continuity, or failover.
• Use reader accounts only when the consumer does not have its own Snowflake account.

A listing can also distribute shared data, but for one known same-region consumer it is less direct than a standard share.

• Replication copies data for locality or business continuity, which the exhibit explicitly says is not required.
• A reader account is intended for consumers that do not already have a Snowflake account, but the exhibit names an existing one.
• A listing with auto-fulfillment is better for broader or cross-region/cross-cloud distribution, not the simplest path for one known same-region consumer.

Question 2

Topic: Sharing, Replication, and Cross-Platform Delivery

A team exposes customer profiles through an internal REST service that uses the Snowflake SQL API. After a masking policy was added to email and phone, the service still authenticates successfully, queries finish normally, and fresh rows appear in the API response. However, those two columns are always masked, while analysts using a different authorized role see cleartext values. What is the best next step?

Options:

• A. Rework the SQL API OAuth integration

• B. Restart the refresh task graph

• C. Resize the virtual warehouse

• D. Review the service role and masking policy conditions

Best answer: D

Explanation: This is a data access issue. Authentication works, queries complete, and fresh rows are returned, so the main problem is that masking policy evaluation for the service role is producing masked values.

The key distinction is where the symptom appears. Control plane integration problems usually prevent authentication, session creation, or API request execution. Pipeline execution problems usually show up as stale, missing, or incomplete data. Here, the service authenticates, the SQL runs successfully, and fresh rows are returned, but protected columns differ by role.

That pattern points to data access logic, specifically how the masking policy evaluates for the role used by the REST service. The next step is to verify the service role, any database-role or view-based access path it uses, and the masking policy condition that determines when cleartext is allowed.

Restarting tasks or resizing compute would not explain successful queries that return current rows with only specific columns masked.

• OAuth mismatch is unlikely because the service already authenticates and executes queries successfully.
• Task restart does not fit because fresh rows are already visible in the API response.
• Warehouse resize addresses latency or queuing, not role-dependent masking results.

Question 3

Topic: Sharing, Replication, and Cross-Platform Delivery

A team loads and transforms order data with streams, tasks, and MERGE statements. An external web service retrieves the final ORDER_STATUS table through the Snowflake SQL API. During hourly backfills, the web service times out, but task runs finish successfully and ORDER_STATUS is current. Query history shows the web service SELECT statements queued on the same warehouse used by the backfill tasks. What is the best next step?

Options:

• A. Replace SQL API calls with Snowpipe Streaming

• B. Rewrite the backfill logic in Snowpark Python

• C. Add a stream on ORDER_STATUS for the web service

• D. Route SQL API queries to a dedicated warehouse

Best answer: D

Explanation: The pipeline is producing current data, so the transformation layer is working. The problem is warehouse contention for the application-facing SQL API queries, which are part of delivery design, so compute isolation is the right fix.

The deciding clue is that tasks complete successfully and the target table is already current. That means the main transformation logic is not failing. The timeout happens when the external service submits SELECT statements through the SQL API, and those statements are queued behind heavy backfill work on the same warehouse.

• The SQL API is only the query submission surface for the consuming application.
• Shared compute is causing delivery latency.
• A separate warehouse isolates serving traffic from ETL spikes.

Rewriting the transforms or changing ingestion features would not address the actual bottleneck, which is queued delivery workload on shared compute.

• Rewrite transforms changes implementation style, but the stem shows the transforms already succeed and keep the table current.
• Use Snowpipe Streaming targets low-latency ingestion, not queued SELECT statements from a consuming application.
• Expose a stream is for tracking table changes, not for serving application reads or removing warehouse contention.

Question 4

Topic: Sharing, Replication, and Cross-Platform Delivery

A team uses database replication to maintain a DR environment in another region. During a failover test, the replicated database is available and the table data is within the expected RPO, but the ingestion service cannot restart because the service user, role grants, and warehouse are missing in the secondary account. Which next step best addresses this problem?

Options:

• A. Use a failover group for databases and required account objects.

• B. Upsize the DR warehouse for recovery tasks.

• C. Increase the database replication refresh frequency.

• D. Rebuild ingestion with Snowpipe Streaming.

Best answer: A

Explanation: This symptom points to continuity, not freshness. The replicated data is already within the target RPO, so tuning ingestion or compute will not restore missing users, roles, grants, or warehouses in the secondary account.

Database replication helps keep replicated data objects available in another region, but it does not by itself solve every recovery dependency. Here, the data copy is already current enough, yet failover still cannot proceed because required account-level objects are absent. That makes this a continuity design issue.

A failover group is the appropriate Snowflake mechanism when recovery must include databases plus dependent account objects needed to run the workload after failover. By contrast, pipeline freshness tools such as faster ingestion, lower-latency CDC, or more warehouse compute address how quickly new data arrives or is transformed. They do not fix missing identities, grants, or warehouses in the DR account.

The key takeaway is to separate recovery readiness from data-latency tuning: this scenario needs broader replication scope, not a faster pipeline.

• More frequent refresh can improve replica currency, but it does not add the missing account objects needed to resume service.
• Lower-latency ingestion addresses fresh data arrival, but the stem says data was already within the expected RPO.
• More warehouse compute may help queueing or task runtime, but it cannot recreate missing users, roles, or grants in the secondary account.

Question 5

Topic: Sharing, Replication, and Cross-Platform Delivery

A data engineering team is exposing curated Snowflake data to an internal serverless REST service. The service must submit SQL statements and call a stored procedure over HTTPS, poll long-running requests asynchronously, and avoid installing JDBC or ODBC drivers on ephemeral compute. Which Snowflake design is the best fit?

Options:

• A. Create an external function that routes requests through API Gateway

• B. Publish the tables with Secure Data Sharing

• C. Use the Snowflake SQL API from the service

• D. Replicate the database to a separate account for the service

Best answer: C

Explanation: The Snowflake SQL API is designed for HTTPS-based programmatic access to Snowflake workloads. It fits stateless services well because it supports submitting SQL, checking status asynchronously, and keeping access control in Snowflake without adding driver management.

The key concept is choosing the Snowflake-native integration path that matches a REST-driven application pattern. For a serverless service that must call Snowflake over HTTPS and handle long-running work asynchronously, the Snowflake SQL API is the best fit. It lets the application submit SQL statements, including CALL statements for stored procedures, receive a handle for execution, and poll for completion without requiring JDBC or ODBC drivers on short-lived compute.

This matches the stem because it provides:

• HTTPS-based programmatic access
• Async execution and status polling
• Snowflake-native role-based governance

The closest distractors either solve a different problem or add unnecessary architecture instead of meeting the REST integration requirement directly.

• External function mismatch: external functions let Snowflake call an external service, not an external application submit work into Snowflake.
• Sharing is not an API: Secure Data Sharing exposes governed data to another Snowflake account, but it does not provide REST-based query execution for an application.
• Replication adds overhead: replicating to another account may isolate workloads, but it does not address the HTTPS and no-driver integration requirement.

Question 6

Topic: Sharing, Replication, and Cross-Platform Delivery

A provider is considering secure sharing for consumers in another region. However, the secondary setup must keep replicated databases and supporting account objects so it can be promoted and continue operating if the primary account is unavailable. Which Snowflake feature meets this requirement?

Options:

• A. Database replication

• B. Secure data sharing

• C. Reader accounts

• D. Failover groups

Best answer: D

Explanation: Failover groups are for business continuity, not just consumer access. They replicate selected databases and supported account objects to a secondary account and allow promotion during an outage, which a sharing-based approach does not provide.

Secure sharing is a data-access mechanism: consumers can query provider-owned data, but they do not receive an independently replicated environment. When the requirement is a secondary account that can continue operating if the primary is unavailable, Snowflake needs replication with failover capability. Failover groups fit that need because they replicate selected databases plus supported account objects to a secondary account and support promotion of that secondary environment.

• Use sharing when consumers only need live access to current data.
• Use database replication when the scope is limited to database copies.
• Use failover groups when the secondary environment must be promotable and operational on its own.

That is why a consumer-facing sharing answer is insufficient here.

• Live access only The option using secure sharing fails because it exposes provider data without creating an independent replicated environment.
• Too narrow The option using database replication misses the requirement for supporting account objects and promotion of the secondary environment.
• Not replication The option using reader accounts is still a sharing model for consumers, not a replicated disaster recovery setup.

Question 7

Topic: Sharing, Replication, and Cross-Platform Delivery

A central Snowflake team distributes customer data to Finance and Sales in separate consumer accounts. To prepare for later cross-region delivery, an architect proposes distributing only raw tables and letting each team maintain its own secure views and task logic locally; analysts already report different active_customer counts, and each schema change triggers fixes in multiple accounts. Which next step best addresses the problem while preserving maintainability and cross-team clarity?

Options:

• A. Centralize KPI logic in a producer-managed curated database and deliver that same layer to all teams.

• B. Keep distributing raw tables and use task graphs in each consumer account to recreate local views after changes.

• C. Replicate the raw database to more regions first, then let each team standardize its own views.

• D. Create separate shared databases per team so each group can rename and organize objects independently.

Best answer: A

Explanation: The failure is semantic drift, not data movement. When each consumer account owns its own view logic, KPI definitions diverge and every upstream change must be implemented multiple times; a producer-managed curated layer keeps one authoritative definition for all teams.

When the stated need is maintainability and cross-team clarity, distributing raw tables and letting every team build its own secure views is the wrong architecture. Even if shared or replicated data is current, local modeling creates duplicate business logic, conflicting KPI definitions, and repeated remediation after every schema change. A better Snowflake pattern is to publish a producer-managed curated database, often with standardized secure views, and deliver that same governed layer to each consumer. Then business-rule updates and schema adjustments happen once, and all teams see the same meaning for active_customer. Replication can still help later with regional delivery, but it should move the curated layer rather than multiply independent semantic definitions.

• Rebuilding local views with task graphs automates drift, but it does not remove duplicate ownership of business logic.
• Replicating raw data to more regions improves placement, not consistency of KPI definitions across teams.
• Giving each team its own shared database structure increases renaming and semantic divergence instead of clarity.

Question 8

Topic: Sharing, Replication, and Cross-Platform Delivery

A Snowflake team ingests partner data from three operational systems, replicates curated tables to another region, and shares them with external consumers. Pipelines finish on time, but source corrections overwrite prior values in the published tables, so consumers cannot reconstruct multi-year history of keys, relationships, and attributes across systems. Engineers propose adding more streams and faster tasks. What is the best next step?

Options:

• A. Increase stream consumption and task cadence on the current-state tables

• B. Add search optimization service to the published tables

• C. Replace the published tables with dynamic tables that refresh more often

• D. Redesign the integration layer with a historized model such as Data Vault

Best answer: D

Explanation: This is no longer mainly an ingestion-timing problem. The requirement is durable, multi-source, multi-year history, so the right move is to revisit the integration model with a historized pattern such as Data Vault before tuning streams, tasks, or refresh frequency.

When published tables overwrite prior values, increasing pipeline frequency only moves the same current-state design faster. The real issue is architectural: external consumers need long-term traceability of business keys, relationships, and descriptive changes across multiple source systems. A historized approach such as Data Vault is designed for that by separating keys, relationships, and changing attributes so history survives source corrections, late-arriving data, and cross-system integration.

In Snowflake, streams, tasks, and dynamic tables help implement ingestion and transformation, but they do not replace the need for the right core model. A strong pattern is to preserve integrated history in the core layer, then publish consumer-friendly marts or shared tables from that foundation. The closest distractor is faster CDC orchestration, but the broken part here is the model, not the load cadence.

• Increasing stream and task cadence only captures current-state changes sooner; it does not create a durable historical model across sources.
• Replacing the layer with dynamic tables changes refresh mechanics, not the underlying historization strategy.
• Search optimization improves selective query performance, but the symptom is missing historical lineage rather than slow lookups.

Question 9

Topic: Sharing, Replication, and Cross-Platform Delivery

A data engineering team lands source data as Parquet in cloud object storage. Snowflake must transform the data into curated datasets, and Spark and Trino users must query those same curated tables without a separate export pipeline. Which Snowflake-oriented architecture best fits?

Options:

• A. Use external tables on the landing files

• B. Use native tables with secure data sharing

• C. Use native tables with dynamic tables

• D. Use Iceberg tables for the curated layer

Best answer: D

Explanation: Iceberg tables are the best fit when curated data must be usable both in Snowflake and in other query engines. They balance storage, transformation, and delivery by keeping the curated layer in an open table format instead of creating a separate export path.

The key concept is choosing an architecture that supports cross-platform delivery without duplicating the curated dataset. Iceberg tables are designed for this pattern: data stays in object storage in an open table format, Snowflake can transform and manage the curated layer, and engines such as Spark or Trino can read the same tables.

In this scenario, the deciding factor is not just transformation automation but the delivery model. External tables expose files in place, but they are not a Snowflake-managed curated table architecture. Native tables work well for Snowflake-only consumption, and dynamic tables help maintain derived data, but both keep the curated layer inside Snowflake. The best trade-off here is an open table architecture that serves both Snowflake and non-Snowflake consumers.

• External files only misses the need for a managed curated layer; external tables mainly expose existing files for query.
• Secure sharing is strong for Snowflake-to-Snowflake delivery, but it does not make the same tables directly readable by Spark or Trino.
• Dynamic tables help automate incremental transformations, but they do not turn native Snowflake tables into an open cross-engine format.

Question 10

Topic: Sharing, Replication, and Cross-Platform Delivery

A company uses secure data sharing from its primary Snowflake account to a reporting account. During a regional outage drill, the primary account became unavailable and reports against the shared database immediately failed. The business now requires a local secondary copy in another region and the ability to promote that secondary during failover. What is the best next step?

Options:

• A. Replace the consumer account with a reader account

• B. Publish the database through a listing

• C. Keep the secure share and resize the consumer warehouse

• D. Replicate to a secondary account and use a failover group

Best answer: D

Explanation: This requirement is about resilience and promoted failover, not just governed access. Secure data sharing provides live access without creating an independent copy, so it does not satisfy a need for a local secondary that can be promoted during an outage.

Secure data sharing and replication solve different problems. Secure sharing is best when consumers need governed, near-live access to provider data without managing extra copies. But it does not create an independently recoverable target for regional resilience or failover promotion. When the requirement is locality, disaster recovery, or promoted failover behavior, the right pattern is replication to a secondary account or region and management through a failover group.

• Replicate the required databases to the secondary environment.
• Use failover capabilities to promote the secondary during an outage.
• Treat sharing as a distribution mechanism, not a DR mechanism.

Changing compute or publishing method may help access patterns, but neither creates a promotable secondary copy.

• Warehouse sizing changes compute performance only; it cannot keep shared data available when the source account is down.
• Reader account still depends on provider-hosted shared data and does not create a secondary copy for promotion.
• Listing publication helps distribute data products, not provide regional failover or promoted recovery.

Continue with full practice

Use the SnowPro Data Engineer DEA-C02 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try SnowPro Data Engineer DEA-C02 on Web View SnowPro Data Engineer DEA-C02 Practice Test

Related focused pages

• Free SnowPro Data Engineer DEA-C02 Full-Length Practice Exam
• Sourcing and Ingestion
• Transformations and Workflows
• Streaming and Orchestration
• Compute and Governance

Free review resource

Read the SnowPro Data Engineer DEA-C02 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.