Skip to main content

Hi,

I want to compare the number of not null records in source (custom sql using JDBC writer) and target (salesforce object) using a VCI. Is there any way to do that in Ataccama version 14.1.0. 

The aim is to get the records which are in source and not in target and records which are in target but not in source.

Thanks.

Hi! Yes, you can use VCI for this task. You can do e.g. outer join (or RIGHT/LEFT depending on your needs) of these two tables and then either use this result table for DQ checks or still in the VCI you can add a new field (e.g. “exists_in_source”) that could say YES, if exists, NO if it doesn’t and the DQ check can then be simply applied to this new field.

Regarding the VCI, please see this documentation: https://support.ataccama.com/home/docs/aip/14.1.0/development-guides/one-desktop-development-guide/working-with-ataccama-one-platform/working-with-data-in-one-desktop/virtual-catalog-items It also explains how to configure the connections to the sources or how to use existing Catalog Items.

Please let me know if you have more questions.

Kind regards,

Anna

Hi @anna.spakova! Thank you so much for the reply. Currently, I have one VCI with a right join, one with left join, and then I am doing the comparison in Ataccama web. Is there a way to have just one VCI for the entire purpose?

Hi @ritgupta I would do just one OUTER join (that’s basically both LEFT and RIGHT) in one VCI. The outer join will have null values where source wasn’t matched with target, but also null values where target wasn’t matched with source. You can then have two fields:

  • “present_in_source” (TRUE for target values present in source - the join result won’t have null values, FALSE if not present, NULL for source value where it doesn’t makes sense)
  • “present_in_target” (the same as above, just vice versa)

So for example:

And the DQ rule itself can check for both fields in two conditions having different explanation messages. Basically where both fields are TRUE, it was matched (valid), if present_in_source is FALSE, you can have MISSING_IN_SOURCE message, if present_in_target is FALSE, you can have MISSING_IN_TARGET message.

What do you think? This is just a quick idea. You can of course extend the logic based on your needs.

Other than that, right now we are not able to work with two tables in one DQ rule (so we cannot compre two VCIs).

Kind regards,

Anna

Reply


Community Terms of Use & ConditionsCookie settings