Describe considerations for working with non-relational data on Azure →hardMultiple ChoiceObjective-mapped

DP-900 Practice Question: Describe considerations for working with non-relational data on Azure

Exhibit

Refer to the exhibit.

```sql
CREATE TABLE SensorData (
    DeviceID INT,
    Timestamp DATETIME2,
    Temperature FLOAT,
    Humidity FLOAT
)
WITH (
    DISTRIBUTION = HASH(DeviceID),
    CLUSTERED COLUMNSTORE INDEX
);
```

You are analyzing a SQL script for an Azure Synapse Analytics dedicated SQL pool as shown in the exhibit. The table 'SensorData' will contain billions of rows. Which statement about the table design is correct?

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

The table uses a clustered columnstore index, which is ideal for large data warehousing tables

A hash distribution on DeviceID distributes rows across distributions based on the hash of DeviceID, which is good for large tables queried frequently by DeviceID. Clustered columnstore index is optimal for large tables in Synapse. Round-robin is for staging tables. Clustered index is for small tables. The table is not replicated because replication is for small dimension tables.

Answer analysis

Option-by-option breakdown

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

✓
The table uses a clustered columnstore index, which is ideal for large data warehousing tables
Why this is correct
Clustered columnstore indexes store data column-wise rather than row-wise, enabling high compression and eliminating unnecessary column scans. For large data warehousing fact tables in Azure Synapse Analytics, this index type is explicitly recommended because it dramatically reduces storage footprint and accelerates analytical queries through batch-mode processing and column elimination. The script's CREATE TABLE command specified this index, making it the accurate description.
✗
The table is replicated across all compute nodes
Why it's wrong here
Replicated tables maintain a full copy of the table on every compute node, which is a strategy reserved for small dimension tables to avoid data movement during joins. In contrast, this script uses HASH distribution on a specified column, which distributes rows across nodes based on a hash of that column rather than replicating the entire table. Replicating a large fact table would be wasteful and incur high storage and maintenance overhead, which is why this option is incorrect.
✗
The table uses round-robin distribution
Why it's wrong here
Round-robin distribution assigns rows to distribution slots in a sequential, cyclic manner without any hashing or ordering logic. While this is useful for staging or rapidly loading data, it is not the distribution method used here because the script explicitly defines HASH distribution on a column. Also, round-robin tables are generally poor for large fact tables as they create heavy data movement during joins and aggregations, so this option is not the correct characterization.
✗
The table uses a heap structure
Why it's wrong here
A heap is a table without a clustered index, where rows are stored in an unordered fashion; heaps are commonly used for staging or temporary tables where fast data insertion is needed and no query optimization is required. However, the script in question creates a clustered columnstore index, which fundamentally differs from a heap by organizing data into compressed column segments. Since columnstore indexing is explicitly part of the definition, calling it a heap structure is factually wrong.

Go deeper

DP-900 Practice Question: Describe considerations for working with non-relational data on Azure

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question