t-sql test data warehouse type 2 changes

Question

I need to look at a data warehouse and check that a type 2 change works correctly

I need to check that the vaild to date on a row is the same as the vaild from date on the next row.

This check is to make sure that a row has been ended has also been started correctly

thanks, Marc

Answer 1

The following relates to Kimball type-2 dimension table.

Note that this assumes

1. 3000-01-01 as a date in far future for the current entries.
2. CustomerKey is an auto-incrementing integer.

This example should give you the list of rows with missing or miss-matched next-entries.

;
with
q_00 as  (
select
      CustomerKey
    , CustomerBusinessKey
    , rw_ValidFrom
    , rw_ValidTo
    , row_number() over (partition by CustomerBusinessKey order by CustomerKey asc) as rn
from dimCustomer
)
select
      a.CustomerKey
    , a.CustomerBusinessKey
    , a.rw_ValidFrom
    , a.rw_ValidTo
    , b.CustomerKey          as b_key
    , b.CustomerBusinessKey  as b_bus_key
    , b.rw_ValidFrom         as b_ValidFrom
    , b.rw_ValidTo           as b_ValidTo
from      q_00 as a
left join q_00 as b on b.CustomerBusinessKey = a.CustomerBusinessKey and (b.rn = a.rn + 1) 
where a.rw_ValidTo < '3000-01-01'
  and a.rw_ValidTo != b.rw_ValidFrom ;

Also useful

-- Make sure there are no nulls
-- for rw_ValidFrom, rw_ValidTo
select
      CustomerKey
    , rw_ValidFrom
    , rw_ValidTo
from dimCustomer
where rw_ValidFrom is null
   or rw_ValidTo   is null ;

-- make sure there are no duplicates in  rw_ValidFrom
-- for the same customer
select
      CustomerBusinessKey
    , rw_ValidFrom
    , count(1) as cnt
from dimCustomer
group by CustomerBusinessKey, rw_ValidFrom
having count(1) > 1  ;

-- make sure there are no duplicates in  rw_ValidTo
-- for the same customer
select
      CustomerBusinessKey
    , rw_ValidTo
    , count(1) as cnt
from dimCustomer
group by CustomerBusinessKey, rw_ValidTo
having count(1) > 1  ;