Create End Date time in Satellites without windows function

Data Vault 2.0
1

Hi

We are using Data Vault 2.0 on snowflake. Created Satellite (S0) without eff_end_date and created view over S0 satellite with eff_end_date using windows function (LEAD).
When querying from Info_mart, it is becoming very slow.

Could you please provide some suggestions:

  1. How to create eff_end_date without windows function?
  2. Can we materialized satellite with eff_end_date? Concerned of duplicating the data .

Any help is highly appreciated

Thanks
Venkat

2

Hi Venkat,

We faced a similar situation where window functions became too slow on snowflake due to the size of some of our satellites. We instead went down the route of creating current point in time tables (thanks Mr Cuba for some very enlightening articles on this) that are updated based on a stream from the sat insert. This means that you can use the cpit table as an index to find your open record in the sat for queries further down the chain (we also use them to speed up sat inserts - so they have to be updated after the sat ) So, the cpit is a merge on business key and updating hashdiff and applts to current position or inserting new. This approach means you have no concerns about end dates. Here’s a link to Patricks article on them.

Cheers
Andy

2 Likes