Assert a query

Matillion ETL
1

Hello team,

 

I have a query as:

 

select count(distinct sfdc_account_id), from account union select count(distinct account_id), from fct_acco

 

I want to know if both counts from above query are same or not? How to do this in matillion?

 

2

Hello @aditya.kommu​

Thank you for raising your post and welcome to the Matillion Community!

There are several ways to achieve this, I have shared some below for you to read over and check out:

Set Up Data Sources:

  • Use the "Table Input" component to pull data from the account table.
  • Use another "Table Input" component to pull data from the fct_acco table.

Aggregate the Counts:

  • For each Table Input component, use the "Aggregate" component to get the distinct counts.
  • For the account table, configure the Aggregate component to count distinct sfdc_account_id.
  • For the fct_acco table, configure the Aggregate component to count distinct account_id.

Join or Union the Results:

  • Since you want to compare the counts, you can use the "Calculator" component to create a single row for each count and then use a "Join" or "Union" component to bring the two counts together for comparison.

Comparison Logic:

  • Use the "Calculator" component to create a new column that indicates if the counts are equal.
  • You might need a "Filter" component to filter based on this comparison if needed.

Calculator Component:

  • Add a column for the comparison result:

CASE WHEN account.distinct_count = fct_acco.distinct_count THEN 'Equal' ELSE 'Not Equal' END AS comparison_result

I hope this helps, please let me know how you get on :)

Kind regards, Joe