Certified Data Engineer Professional Exam

Exam Certified Data Engineer Professional All QuestionsBrowse all questions from this exam

Question 40

The view updates represents an incremental batch of all newly ingested data to be inserted or updated in the customers table.

The following logic is used to process these records.

Which statement describes this implementation?

The customers table is implemented as a Type 3 table; old values are maintained as a new column alongside the current value.

The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted.

The customers table is implemented as a Type 0 table; all writes are append only with no changes to existing values.

The customers table is implemented as a Type 1 table; old values are overwritten by new values and no history is maintained.

The customers table is implemented as a Type 2 table; old values are overwritten and new customers are appended.

Correct Answer: B

The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted. This is evident from the provided SQL logic. When records are matched and there are changes in the address, the current record is marked as no longer current by setting 'current' to false and updating the 'end_date'. New records are then inserted with the new values and marked as current. This behavior is characteristic of a Type 2 Slowly Changing Dimension (SCD), where changes in data are tracked by maintaining historical records.

Discussion

spaceexplorerOption: B

B is correct

chokthewaOption: B

B is correct.

imatheushenriqueOption: B

B. The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted. A Type 1 table does not track changes in dimensional attributes - the new value overwrites the existing value. Here, we do not preserve historical changes in data. A Type 2 Table tracks change over time by creating new rows for each change. A new dimension record is inserted with a high-end date or one with NULL. The previous record is "closed" with an end date. This approach maintains a complete history of changes and allows for as-was reporting use cases. A data warehousing method called Slowly Changing Dimension (SCD) Type 3 is used to track both the old and new values while managing historical changes in data over time. To reflect the historical and present values of an attribute, SCD Type 3 keeps two extra columns in the dimension table.

kz_dataOption: B

B is correct