Exam DP-203 topic 1 question 38 discussion

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

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

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

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.

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.

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.

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

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

I think: Type 1 and Surrogate key

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

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

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).

Type 1 plus surrogate Key

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

Type 2 & Surrogate Key

Ans 1 - TYPE 2 Ans 2 - Product key is a surrogate key (identity column) Note: Product number would be the business key if I had to pick one

t2 & is a surrogate key.