Cut costly queries in reports: Use BigQuery's INFORMATION_SCHEMA.JOBS to track query run counts and slot time.; Filter partitioned tables with date conditions that enable partition pruning.; Reduce report frequency only if source cutoff and delivery times are still met.
Image: Business Insight Stack

Reporting Operations

Part of BI system performance and cost control

Reducing expensive queries in recurring reports

Find repeated report queries, reduce unnecessary reads and runs, and verify the result under the relevant billing model.

Desk Analytics · · 3 min read

Reduce a recurring report's total query work before concentrating on its slowest run. Find which queries recur, how often they run and what they process. Remove unnecessary reads or runs while preserving the approved figure and required update time.

Find repeated work

List the interactive queries, model refreshes, scheduled extracts and preparation jobs that support the report. Match them to source records by job or query ID, time, account and process where possible. Record run count, elapsed time, processed data or slot use, retries and the reporting period covered.

BigQuery's INFORMATION_SCHEMA.JOBS views provide job metadata; its query plan shows execution stages; top-level job statistics include slot time. In Snowflake, use Query History and its Duration column to inspect execution times; Query Profile can identify costly operators.

These records help locate work; they do not prove what a particular report costs. Apply the account's billing terms before assigning money to a query.

BigQuery vs Snowflake: Query Performance Monitoring Tools

  • BigQueryUse `INFORMATION_SCHEMA.JOBS` for job metadata; query plan for execution stages; slot time in top-level stats.
  • SnowflakeUse Query History (Duration column) and Query Profile to identify costly operators.

Key Metrics to Track for Report Optimisation

Run count
Number of times the query executes per period
Elapsed time
Total execution duration per run
Processed data
Bytes read from storage (critical for billing)
Slot time
Compute resources consumed (BigQuery only)

Remove work the answer does not need

  1. Columns:Select only the fields needed. In BigQuery, LIMIT on SELECT * does not by itself reduce the table bytes read.
  2. Periods:Restrict the query to the required dates. On a partitioned BigQuery table, the filter must qualify for partition pruning; partitioning alone is insufficient.
  3. Rows and joins:Check eligibility filters, join keys and row multiplication before aggregation. A faster result with missing records is not an improvement.
  4. Repeated calculations:Consider a maintained summary where several runs need the same result. Include its creation, refresh and storage work in the comparison.
  5. Frequency:Reduce a schedule only if the report still meets its agreed source cutoff and delivery time.

Interpret the effect under the relevant billing model. Lower billable bytes may reduce an on-demand query charge. Reserved capacity or a running warehouse needs a different calculation; faster execution need not reduce fixed spend. Compare workload and billing records for the same period before reporting savings.

Optimising Recurring Reports: Key Steps to Reduce Query Costs

  1. Select only required columnsAvoid `SELECT *` — limit fields to those needed for reporting. In BigQuery, `LIMIT` does not reduce bytes read.
  2. Filter by required date periodsUse partitioned tables with qualifying filters to enable partition pruning. Partitioning alone is insufficient.
  3. Minimise row and join overheadValidate join keys and eligibility filters to prevent row multiplication before aggregation.
  4. Cache repeated calculationsMaintain a summary table if multiple runs require the same result; include refresh and storage costs in analysis.
  5. Adjust schedule only if justifiedReduce frequency only if delivery time and source cutoff remain met.

Verify meaning and recurrence

Choose a closed reporting period. Keep the source snapshot, query version, parameters, access context and approved totals. Compare the revised result with the same population, date rule, headline figure, useful groups and contributing identifiers. Resolve differences before judging performance.

Then observe a representative recurrence cycle, including runs, retries, overlap, cutoff, latency and billable usage where available. A summary that speeds up reading but refreshes too often may move work elsewhere. Keep the earlier query and a reversal route until the revised path reliably produces the agreed answer.

Pre-Optimisation Verification Checklist

  • Use same source snapshotEnsure unchanged underlying data during comparison
  • Match query version and parametersKeep identical logic and inputs across runs
  • Verify approved totalsConfirm headline figures and groupings are consistent
  • Test recurrence cycleCheck runs, retries, overlap, latency and billable usage
  • Retain original queryKeep a reversal route until new process is stable

More from Reporting Operations

Reporting Operations

Analytical forecasting

Define a business forecast, compare methods with a simple benchmark, assess earlier predictions and communicate uncertainty before using the result.

Desk Analytics · · 4 min read