Microsoft DP-600 Exam - Topic 2 Question 44 Discussion

Actual exam question for Microsoft's DP-600 exam

Question #: 44
Topic #: 2

You have a Fabric tenant that contains a warehouse.

You are designing a star schema model that will contain a customer dimension. The customer dimension table will be a Type 2 slowly changing dimension (SCD).

You need to recommend which columns to add to the table. The columns must NOT already exist in the source.

Which three types of columns should you recommend? Each correct answer presents part of the solution.

NOTE: Each correct answer is worth one point.

Aan effective end date and time

Ba foreign key

Ca surrogate key

Da natural key

Ean effective start date and time

Show Suggested Answer

Suggested Answer: A, C, E

For a Type 2 slowly changing dimension (SCD), you typically need to add the following types of columns that do not exist in the source system:

An effective start date and time (E): This column records the date and time from which the data in the row is effective.

An effective end date and time (A): This column indicates until when the data in the row was effective. It allows you to keep historical records for changes over time.

A surrogate key (C): A surrogate key is a unique identifier for each row in a table, which is necessary for Type 2 SCDs to differentiate between historical and current records.

by Marya at Nov 25, 2025, 10:32 PM

Limited Time Offer

25%

Off

Get Premium DP-600 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Della

2 months ago

Surrogate key, effective start, and end dates are the way to go!

upvoted 0 times

...

Fredric

2 months ago

I feel like the foreign key could complicate things. Let's keep it simple.

upvoted 0 times

...

Judy

2 months ago

A natural key might not be necessary since we have the surrogate key.

upvoted 0 times

...

Keena

2 months ago

I agree with both. A surrogate key is essential too for uniqueness.

upvoted 0 times

...

Rosalyn

2 months ago

Definitely! An effective end date and time is also important for the SCD.

upvoted 0 times

...

Kristal

2 months ago

I think we need an effective start date and time. It's crucial for tracking changes.

upvoted 0 times

...

Tiara

3 months ago

Surrogate key? Really? Seems a bit redundant.

upvoted 0 times

...

Reuben

3 months ago

Effective end date and time is essential, no doubt!

upvoted 0 times

...

Tricia

3 months ago

I thought a natural key was important too?

upvoted 0 times

...

Carmen

4 months ago

A surrogate key is a must for SCD Type 2.

upvoted 0 times

...

Isaac

4 months ago

B? Really? Why would you need a foreign key in a dimension table? *facepalm*

upvoted 0 times

...

Merilyn

4 months ago

A, C, E - Can't go wrong with the standard SCD setup.

upvoted 0 times

...

Justine

4 months ago

A, C, E - Easy peasy, these are the classic SCD columns.

upvoted 0 times

...

Ressie

4 months ago

I feel like we need to include both start and end dates, but I'm not sure if we need the natural key as well. I guess it could be A and E, but what about D?

upvoted 0 times

...

Odelia

4 months ago

I practiced a similar question last week, and I think a surrogate key is essential for uniquely identifying each record. So, C should definitely be one of the answers.

upvoted 0 times

...

Junita

5 months ago

I'm a bit unsure about the foreign key. I thought it was more relevant for linking tables rather than for SCDs. Could it be B?

upvoted 0 times

...

Kaitlyn

5 months ago

I remember we discussed Type 2 SCDs in class, and I think we need to track the effective start and end dates for changes. So, E might be one of the answers.

upvoted 0 times

...

Paulina

5 months ago

Got it, I'll go with A), C), and E) as my three recommendations. That should cover the basics for this type of slowly changing dimension.

upvoted 0 times

...

I think the key is to focus on the core components of a Type 2 SCD - the effective dates, the surrogate key, and potentially a natural key if it's available in the source. B) and D) are less essential for this particular design.

upvoted 0 times

...

Lenora

5 months ago

A, C, E - Gotta have those effective dates and a surrogate key.

upvoted 0 times

...

Veronika

5 months ago

Definitely need an effective start date and time!

upvoted 0 times

...

Chauncey

6 months ago

I'm a bit confused about the foreign key and natural key options. Do I need both of those as well?

upvoted 0 times

...

Roxane

6 months ago

A, C, E - Yup, that's the way to do it. No need to reinvent the wheel.

upvoted 0 times

...

Penney

6 months ago

A, C, E - This covers the key elements of a Type 2 SCD.

upvoted 0 times

...

Marylou

6 months ago

Okay, let's see. I know I need to include an effective start and end date, so that's A) and E). And a surrogate key is definitely important, so C) is a must.

upvoted 0 times

...