First of all, you should look into sharding, clustering, or partitioning the data. All of these techniques can reduce query costs.
Sharding splits data across multiple tables. Typically, you split by some large grouping value and put that in the table name. So “sales” becomes “sales_midwest”, “sales_northeast”, etc. Then you either make your query smart enough to pick the right shard or use wildcard tables and the _table_suffix pseudo column.
Clustering is similar indices on a traditional relational database. You specify columns that are likely to be useful in queries.
Partitioning specifies either an integer or date column. Behind the scenes, BigQuery stores each partition in a different table. If the where clause references the partition column, then BigQuery only scans the relevant partitions. That means you only get billed for those partitions.
Depending on the nature of your data, one or more of these techniques could help.
Otherwise, BigQuery lets you save the results of one query into a destination table. You just set the destination table using whichever SDK or API you are using.
You can also set the destination table to automatically expire after a set amount of time. That requires a separate API call to BigQuery before you submit the actual query job. This step would add noticeable lag to an interactive system. Alternatively, you can add a background process to clean up the result tables.
All of this adds considerable complexity. Before implementing any of it, you should estimate how many queries you actually expect. Compute the cost of running the full table scan on every one of those. I’d expect the number will either be so low that you can confidently ignore the costs or high enough that you can have a meaningful conversation comparing development cost to BigQuery cost.