There is a particular kind of frustration that comes from staring at a query you know is correct and watching it fail. No syntax error. No logic problem. Just a wall.
We hit two of them on the same project.
What we were building
The job was to migrate ga4_daily_snapshot for a large enterprise client from a BigQuery scheduled query into a proper Dataform pipeline. The scheduled query had been added to over time until it was too large to maintain with any confidence. Moving it to Dataform would give it version control, proper documentation, modular structure and a dependency graph that a future engineer could actually understand.
The pipeline takes preprocessed GA4 event data from a cleaned_events source table and produces one row per session per day. Each row carries session dimensions, attribution data and around 122 event-type columns, each an array of everything that happened for that event type within the session. It is a denormalised snapshot designed to be queried quickly for reporting, without touching the raw event tables each time.
122 event types is a lot. That number is what caused both problems.
The first wall: BigQuery's query complexity limit
The natural first architecture was to write one query that pulled all 122 event types inline, with each event as a subquery. The SQL logic was sound. Each subquery tested fine individually. The final query looked like it should work.
BigQuery refused to run it.
The error was not immediately helpful. It pointed at resource limits and query complexity rather than any specific line of code, which meant the first stretch of investigation was spent looking for problems that were not there. Eventually it became clear that BigQuery places a hard limit on how complex a single query can be, independent of data size, memory or time. Too many nested subqueries in one go and BigQuery will not execute it, regardless of whether the logic is correct.
The fix was to stop trying to do everything in one query. Each event type became its own Dataform table, materialised separately before the final snapshot needed it. The snapshot then joined against 122 pre-built tables rather than calculating 122 things inline. BigQuery was fine with that. Each individual join was simple, even though there were many of them.
This also had an unplanned benefit: each event table could now be tested independently, which made debugging far more targeted than it would have been with one enormous query.
With 122 separate event tables now sitting in the DAG, the final snapshot needed to declare a dependency on all of them. Dataform has a hard limit of 50 dependencies per action.
We were 72 over it.
The workaround is documented but not prominently. The answer is intermediate bundle tables. We introduced three: int_arr_bundle_a, int_arr_bundle_b and int_arr_bundle_c. Each joins roughly 40 of the event array tables. The final snapshot then joins the three bundles rather than 122 individual tables, leaving it with five total dependencies including the session base table. Below the limit, the pipeline compiles cleanly.
The bundles are not arbitrary. They group related event types together, which also makes the pipeline easier to navigate when something needs debugging. More importantly, the codebase documents why this layer exists. Without that note a future engineer would reasonably look at the intermediate bundle step and consider removing it as unnecessary indirection. The answer, which needs to survive beyond the people who built it, is that Dataform would not compile the pipeline without it.
Handling 122 event types without writing 122 files
Managing individual SQLX files for each standard event by hand was not realistic at that scale. A JavaScript loop engine handles it instead. A file called standard_event_arrays.js reads from a centralised event config and generates one table per standard event from a shared template. Only the handful of events that need non-standard logic, such as page views which require custom deduplication, get their own hand-written SQLX files.
Adding a new standard event means adding one line to the config. Changing the shared extraction logic means updating one template rather than touching dozens of files.
The architecture that came out of it
Staging sources feed into intermediate session models, which feed into 122 event array tables, which feed into three bundle tables, which feed into the final snapshot. Each layer has a clear job. The event logic is independently testable. The dependency graph is visible and small enough to reason about.
None of that was the plan going in. The original plan was one large query that calculated everything in a single pass. It would have worked if BigQuery had allowed it, and it would have been harder to debug, harder to extend and harder to hand over when the time came.
Both limits were frustrating to hit. Neither of them is prominently documented. And working around them produced a cleaner pipeline than the one we set out to build.
