Design and implement data storage →easyMultiple ChoiceObjective-mapped

DP-203 Design and implement data storage Practice Question

Exhibit

Refer to the exhibit.

SELECT 
  COUNT(DISTINCT user_id) AS unique_users,
  COUNT(DISTINCT session_id) AS unique_sessions
FROM visits
WHERE date BETWEEN '2024-01-01' AND '2024-01-31';

You run the above query on a table named 'visits' in a dedicated SQL pool. The table has 1 billion rows and is hash-distributed on user_id. The query takes a long time. What is the most likely reason?

⚠ Common exam trap

The trap here is that candidates often blame the distribution key mismatch (Option B) or filter pushdown (Option A), overlooking the fact that COUNT(DISTINCT) forces a global data movement step regardless of distribution strategy.

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

✓

COUNT(DISTINCT) operations are expensive because they require data movement across distributions.

In a dedicated SQL pool, COUNT(DISTINCT) is inherently expensive because it requires all distinct values to be gathered across distributions before counting. Since the table is hash-distributed on user_id, the distinct count on a different column (likely visit_date or another attribute) forces data shuffling across all distributions to ensure uniqueness, causing significant performance degradation.

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 query uses a date filter which cannot be pushed down to the distribution.
Why it's wrong here
Date filter can be pushed down, but the main bottleneck is the distinct count.
✗
The table is hash-distributed on user_id, but the query uses a different column for aggregation.
Why it's wrong here
user_id is the distribution key; this should help, but COUNT(DISTINCT) still requires shuffling.
✗
The table should use a replicated distribution instead of hash distribution.
Why it's wrong here
Replicated distribution is for small tables; this table is large.
✓
COUNT(DISTINCT) operations are expensive because they require data movement across distributions.
Why this is correct
COUNT(DISTINCT) needs to combine distinct values from all distributions.

Go deeper

DP-203 Design and implement data storage Practice Question

Exhibit

Why each option matters

Option-by-option breakdown

Related to this question