ARA-C01 Data Engineering Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Run an INSERT for new keys and a separate UPDATE for existing keys as two statements inside an explicit transaction.

  Why it's wrong here

  Two separate statements can work but are not atomic with respect to concurrent readers unless wrapped carefully, and they do not handle multiple source rows per key without additional deduplication logic. The UPDATE would also need a correlated subquery or join that can produce nondeterministic results when duplicates exist. This is more error-prone than a single MERGE with a deduplicated source.

• ✗

  Use INSERT OVERWRITE to replace the entire target table with a fresh join of the staging table and the existing target.

  Why it's wrong here

  INSERT OVERWRITE replaces all rows in the target, which is expensive for a large dimension and can break dependent objects or time travel expectations during the swap. It also requires a correct join that handles duplicates, so the deduplication problem remains. For late-arriving updates to a large table, a targeted MERGE is far more efficient than rewriting the whole table.

• ✗

  Create a Stream on the staging table and consume it with a task that issues individual UPDATE statements per changed row.

  Why it's wrong here

  A Stream captures changes, but issuing one UPDATE per changed row is inefficient at scale and still does not deduplicate multiple changes to the same key within a single stream batch. The task would need additional logic to collapse records. This approach adds complexity and cost without solving the duplicate-key problem that MERGE with a deduplicated source handles directly.

• ✓

  Use a MERGE statement whose source is a subquery that deduplicates the staging table by business key, keeping the latest record per key.

  Why this is correct

  MERGE applies inserts, updates, and optionally deletes in one atomic statement. Deduplicating the source with a windowed subquery that ranks rows per business key and keeps the most recent ensures each target row is matched at most once. This prevents the error Snowflake raises when a source row joins to multiple target rows or the source has duplicates for a matched key, giving deterministic results.