Stack Overflow
Stack Overflow

Questions tagged [scd2]

Use this to tag questions related to ETL. SCD, i.e. Slowly Changing Data are data, which change slowly but unpredictably, rather than according to a regular schedule.

A Slowly Changing Dimension (SCD) is a dimension that stores and manages both current and historical data over time in a data warehouse. A Type 2 SCD retains the full history of values. When the value of a chosen attribute changes, the current record is closed. For this type of slowly changing dimension, add a new record encompassing the change and mark the old record as inactive. This allows the fact table to continue to use the old version of the data for historical reporting purposes leaving the changed data in the new record to only impact the fact data from that point forward.

85 questions
5
votes
5 answers

SQL Query for SCD Type 2

I am given the following table with the following problem: Create a Slowly Changing Dimension Type 2 from the dataset. EMPLOYEE table has daily records for each employee. Type 2 - Will have effective data and expire date. Employee…
LukeThomas
  • 75
  • 1
  • 1
  • 6
4
votes
1 answer

Handling Deletes in SCD Type 2

As a Modeler trying to find out what is the best way to handle deletes in SCD Type 2 tables. As per principle an SCD Type 2 table is to track changes using ETL dates like START_DT and END_DT. START_DT will be the date the record is effective…
Ankit Srivastava
  • 185
  • 3
  • 13
4
votes
2 answers

How to create a report of per-day changes over SCD 2 table

I need to generate report that will show number of new / changed rows on per-day basis for SCD table. Here is the SQL to create the table: create table #scd( code nvarchar not null , startdate date not null , enddate date ); alter table…
Nafas
  • 183
  • 1
  • 9
3
votes
2 answers

SCD2 + Merge Statement + SQL Server

I am trying work out with MERGE statment to Insert / Update Dimension Table of Type SCD2 My source is a Table var to Merge with Dimension table. My MERGE statement is throwing an error as: The target table 'DM.DATA_ERROR.ERROR_DIMENSION' of the …
Sreedhar
  • 29,307
  • 34
  • 118
  • 188
2
votes
1 answer

SQL Merge to Update table with changed history

I have been tasked with building a history table in SQL. I have already built the base table which contains multiple left joins amongst other things. The base table will need to be compared to another table and only update specific columns that have…
teelove
  • 73
  • 5
2
votes
0 answers

SCD2 implementation in PostgreSQL with 'create rule/trigger'

I wanted to implement SCD2 in postgreSQL. For now, I am trying with 'create rule' as I wanted to update primary table(MAIN for example) based on staging table (STG for example which is truncate and load). So whenever there is insert in staging table…
annonymous 007
  • 21
  • 2
2
votes
4 answers

Merge lines over timespan in SCD2 table

I'm having the following table sourced from a SCD2 table. From this source table, I have selected only a few columns, which reults in several lines looking exactly similar. I want to remove the unnecessary lines, those that contain the same data,…
Elwood B.
  • 31
  • 3
2
votes
3 answers

Maintain Historical data changes in Parent-child table

1 Employee has N Address. Here I need to maintain the historical information of Employee and Address changes if any changes is done by any users in these two table. Table Employee: Employee( EmpID BIGINT PRIMARY KEY IDENTITY(1,1), Name…
Rahul Roshan
  • 77
  • 3
  • 10
2
votes
1 answer

MERGE attempted to UPDATE or DELETE the same row more than once

I am trying to load a standard Kimball SCD2 Dimension, using a merge statement which I got from the following website: http://www.kimballgroup.com/2008/11/design-tip-107-using-the-sql-merge-statement-for-slowly-changing-dimension-processing/ This…
Erik hoeven
  • 1,442
  • 6
  • 26
  • 41
1
vote
1 answer

Power BI - Filtering model on latest version of all attributes of all dimensions through DAX

I have a model that's comprised of multiple tables containing, for every ID, multiple rows with a valid_from and valid_to dates. This model has one table in that is linked to every other table (a table working as both a fact and a dimension). This…
Rami Ouanes
  • 35
  • 5
1
vote
1 answer

Joining multiple tables with ValidFrom/ValidTo dates (SCD2)

Question: How do I JOIN multiple (3+) tables, which all have SCD type 2 validFrom/validTo dates in them? I have the following tables: -- table 1 CREATE TABLE dbo.Clients ( clientCode varchar(10) NOT NULL, startDate date NOT NULL, …
matt_vice89
  • 65
  • 2
  • 6
1
vote
1 answer

Implementing Revisioning with SCD2/4

I'm working on a project that is a sort of bill of materials manager that needs to support revisioning of components. After some research, I've stumbled upon slowly changing dimensions type 2 and type 4 which seems like the most appropriate pattern…
user1960118
  • 367
  • 6
  • 17
1
vote
1 answer

SCD-2 in data modelling: how do I detect changes?

I know the concept of SCD-2 and I'm trying to improve my skills about it doing some practices. I have the next scenario/experiment: I'm calling daily to a rest API to extract information about companies. In my initial load to the DB everything is…
Francisco Albert
  • 1,577
  • 2
  • 17
  • 34
1
vote
1 answer

Spark Scala FoldLeft Performance slowness

Hi I am trying to do a scdtype2 update in dataframe having 280 columns. val newYRecs = stgDF.columns .foldLeft(joinedDF) {(tempDF,colName) => tempDF.withColumn("new_" + colName,…
mehere
  • 1,487
  • 5
  • 28
  • 50
1
vote
0 answers

Implementing SCD type2 using pyspark

I was trying to implement SCD type 2 using pyspark and insert data into Teradata . I was able to generate the data frame which has both old history record (which is already present in the database ) and new fresh records , but when I do…
techblue
  • 13
  • 1
  • 3
1
2 3 4 5 6