How to Structure dbt Models for High-Volume Ad Logs

Your programmatic ad logs generate millions of rows daily, but BigQuery is billing you for terabytes every time an incremental update runs. Scanning a multi-terabyte log table just to process a few megabytes of new click data destroys your reporting margins before the campaign even finishes.

To minimize compute costs on high-volume logs, you must pair daily date partitioning with clustering on high-frequency keys, switch your dbt strategy from a standard merge to static `insert_overwrite`, and hardcode `require_partition_filter` to true. This stops full-table scans and restricts processing strictly to the active partitions.

Why do incremental merges cost so much?

BigQuery charges for on-demand compute based on the total bytes scanned during a query. The default dbt behavior for an incremental model uses a native `MERGE` statement keyed to a unique identifier. Even if your model filters the source data to only select yesterday's logs, the merge operation forces BigQuery to scan the entire destination table to find matching rows for updates.

If you maintain a two-terabyte event table, an hourly incremental merge scans all two terabytes every single run. The compute cost of finding a few thousand new ad clicks quickly exceeds the value of the data itself.

You can attempt to patch this using the `incremental_predicates` configuration, which forces BigQuery to apply a partition filter on the destination table during the merge condition. It successfully reduces scanned data, but it introduces a severe data integrity trade-off. If an ad platform sends late-arriving conversion data for a record that falls outside your predicate window, the merge will fail to match the existing row. Instead of updating, it inserts an entirely new row and creates duplicate records. For marketing attribution models requiring absolute precision, this optimization creates more problems than it solves.

Which incremental strategy fits ad logs?

Since programmatic ad logs are typically append-only event streams or date-bounded aggregations, you should abandon row-by-row merges entirely. The most efficient approach for this data is the `insert_overwrite` strategy.

Instead of matching individual rows, this strategy instructs BigQuery to drop and replace entire partitions as atomic units. When dbt compiles the model, it executes a `MERGE INTO` statement with a constant false predicate. The query optimizer recognizes this condition, skips the table-wide join entirely, deletes the targeted partitions, and inserts the new source records. The scan volume is completely restricted to the partitions undergoing replacement.

You can configure this overwrite mechanism statically or dynamically. A static configuration, where you explicitly supply the specific partition dates to replace, is consistently the cheapest and fastest option. Dynamic `insert_overwrite` executes additional queries to calculate the maximum partition timestamp. This extra overhead makes it inefficient for standard daily updates, and it only becomes cost-effective if your incremental runs process hundreds of gigabytes at once.

If you process immense log volumes and only need to update a single partition at a time, you can bypass the standard dbt merge completely. Using a `WRITE_TRUNCATE` configuration with a partition decorator writes data directly to the destination. One production benchmark found that replacing a standard merge with a write-truncate operation cut a 43-minute query down to 26 minutes for the exact same output. However, this approach breaks atomicity if applied across multiple partitions, so it is strictly for single-partition workflows.

How should you partition and cluster?

Changing your incremental logic only works if the underlying physical table is structured correctly. For time-series ad data, you must define a `partition_by` dictionary in your model configuration, setting the data type to date or timestamp and keeping the default daily granularity.

Partitioning by date alone drops query costs significantly by pruning irrelevant days, but BigQuery still has to scan every row within the active date. If an analyst queries a 30-day window on a 3.2-terabyte table, partitioning reduces the scan to roughly 500 gigabytes. To optimize the rest, you must pair your date partitioning with clustering on high-frequency filter columns, such as campaign IDs or customer IDs.

Clustering sorts the rows inside each partition into contiguous storage blocks. Because real-world advertising data is highly skewed — a few high-spend campaigns generate the vast majority of your events — clustering allows the database engine to skip past irrelevant blocks within the active day. Combining daily partitioning with client clustering can reduce a 3.2-terabyte full-table scan down to roughly 12 gigabytes. Managing this physical architecture is a foundational step in The AdTech Data Engineering Guide to Managing Scale and Cost.

How do you stop accidental full scans?

The most perfectly engineered dbt model will still rack up massive charges if a downstream analyst queries the presentation layer without a date filter. By default, BigQuery tables allow these unguarded queries to run.

To prevent this, you must configure the dbt model with `require_partition_filter = true`. Once compiled and executed, this boolean setting alters the table options so that BigQuery immediately rejects any query lacking a partition predicate. It throws a validation error rather than billing you for a full-table scan.

Before enforcing this rule in production, audit your existing query history. Enabling the guard will instantly break any legacy business intelligence dashboards or downstream dbt tests that do not currently filter by date.

This strict physical guardrail is exactly why SproutMe Companion can generate cross-channel performance reports on demand without risking your warehouse budget. Because the underlying data lake enforces mandatory partition filters, the agents extract insights efficiently, querying only the necessary time windows rather than scanning years of historical logs.

Finally, apply the `partition_expiration_days` configuration to manage long-term storage automatically. Raw ad logs rarely need to be queried indefinitely. Setting this limit instructs BigQuery to drop partitions older than your specified window, zeroing out their storage costs without requiring any manual cleanup macros.

Conclusion

Managing high-volume programmatic ad logs requires strict control over how data is read and written. By switching your incremental strategy to static `insert_overwrite`, pairing daily partitions with clustering on high-frequency identifiers, and explicitly requiring partition filters, you stop BigQuery from scanning historical data unnecessarily. This structure keeps compute costs predictable and protects your margins as campaign volume scales. To stop wrestling with warehouse architecture and start deploying agents that optimize campaigns from your grounded data, contact our team.