Microbatch incremental models in dbt

1.Microbatch: an incremental strategy built around time

Microbatch is an incremental strategy for large time-series tables. It is not a materialization of its own. You still set materialized='incremental' and then choose incremental_strategy='microbatch'. Like any incremental model, it avoids rebuilding the whole table on every run (the other strategies and how to choose between them are in [selecting an incremental strategy](lesson:1.11)). What changes is how the work is divided up. Microbatch uses a single time column, event_time, to define time ranges, and runs one query for each range.

Microbatch requires dbt v1.9 or higher. A project that defines its own custom microbatch macro must also set a distinct behavior flag in dbt_project.yml to enable batched execution. Without a custom macro there is nothing to set, because dbt handles microbatching automatically for any model that uses the strategy.

Other incremental strategies think in terms of "old" data and "new" data. Microbatch treats every batch as an atomic unit instead. A batch is one bounded time period, a single day by default, and dbt builds or replaces it on its own. This happens on the first build, on routine incremental runs and on backfills. A daily model run on October 2 builds the October 2 batch and swaps it into the target table. It makes no difference whether that day already had rows. Given the same input, reprocessing a batch any number of times produces the same table.

The model SQL must return exactly one batch of data, and it carries no is_incremental() filter. That is the sharpest difference from other strategies, where you write the "what is new" filter yourself. Here dbt generates the time filters from event_time, batch_size and lookback, and picks the replacement mechanism for the adapter. As a bonus, you can preview the model and see exactly the rows one batch will write.

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='session_start',
    begin='2020-01-01',
    batch_size='day'
) }}

Because batches are independent, dbt can do things a single incremental query cannot. It can rerun only the batches that failed, run batches at the same time, and backfill a date range without the hand-written conditional logic that backfills usually need. Microbatch adds to the existing strategies rather than replacing them. Without a trustworthy event timestamp, or when you want full control over the incremental logic, another strategy is the better choice.

Sources1

2.Required configs and adapter-specific settings

Three settings are required on every microbatch model. event_time names the column that says when each row occurred. begin is the "beginning of time": the first batch that an initial build or full refresh processes. batch_size sets how large each batch is, and accepts hour, day, month or year. Take a daily model with begin set a year back. A full build of it runs one query per day, plus one for today.

lookback is how microbatch deals with late-arriving data. The bookmark is the most recent batch already processed, and lookback is the number of batches before it that each run reprocesses along with the current one. With the default of 1, each run also rebuilds the batch just before the bookmark, so rows that arrived a day late are picked up without a full refresh. A larger value widens that window, at the cost of reprocessing more batches on every run.

A worked case: a daily model with lookback=3, run on October 1, builds four batches, September 28, 29, 30 and October 1. The same arithmetic sizes the window for a delay. Data that can arrive up to 48 hours late on a daily model needs a lookback of 2, so that the two prior days are rebuilt on each run.

On each adapter, dbt replaces a whole batch using the most efficient atomic mechanism that adapter offers. That choice explains the extra settings some adapters need. dbt-postgres replaces batches through merge, and a merge must match incoming rows to existing ones, so Postgres also needs unique_key. dbt-bigquery and dbt-spark replace batches through insert_overwrite, so they need partition_by. The documented extra-config list covers only these three adapters.

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
     ? ='sales_id', ## required for dbt-postgres
    event_time='transaction_date',
    begin='2023-01-01',
    batch_size='day'
) }}

Set full_refresh: false on microbatch models so they ignore invocations that pass --full-refresh. Running dbt run --full-refresh on a microbatch model by itself does not reset or reload data unless the model has a begin config. To reprocess history deliberately, use a targeted backfill: a full refresh with both --event-time-start and --event-time-end supplied.

Sources21

3.Which upstream refs get filtered per batch

In a microbatch model, dbt filters upstream inputs for you. When a parent has event_time configured, dbt wraps that ref() in a filter on the current batch's time range. Sources and seeds can carry event_time too, and are filtered the same way. You never write a where clause on the batch window yourself. The filter applies only to parents that declare event_time, and the parent's column can have a different name from the child's.

Here is a concrete case. A sessions model joins page_views to customers. page_views is a large time-series table that configures page_view_start as its event_time. customers is a small dimension table whose rows change at any time, and it configures no event_time.

models:
  - name: page_views
    config:
      event_time: page_view_start

with page_views as (

    select * from (
        -- filtered on configured event_time
        select * from "analytics"."page_views"
        where page_view_start >= '2024-10-01 00:00:00'  -- Oct 1
        and page_view_start < '2024-10-02 00:00:00'
    )

),

