Skip to content

timeliness table sensors

data freshness

Full sensor name 

table/timeliness/data_freshness
Description
Table sensor that runs a query calculating maximum days since the most recent event.

SQL Template (Jinja2) 

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}

{% macro render_current_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMP_DIFF(
        CURRENT_TIMESTAMP(),
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATE_DIFF(
        CURRENT_DATE(),
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        DAY
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATETIME_DIFF(
        CURRENT_DATETIME(),
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- else -%}
    TIMESTAMP_DIFF(
        CURRENT_TIMESTAMP(),
        MAX(
            SAFE_CAST({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
        ),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}

{% macro render_current_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        CURRENT_TIMESTAMP()
    ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATEDIFF(
        DAY,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        CURRENT_DATE()
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        CURRENT_TIMESTAMP()
    ) / 24.0 / 3600.0
    {%- else -%}
    TIMESTAMPDIFF(
        SECOND,
        CURRENT_TIMESTAMP(),
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}

{% macro render_current_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
        CURRENT_DATE - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- else -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})::TIMESTAMP)
    )) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}

{% macro render_current_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
        CURRENT_DATE - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- else -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})::TIMESTAMP)
    )) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}

{% macro render_current_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        CURRENT_TIMESTAMP
    ) / 24.0 / 3600.0 / 1000.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATEDIFF(DAY,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        CURRENT_DATE
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        CURRENT_TIMESTAMP
    ) / 24.0 / 3600.0 / 1000.0
    {%- else -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX(
            TRY_TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
        )
        CURRENT_TIMESTAMP
    ) / 24.0 / 3600.0 / 1000.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}

{% macro render_current_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
        DATEDIFF(SECOND,
            MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
            SYSDATETIMEOFFSET()
        ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
        DATEDIFF(DAY,
            MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
            GETDATE()
        )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
        DATEDIFF(SECOND,
            MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
            GETDATE()
        ) / 24.0 / 3600.0
    {%- else -%}
        DATEDIFF(SECOND,
            MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
            SYSDATETIMEOFFSET()
        ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

data ingestion delay

Full sensor name 

table/timeliness/data_ingestion_delay
Description
Table sensor that runs a query calculating the time difference in days between the most recent transaction timestamp and the most recent data loading timestamp.

SQL Template (Jinja2) 

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_max_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMP_DIFF(
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATE_DIFF(
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        DAY
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATETIME_DIFF(
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- else -%}
    TIMESTAMP_DIFF(
        MAX(
            SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
        ),
        MAX(
            SAFE_CAST({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
        ),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_max_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_max_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATEDIFF(
        DAY,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    ) / 24.0 / 3600.0
    {%- else -%}
    TIMESTAMPDIFF(
        SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }} AS TIMESTAMP),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
    ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_max_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_max_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- else -%}
    EXTRACT(EPOCH FROM (
        MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})::TIMESTAMP) - MAX(({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})::TIMESTAMP)
    )) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_max_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_max_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) - MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- else -%}
    EXTRACT(EPOCH FROM (
        MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})::TIMESTAMP) - MAX(({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})::TIMESTAMP)
    )) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_max_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_max_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    ) / 24.0 / 3600.0 / 1000.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATEDIFF(
        DAY,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    ) / 24.0 / 3600.0 / 1000.0
    {%- else -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX(TRY_TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})),
        MAX(TRY_TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
    ) / 24.0 / 3600.0 / 1000.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_max_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_max_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
        DATEDIFF(SECOND,
            MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
            MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
        ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATEDIFF(DAY,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATEDIFF(SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    ) / 24.0 / 3600.0
    {%- else -%}
    DATEDIFF(SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_max_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

data staleness

Full sensor name 

table/timeliness/data_staleness
Description
Table sensor that runs a query calculating the time difference in days between the current date and most recent data loading timestamp (staleness).

SQL Template (Jinja2) 

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}

{% macro render_current_ingestion_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMP_DIFF(
        CURRENT_TIMESTAMP(),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATE_DIFF(
        CURRENT_DATE(),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        DAY
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATETIME_DIFF(
        CURRENT_DATETIME(),
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- else -%}
    TIMESTAMP_DIFF(
        CURRENT_TIMESTAMP(),
        MAX(
            SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
        ),
        MILLISECOND
    ) / 24.0 / 3600.0 / 1000.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_ingestion_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}

{% macro render_current_ingestion_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        CURRENT_TIMESTAMP()
    ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATEDIFF(
        DAY,
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        CURRENT_DATE()
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        SECOND,
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        CURRENT_TIMESTAMP()
    ) / 24.0 / 3600.0
    {%- else -%}
    TIMESTAMPDIFF(
        SECOND,
        MAX(CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)),
        CURRENT_TIMESTAMP()
    ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_ingestion_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}

{% macro render_current_ingestion_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
        CURRENT_DATE - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- else -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})::TIMESTAMP)
    )) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_ingestion_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}

