SCD Type 2 using DQ rules

Hi ​@Nataliia,

The today() function will return a date, and the now() function a datetime. 

With the dateAdd function you can substract a day, so like dateAdd(now(),-1,’DAY’).

When developing with expressions this page will be of support: ONE Expressions - Ataccama ONE Gen2 Platform Latest

Kind regards,

Albert

Hi ​@Nataliia -- there may be a number of ways to approach this, depending on your criteria for data freshness. Do you consider data fresh if all current records in the dimension table are updated no earlier that yesterday? If so the logic for your DQ rule could be something like:

WHEN IsCurrent is TRUE and eff_strt_dttm >= dateAdd(now(),-1,’DAY’) THEN Result is VALID, ELSE Result is INVALID

If you have a different validation in mind, please let me know, happy to discuss your case.

Hey guys, thanks for your answers. But what in case values are different  in the column and just the newest ones have today's date, how I find the maximum value in the column and then compare? This is how my tries looks like, but looks it doesn't aggregate it
 

Hi ​@Nataliia, here's how I'd check for groups of records where even the maximum/newest date in the timestamp column is older than yesterday:

There's a couple of things to look out for:

• the Group By field is blank here - which means the entire dataset would be evaluated as a single group. That's probably not what you want, so apart from the etl_insrt_dttm input you should introduce the primary key as another input attribute from the data (or several attributes which you would then concatenate into a key using the Variable field). 
• if the maximum date record in a given group violates the rule then all records in this group will be flagged as invalid - that's the nature of aggregation rules as they look at a group not at an individual row in itself.
• if you want to flag individual records that are out of date, consider using a regular/non-aggregation rule and adding another input attribute which would filter for current records (as I'm assuming your table contains historical records too and you don't need to evaluate them since they are by definition out of date, so they must be already flagged as such).  You might end up with an expression like WHEN IsCurrent is TRUE and eff_strt_dttm < dateAdd(today(),-1,’DAY’) THEN Result is INVALID, ELSE Result is VALID

Hope this gives you a starting point - if you have other questions or comments, please share!