ZapBounce and BigQuery
BigQuery charges by bytes scanned, which changes the design of a recurring verification job more than the verification cost does.
A query that selects every address from a large table each night is the expensive part, and it is trivially avoidable.
A full table scan each run costs more than the verification
Selecting an email column from a hundred-million-row table scans the whole column every time. Run that nightly and the query cost dwarfs the credits.
Partitioning on the check date, or keeping verdicts in a separate table joined on a key, means each run reads only the rows that need checking.
The second design problem is storing a verdict without a timestamp. A column called email_status with no check date becomes a claim that quietly expires and gets quoted in dashboards as current.
How the data moves
Keep verdicts in their own table
Address, verdict, check date, and the batch's coverage. Join it rather than adding columns to a wide fact table.
Partition the verdict table by check date
So the query selecting stale rows reads one partition rather than the whole history.
Export candidates, verify, load results
A scheduled query writes candidates to a staging table, a job submits them as a batch, and the results load back. Nothing streams row by row.
Load results with a load job, not streaming inserts
Loading a results file is free; streaming inserts are not, and the volume here suits a load job.
Setting it up
- Create a verdict table with address, verdict, check date and coverage columns, partitioned by check date.
- Write a scheduled query selecting addresses with no verdict or a stale one.
- Submit the results as a batch from a Cloud Function or a scheduled job.
- Load the completed results back with a load job rather than streaming inserts.
- Join the verdict table into downstream views rather than copying the column.
- Set a staleness threshold in the view, so consumers cannot read a two-year-old verdict as current.
BigQuery: common questions
Why is my job so expensive?
Almost certainly a full column scan each run. Partition the verdict table and select only stale or new rows.
Should the verdict be a column on the main table?
A separate table joined on the address. Adding a column to a wide fact table means rewriting it to update a verdict.
How do I stop stale verdicts being quoted?
Put the staleness rule in the view, not in a convention. A rule nobody can run is a note.
Check a BigQuery export today
100 free checks a month, no card. Unknown results and duplicates are never billed.