increment_offset

Usage

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

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: