17.15. Scheduled Queries in BigQuery

In the previous lesson, we explained creating views in BigQuery and its pros and cons. In this lesson, we'll explore how to schedule queries in BigQuery to fetch near-real-time data and cache it for faster access in Looker Studio.

Scheduling Queries

Scheduled queries allow us to run a query at preset intervals, such as every hour, 30 minutes, daily or weekly, and save the result in a native table. This way, we can cache the results of our views and make them available for faster access with the BI engine.

Enabling Scheduled Queries

If you're using scheduled queries for the first time in your project or account, you need to enable them by clicking Enable API. This is a one-time setup that takes only a few seconds.

Creating a New Scheduled Query

To create a new scheduled query:

1. Start from the BigQuery screen with your desired query already written.
2. Click on Schedule Query.
3. Give your scheduled query a name (e.g., "query_analysis") and add "_sq" to identify it as a scheduled query.
4. Choose how often you want the query to run (daily, hourly etc.) and set start and end dates if needed.
5. Set a destination table for your query results by selecting the dataset (e.g., "query_analysis") and table ID (using the same name as your scheduled query).

Configuring Table Settings

When configuring your destination table settings:

• Decide whether you want each run's results appended to existing data or if you want to override existing data with new results.
• Choose where you want your data stored; however, this is optional.

Save your settings and wait for the first run of your scheduled query.

Connecting Looker Studio to Your Scheduled Query

Once your scheduled query has started running:

1. Go back to Looker Studio's edit mode.
2. Edit your data source and rename it to include "Scheduled Query."
3. Edit the connection and switch from connecting to the table to connecting to your scheduled query.
4. Reconnect and refresh your data.

Now, you should see that your Looker Studio report is fetching data from the scheduled query, which is accelerated by the BI engine and cached on an hourly basis. This provides a faster, more efficient way of accessing near-real-time data in Looker Studio.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn