Skip to content

Continuous aggregates

Continuous aggregates are the reason that this adapter exists. With this adapter, you can use dbt to manage your continuous aggregates and their configuration.

Info

Consult the Timescale docs for more information regarding continuous aggregates.

Materialized views

dbt-postgres 1.6 added support for materialized views. This feature is also still available in this adapter. The main difference between materialized views and continuous aggregates is that continuous aggregates are automatically refreshed (based on a policy) by TimescaleDB, while materialized views are refreshed manually or when you run dbt run.

Full refresh

Once continuous aggregates have been created, only its metadata will be updated on subsequent runs. If you need to fully refresh the continuous aggregate, - maybe because you have changed its implementation - you can use the full-refresh option in dbt to drop and recreate the continuous aggregate.

CTEs and subqueries not supported

TimescaleDB does not support CTEs, subqueries and set-returning functions in continuous aggregates.

Usage

To use continuous aggregates, you need to set the materialized config to continuous_aggregate.

models/my_aggregate.sql
{{
  config(materialized='continuous_aggregate')
}}
select
    count(*),
    time_bucket(interval '1 day', time_column) as bucket
from {{ source('a_hypertable') }}
group by 2
dbt_project.yaml
models:
  your_project_name:
    model_name:
      +materialized: continuous_aggregate
# ...

Configuration options

dbt-specific options: refreshing upon creation

Continuous aggregates are refreshed automatically by TimescaleDB. This is configured using a refresh policy.

They are also refreshed initially when they are created. This is done by default but can be disabled by setting the refresh_now config option to false.

TimescaleDB continuous aggregate options

All TimescaleDB continuous aggregate configuration options as of version 2.12 are supported through model configuration as well:

  • materialized_only
  • create_group_indexes

TimescaleDB refresh policy options

A continuous aggregate is usually used with a refresh policy. This is configured using the refresh_policy config option. The following options are supported:

  • start_offset
  • end_offset
  • schedule_interval
  • initial_start
  • timezone
models/my_aggregate.sql
{{
  config(
    materialized='continuous_aggregate',
    refresh_policy={
      'start_offset': "interval '1 month'",
      'end_offset': "interval '1 hour'",
      'schedule_interval': "interval '1 hour'",
    })
}}
select
    count(*),
    time_bucket(interval '1 day', time_column) as bucket
from {{ source('a_hypertable') }}
group by 2
dbt_project.yaml
models:
  your_project_name:
    model_name:
      +materialized: continuous_aggregate
      +refresh_policy:
        start_offset: "interval '1 month'"
        end_offset: "interval '1 hour'"
        schedule_interval: "interval '1 hour'"
# ...

Info

Consult the Timescale docs for more information regarding these settings.