Last updated: July 22, 2025
Data staleness data quality checks, SQL examples
A table-level check that calculates the time difference between the last timestamp when any data was loaded into a table and the current time. This check can only be use when a data pipeline, ETL process, or trigger in the data warehouse is filling an extra column with the timestamp when the data loading job was loaded. The ingestion column used for comparison is defined as the timestamp_columns.ingestion_timestamp_column on the table configuration. This check is also known as "Data Staleness".
The data staleness data quality check has the following variants for each type of data quality checks supported by DQOps.
profile data staleness
Check description
Calculates the time difference in days between the current date and the most recent data ingestion timestamp (staleness)
Data quality check name | Friendly name | Category | Check type | Time scale | Quality dimension | Sensor definition | Quality rule | Standard |
---|---|---|---|---|---|---|---|---|
profile_data_staleness |
Data staleness (Maximum number of days since the recent ingestion) | timeliness | profiling | Timeliness | data_staleness | max_days |
Command-line examples
Please expand the section below to see the DQOps command-line examples to run or activate the profile data staleness data quality check.
Managing profile data staleness check from DQOps shell
Activate this data quality using the check activate CLI command, providing the connection name, table name, check name, and all other filters. Activates the warning rule with the default parameters.
dqo> check activate -c=connection_name -t=schema_name.table_name -ch=profile_data_staleness --enable-warning
You can also use patterns to activate the check on all matching tables and columns.
dqo> check activate -c=connection_name -t=schema_prefix*.fact_* -ch=profile_data_staleness --enable-warning
Additional rule parameters are passed using the -Wrule_parameter_name=value.
Activate this data quality using the check activate CLI command, providing the connection name, table name, check name, and all other filters. Activates the error rule with the default parameters.
dqo> check activate -c=connection_name -t=schema_name.table_name -ch=profile_data_staleness --enable-error
You can also use patterns to activate the check on all matching tables and columns.
dqo> check activate -c=connection_name -t=schema_prefix*.fact_* -ch=profile_data_staleness --enable-error
Additional rule parameters are passed using the -Erule_parameter_name=value.
Run this data quality check using the check run CLI command by providing the check name and all other targeting filters. The following example shows how to run the profile_data_staleness check on all tables on a single data source.
It is also possible to run this check on a specific connection and table. In order to do this, use the connection name and the full table name parameters.
You can also run this check on all tables on which the profile_data_staleness check is enabled using patterns to find tables.
YAML configuration
The sample schema_name.table_name.dqotable.yaml file with the check configured is shown below.
# yaml-language-server: $schema=https://cloud.dqops.com/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
timestamp_columns:
event_timestamp_column: col_event_timestamp
ingestion_timestamp_column: col_inserted_at
profiling_checks:
timeliness:
profile_data_staleness:
warning:
max_days: 1.0
error:
max_days: 2.0
fatal:
max_days: 1.0
columns:
col_event_timestamp:
labels:
- optional column that stores the timestamp when the event/transaction happened
col_inserted_at:
labels:
- optional column that stores the timestamp when row was ingested
Samples of generated SQL queries for each data source type
Please expand the database engine name section to see the SQL query rendered by a Jinja2 template for the data_staleness data quality sensor.
BigQuery
{% 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() -}}
ClickHouse
{% import '/dialects/clickhouse.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' -%}
DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDateTime64(now(), 3)
) / 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(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDate(now())
)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATE_DIFF(
'MILLISECOND',
toDateTime(now()),
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
) / 24.0 / 3600.0 / 1000.0
{%- else -%}
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}, 3)
),
toDateTime64(now(), 3)
) / 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() -}}
Databricks
{% import '/dialects/databricks.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
DB2
{% import '/dialects/db2.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' -%}
SECONDS_BETWEEN(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' -%}
DAYS_BETWEEN(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' -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- else -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
DuckDB
{% import '/dialects/duckdb.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') }})::TIMESTAMP WITH TIME ZONE
)) / 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') }})::TIMESTAMP WITH TIME ZONE
{%- 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') }})::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))::TIMESTAMP WITH TIME ZONE
)) / 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() -}}
HANA
{% import '/dialects/hana.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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
NANO100_BETWEEN(
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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- else -%}
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
MariaDB
{% import '/dialects/mariadb.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() -}}
MySQL
{% 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() -}}
Oracle
{% import '/dialects/oracle.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' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- else -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
PostgreSQL
{% 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() -}}
Presto
{% import '/dialects/presto.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
QuestDB
{% import '/dialects/questdb.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 (
NOW() - 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' -%}
TODAY() - 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 (
NOW() - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
NOW() - 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_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
Redshift
{% 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() -}}
Snowflake
{% 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() -}}
Spark
{% import '/dialects/spark.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SQL Server
{% 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() -}}
Teradata
{% import '/dialects/teradata.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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATEDIFF(
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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- else -%}
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 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() -}}
SELECT
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0 AS actual_value
FROM "<target_schema>"."<target_table>" AS analyzed_table
Trino
{% import '/dialects/trino.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
Expand the Configure with data grouping section to see additional examples for configuring this data quality checks to use data grouping (GROUP BY).
Configuration with data grouping
Sample configuration with data grouping enabled (YAML) The sample below shows how to configure the data grouping and how it affects the generated SQL query.
# yaml-language-server: $schema=https://cloud.dqops.com/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
timestamp_columns:
event_timestamp_column: col_event_timestamp
ingestion_timestamp_column: col_inserted_at
default_grouping_name: group_by_country_and_state
groupings:
group_by_country_and_state:
level_1:
source: column_value
column: country
level_2:
source: column_value
column: state
profiling_checks:
timeliness:
profile_data_staleness:
warning:
max_days: 1.0
error:
max_days: 2.0
fatal:
max_days: 1.0
columns:
col_event_timestamp:
labels:
- optional column that stores the timestamp when the event/transaction happened
col_inserted_at:
labels:
- optional column that stores the timestamp when row was ingested
country:
labels:
- column used as the first grouping key
state:
labels:
- column used as the second grouping key
Please expand the database engine name section to see the SQL query rendered by a Jinja2 template for the data_staleness sensor.
BigQuery
{% 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() -}}
SELECT
TIMESTAMP_DIFF(
CURRENT_TIMESTAMP(),
MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
),
MILLISECOND
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
ClickHouse
{% import '/dialects/clickhouse.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' -%}
DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDateTime64(now(), 3)
) / 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(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDate(now())
)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATE_DIFF(
'MILLISECOND',
toDateTime(now()),
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
) / 24.0 / 3600.0 / 1000.0
{%- else -%}
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}, 3)
),
toDateTime64(now(), 3)
) / 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() -}}
SELECT
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull(analyzed_table."col_inserted_at", 3)
),
toDateTime64(now(), 3)
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Databricks
{% import '/dialects/databricks.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SELECT
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
DB2
{% import '/dialects/db2.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' -%}
SECONDS_BETWEEN(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' -%}
DAYS_BETWEEN(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' -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- else -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX(analyzed_table."col_inserted_at")) / 24.0 / 3600.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
DuckDB
{% import '/dialects/duckdb.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') }})::TIMESTAMP WITH TIME ZONE
)) / 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') }})::TIMESTAMP WITH TIME ZONE
{%- 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') }})::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))::TIMESTAMP WITH TIME ZONE
)) / 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at"))::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
HANA
{% import '/dialects/hana.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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
NANO100_BETWEEN(
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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- else -%}
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP(analyzed_table."col_inserted_at")
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
MariaDB
{% import '/dialects/mariadb.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() -}}
SELECT
TIMESTAMPDIFF(
SECOND,
MAX(CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)),
CURRENT_TIMESTAMP()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
MySQL
{% 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() -}}
SELECT
TIMESTAMPDIFF(
SECOND,
MAX(CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)),
CURRENT_TIMESTAMP()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Oracle
{% import '/dialects/oracle.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' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- else -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX(analyzed_table."col_inserted_at") AS DATE)) AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
PostgreSQL
{% 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Presto
{% import '/dialects/presto.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST(analyzed_table."col_inserted_at" AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "your_trino_database"."<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
QuestDB
{% import '/dialects/questdb.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 (
NOW() - 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' -%}
TODAY() - 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 (
NOW() - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
NOW() - 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_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
EXTRACT(EPOCH FROM (
NOW() - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM(
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Redshift
{% 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Snowflake
{% 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() -}}
SELECT
TIMESTAMPDIFF(
MILLISECOND,
MAX(
TRY_TO_TIMESTAMP(analyzed_table."col_inserted_at")
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Spark
{% import '/dialects/spark.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SELECT
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
SQL Server
{% 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() -}}
SELECT
DATEDIFF(SECOND,
MAX(analyzed_table.[col_inserted_at]),
SYSDATETIMEOFFSET()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.[country] AS grouping_level_1,
analyzed_table.[state] AS grouping_level_2
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table
GROUP BY analyzed_table.[country], analyzed_table.[state]
ORDER BY level_1, level_2
,
Teradata
{% import '/dialects/teradata.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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATEDIFF(
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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- else -%}
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 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() -}}
SELECT
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Trino
{% import '/dialects/trino.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST(analyzed_table."col_inserted_at" AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "your_trino_catalog"."<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
daily data staleness
Check description
Daily calculating the time difference in days between the current date and the most recent data ingestion timestamp (staleness)
Data quality check name | Friendly name | Category | Check type | Time scale | Quality dimension | Sensor definition | Quality rule | Standard |
---|---|---|---|---|---|---|---|---|
daily_data_staleness |
Data staleness (Maximum number of days since the recent ingestion) | timeliness | monitoring | daily | Timeliness | data_staleness | max_days |
Command-line examples
Please expand the section below to see the DQOps command-line examples to run or activate the daily data staleness data quality check.
Managing daily data staleness check from DQOps shell
Activate this data quality using the check activate CLI command, providing the connection name, table name, check name, and all other filters. Activates the warning rule with the default parameters.
dqo> check activate -c=connection_name -t=schema_name.table_name -ch=daily_data_staleness --enable-warning
You can also use patterns to activate the check on all matching tables and columns.
dqo> check activate -c=connection_name -t=schema_prefix*.fact_* -ch=daily_data_staleness --enable-warning
Additional rule parameters are passed using the -Wrule_parameter_name=value.
Activate this data quality using the check activate CLI command, providing the connection name, table name, check name, and all other filters. Activates the error rule with the default parameters.
dqo> check activate -c=connection_name -t=schema_name.table_name -ch=daily_data_staleness --enable-error
You can also use patterns to activate the check on all matching tables and columns.
dqo> check activate -c=connection_name -t=schema_prefix*.fact_* -ch=daily_data_staleness --enable-error
Additional rule parameters are passed using the -Erule_parameter_name=value.
Run this data quality check using the check run CLI command by providing the check name and all other targeting filters. The following example shows how to run the daily_data_staleness check on all tables on a single data source.
It is also possible to run this check on a specific connection and table. In order to do this, use the connection name and the full table name parameters.
You can also run this check on all tables on which the daily_data_staleness check is enabled using patterns to find tables.
YAML configuration
The sample schema_name.table_name.dqotable.yaml file with the check configured is shown below.
# yaml-language-server: $schema=https://cloud.dqops.com/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
timestamp_columns:
event_timestamp_column: col_event_timestamp
ingestion_timestamp_column: col_inserted_at
monitoring_checks:
daily:
timeliness:
daily_data_staleness:
warning:
max_days: 1.0
error:
max_days: 2.0
fatal:
max_days: 1.0
columns:
col_event_timestamp:
labels:
- optional column that stores the timestamp when the event/transaction happened
col_inserted_at:
labels:
- optional column that stores the timestamp when row was ingested
Samples of generated SQL queries for each data source type
Please expand the database engine name section to see the SQL query rendered by a Jinja2 template for the data_staleness data quality sensor.
BigQuery
{% 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() -}}
ClickHouse
{% import '/dialects/clickhouse.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' -%}
DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDateTime64(now(), 3)
) / 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(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDate(now())
)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATE_DIFF(
'MILLISECOND',
toDateTime(now()),
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
) / 24.0 / 3600.0 / 1000.0
{%- else -%}
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}, 3)
),
toDateTime64(now(), 3)
) / 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() -}}
Databricks
{% import '/dialects/databricks.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
DB2
{% import '/dialects/db2.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' -%}
SECONDS_BETWEEN(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' -%}
DAYS_BETWEEN(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' -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- else -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
DuckDB
{% import '/dialects/duckdb.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') }})::TIMESTAMP WITH TIME ZONE
)) / 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') }})::TIMESTAMP WITH TIME ZONE
{%- 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') }})::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))::TIMESTAMP WITH TIME ZONE
)) / 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() -}}
HANA
{% import '/dialects/hana.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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
NANO100_BETWEEN(
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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- else -%}
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
MariaDB
{% import '/dialects/mariadb.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() -}}
MySQL
{% 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() -}}
Oracle
{% import '/dialects/oracle.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' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- else -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
PostgreSQL
{% 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() -}}
Presto
{% import '/dialects/presto.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
QuestDB
{% import '/dialects/questdb.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 (
NOW() - 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' -%}
TODAY() - 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 (
NOW() - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
NOW() - 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_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
Redshift
{% 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() -}}
Snowflake
{% 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() -}}
Spark
{% import '/dialects/spark.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SQL Server
{% 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() -}}
Teradata
{% import '/dialects/teradata.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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATEDIFF(
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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- else -%}
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 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() -}}
SELECT
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0 AS actual_value
FROM "<target_schema>"."<target_table>" AS analyzed_table
Trino
{% import '/dialects/trino.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
Expand the Configure with data grouping section to see additional examples for configuring this data quality checks to use data grouping (GROUP BY).
Configuration with data grouping
Sample configuration with data grouping enabled (YAML) The sample below shows how to configure the data grouping and how it affects the generated SQL query.
# yaml-language-server: $schema=https://cloud.dqops.com/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
timestamp_columns:
event_timestamp_column: col_event_timestamp
ingestion_timestamp_column: col_inserted_at
default_grouping_name: group_by_country_and_state
groupings:
group_by_country_and_state:
level_1:
source: column_value
column: country
level_2:
source: column_value
column: state
monitoring_checks:
daily:
timeliness:
daily_data_staleness:
warning:
max_days: 1.0
error:
max_days: 2.0
fatal:
max_days: 1.0
columns:
col_event_timestamp:
labels:
- optional column that stores the timestamp when the event/transaction happened
col_inserted_at:
labels:
- optional column that stores the timestamp when row was ingested
country:
labels:
- column used as the first grouping key
state:
labels:
- column used as the second grouping key
Please expand the database engine name section to see the SQL query rendered by a Jinja2 template for the data_staleness sensor.
BigQuery
{% 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() -}}
SELECT
TIMESTAMP_DIFF(
CURRENT_TIMESTAMP(),
MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
),
MILLISECOND
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
ClickHouse
{% import '/dialects/clickhouse.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' -%}
DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDateTime64(now(), 3)
) / 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(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDate(now())
)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATE_DIFF(
'MILLISECOND',
toDateTime(now()),
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
) / 24.0 / 3600.0 / 1000.0
{%- else -%}
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}, 3)
),
toDateTime64(now(), 3)
) / 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() -}}
SELECT
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull(analyzed_table."col_inserted_at", 3)
),
toDateTime64(now(), 3)
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Databricks
{% import '/dialects/databricks.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SELECT
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
DB2
{% import '/dialects/db2.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' -%}
SECONDS_BETWEEN(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' -%}
DAYS_BETWEEN(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' -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- else -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX(analyzed_table."col_inserted_at")) / 24.0 / 3600.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
DuckDB
{% import '/dialects/duckdb.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') }})::TIMESTAMP WITH TIME ZONE
)) / 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') }})::TIMESTAMP WITH TIME ZONE
{%- 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') }})::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))::TIMESTAMP WITH TIME ZONE
)) / 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at"))::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
HANA
{% import '/dialects/hana.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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
NANO100_BETWEEN(
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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- else -%}
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP(analyzed_table."col_inserted_at")
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
MariaDB
{% import '/dialects/mariadb.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() -}}
SELECT
TIMESTAMPDIFF(
SECOND,
MAX(CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)),
CURRENT_TIMESTAMP()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
MySQL
{% 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() -}}
SELECT
TIMESTAMPDIFF(
SECOND,
MAX(CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)),
CURRENT_TIMESTAMP()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Oracle
{% import '/dialects/oracle.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' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- else -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX(analyzed_table."col_inserted_at") AS DATE)) AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
PostgreSQL
{% 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Presto
{% import '/dialects/presto.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST(analyzed_table."col_inserted_at" AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "your_trino_database"."<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
QuestDB
{% import '/dialects/questdb.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 (
NOW() - 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' -%}
TODAY() - 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 (
NOW() - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
NOW() - 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_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
EXTRACT(EPOCH FROM (
NOW() - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM(
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Redshift
{% 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Snowflake
{% 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() -}}
SELECT
TIMESTAMPDIFF(
MILLISECOND,
MAX(
TRY_TO_TIMESTAMP(analyzed_table."col_inserted_at")
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Spark
{% import '/dialects/spark.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SELECT
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
SQL Server
{% 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() -}}
SELECT
DATEDIFF(SECOND,
MAX(analyzed_table.[col_inserted_at]),
SYSDATETIMEOFFSET()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.[country] AS grouping_level_1,
analyzed_table.[state] AS grouping_level_2
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table
GROUP BY analyzed_table.[country], analyzed_table.[state]
ORDER BY level_1, level_2
,
Teradata
{% import '/dialects/teradata.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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATEDIFF(
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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- else -%}
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 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() -}}
SELECT
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Trino
{% import '/dialects/trino.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST(analyzed_table."col_inserted_at" AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "your_trino_catalog"."<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
monthly data staleness
Check description
Monthly monitoring calculating the time difference in days between the current date and the most recent data ingestion timestamp (staleness)
Data quality check name | Friendly name | Category | Check type | Time scale | Quality dimension | Sensor definition | Quality rule | Standard |
---|---|---|---|---|---|---|---|---|
monthly_data_staleness |
Data staleness (Maximum number of days since the recent ingestion) | timeliness | monitoring | monthly | Timeliness | data_staleness | max_days |
Command-line examples
Please expand the section below to see the DQOps command-line examples to run or activate the monthly data staleness data quality check.
Managing monthly data staleness check from DQOps shell
Activate this data quality using the check activate CLI command, providing the connection name, table name, check name, and all other filters. Activates the warning rule with the default parameters.
dqo> check activate -c=connection_name -t=schema_name.table_name -ch=monthly_data_staleness --enable-warning
You can also use patterns to activate the check on all matching tables and columns.
dqo> check activate -c=connection_name -t=schema_prefix*.fact_* -ch=monthly_data_staleness --enable-warning
Additional rule parameters are passed using the -Wrule_parameter_name=value.
Activate this data quality using the check activate CLI command, providing the connection name, table name, check name, and all other filters. Activates the error rule with the default parameters.
dqo> check activate -c=connection_name -t=schema_name.table_name -ch=monthly_data_staleness --enable-error
You can also use patterns to activate the check on all matching tables and columns.
dqo> check activate -c=connection_name -t=schema_prefix*.fact_* -ch=monthly_data_staleness --enable-error
Additional rule parameters are passed using the -Erule_parameter_name=value.
Run this data quality check using the check run CLI command by providing the check name and all other targeting filters. The following example shows how to run the monthly_data_staleness check on all tables on a single data source.
It is also possible to run this check on a specific connection and table. In order to do this, use the connection name and the full table name parameters.
You can also run this check on all tables on which the monthly_data_staleness check is enabled using patterns to find tables.
YAML configuration
The sample schema_name.table_name.dqotable.yaml file with the check configured is shown below.
# yaml-language-server: $schema=https://cloud.dqops.com/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
timestamp_columns:
event_timestamp_column: col_event_timestamp
ingestion_timestamp_column: col_inserted_at
monitoring_checks:
monthly:
timeliness:
monthly_data_staleness:
warning:
max_days: 1.0
error:
max_days: 2.0
fatal:
max_days: 1.0
columns:
col_event_timestamp:
labels:
- optional column that stores the timestamp when the event/transaction happened
col_inserted_at:
labels:
- optional column that stores the timestamp when row was ingested
Samples of generated SQL queries for each data source type
Please expand the database engine name section to see the SQL query rendered by a Jinja2 template for the data_staleness data quality sensor.
BigQuery
{% 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() -}}
ClickHouse
{% import '/dialects/clickhouse.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' -%}
DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDateTime64(now(), 3)
) / 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(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDate(now())
)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATE_DIFF(
'MILLISECOND',
toDateTime(now()),
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
) / 24.0 / 3600.0 / 1000.0
{%- else -%}
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}, 3)
),
toDateTime64(now(), 3)
) / 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() -}}
Databricks
{% import '/dialects/databricks.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
DB2
{% import '/dialects/db2.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' -%}
SECONDS_BETWEEN(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' -%}
DAYS_BETWEEN(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' -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- else -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
DuckDB
{% import '/dialects/duckdb.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') }})::TIMESTAMP WITH TIME ZONE
)) / 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') }})::TIMESTAMP WITH TIME ZONE
{%- 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') }})::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))::TIMESTAMP WITH TIME ZONE
)) / 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() -}}
HANA
{% import '/dialects/hana.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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
NANO100_BETWEEN(
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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- else -%}
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
MariaDB
{% import '/dialects/mariadb.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() -}}
MySQL
{% 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() -}}
Oracle
{% import '/dialects/oracle.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' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- else -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
PostgreSQL
{% 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() -}}
Presto
{% import '/dialects/presto.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
QuestDB
{% import '/dialects/questdb.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 (
NOW() - 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' -%}
TODAY() - 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 (
NOW() - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
NOW() - 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_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
Redshift
{% 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() -}}
Snowflake
{% 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() -}}
Spark
{% import '/dialects/spark.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SQL Server
{% 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() -}}
Teradata
{% import '/dialects/teradata.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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATEDIFF(
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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- else -%}
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 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() -}}
SELECT
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0 AS actual_value
FROM "<target_schema>"."<target_table>" AS analyzed_table
Trino
{% import '/dialects/trino.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
Expand the Configure with data grouping section to see additional examples for configuring this data quality checks to use data grouping (GROUP BY).
Configuration with data grouping
Sample configuration with data grouping enabled (YAML) The sample below shows how to configure the data grouping and how it affects the generated SQL query.
# yaml-language-server: $schema=https://cloud.dqops.com/dqo-yaml-schema/TableYaml-schema.json
apiVersion: dqo/v1
kind: table
spec:
timestamp_columns:
event_timestamp_column: col_event_timestamp
ingestion_timestamp_column: col_inserted_at
default_grouping_name: group_by_country_and_state
groupings:
group_by_country_and_state:
level_1:
source: column_value
column: country
level_2:
source: column_value
column: state
monitoring_checks:
monthly:
timeliness:
monthly_data_staleness:
warning:
max_days: 1.0
error:
max_days: 2.0
fatal:
max_days: 1.0
columns:
col_event_timestamp:
labels:
- optional column that stores the timestamp when the event/transaction happened
col_inserted_at:
labels:
- optional column that stores the timestamp when row was ingested
country:
labels:
- column used as the first grouping key
state:
labels:
- column used as the second grouping key
Please expand the database engine name section to see the SQL query rendered by a Jinja2 template for the data_staleness sensor.
BigQuery
{% 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() -}}
SELECT
TIMESTAMP_DIFF(
CURRENT_TIMESTAMP(),
MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
),
MILLISECOND
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `your-google-project-id`.`<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
ClickHouse
{% import '/dialects/clickhouse.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' -%}
DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDateTime64(now(), 3)
) / 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(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
toDate(now())
)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATE_DIFF(
'MILLISECOND',
toDateTime(now()),
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
) / 24.0 / 3600.0 / 1000.0
{%- else -%}
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}, 3)
),
toDateTime64(now(), 3)
) / 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() -}}
SELECT
DATE_DIFF(
'MILLISECOND',
MAX(
toDateTime64OrNull(analyzed_table."col_inserted_at", 3)
),
toDateTime64(now(), 3)
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Databricks
{% import '/dialects/databricks.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SELECT
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
DB2
{% import '/dialects/db2.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' -%}
SECONDS_BETWEEN(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' -%}
DAYS_BETWEEN(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' -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- else -%}
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})) / 24.0 / 3600.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
SECONDS_BETWEEN(CURRENT_TIMESTAMP, MAX(analyzed_table."col_inserted_at")) / 24.0 / 3600.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
DuckDB
{% import '/dialects/duckdb.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') }})::TIMESTAMP WITH TIME ZONE
)) / 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') }})::TIMESTAMP WITH TIME ZONE
{%- 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') }})::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX(({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))::TIMESTAMP WITH TIME ZONE
)) / 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at"))::TIMESTAMP WITH TIME ZONE
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
HANA
{% import '/dialects/hana.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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
NANO100_BETWEEN(
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' -%}
NANO100_BETWEEN(
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- else -%}
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
NANO100_BETWEEN(
MAX(
TO_TIMESTAMP(analyzed_table."col_inserted_at")
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 / 10000 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
MariaDB
{% import '/dialects/mariadb.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() -}}
SELECT
TIMESTAMPDIFF(
SECOND,
MAX(CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)),
CURRENT_TIMESTAMP()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
MySQL
{% 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() -}}
SELECT
TIMESTAMPDIFF(
SECOND,
MAX(CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)),
CURRENT_TIMESTAMP()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Oracle
{% import '/dialects/oracle.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' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- else -%}
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS DATE))
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
(CAST(CURRENT_TIMESTAMP AS DATE) - CAST(MAX(analyzed_table."col_inserted_at") AS DATE)) AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
PostgreSQL
{% 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_postgresql_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Presto
{% import '/dialects/presto.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST(analyzed_table."col_inserted_at" AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "your_trino_database"."<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
QuestDB
{% import '/dialects/questdb.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 (
NOW() - 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' -%}
TODAY() - 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 (
NOW() - MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }})
)) / 24.0 / 3600.0
{%- else -%}
EXTRACT(EPOCH FROM (
NOW() - 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_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM(
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
EXTRACT(EPOCH FROM (
NOW() - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM(
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Redshift
{% 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() -}}
SELECT
EXTRACT(EPOCH FROM (
CURRENT_TIMESTAMP - MAX((analyzed_table."col_inserted_at")::TIMESTAMP)
)) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_redshift_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Snowflake
{% 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() -}}
SELECT
TIMESTAMPDIFF(
MILLISECOND,
MAX(
TRY_TO_TIMESTAMP(analyzed_table."col_inserted_at")
),
CURRENT_TIMESTAMP
) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "your_snowflake_database"."<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Spark
{% import '/dialects/spark.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' -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(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' -%}
DATEDIFF(
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' -%}
(
BIGINT(CURRENT_DATETIME())
-
BIGINT(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}))
) / 24.0 / 3600.0
{%- else -%}
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS 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() -}}
SELECT
(
BIGINT(CURRENT_TIMESTAMP())
-
BIGINT(MAX(
SAFE_CAST(analyzed_table.`col_inserted_at` AS TIMESTAMP)
))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.`country` AS grouping_level_1,
analyzed_table.`state` AS grouping_level_2
FROM `<target_schema>`.`<target_table>` AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
SQL Server
{% 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() -}}
SELECT
DATEDIFF(SECOND,
MAX(analyzed_table.[col_inserted_at]),
SYSDATETIMEOFFSET()
) / 24.0 / 3600.0 AS actual_value,
analyzed_table.[country] AS grouping_level_1,
analyzed_table.[state] AS grouping_level_2
FROM [your_sql_server_database].[<target_schema>].[<target_table>] AS analyzed_table
GROUP BY analyzed_table.[country], analyzed_table.[state]
ORDER BY level_1, level_2
,
Teradata
{% import '/dialects/teradata.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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
DATEDIFF(
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(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0
{%- else -%}
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}) AS TIMESTAMP)) DAY(4) TO SECOND))
) / 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() -}}
SELECT
(
EXTRACT(DAY FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 86400
+ EXTRACT(HOUR FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 3600
+ EXTRACT(MINUTE FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND)) * 60
+ EXTRACT(SECOND FROM ((CURRENT_TIMESTAMP - CAST(MAX(analyzed_table."col_inserted_at") AS TIMESTAMP)) DAY(4) TO SECOND))
) / 24.0 / 3600.0 AS actual_value,
analyzed_table."country" AS grouping_level_1,
analyzed_table."state" AS grouping_level_2
FROM "<target_schema>"."<target_table>" AS analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
Trino
{% import '/dialects/trino.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' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- elif lib.is_local_date(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'DAY',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_DATE
) AS DOUBLE)
{%- elif lib.is_local_date_time(table.columns[table.timestamp_columns.ingestion_timestamp_column].type_snapshot.column_type) == 'true' -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }}),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- else -%}
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST({{ lib.render_column(table.timestamp_columns.ingestion_timestamp_column, 'analyzed_table') }} AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0
{%- endif -%}
{%- endmacro -%}
SELECT
{{ render_current_ingestion_diff() }} AS actual_value
{{- lib.render_data_grouping_projections_reference('analyzed_table') }}
{{- lib.render_time_dimension_projection_reference('analyzed_table') }}
FROM (
SELECT
original_table.*
{{- lib.render_data_grouping_projections('original_table') }}
{{- lib.render_time_dimension_projection('original_table') }}
FROM {{ lib.render_target_table() }} original_table
) analyzed_table
{{- lib.render_where_clause() -}}
{{- lib.render_group_by() -}}
{{- lib.render_order_by() -}}
SELECT
CAST(DATE_DIFF(
'MILLISECOND',
MAX(
TRY_CAST(analyzed_table."col_inserted_at" AS TIMESTAMP)
),
CURRENT_TIMESTAMP
) AS DOUBLE) / 24.0 / 3600.0 / 1000.0 AS actual_value,
analyzed_table.grouping_level_1,
analyzed_table.grouping_level_2
FROM (
SELECT
original_table.*,
original_table."country" AS grouping_level_1,
original_table."state" AS grouping_level_2
FROM "your_trino_catalog"."<target_schema>"."<target_table>" original_table
) analyzed_table
GROUP BY grouping_level_1, grouping_level_2
ORDER BY grouping_level_1, grouping_level_2
What's next
- Learn how to configure data quality checks in DQOps
- Look at the examples of running data quality checks, targeting tables and columns