BigQuery and CodeWords: getting answers out of the warehouse
The warehouse holds the answer and nobody reads it. Scheduled queries, alerting on what changed, writing results back to the tools people use, and keeping query cost under control.
Aymeric Zhuo11 min read
On this page
Most organizations with a warehouse have the same problem: the answer to the question somebody just asked has been sitting in BigQuery for three months, and nobody looked because looking means writing SQL and opening a console.
The useful automation around a warehouse is rarely about loading data into it. It is about getting answers back out, into the places people actually work, on a schedule nobody has to remember.
What we'll cover
- The gap between the warehouse and the decision
- What the BigQuery API reaches
- Connecting it to CodeWords
- Eight automations worth building
- Keeping query cost under control
- Building it so it survives
- Limits worth knowing about
- Frequently asked questions
The gap between the warehouse and the decision
Four things stand between a warehouse and somebody acting on what it knows.
Someone has to ask. Dashboards answer standing questions. The interesting questions are the ones nobody thought to put on a dashboard, and they only get asked when somebody already suspects the answer.
The answer arrives in the wrong place. A number in a console is not a number in the meeting, and the gap between them is a person remembering to look and then copying it somewhere.
Nothing pushes. A warehouse does not tell you that a metric moved. It waits to be asked, which means a problem visible in the data on Tuesday can be discovered on the following Monday.
Getting data back out to operational systems is awkward. The warehouse knows which accounts are at risk; the people who act on that work in a CRM, and the two do not talk without somebody building the bridge.
Automation closes all four, and the last two are where the value concentrates.
What the BigQuery API reaches
Query jobs run SQL and return results, synchronously for small results and as jobs for large ones. This is the core of everything here.
Datasets and tables can be listed, created, and described, which lets an automation write results into a table rather than only returning them.
Table data can be inserted directly through the streaming interface for row-by-row writes, or loaded in bulk from storage.
Job metadata reports bytes processed, which is what makes cost monitoring possible, since cost on the on-demand model is a function of bytes scanned.
Scheduled queries exist natively in BigQuery and are worth using for pure SQL-to-table work. The reason to reach outside is when the result needs to go somewhere that is not another table.
The dividing line worth drawing: BigQuery's own scheduled queries are good at moving data within the warehouse. Everything about getting an answer to a person or another system is outside that boundary.
Connecting it to CodeWords
CodeWords connects to more than 3,000 integrations, and the connection is made once and reused.
- Open CodeWords and start a new automation.
- Describe what you want in plain language to Cody, the automation builder: the question, where the answer should go, and when it should run.
- Authorize the connection. BigQuery authenticates through a Google Cloud service account, and the account should be granted only the dataset access the automation needs.
- Describe the exceptions: an empty result, a query that fails, a figure outside a plausible range.
- Run it against a known period where you already know the answer, and compare.
You describe the outcome; Cody builds it, connects it, and deploys it. When the question changes you describe the change. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.
Eight automations worth building
The morning number. One query, one figure, delivered to a channel before the meeting. Unglamorous and the most reliably used automation any data team ships, because it removes the person who used to run it by hand at eight.
Threshold alerting. A query that returns rows only when something is wrong — orders below a floor, error rates above a ceiling, a pipeline that has not written since yesterday — and stays silent otherwise. Silence is the point, which makes the next pattern essential.
Heartbeat confirmation. A brief daily line confirming the checks ran and found nothing. Without it, a broken alerting automation and a quiet week look identical.
Anomaly detection against a baseline. Compare against the same hour of the same weekday over recent weeks rather than a flat threshold, so Monday mornings stop generating false alarms and quiet Sunday problems stop being missed.
Reverse ETL to operational systems. Push a computed segment back into the CRM, the email platform, or the support tool, so the people who act on it see it where they work. This is where a warehouse stops being a reporting asset and starts being operational.
Freshness monitoring. Check that each table you depend on has been updated recently, and alert on the absence of data rather than on bad data. Pipelines fail silently far more often than they fail loudly.
Cost reporting. Query the job metadata to report what was spent, by user and by query. On-demand pricing charges for bytes scanned, and a single careless query can cost more than a month of the rest.
Scheduled extracts with judgement. Run the query, summarise what changed in plain language alongside the figures, and send both. The numbers are the record; the summary is what gets read.
Keeping query cost under control
Worth its own section, because automation multiplies whatever your query habits are.
Cost follows bytes scanned, not rows returned. A query returning one number can scan a terabyte. SELECT COUNT(*) over a wide table is expensive for a result that looks trivial.
Partition and cluster the tables automations query. A date-partitioned table queried with a date filter scans a fraction of what an unpartitioned one does, and this single change is usually the largest saving available.
Never write SELECT * in an automation. It scans every column. Naming the five you need is the difference between cents and pounds, on every run, forever.
Materialize what you query repeatedly. If six automations all compute the same aggregate, compute it once into a table and have them read that.
Set a maximum bytes billed on automated queries. A query that would scan more than expected fails rather than running, which turns a surprise invoice into an error message.
Watch the frequency. An hourly automation is 720 runs a month. The same query run daily is 30. Ask honestly how often the answer actually changes.
Building it so it survives
Validate the result before acting on it. A query returning zero rows might mean nothing is wrong, or might mean the pipeline did not run. Those are opposite situations and they look identical to an automation that only checks for problems.
Sanity check the magnitude. A figure outside a plausible range should stop and flag rather than send. Reporting revenue as zero because a join broke is worse than reporting nothing.
Use a service account with least privilege. Read-only access to the specific datasets involved. An automation with broad project access is a credential worth more than the automation.
Log the query and the bytes scanned. When somebody questions a figure, you want the exact SQL that produced it, and when the bill arrives you want to know which automation was responsible.
Keep the SQL where people can read it. Query logic buried inside an automation nobody opens becomes untrustworthy the first time someone asks how a number was calculated.
Limits worth knowing about
Query timeouts and result size apply. Large results should be written to a destination table rather than returned inline.
Streaming inserts have their own quotas and a buffer period during which rows are not immediately available to some operations, which surprises people writing then immediately reading.
Concurrent query limits apply per project, so an automation firing many queries at once can queue behind itself.
On-demand and capacity pricing behave differently. On-demand charges per byte scanned, so the optimizations above matter directly. On reserved capacity the cost is fixed and the constraint becomes slot contention, which changes what you should optimize.
Data residency is a project and dataset setting. An automation moving results out of the warehouse is moving data across whatever boundary you set, which is worth checking before it becomes a habit.
Frequently asked questions
Why not use BigQuery's scheduled queries?
For SQL that writes to another table, use them — they are native, free of extra infrastructure, and well suited to that job. The reason to reach outside is that a scheduled query cannot send a message, update a CRM, apply judgement to the result, or do anything at all when the result is empty.
How do I stop an automation running up a large bill?
Set a maximum bytes billed on the query so it fails rather than scanning more than expected, partition the tables it reads, name your columns instead of selecting everything, and report cost per automation so you can see which one is expensive. All four are cheap to do and each catches a different failure.
Can an automation write back to BigQuery?
Yes, by streaming rows or by loading in bulk. A common and useful pattern is writing the automation's own outputs back into a table, which gives you a history of what it computed independently of the source data, and makes questions about last quarter answerable without re-running anything.
What service account permissions does it need?
The minimum that works: job user on the project to run queries, and data viewer on the specific datasets involved. Add write access only where the automation genuinely writes, and to the specific dataset rather than the project.
How do I handle a query that sometimes returns nothing?
Decide explicitly what empty means for that query, because it is usually ambiguous. An alerting query returning nothing is good news; an extract returning nothing probably means something upstream failed. Encode which one it is rather than letting the automation guess.
Can it summarise results in plain language?
Yes, and the useful arrangement is to send both the figures and the summary. The numbers are the record and the summary is what people actually read. Keep the computation deterministic and let the model describe rather than calculate, so the figures are always right even when the wording varies.
How fresh is the data an automation reads?
As fresh as whatever loads it, which is the thing to verify rather than assume. Freshness monitoring is worth building before anything that depends on the data being current, because an automation reading a stale table will confidently report yesterday's world as though it were today's.
Should the automation query raw tables or a prepared view?
A prepared view, almost always. It gives you one place to change the logic, it keeps the business definition visible to people who are not reading automation code, and it lets you partition and cluster underneath without touching anything downstream. Automations querying raw tables directly tend to embed assumptions that then exist in six places.
How do I test a query automation safely?
Run it against a period where you already know the answer and compare. Then run it alongside whatever currently produces that number, for a couple of cycles, before anyone relies on it. Discrepancies in that window are usually genuine discoveries about the data rather than bugs in the query.
What to build first
Start with the morning number. One query, one figure, delivered before the meeting. It is unglamorous, it retires a recurring manual task immediately, and it teaches you the connection and the permissions on something low-risk.
Two things make the difference between a first automation that sticks and one that gets abandoned. Run it alongside whatever happens now for a cycle or two and compare the outputs, since the differences are nearly always genuine discoveries rather than bugs. And have it report that it ran even when it found nothing, because an automation whose only output is silence is one nobody can distinguish from a broken one.