Query Tags in Snowflake and Coalesce

Coalesce uses JSON query_tag in Snowflake to track job execution. You'll learn how to view the query_tag and ways to use it.

Query Tags Applied by Coalesce

Coalesce automatically adds a query tag to every SQL query that runs as part of a Job. The query tag is a JSON string with these fields:

• jobName: The name of the Job that's running.
• nodeID: The unique identifier for the Node that's running.
• nodeName: The name of the Node that's running.
• runID: The unique identifier for the Job run.
• runType: The type of operation, such as Refresh or Deploy.
• stageName: The stage in the run, such as Insert into table.
• storageLocation: The target Storage Location.

Query Tag Example

{
 "coalesce": {
  "jobName": "Refreshed Job - customer refresh",
  "nodeID": "b4e70d66-0b69-48b2-95c5-3ec198467287",
  "nodeName": "DIM_CUSTOMER_LOYALTY",
  "runID": "18764",
  "runType": "Refresh",
  "stageName": "MERGE  Sources",
  "storageLocation": "STG"
 }
}

Run ID

The RunID is a unique identifier assigned to each job execution in Coalesce. It allows you to track all queries and operations that were part of a specific job run, enabling you to correlate Coalesce metadata with Snowflake query history for debugging, auditing, and cost analysis

Inspect Query Tags in Query History

This query reads recent query history and shows the query tag and SQL text.

SELECT
    start_time,
    query_tag,
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
    END_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())
))
WHERE query_tag IS NOT NULL
ORDER BY start_time DESC;

-- You can also use SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

SELECT
    start_time,
    query_tag,
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('hour', -1, CURRENT_TIMESTAMP())
  AND query_tag IS NOT NULL
ORDER BY start_time DESC;

How Inspect Query Works

• Call the QUERY_HISTORY table function

FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
    END_TIME_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())
))

• INFORMATION_SCHEMA.QUERY_HISTORY returns query history for the current account
• Wrapping it in TABLE(...) makes it usable in the FROM clause
• The END_TIME_RANGE_START argument tells Snowflake to return queries that ended after a specific time
• DATEADD('hour', -1, CURRENT_TIMESTAMP()) moves back one hour from the current timestamp

The result is a set of queries that completed in the last hour.

---

What's Next?

• Snowflake - QUERY_HISTORY , QUERY_HISTORY_BY_*

---