DP-900 Describe an analytics workload on Azure 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.

• ✗

  Use INSERT statements to add rows incrementally

  Why it's wrong here

  Using individual INSERT statements for each row or small batches generates excessive transaction log entries and lock requests, which severely degrades concurrent query performance in a dedicated SQL pool. Because Synapse uses a massively parallel processing (MPP) architecture, row-by-row operations bypass the benefits of bulk loading and force the engine to continually maintain rowstore indexes and statistics. For large daily fact loads, this approach is orders of magnitude slower than a bulk-load staging pattern.

• ✗

  Use CREATE TABLE AS SELECT (CTAS) to build a new table and rename it

  Why it's wrong here

  CREATE TABLE AS SELECT (CTAS) is a set-based operation that avoids row-level locking, but it rebuilds the entire table, including re-distributing and re-writing all existing rows, which is expensive for a frequently updated fact table. The subsequent rename to swap the new table into place is a metadata operation, yet it creates a window where the old table cannot be queried, and any queries referencing the old name may fail or be blocked during the swap. This pattern is better suited for one-time or periodic full refreshes, not for adding a daily partition of data.

• ✓

  Load data into a staging table, then use partition switching to swap the latest partition

  Why this is correct

  Staging the new data into a table with the same schema and partition alignment, then using ALTER TABLE ... SWITCH PARTITION, is the recommended pattern for loading incremental fact data in Azure Synapse. This operation moves whole partition boundaries as a metadata-only change, so it completes in milliseconds, does not rewrite indexes row-by-row, and only requires a brief schema modification lock that does not block concurrent reads. Because the staging table is separate, the target fact table remains fully available during load, and after the switch the latest partition is atomically visible to all queries.

• ✗

  Use PolyBase to load data directly into the fact table

  Why it's wrong here

  PolyBase is designed for reading external data or loading flat files, but loading directly into the fact table with INSERT ... SELECT from PolyBase causes full table or partition locking, index fragmentation, and increased log activity because every row is absorbed into the target immediately. It also does not take advantage of staging and atomic partition replacement, so any validation or transformation errors can leave the fact table in an inconsistent state. While PolyBase avoids ETL tooling, it is still a bulk load that conflicts with concurrent reporting workloads, making it inferior to partition switching.

Go deeper

Related to this question