availability table sensors

table availability

Full sensor name

table/availability/table_availability

Description
Table availability sensor that executes a row count query.

SQL Template (Jinja2)

BigQueryMySQLPostgreSQLRedshiftSnowflakeSQL Server

{% import '/dialects/bigquery.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
GROUP BY time_period
ORDER BY time_period

{% import '/dialects/mysql.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
GROUP BY time_period
ORDER BY time_period

{% import '/dialects/postgresql.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
GROUP BY time_period
ORDER BY time_period

{% import '/dialects/redshift.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
GROUP BY time_period
ORDER BY time_period

{% import '/dialects/snowflake.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
GROUP BY time_period
ORDER BY time_period

{% import '/dialects/sqlserver.sql.jinja2' as lib with context -%}
SELECT
    CASE
       WHEN COUNT(*) > 0 THEN COUNT(*)
       ELSE 1.0
    END AS actual_value
FROM
    (
        SELECT
            *
        FROM {{ lib.render_target_table() }} AS analyzed_table
        {{ lib.render_where_clause() }}
        LIMIT 1
    ) AS tab_scan

---