The screenshot 1 failed but screenshot 2 succeed for the incremental update. cam.date is already a timestamp without timezone. It only work when I cast it into text. If I keep it as timestamp, it says
function date_add(text, bigint, timestamp without time zone) does not exist
It is really weird to me, how come the cam.date can determine whether the macro (screenshot 3) work or not? The screenshot 3&4 are the utils function I wrote for my project. In addition, this macro actually works in other models in my project where ::timestamp is completely ok. I got really confused on why it sometimes works and sometimes does not.
The screenshot 1 and 2 are almost exactly same expect in where clause
cam.date::timestamp > …
vs
cam.date::text > …
{{ config(
materialized='incremental',
unique_key=['channel','country_code','date','campaign_id','ad_group_id']
) }}
{{ simple_cte([
('amazonads_dsp_campaigns', 'source_ads__amazon_dsp_report_campaign_performance'),
]) }},
amazonads_dsp_matrics as (
select
'DSP' as channel,
null::bigint as profile_id,
cam.advertiser_country as country_code,
cam.date::timestamp as "date",
cam.order_id::text as campaign_id,
"order" as campaign_name,
'' as ad_group_id,
'' as ad_group_name,
sum(cam.click_throughs) as clicks,
sum(cam.total_cost) as cost,
sum(cam.impressions) as impressions,
sum(cam.total_sales_usd) as sales14d,
sum(cam.total_purchases_clicks) as conversions14d
from
amazonads_dsp_campaigns cam
{% if is_incremental() %}
where
cam.date::text > {{ get_inc_key_timestamp_with_lookback(this, 'date', '', 48) }}
{% endif %}
{{ dbt_utils.group_by(8) }}
)
select * from amazonads_dsp_matrics