increment_offset

Usage

view: my_view {
  derived_table: {
    increment_key: ["created_date"]
    increment_offset: 1
    ...
  }
}
Hierarchy
view
derived_table
increment_offset

- or -

explore
aggregate_table
materialization
increment_offset
Default Value
0

Accepts
An integer

Special Rules

Definition

You can create incremental PDTs in your project if your dialect supports them. An incremental PDT is a persistent derived table (PDT) that Looker builds by appending fresh data to the table, instead of rebuilding the table in its entirety. See the Incremental PDTs documentation page for more information.

The increment_key parameter specifies the time period for which fresh data should be queried and appended to the aggregate table. The increment_offset parameter is an optional parameter you can use if you want to rebuild the table for previous time periods at the same time that fresh data is appended to the table. The increment_offset parameter defines the number of previous time periods that will be rebuilt when appending data to an incremental PDT.

The increment_offset parameter is useful in the case of late-arriving data, where previous time periods may have new data that wasn't included when the corresponding increment was originally built and appended to the PDT.

The default increment_offset value is 0, which means that only the new data from the current increment is appended to the table. If you set the increment_offset to 1, late-arriving data from the previous increment will be added to the table in addition to the new data from the current time increment.

See the Incremental PDTs documentation page for some example scenarios that illustrate how incremental PDTs work and that show the interaction of increment_key, increment_offset, and persistence strategy.

See the Supported database dialects for incremental PDTs section on this page for the list of dialects that support incremental PDTs.

Example

For example, this PDT is rebuilt in increments of one day (increment_key: event_day), going back three days (increment_offset: 3):

derived_table: {
  datagroup_trigger: usagetable_etl
  increment_key: "event_day"
  increment_offset: 3
  explore_source: events {
    column: name { field: account.name }
    column: account_id { field: account.id }
    column: database_dialect {}
    column: count_events {}
    column: event_day {}
   }
 }

Supported database dialects for incremental PDTs

For Looker to support incremental PDTs in your Looker project, your database dialect must support Data Definition Language (DDL) commands that enable deleting and inserting rows.

The following table shows which dialects support incremental PDTs in the latest release of Looker:

Dialect Supported?
Actian Avalanche
No
Amazon Athena
No
Amazon Aurora MySQL
No
Amazon Redshift
Yes
Apache Druid
No
Apache Druid 0.13+
No
Apache Druid 0.18+
No
Apache Hive 2.3+
No
Apache Hive 3.1.2+
No
Apache Spark 3+
No
ClickHouse
No
Cloudera Impala 3.1+
No
Cloudera Impala 3.1+ with Native Driver
No
Cloudera Impala with Native Driver
No
DataVirtuality
No
Databricks
Yes
Denodo 7
No
Denodo 8
No
Dremio
No
Dremio 11+
No
Exasol
No
Firebolt
No
Google BigQuery Legacy SQL
No
Google BigQuery Standard SQL
Yes
Google Cloud PostgreSQL
Yes
Google Cloud SQL
No
Google Spanner
No
Greenplum
Yes
HyperSQL
No
IBM Netezza
No
MariaDB
No
Microsoft Azure PostgreSQL
Yes
Microsoft Azure SQL Database
No
Microsoft Azure Synapse Analytics
Yes
Microsoft SQL Server 2008+
No
Microsoft SQL Server 2012+
No
Microsoft SQL Server 2016
No
Microsoft SQL Server 2017+
No
MongoBI
No
MySQL
Yes
MySQL 8.0.12+
Yes
Oracle
No
Oracle ADWC
No
PostgreSQL 9.5+
Yes
PostgreSQL pre-9.5
Yes
PrestoDB
No
PrestoSQL
No
SAP HANA 2+
No
SingleStore
No
SingleStore 7+
No
Snowflake
Yes
Teradata
No
Trino
No
Vector
No
Vertica
Yes