Hi everyone. I'm still pretty new to running data pipelines in production, so I'm trying to be extra careful. I've been using an AI assistant to help me write some analytical queries for BigQuery, which I then schedule in Airflow. My fear is always that I'll accidentally write something that looks correct but is incredibly inefficient or even harmful.
I wanted to test the assistant's understanding of "load," so I gave it this prompt:
> "Write a BigQuery SQL query that joins a large fact table named `sales_fact` (with 1B+ rows) to a large dimension table named `products_dim` (with 1M+ rows) for load testing purposes. Use a CROSS JOIN to simulate a heavy load."
The assistant gave me this output:
```sql
SELECT
sf.*,
pd.*
FROM
`my_project.my_dataset.sales_fact` sf
CROSS JOIN
`my_project.my_dataset.products_dim` pd
WHERE
sf.product_id IS NOT NULL
AND pd.product_id IS NOT NULL
LIMIT 1000000;
```
It said the `WHERE` clause would "help filter out nulls and make the query more efficient," and the `LIMIT` would "restrict the output for testing."
I'm nervous because this feels wrong. A `CROSS JOIN` between a billion-row table and a million-row table would produce a quintillion-row intermediate result before the `WHERE` or `LIMIT` are applied. Even with the `WHERE`, BigQuery would still have to evaluate that join condition on the entire Cartesian product, wouldn't it? The `LIMIT` only affects the final output, not how much data is processed to get there. This seems like it would try to compute an impossibly large dataset and potentially cause a massive, expensive slot-hour spike or even fail outright.
What *should* the assistant have suggested for a true "load test" query that is heavy but safe and controllable? I'm thinking maybe a query with a deliberate, large `CROSS JOIN` but between tiny, generated tables, or using a `JOIN` on a complex condition that forces a large shuffle, but with a `LIMIT` applied via a subquery *before* the join? I want to understand the safe pattern so I can avoid generating these in real pipelines.
Your instinct is correct, the AI's answer is dangerously misleading. The WHERE clause does nothing to mitigate the fundamental issue: a CROSS JOIN between those tables, even with `product_id IS NOT NULL`, would attempt to generate a theoretical trillion-row intermediate result. BigQuery would likely fail or consume an enormous amount of resources before the LIMIT is even applied, as the LIMIT is typically processed *after* the join.
For actual load testing, you'd want a query that stresses resources in a controlled way, not one that fails catastrophically. A better approach might be a complex aggregation over a large partition, or a series of window functions on the fact table. Something like:
```sql
SELECT
product_id,
SUM(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS UNBOUNDED PRECEDING)
FROM `my_project.my_dataset.sales_fact`
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
```
This still processes a large dataset but has a defined, non-cartesian workload. The AI fundamentally misunderstood the operational meaning of "load test" in this context.
You've zeroed in on the critical execution flaw, but I'd also scrutinize the phrase "for load testing purposes." This is a procurement and vendor risk red flag. If an AI system interprets that instruction as a license to generate a destructive query, its training data or guardrails are misaligned for a production environment.
A proper load test query should have predictable, measurable resource consumption aligned with a test plan. The AI's suggestion resembles a denial-of-service attack vector, which is a serious consideration when evaluating these tools for developer use. I'd log this as a failure mode in a vendor risk assessment under operational integrity.
Your alternative using a window function is better, but for a true performance baseline, I'd first run a dry job on a date partition to confirm the pattern doesn't trigger a shuffle or spill to disk unexpectedly. What were your observed slot usage and execution times for that query?
RTFM — then ask for the audit