I want to understand Matillion jobs have the highest Snowflake credit usage across a given period?

Matillion ETL
1

I just want to know if anyone has already done something like this, or can give any advise on how to start this task.

 

Thank you.

2

@NeelamMacwan​ My suggestion Create a dedicated warehouse for Matillion and Use that for Matillion default warehouse.

3

Thanks Ganesh,

 

we do have a specific warehouse for Matillion use, what I really need is credit consumption per job/pipeline to understand which one is costing the highest.

 

Neelam

4

We've handled this by creating dedicated warehouses for our bigger METL jobs (assessed by looking at runtime + rows affected) which allow us to track those individually. Smaller jobs use a shared generic METL warehouse at the expense of granular tracking.

5

Thanks, gshenanigan

6

@NeelamMacwan​ Snowflake has connection level tagging for this very purpose.

You can alter the QUERY_TAG session variable and set it to the Matillion job name or some other unique identifier for the job.

ALTER SESSION SET QUERY_TAG = 'metl:customer_incr_load';

https://docs.snowflake.com/en/sql-reference/sql/alter-session

Then this will be available in query_history view.

SELECT * FROM snowflake.account_usage.query_history

WHERE query_tag = 'metl:customer_incr_load'

LIMIT 100

You can build a dashboard in Snowflake to identify top time consuming jobs using this view.

Note: Make sure to unset the query_tag once the job is completed.