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

Exam DP-203 All Questions

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

Exam DP-203 topic 1 question 79 discussion

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

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named DimSalesPerson. DimSalesPerson contains the following columns:

• RepSourceID
• SalesRepID
• FirstName
• LastName
• StartDate
• EndDate
• Region

You are developing an Azure Synapse Analytics pipeline that includes a mapping data flow named Dataflow1. Dataflow1 will read sales team data from an external source and use a Type 2 slowly changing dimension (SCD) when loading the data into DimSalesPerson.

You need to update the last name of a salesperson in DimSalesPerson.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

  • A. Update three columns of an existing row.
  • B. Update two columns of an existing row.
  • C. Insert an extra row.
  • D. Update one column of an existing row.
Show Suggested Answer Hide Answer
Suggested Answer: CD 🗳️
by Yemeral at May 5, 2023, 10:53 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
bakamon
Highly Voted 2 years, 2 months ago
Selected Answer: BC
1) Insert an extra row with the updated last name and the current date as the StartDate. 2) Update two columns of an existing row: set the EndDate of the previous row for that salesperson to the current date and set the current value of the SalesRepID column to inactive.
upvoted 23 times
hypersam
7 months, 1 week ago
SalesRepID is either business key or surrogate key, and you don't change it... there is no column in this table that acts as active flag, so you only need to change EndDate column.
upvoted 2 times
...
bakamon
2 years, 2 months ago
This will preserve the history of changes to the salesperson’s last name while keeping the most current information in the table
upvoted 2 times
robmz
1 year, 4 months ago
Exactly. SCD TYPE2 Needs three columns: StartDate, EndDate and isActive flag.
upvoted 2 times
Dusica
1 year, 3 months ago
active flag is optional for type 2 and in this scenario does not exist
upvoted 6 times
...
...
...
...
Ankit_Az
Highly Voted 2 years, 2 months ago
Selected Answer: CD
CD is correct
upvoted 21 times
...
Stuartsen
Most Recent 5 months, 1 week ago
Selected Answer: CD
Correct C, D
upvoted 1 times
...
samianae
6 months, 2 weeks ago
Selected Answer: BC
correct answer B and C
upvoted 1 times
...
EmnCours
8 months, 2 weeks ago
Selected Answer: CD
Correct Answer: CD
upvoted 1 times
...
Dusica
1 year, 3 months ago
C - extra row with new surname and new start date D-set end date in the existing column That makes it type 2 - one of the variations off
upvoted 2 times
...
s_unsworth
1 year, 5 months ago
I originally thought it would be BC, but then I realised you WOULDN'T update the Last Name, you want to retain the history of the LastName. You would update the EndDate column for that record to mark it as historic and then insert a new row with the new LastName.
upvoted 7 times
...
dakku987
1 year, 7 months ago
Selected Answer: CD
CD is correct as in scd2 we need startdate,enddate that is already present what we need is "ISActive/flag" and one more row thats all its takes to make scd2
upvoted 1 times
...
hassexat
1 year, 11 months ago
Selected Answer: CD
C & D are correct
upvoted 3 times
...
tankwayep
1 year, 11 months ago
Selected Answer: CD
- Update one column: EndDate to the change date - Insert a new record with the new value of LastName, StartDate as the change date.
upvoted 10 times
...
kkk5566
1 year, 11 months ago
Selected Answer: CD
correct
upvoted 2 times
...
lfss
1 year, 12 months ago
cd is correct
upvoted 2 times
...
Deeksha1234
2 years ago
Selected Answer: CD
answer should be CD, since activeRow flag is not present, we need to update only end date.
upvoted 9 times
...
Rob77
2 years, 2 months ago
It's SCD Type 2 - you need to update at least three columns in the original raw: Surname, StartDate and EndDate. (IsActive if one exists). Then insert new record. A and C
upvoted 1 times
Rob77
2 years, 2 months ago
* "original row"
upvoted 1 times
peches
2 years, 2 months ago
but if you update the surname on the original row, don't you lose the previous value?
upvoted 7 times
...
...
Vanq69
1 year, 10 months ago
No the StartDate stays, you only need to update the EndDate in the original row, the old name also stays to track which names he had, only the new row should have the new name. So you would only need to edit the EndDate column on the old row and since there is no "IsActive" flag you ignore it, maybe it's just queried by date and sorted by date and you take the last row which is the newest.
upvoted 3 times
...
...
laurasscastro
2 years, 3 months ago
For me this is a little dubious since besides the end date update for the record we could have flg_is_active as well. Making B a possible answer in my opinion
upvoted 4 times
ajhak
2 years, 2 months ago
It's saying "update on column of an EXISTING row". AKA you're just changing the IsCurrent part of the existing row, that's it.
upvoted 1 times
...
...
henryphchan
2 years, 3 months ago
Selected Answer: CD
The answer is correct
upvoted 4 times
...
jeroenmouse
2 years, 3 months ago
Selected Answer: CD
SCD Type 2 will have historical changes hence we will have new row and we need to update the existing row's end date. Hence - CD https://www.sqlshack.com/implementing-slowly-changing-dimensions-scds-in-data-warehouses/
upvoted 6 times
...
Load full discussion...
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