17.8. Writing SQL in BigQuery

In this lesson, we will explore writing SQL in BigQuery and understand the differences between native and external tables when working with Looker Studio.

Split Tab View in BigQuery

When working with BigQuery, you can use the split tab view to see the schema of a table or details of a table as you write queries on the side. This allows you to work with different queries side by side and see how they interact.

For example, we can select everything from a table within our project:

SELECT * FROM `project_name.dataset_name.table_name`

Comparing Native and External Tables

To demonstrate the differences between external and native tables, we will run simple queries on both types of tables. One table is from Google Search Console (external), while the other is from Google Analytics (native).

After running both queries, we can compare their execution details to see how long it took for BigQuery to perform each calculation. The native table query is significantly faster than the external one.

Native Table

• Data is stored within BigQuery

• Faster query execution

• Static data (not live)

External Table

• Data accessed through an API (e.g., Google Sheets)

• Slower query execution due to API requests

• Live data that can change

Working with Live Data

With external tables like Google Search Console data, changes made in the source (e.g., Google Sheets) are reflected immediately when running queries in BigQuery. This makes it ideal for scenarios where live data updates are crucial.

However, if you change the name or range of your source data sheet, it might cause errors when querying that table in BigQuery because it relies on specific references.

In Practice: Data Modeling with Native and External Tables

Now that we have an understanding of native and external tables' key differences, we can begin working with our Google Search Console and Google Analytics data to perform more advanced data modeling in BigQuery.