Insert query ID in adapter response

[louis-zhang-unity3d]

Describe the feature

Currently the adapter response of the Trino connector is by default a "SUCCESS" message . By looking up in the table system.runtime.queries, the Trino query_id provides access to useful performance metadata that can be used for observability. Including this attribute in the adapter response would greatly enhance observability of dbt models by enabling traceability for each model run.

The query_id can be accessed by the ._query.query_id attribute of the trino.dbapi.Cursor object.

Other dbt providers (i.e. BigQuery, Snowflake) add a similar ID to their adapter response as well.

Describe alternatives you've considered

No response

Who will benefit?

This will benefit all data teams looking to monitor the performance of their dbt model runs over time, e.g. runtime, successes/failures, etc. Each dbt model run can be tied back to underlying metadata within trino itself.

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[findinpath]

@louis-zhang-unity3d could you please add more details regarding the benefit part? Where could the query id be used/persisted for being further used? Do you mean something like a time series db label?

Other dbt providers (i.e. BigQuery, Snowflake) add a similar ID to their adapter response as well.

Can you please share any documentation/code reference for any of the above mentioned adapters?

[louis-zhang-unity3d]

@findinpath The system.runtime.queries table (https://trino.io/docs/current/connector/system.html#runtime-queries)
contains information about currently and recently running queries on the Trino cluster. From this table, which contains the query_id as a column, you can find out the original query SQL text, the identity of the user who ran the query, and performance information about the query, including how long the query was queued and analyzed. Our own use case would be to retrieve the adapter_response field from DBT's run_results.json output to get the query ID and retrieve the metadata from system.runtime.queries.

Examples of the other adapters including this are

• BigQuery https://github.com/dbt-labs/dbt-bigquery/blob/main/adapters/bigquery/connections.py#L514
• Snowflake https://github.com/dbt-labs/dbt-snowflake/blob/main/dbt/adapters/snowflake/connections.py#L358

[mdesmet]

Hi @louis-zhang-unity3d, I think that change is very logical and welcome but we should probably also add a public field cursor.query_id in Trino Python Client's Cursor to accommodate for this.

[mdesmet]

I added a PR on trino-python-client to expose the query_id: trinodb/trino-python-client#295

@louis-zhang-unity3d : If you want you can already start working on it, ping us if you need some help!

[aezomz]

Whats the status for this, @louis-zhang-unity3d ? I am looking for the same as well.

[louis-zhang-unity3d]

@mdesmet could I get permissions to push to a remote branch and open a PR? thanks!

[hovaesco]

You would need to create a fork of dbt-trino repository first and then open a pull request from there.

[aezomz]

Do you all think it make sense to store the compiled query as well?
I use DBT test and will like to store the compiled query for reference, especially tests that are failing.

Good to have similar to how snowflake adapter is doing.
rows_affected, code, query_id and _message if we want it.

I think query_id and _message is already covered above.

    return SnowflakeAdapterResponse(
          _message="{} {}".format(code, cursor.rowcount),
          rows_affected=cursor.rowcount,
          code=code,
          query_id=cursor.sfqid,
      )  # type: ignore