I am trying to set query and call run_query()
in a dbt macro called get_records
but unable to set the query correctly using the argument in the where clause. Here is the macro:
{% macro get_records(lead_id) %}
{% set sql_statement %}
select lead_record from {{ ref('source_table') }} where lead_id = {{ lead_id }} limit 1
{% endset %}
{{ print(query) }}
{% if execute %}
{% set results = run_query(sql_statement) %}
{% set records = results.columns[0].values() %}
{% else %}
{% set records = [] %}
{% endif %}
-- do something here
{% endmacro %}
But, the query select lead_record from {{ ref('source_table') }} where lead_id = {{ lead_id }} limit 1
getting compiled as following:
select lead_record from `source` where lead_id = lead_id
which is wrong. Is there a way to set a query using the argument value in where clause ? thanks!.