– macros/get_incremental_strategies.sql
{% macro insert_delete_strategy(source_table, destination_table, unique_key_columns) %}
{% if is_incremental() %}
– Delete outdated rows from the destination table
DELETE FROM {{ ref(‘destination_details’) }}
WHERE EXISTS (
SELECT 1
FROM {{ ref(‘source_details’) }}
WHERE {{ ref(‘destination_details’) }}.{{ unique_key_columns }} = {{ ref(‘source_details’) }}.{{ unique_key_columns }}
);
-- Insert new rows into the destination table
INSERT INTO {{ref('destination_details') }}
SELECT *
FROM {{ ref('source_details') }}
WHERE NOT EXISTS (
SELECT 1
FROM {{ ref('destination_details') }}
WHERE {{ ref('destination_details') }}.{{ unique_key_columns }} = {{ ref('source_details') }}.{{ unique_key_columns }}
);
{% else %}
– Full load from the source table to the destination table
TRUNCATE TABLE {{ ref(‘destination_details’) }};
INSERT INTO {{ ref(‘destination_details’) }}
SELECT *
FROM {{ ref(‘source_details’)}};
{% endif %}
{% endmacro %}
this insert_delete_strategy in my macros
This is dstinsedel.sql under models folder
– depends_on: {{ ref(‘destination_details’) }}
{{ config(materialized=‘incremental’, unique_key=“Id”, incremental_strategy=‘delete+insert’) }}
{{insert_delete_strategy(‘source_details’, ‘destination_details’, ‘Id’) }}
error getting like this
