Using a non-UTC timestamp when calculating source freshness

Archive
1

Hi,

I configured data freshness for my sources.
This results in following query :

select
      max(to_timestamp(erdat||erzet,'yyyymmddhh24miss')) as max_loaded_at,
      convert_timezone('UTC', current_timestamp()) as snapshotted_at
    from EDW.stage.erp_vbap_1_stg

The output of this query is :

MAX_LOADED_AT SNAPSHOTTED_AT
2020-06-05 09:30:54.000 2020-06-05 07:45:48.860 +0000

The difference is not calculated in the db through sql but internally by dbt.
What is the used algorithm?
Are time zone corrections applied? Should everything first been transformed to UTC?

2

Hi Marc!

It looks like your loaded_at_timestamp is in a non-UTC timezone!

I’d recommend casting your loaded_at_field to UTC:

version: 2

sources:
  - name: jaffle_shop
    loaded_at_field: "convert_timezone('UTC', 'Australia/Sydney', created_at_local)"
    tables: 
      - name: ...
3

Hi Claire,
thanks for the answer.
I converted everything to utc.
select
max(convert_timezone(‘Europe/Paris’,‘UTC’,to_timestamp(erdat||erzet,‘yyyymmddhh24miss’))) as max_loaded_at,
convert_timezone(‘UTC’, current_timestamp()) as snapshotted_at
from EDW.stage.erp_vbak_1_stg

MAX_LOADED_AT SNAPSHOTTED_AT
2020-06-09 06:30:54.000 2020-06-09 07:39:50.313 +0000

How is the datediff algorithm in dbt?

Regards,
Marc