Does GA4Dataform’s incremental pattern “cost” a full scan?

One of my favourite things about GA4Dataform is that our code is entirely accessible to our users. They can see exactly how we build our tables. The immediate corollary is that when they don’t understand something, they can ask us about it. Either we got something wrong and need to fix it, or something needed explaining. These exchanges always benefit both parties. A user recently asked us about the cost of our pre_operations incrementality logic. I think the full answer is worth sharing.

Hi Simon,

I look at your Dataform code on GitHub and I have some questions about the pre_operations incremental pattern. It seems that this approach may actually be causing a full scan of the target table during every incremental run.

As I understand it, in order to evaluate “What is the maximum session_date among all rows where is_final = TRUE?” BigQuery still has to inspect rows across all partitions, because is_final is not the partition key. In other words, although the result is just a single date used for partition pruning, the watermark calculation itself appears to require scanning the historical table. Is it correct?

/* incrementality */
pre_operations {
  DECLARE date_checkpoint DATE;
  SET @@query_label = "${helpers.executionLabels()}";
  SET date_checkpoint = (
    ${when(incremental(),
      `SELECT
        COALESCE(MAX(session_date)+1, DATE('${config.GA4_START_DATE}'))
      FROM ${self()}
      WHERE is_final = TRUE`,
      `SELECT DATE('${config.GA4_START_DATE}')`)} /* the default, when it's not incremental */
  );
  -- delete some older data, since this may be updated later by GA4
  ${
    when(incremental(),
      `DELETE FROM ${self()} WHERE session_date >= date_checkpoint`
    )
  }
}

Thank you!
Best Regards

This sharp question is a good reminder that what our incremental model actually costs you is genuinely not obvious, and worth walking through properly. Yes, the checkpoint query cannot prune partitions. It still costs almost nothing. To see why, we need to look at two things: how the delete-and-insert pattern works, and what BigQuery charges you for. Let’s start with the pattern.

The delete-and-insert pattern

Incremental loads: reading only what’s new

An incremental query reads only the new rows from a source table and appends them to the target. What counts as “new” is set by a reference point, usually a date. The query reads only the rows dated after that reference point. A run on Wednesday morning reads Tuesday’s rows from the source table, and only Tuesday’s, then appends them to the target table. Thursday morning reads Wednesday’s, and so on. This append logic has two consequences. The first is cost and performance. Each run processes one day instead of rebuilding the whole table every morning. It is faster and cheaper. The second is that those rows, once written, are meant to stay untouched.

Late updates, and how to catch them

That second point is causing a conflict with the way the BigQuery export works. Google can update exported rows for up to 72 hours after the fact, and sometimes longer. So Tuesday’s rows can change after we have already written them. To catch those updates, we add a delete step. Before we append new rows, we delete the previous days’ rows from the target table. Take a Wednesday run. It first deletes the rows the target holds for Sunday, Monday and Tuesday. Then it reads the source from Sunday onward and inserts those days again. The fresh rows replace the old ones. That is the delete-and-insert pattern.

What BigQuery charges for

For on-demand pricing, you pay for the data each query processes, at $6.25 per TiB, with the first 1 TiB each month free. Run SELECT * on an unpartitioned table and it scans the whole table. You are billed for all of it. Run SELECT session_date FROM x WHERE session_date = ‘2027-07-10’ on a table partitioned by date, and it scans only the session_date column, and only the partition for that date. You are billed only for what it scans. Within a column, billing uses a fixed logical size per data type. For example, a DATE type column is billed at 8 bytes per row, and a BOOL type column at 1 byte. The cost of reading those columns is simply the row count times nine. BigQuery bills you for the data a query scans, not for the rows it writes or removes. Appending rows costs nothing on its own. A delete is billed only for the scan it does to find the rows, not for removing them.

The checkpoint in GA4Dataform

In GA4Dataform the delete-and-insert pattern lives in a pre_operations block. It runs before the main query on every incremental build. Here is what it does, step by step:

SET date_checkpoint = (
  SELECT COALESCE(MAX(session_date) + 1, DATE('2020-01-01'))
  FROM ${self()}
  WHERE is_final = TRUE
);

DELETE FROM ${self()} WHERE session_date >= date_checkpoint;

1. Filter to final rows. WHERE is_final = TRUE keeps only the days that are complete. is_final is a stored column, computed from the configurable DATA_IS_FINAL_DAYS variable. It defaults to 3 days.
2. Take the maximum. MAX(session_date) runs over those filtered rows. The result is the last day known to be complete. That is not the same as the last day present in the table.
3. Add one day. The + 1 moves the checkpoint off that last complete day and onto the first day that is not complete. Everything from there forward gets rewritten. Without the offset, each run would delete and rebuild a day that was already complete.
4. Fall back if there is nothing to read. On the first build the table is empty, so MAX() returns NULL and so does the + 1. COALESCE catches that and substitutes the configured start date. The model then builds from the beginning.
5. Assign the checkpoint. The SET stores the resulting date in date_checkpoint. Everything above exists to produce this one value.
6. Delete from the checkpoint forward. DELETE FROM ... WHERE session_date >= date_checkpoint removes every row at or after that checkpoint date. Those are exactly the days that might still change. Clearing them lets the model write fresh values in their place.

The answer

So yes, every run reads every partition. is_final is not a partition key, so nothing prunes. However, the checkpoint subquery reads only two columns, session_date and is_final. One is a DATE, the other a BOOL. BigQuery counts a date as 8 bytes and a boolean as 1. That is 9 bytes per row, once per run. The DELETE costs nothing. BigQuery drops the selected partitions without scanning them, so the delete processes zero bytes. Nine bytes a row is the entire cost of the block. Let’s take a site with 100,000 sessions a day, building once every morning. After one day the table holds 100,000 rows. That is 100,000 × 9 bytes, so the run reads 900 KB. At $6.25 per TiB it costs $0.000005. After one year the table holds 36.5 million rows. That is 36.5 million × 9 bytes, so the run reads 328 MB and the day costs $0.002. The table was smaller for most of the year, so all 365 runs together come to $0.34. After three years the table holds 109.5 million rows. That is 109.5 million × 9 bytes, so the run reads 986 MB and the day costs $0.006. All three years together come to $3.07. A single full refresh of the model would cost more than three years of this pre_operations statement. Other incremental strategies would avoid scanning the table at all. So far, none of them look worth what we would give up in flexibility and simplicity.