ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam DP-600 All Questions

View all questions & answers for the DP-600 exam
Go to Exam

Exam DP-600 topic 1 question 85 discussion

Actual exam question from Microsoft's DP-600
Question #: 85
Topic #: 1
[All DP-600 Questions]

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.

  • A. a foreign key
  • B. a natural key
  • C. an effective end date and time
  • D. a surrogate key
  • E. an effective start date and time
Show Suggested Answer Hide Answer
Suggested Answer: CDE 🗳️
by VAzureD at April 24, 2024, 11:32 a.m.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
6c79d6f
Highly Voted 5 months, 2 weeks ago
Selected Answer: CDE
Because the table is set to SCD2 there will be a new row for each change in an existing row with an start and an end date (from when to when the row was valid). therefore the ' old' primary key will be duplicated. That is why a new key, a surrogate key, is introduced which makes each row unique again
upvoted 13 times
...
stilferx
Most Recent 6 months ago
Selected Answer: CDE
IMHO, CDE, because: start & end - must surrogate - which is autoincremental id - is must. Link: https://learn.microsoft.com/en-us/training/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types
upvoted 4 times
...
newusername
6 months, 2 weeks ago
Selected Answer: CDE
To create SCD type 2 one needs to add a surrogate key + start/end date beside the other technical attributes. Therefore CDE.
upvoted 3 times
...
vish9
6 months, 3 weeks ago
Selected Answer: CDE
As per chat GPT: Surrogate keys are typically used in dimension tables rather than fact tables. In a data warehouse, a surrogate key is a unique identifier assigned to each record in a dimension table, usually for internal processing and joining purposes. It provides a stable reference to the dimension record, regardless of any changes in the natural key or other attributes.
upvoted 1 times
dp600
6 months, 2 weeks ago
chatgpt is not a source
upvoted 6 times
b4163b3
6 months, 2 weeks ago
It is often a better source than some random here...
upvoted 1 times
...
...
...
Unbounded
6 months, 3 weeks ago
Selected Answer: BCE
B: A natural key would be the Dim tables own Primarykey column not the source Primary key C & D: is requires to incorporate SCD Type 2.
upvoted 1 times
Unbounded
6 months, 3 weeks ago
Sorry there is a type in my comment above it should read C&E so the correct answer is BC&E
upvoted 1 times
newusername
6 months, 2 weeks ago
I didn't get, why would you create a new "Dim table own Primarykey"?
upvoted 1 times
...
...
2fe10ed
6 months, 1 week ago
A dimension's "Natural Key" is usually part of its description elements, like the SSN in the US. That information would already be present in the source table, and it's not the object of the question since it states that "The columns must NOT already exist in the source." Since the question states that we are building a star schema, the foreign keys should be present only on the fact table. Thus, the correct answer needs to be CDE.
upvoted 4 times
...
...
VAzureD
6 months, 3 weeks ago
Selected Answer: CDE
Let's discard, A. A FOREIGN KEY in SQL is a key (a column field) that is used to relate two tables. The FOREIGN KEY field is related or linked to the PRIMARY KEY of another database table. It already exists at the origin. B. Natural key, likewise, already exists in the origin. CDE, are the fields that we must create in our ETL to create an SCD2.
upvoted 2 times
Unbounded
6 months, 3 weeks ago
Surrogate key is used in Fact Tables not Dim Tables
upvoted 1 times
AdventureChick
5 months ago
A surrogate key is absolutely used in Dim tables (except for DimDate where we usually use the YYYYMMDD as a primary key). From Kimball: "Surrogate keys are used to implement the primary keys of almost all dimension tables. In addition, single column surrogate fact keys can be useful, albeit not required." https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/fact-surrogate-key/#:~:text=Surrogate%20keys%20are%20used%20to,be%20useful%2C%20albeit%20not%20required.
upvoted 1 times
...
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago