log detailed results in dbt clickhouse

README.md

Summary

The repo shows how to log run results in DBT with ClickHouse SQL.

The idea is to use on-run-end post-hook. The code logs detailed results data, including invocation arguments and timestamps.

This improves upon https://gist.github.com/jeremyyeo/064106e480106b49cd337f33a765ef20.

⚠️ recall that ClickHouse does not support schemas, so re-work qualified paths when using JINJA

select *
from my_database.run_results_dbt

dbt_project.yml

...

on-run-end:
 - "{{ log_results(results) }}"

log_results.sql

{% macro log_results(results) %}

  {% if execute %}

    {% for res in results -%}

      {% set tidy_message = res.message.replace("'", '"') %}
      {% set query -%}
        insert into {{ target.schema }}.run_results_dbt values (
            '{{ invocation_id }}',
            '{{ run_started_at.strftime("%Y-%m-%d %H:%M:%S") }}',
            '{{ res.node.unique_id }}',
            '{{ res.status }}', 
            '{{ tidy_message }}',
            '{{ tojson(invocation_args_dict) }}',
        );
      {%- endset %}
      {% do run_query(query) %}

    {% endfor %}
  {% endif %}

{% endmacro %}


{% macro create_run_end_results_table() %}

  CREATE TABLE IF NOT EXISTS {{ target.schema }}.run_results_dbt (
    `invocation_id` String,
    `run_started_at` DateTime,
    `model_id` String,
    `status` String,
    `message` String,
    `invocation` String,
  )
  ENGINE = MergeTree()
  ORDER BY run_started_at
  SETTINGS index_granularity = 8192

{% endmacro %}