{% macro render_current_ingestion_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
        CURRENT_DATE - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
    )) / 24.0 / 3600.0
    {%- else -%}
    EXTRACT(EPOCH FROM (
        CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})::TIMESTAMP)
    )) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_ingestion_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}

{% macro render_current_ingestion_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        CURRENT_TIMESTAMP
    ) / 24.0 / 3600.0 / 1000.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    DATEDIFF(
        DAY,
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        CURRENT_DATE
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
        CURRENT_TIMESTAMP
    ) / 24.0 / 3600.0 / 1000.0
    {%- else -%}
    TIMESTAMPDIFF(
        MILLISECOND,
        MAX(
            TRY_TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
        ),
        CURRENT_TIMESTAMP
    ) / 24.0 / 3600.0 / 1000.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_ingestion_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}

{% macro render_current_ingestion_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
        DATEDIFF(SECOND,
            MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
            SYSDATETIMEOFFSET()
        ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
        DATEDIFF(DAY,
            MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
            GETDATE()
        )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
        DATEDIFF(SECOND,
            MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
            GETDATE()
        ) / 24.0 / 3600.0
    {%- else -%}
        DATEDIFF(SECOND,
            MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
            SYSDATETIMEOFFSET()
        ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_current_ingestion_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

partition reload lag

Full sensor name 

table/timeliness/partition_reload_lag
Description
Table sensor that runs a query calculating maximum difference in days between ingestion timestamp and event timestamp rows.

SQL Template (Jinja2) 

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        TIMESTAMP_DIFF(
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            MILLISECOND
        )
    ) / 24.0 / 3600.0 / 1000.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        DATE_DIFF(
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            DAY
        )
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        DATETIME_DIFF(
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            MILLISECOND
        )
    ) / 24.0 / 3600.0 / 1000.0
    {%- else -%}
    MAX(
        TIMESTAMP_DIFF(
            SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP),
            SAFE_CAST({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }} AS TIMESTAMP),
            MILLISECOND
        )
    ) / 24.0 / 3600.0 / 1000.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/mysql.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
         TIMESTAMPDIFF(
            SECOND,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        DATEDIFF(
        DAY,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        TIMESTAMPDIFF(
            SECOND,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    ) / 24.0 / 3600.0
    {%- else -%}
    MAX(
         TIMESTAMPDIFF(
            SECOND,
            CAST({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }} AS TIMESTAMP),
            CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
        )
    ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        EXTRACT(EPOCH FROM (
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} - {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}
        ))
    ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} - {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}
        )
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        EXTRACT(EPOCH FROM (
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} - {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}
        ))
    ) / 24.0 / 3600.0
    {%- else -%}
    MAX(
        EXTRACT(EPOCH FROM (
            ({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})::TIMESTAMP - ({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})::TIMESTAMP
        ))
    ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
{% macro render_ingestion_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        EXTRACT(EPOCH FROM (
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} - {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}
        ))
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} - {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}
        )
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        EXTRACT(EPOCH FROM (
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} - {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}
        ))
    ) / 24.0 / 3600.0
    {%- else -%}
    MAX(
        EXTRACT(EPOCH FROM (
            ({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})::TIMESTAMP - ({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }})::TIMESTAMP
        ))
    ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        TIMESTAMPDIFF(
            MILLISECOND,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    ) / 24.0 / 3600.0 / 1000.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        DATEDIFF(
            DAY,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        TIMESTAMPDIFF(
            MILLISECOND,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    ) / 24.0 / 3600.0 / 1000.0
    {%- else -%}
    MAX(
        TIMESTAMPDIFF(
            MILLISECOND,
            TRY_TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }}),
            TRY_TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
        )
    ) / 24.0 / 3600.0 / 1000.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}

{% macro render_ingestion_event_diff() -%}
    {%- if lib.is_instant(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_instant(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        DATEDIFF(SECOND,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    ) / 24.0 / 3600.0
    {%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        DATEDIFF(
            DAY,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    )
    {%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true'
    and lib.is_local_date_time(table.columns[table.timestamp_columns.event_timestamp_column].type_snapshot.column_type) == 'true' -%}
    MAX(
        DATEDIFF(SECOND,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    ) / 24.0 / 3600.0
    {%- else -%}
    MAX(
        DATEDIFF(SECOND,
            {{ lib.render_column(table.timestamp_columns.event_timestamp_column, 'analyzed_table') }},
            {{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}
        )
    ) / 24.0 / 3600.0
    {%- endif -%}
{%- endmacro -%}

SELECT
    {{ render_ingestion_event_diff() }} AS actual_value
    {{- lib.render_data_grouping_projections('analyzed_table') }}
    {{- lib.render_time_dimension_projection('analyzed_table') }}
FROM {{ lib.render_target_table() }} AS analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}