Skip to content

Last updated: July 22, 2025

DQOps data quality availability sensors, SQL examples

All data quality sensors in the availability category supported by DQOps are listed below. Those sensors are measured on a table level.


table availability

Table availability sensor runs a simple table scan query to detect if the table is queryable. This sensor returns 0.0 when no failure was detected or 1.0 when a failure was detected.

Sensor summary

The table availability sensor is documented below.

Target Category Full sensor name Source code on GitHub
table availability table/availability/table_availability sensors/table/availability

Jinja2 SQL templates

The templates used to generate the SQL query for each data source supported by DQOps is shown below.

{% import '/dialects/bigquery.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/clickhouse.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/databricks.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/db2.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/duckdb.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/hana.sql.jinja2' as lib with context -%}
SELECT
    CAST(0.0 AS DOUBLE) AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/mariadb.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/mysql.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/oracle.sql.jinja2' as lib with context -%}
SELECT
    CASE
       WHEN COUNT(*) > 0 THEN COUNT(*)
       ELSE 1.0
    END AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/postgresql.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/presto.sql.jinja2' as lib with context -%}
SELECT
    CAST(0.0 AS DOUBLE) AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/questdb.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/redshift.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/snowflake.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/spark.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
FROM
    (
        SELECT TOP 1
            *
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
    ) AS tab_scan
{% import '/dialects/teradata.sql.jinja2' as lib with context -%}
SELECT
    0.0 AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        QUALIFY ROW_NUMBER() OVER (ORDER BY 1) = 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}
{% import '/dialects/trino.sql.jinja2' as lib with context -%}
SELECT
    CAST(0.0 AS DOUBLE) AS actual_value
    {{- lib.render_time_dimension_projection('tab_scan') }}
FROM
    (
        SELECT
            *
            {{- lib.render_time_dimension_projection('analyzed_table') }}
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan
{% if lib.time_series is not none -%}
GROUP BY time_period
ORDER BY time_period
{%- endif -%}

What's next