customers as (

    select * from "analytics"."customers"

),

A full scan of a small dimension table is harmless. A full scan of a billion-row fact table that someone forgot to tag costs a lot, because it is repeated for every batch. Configure event_time on every large parent that should be filtered. The reverse case also exists: a parent has event_time, but this model needs all of it. Write ref('upstream_model').render() to opt that one reference out. Use this sparingly, since the parents that carry event_time are usually the large ones.

Choose the event_time column carefully. It should record when the event happened, such as account_created_at or session_began_at, not when the row was loaded. Ingestion timestamps can work, but they have known failure modes.

With last_updated_at or loaded_at, the column changes whenever a row is updated. An updated row therefore lands in a later batch while its old copy still sits in the earlier batch, and the table ends up with both. lookback reduces this, but an update that falls outside the lookback window is never reprocessed, so it cannot prevent duplicates entirely.

ingested_at is stamped by the ingestion tool, not by the source. If the connector is resynced, every row gets a new ingested_at, so rows already loaded under their first date are loaded again under a second date. Microbatches stay correct as long as no resync happens, or you run a full refresh when one does. The same event_time setting also drives [sample-mode runs](lesson:1.13).

Sources13

4.Parallel or sequential batch execution

Independent batches can run at the same time, but only under certain conditions. Parallel batch execution is available on Snowflake and BigQuery. Even there, the first and last batches of a run never run in parallel with others. A model with 12 batches can run its middle batches in parallel, up to the number of available threads.

In most cases you don't configure this, because dbt detects it itself. Once the prerequisites are met and concurrent_batches is unset, dbt checks whether the model uses {{ this }}. Batches that read from the model's own table would conflict with each other, so a model that references {{ this }} runs its batches sequentially. Otherwise they run in parallel.

Which mode is right depends on the logic. Parallel runs are faster, but the result must not depend on the order in which batches run or finish. Per-transaction calculations meet that rule. Cumulative metrics do not, because each period builds on the one before it. They need sequential runs, which are slower but keep the batches in order. When auto-detection gets it wrong, concurrent_batches overrides it. You can set it in the model's config block or in dbt_project.yml. It is an override, not a gate: setting it to true does not lift the adapter and first/last-batch conditions.

models:
  +concurrent_batches: true # value set to true to run batches in parallel

Sources4

5.Backfilling a date range and retrying failed batches

Backfills reuse the batch machinery. Sometimes historical data has to be rebuilt, because a source was corrected or business logic changed. In that case, select the model and give dbt a start and an end for event_time. dbt processes every batch in that window as an independent query, exactly as on a normal run, with no conditional backfill code in the model.

dbt run --event-time-start "2024-09-01" --event-time-end "2024-09-04"

The two flags only work as a pair. If you pass --event-time-start alone, or --event-time-end alone, the command is invalid. All time values are read as UTC: the event_time column, begin, and both flags. A team in UTC+10 that expects "2024-09-01" to mean its local midnight will find the window ten hours off.

When some batches fail, you don't have to rebuild the window. Each batch is idempotent, so [dbt retry](lesson:7.2) reprocesses only the batches that failed, and the successful ones stay as they are. Suppose 2 of 30 daily batches time out: the retry reruns two queries, not thirty.

Sources1

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Microbatch is its own materialization, configured with materialized='microbatch'.Why is that wrong?

   Microbatch is an incremental strategy. The model uses materialized='incremental' together with incremental_strategy='microbatch'.

   Covered in Microbatch: an incremental strategy built around time

2. 2.Running dbt run --full-refresh on a microbatch model reloads all of its history.Why is that wrong?

   On its own it does not reset or reload data unless the model has a begin config. A deliberate reload needs both --event-time-start and --event-time-end, and full_refresh: false is the recommended guard.

   Covered in Required configs and adapter-specific settings

3. 3.Setting event_time on the microbatch model is enough for dbt to filter all of its upstream refs.Why is that wrong?

   dbt filters only parents that configure their own event_time. Any other parent is read in full for every batch.

   Covered in Which upstream refs get filtered per batch

4. 4.Setting concurrent_batches: true makes every batch, including the first and last, run in parallel on any adapter.Why is that wrong?

   concurrent_batches only overrides dbt's auto-detection. Parallel runs still need a supported adapter and apply only to batches that are neither first nor last.

   Covered in Parallel or sequential batch execution

5. 5.A cumulative running-total metric is safe to compute with parallel batches.Why is that wrong?

   Cumulative calculations depend on batch order, so they need sequential runs.

   Covered in Parallel or sequential batch execution