Use temporal table to build a SCD

Database
Enthusiast

Use temporal table to build a SCD

Can I have a control over valid time before populating a temporal table ?

 

I need to be able to perform an Insert\update statement and want teradata not to capture the system date time , But treat the statement as if it is now 2010-01-01.

 

We will build a large number of SCD tables, and Need to avoid doing them in traditional SQL statement by which I have to update the end date of the record , before Inserting a new row. ?

 

 

 

Tags (3)

Accepted Solutions
Teradata Employee

Re: Use temporal table to build a SCD

Oh, I misunderstood. The temporal feature does not automatically adjust existing rows when inserting historical rows.

And note that a "period" is (closed,open) - includes the beginning date/time up to but not including the end date/time. So if you are using the temporal feature, you would need the ending bound in your example to be 2010-01-01, not 2009-12-31.

 

If you have the data values with just associated start dates, you can load that data (in a non-temporal table) and then assign the respective period end dates in an INSERT/SELECT:

COALESCE(MIN(StartDate) OVER (PARTITION BY key-columns ORDER BY StartDate ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),date'9999-12-31')

1 ACCEPTED SOLUTION
4 REPLIES
Enthusiast

Re: Use temporal table to build a SCD

Any reply  is highly appreciated 

 

Teradata Employee

Re: Use temporal table to build a SCD

For Teradata temporal, use a SEQUENCED VALIDTIME temporal qualifier.

 

(For ANSI temporal, the application is responsible for ValidTime on INSERT.)

Enthusiast

Re: Use temporal table to build a SCD

Hi fred 

I am searching for a solution by which 

I can insert a record at 18 Jan 2018 , But want it to be treated as if it is occured at  01 Jan 2010.  Adding to this, record opened before 01 Jan 2010 , should to closed auto. by 31 Dec 2009. ( update the end date by 31 Dec 2009 ) 

 

Using SEQUENCED VALIDTIME give me the option to Insert a record as of 01 Jan 2010, but will not closed the prev. loaded data

 

Teradata Employee

Re: Use temporal table to build a SCD

Oh, I misunderstood. The temporal feature does not automatically adjust existing rows when inserting historical rows.

And note that a "period" is (closed,open) - includes the beginning date/time up to but not including the end date/time. So if you are using the temporal feature, you would need the ending bound in your example to be 2010-01-01, not 2009-12-31.

 

If you have the data values with just associated start dates, you can load that data (in a non-temporal table) and then assign the respective period end dates in an INSERT/SELECT:

COALESCE(MIN(StartDate) OVER (PARTITION BY key-columns ORDER BY StartDate ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),date'9999-12-31')