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:
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
product key is a surrogate key as it is an identity column
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
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:
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
Type2 because there are start and end columns and ProductKey is a surrogate key. ProductNumber seems a business key.
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
Exactly how I saw it
When the product is not being sold anymore, it becomes a historical record. Hence Type 2.
With type 2, you normally don't update any column of a row other than row start date and end date.
It is type 1 not 2
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.
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
SCD: Type1 ProductKey is Surrogate key.
Type 1 plus 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
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
Type 2 & Surrogate Key
t2 & is a surrogate key.
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.
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).
I agree that it is a surrogate key and not s business one.