Hello,
I would like to write macro which will add metadata to the given model (can be incremental). I started with this:
{% macro add_metadata() %}
{% if is_incremental() %}
, current_timestamp() as updated_at
, session_user() as updated_by
{% else %}
, current_timestamp() as created_at
, session_user () as created_by
, null as updated_at
, null as updated_by
{% endif %}
{% endmacro %}
but the problem is that when I have staging model like this for example:
SELECT 1 AS id, 'Alice' AS name, 100 AS score
UNION ALL
SELECT 2, 'Bob', 200
UNION ALL
SELECT 3, 'Charlie', 300
and my target model looks like this:
{{ config(
materialized='incremental',
unique_key='id'
) }}
WITH source_data AS (
SELECT s.*
{{ add_metadata()}}
FROM {{ ref('testing_metadata') }} s
)
SELECT *
FROM source_data
I am always receiving error that fields created_at and created_by do not exist because it tries to take it from staging model. How to improve this macro to change only updated_by and updated_at fields when I will be running the model for 2nd and next times and fields created_at and created_by will remain the same?