17.14. Creating Views in BigQuery

In this lesson, we'll explore how to save a query as a view in BigQuery and understand the differences between views and tables. Then, we'll see how to use these views in Looker Studio.

Saving a Query as a View

When working with queries in BigQuery, you have the option to save your query as a view instead of just saving the result or the query itself. A view is different from a table and will be represented by a different icon within your dataset.

To create a view, simply choose your data center project dataset and use the same name for both the query analysis and underlying table. After saving it, you'll notice that another entity with a distinct icon is added to your dataset.

Understanding Views

Clicking on the new view will show you its schema but not its preview. This indicates that it's not an ordinary dataset but rather a definition of data - essentially, it's that specific query running. In Looker Studio, you can connect to both tables and views.

To identify whether you're connecting to a view or table in Looker Studio, it's helpful to add "underlying table" or "underlying view" when naming them since there's no icon available for differentiation at this stage.

Using Views in Looker Studio

When adding your new view to Looker Studio, it will behave similarly to any other dataset. However, keep in mind that unlike tables, views cannot be previewed like extended tables; they must be queried.

If external tables are involved, BigQuery will request live data from sources such as Google Sheets when executing the view query. The result is live data calculated on-the-fly at the time of request.

In Looker Studio, if you duplicate your existing table and connect it to your new view dataset instead of using an actual table source, you should see the same information – with the exception that it's not accelerated by BigQuery. This is because there's no hard data stored in BigQuery for views, only a set of different definitions.

Comparing Views and Tables

The main difference between views and tables is that views provide live data, while tables do not. However, tables can be accelerated by BigQuery Engine, while views cannot. This results in faster execution times for tables compared to views.

If you wish to have live or near-live data with acceleration, one solution is to use scheduled queries in BigQuery. This feature allows you to balance the need for updated information with faster performance.

Enroll in Looker Studio Masterclass →

📩 Receive my weekly Looker Studio tips

🖇 Connect with me on LinkedIn