Exam DP-203 All Questions

View all questions & answers for the DP-203 exam

Exam DP-203 topic 1 question 38 discussion

Actual exam question from Microsoft's DP-203

Question #: 38
Topic #: 1

HOTSPOT -
You are creating dimensions for a data warehouse in an Azure Synapse Analytics dedicated SQL pool.
You create a table by using the Transact-SQL statement shown in the following exhibit.

Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: Type 2 -
A Type 2 SCD supports versioning of dimension members. Often the source system doesn't store versions, so the data warehouse load process detects and manages changes in a dimension table. In this case, the dimension table must use a surrogate key to provide a unique reference to a version of the dimension member. It also includes columns that define the date range validity of the version (for example, StartDate and EndDate) and possibly a flag column (for example,
IsCurrent) to easily filter by current dimension members.
Incorrect Answers:
A Type 1 SCD always reflects the latest values, and when changes in source data are detected, the dimension table data is overwritten.

Box 2: a business key -
A business key or natural key is an index which identifies uniqueness of a row based on columns that exist naturally in a table according to business rules. For example business keys are customer code in a customer table, composite of sales order header number and sales order item line number within a sales order details table.
Reference:
https://docs.microsoft.com/en-us/learn/modules/populate-slowly-changing-dimensions-azure-synapse-analytics-pipelines/3-choose-between-dimension-types

by Arindamb at May 1, 2021, 10:45 a.m.

Comments

Submit Cancel

nkav

Highly Voted 4 years, 2 months ago

product key is a surrogate key as it is an identity column

upvoted 238 times

Bouhdy

11 months ago

TOTALLY AGREE HERE !!!! The ProductKey column is defined as an IDENTITY(1,1) column, which means it is an auto-incremented value and serves as a unique identifier for each row. This is characteristic of a surrogate key, which is used in dimensional modeling to uniquely identify records in a dimension table, especially in a Type 2 SCD.

upvoted 5 times

...

111222333

4 years, 2 months ago

Agree on the surrogate key, exactly. "In data warehousing, IDENTITY functionality is particularly important as it makes easier the creation of surrogate keys." Why ProductKey is certainly not a business key: "The IDENTITY value in Synapse is not guaranteed to be unique if the user explicitly inserts a duplicate value with 'SET IDENTITY_INSERT ON' or reseeds IDENTITY". Business key is an index which identifies uniqueness of a row and here Microsoft says that identity doesn't guarantee uniqueness. References: https://azure.microsoft.com/en-us/blog/identity-now-available-with-azure-sql-data-warehouse/ https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-identity

upvoted 17 times

rikku33

3 years, 10 months ago

Type 2 In order to support type 2 changes, we need to add four columns to our table: · Surrogate Key – the original ID will no longer be sufficient to identify the specific record we require, we therefore need to create a new ID that the fact records can join to specifically. · Current Flag – A quick method of returning only the current version of each record · Start Date – The date from which the specific historical version is active · End Date – The date to which the specific historical version record is active With these elements in place, our table will now look like:

upvoted 14 times

Gikan

1 year, 6 months ago

You know the start and end date of the selling, but not the version of the product (size, color....) If the waterpump size has changed for a car by rhe producer but the function product id are the same, selling is continuous you can not store it into this structure. But you can store at the timepoints and see validation point in creation date of the record. Therefore it is type 1

upvoted 7 times

...

sagga

Highly Voted 4 years, 2 months ago

Type2 because there are start and end columns and ProductKey is a surrogate key. ProductNumber seems a business key.

upvoted 51 times

DrC

4 years, 2 months ago

The start and end columns are for when to when the product was being sold, not for metadata purposes. That makes it: Type 1 – No History Update record directly, there is no record of historical values, only current state

upvoted 124 times

captainbee

4 years, 1 month ago

Exactly how I saw it

upvoted 2 times

...

Kyle1

3 years, 10 months ago

When the product is not being sold anymore, it becomes a historical record. Hence Type 2.

upvoted 2 times

Yuri1101

3 years, 7 months ago

With type 2, you normally don't update any column of a row other than row start date and end date.

upvoted 1 times

...

rockyc05

3 years, 5 months ago

It is type 1 not 2

upvoted 4 times

...

borinot

2 years, 8 months ago

I agree with the first part. From just the table it's impossible to know if the changes in the products are ignored or are updated, if you don't see the ETL. I suppose there is some mistake in the name of the fields start end effective fields.

upvoted 2 times

...

Load full discussion...

...

AMJB

Most Recent 4 months, 2 weeks ago

I agree with the surrogate key cos of the identiy (1,1) clause however, if you look at the adventureworks dw tables, product key is a business key or primary key on the dimproduct table. It is confusing but i guess the gve away is that the table is a SCD table so yea I'm swayed towards Dimensin table

upvoted 1 times

...

roopansh.gupta2

11 months, 2 weeks ago

SURROGATE KEY AND SCD 1: The absence of a Current_Flag or similar indicator and the RowUpdatedDateTime column might suggest that the DimProduct table is not strictly adhering to a Type 2 Slowly Changing Dimension (SCD). Instead, it seems to be implementing a Type 1 Slowly Changing Dimension (SCD). Further evidence for SCD 1: RowUpdatedDateTime: The RowUpdatedDateTime column indicates when the row was last modified, which is consistent with Type 1 SCDs where data is overwritten. No Historical Tracking: There is no mechanism to track historical changes (e.g., Current_Flag, EffectiveDate, EndDate). Without these, the table does not store previous versions of the data, reinforcing that this is a Type 1 SCD.

upvoted 5 times

...

jonatahs18

1 year ago

The ProductKey is a surrogate key, as you can se the query use IDENTITY(1,1) to identify unique values in the table. If was a Business Key, the query don't need to specific the INDENTITY, usually we use Concat to create new business Key or naturally the database return that.

upvoted 1 times

...

Okkier

1 year ago

Operational transaction systems don't use keys as their ID columns. They generally use product IDs, which refer to a business key. When we hit the dimension design for star schemas, we generally name them product keys. The second column in the table, which is product source ID, I've read as being an ID combined with the source where the record has come from, which makes it the business key. This is generally what we do for multi-sourced dimensions, i.e., different systems that can deliver products into a single dimension table. I wouldn't create a product source ID column; I would create a source column and a product ID column, which then become the combined business key. These questions are overly simplified and not providing enough clear guidance, which is simply confusing and leads to incorrect answers and inaccurate results.

upvoted 1 times

...

jpgsa11

1 year, 2 months ago

A Product can stop selling gets a sellendDate and the table does not receive a new version of the record. On the other hand a SCD1 can have a SK to simplify joins

upvoted 2 times

...

Dusica

1 year, 3 months ago

This is a far too easy question to get it wrong by the site admins

upvoted 2 times

...

olayvo

1 year, 3 months ago

I think: Type 1 and Surrogate key

upvoted 1 times

SheShanD

11 months, 1 week ago

why need surrogate key for type 1 ?

upvoted 1 times

...

Pankaj_128

1 year, 3 months ago

Correct Answer : Type 2 SCD (start date , end date , surrogate key) Surrogate key as its an IDENTITY column.

upvoted 1 times

...

Alongi

1 year, 4 months ago

Type 1 because there is a time of row update, and not a column validity flag. Surrogate key, identity column is always a surrogate .

upvoted 2 times

...

s_unsworth

1 year, 5 months ago

The SellStartDate and SellEndDate wont be updated. They seem to be fixed columns. As you have a RowInsert and a RowUpdate it would be deemed to be a Type 1 because the row would be updated in place rather than a new row created (Type 2). Plus as the ID is an identity column it is a Surrogate Key - it has been created by the system not by the business. Business Keys are Keys that are provided by the business with business logic, such as an order number or a customer number (e.g. CUST-00001).

upvoted 2 times

...

Jdfer34

1 year, 6 months ago

Type 1 plus surrogate Key

upvoted 4 times

...

blazy001

1 year, 7 months ago

13 years in this stuff product key is a surrogate , business key comes from the ERP business app and it is a type 1 NOT 2 an insert and update column does not tell you the date from and to , hallo

upvoted 16 times

...