Understanding BigQuery Table Metadata: Why Empty Type 2 Tables Can Still Return Results

Описание: Discover why querying empty Type 2 tables like `inputs` in `BigQuery` can still yield results. Learn about views and how they function within datasets.
---
This video is based on the question https://stackoverflow.com/q/70493089/ asked by the user 'sci9' ( https://stackoverflow.com/u/3436831/ ) and on the answer https://stackoverflow.com/a/70494948/ provided by the user 'Daniel Zagales' ( https://stackoverflow.com/u/14400998/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Bigquery tables meta data: type 2 tables are empty but queryable

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding BigQuery Table Metadata: Why Empty Type 2 Tables Can Still Return Results

If you've ever dived into Google BigQuery, you may have been puzzled by the behavior of certain tables when running queries. One particular case involves Type 2 tables, which are often found in complex datasets like crypto_bitcoin. You might notice that a query on an ostensibly empty table, such as inputs, actually returns specific results. This raises a question: why does this happen? Let’s explore the issue further.

The Background of BigQuery Tables

BigQuery is a cloud data warehouse that enables fast SQL queries and interactive analysis of large datasets. Tables in BigQuery can be of different types, including:

Standard Tables: These store data in rows and columns.

Views: These are virtual tables that don't store data themselves but are defined by a SQL query that pulls data from one or more base tables.

In the context of the crypto_bitcoin dataset, both inputs and outputs are classified as Type 2 tables, which generally indicates they are views. Let’s further investigate how that affects your queries.

Why Are Type 2 Tables Empty but Queryable?

Metadata Breakdown

When examining the metadata of the crypto_bitcoin dataset, we find the following relevant details for the inputs and outputs tables:

inputs:

Row Count: 0

Size Bytes: 0

outputs:

Row Count: 0

Size Bytes: 0

This metadata confirms that both tables do not store any data. Instead, they are essentially windows into the underlying transactional data without holding any information themselves.

The Role of Views

Because inputs and outputs are views, they do not retain any of their own data. Instead, they reference data from source tables, particularly from the main transactions dataset. Here’s how it works:

When you execute a query on inputs, BigQuery retrieves the necessary data from the underlying transactions table.

Hence, even if inputs appears empty, it can pull relevant data from transactions, which is why the query can return valuable results, as shown below:

[[See Video to Reveal this Text or Code Snippet]]

The results return actual values, demonstrating that there’s underlying data connected directly through the transactions table.

Conclusion

In summary, the reason why querying empty Type 2 tables like inputs and outputs in BigQuery can yield results lies in their nature as views. Instead of containing data directly, they refer to the richer datasets they are derived from, making them powerful tools for data analysis. Understanding this concept enhances your ability to work effectively within BigQuery and interpret the results you obtain from your queries.

By keeping in mind the distinction between tables and views, you can navigate BigQuery more adeptly and leverage its capabilities to gain insights from vast datasets, even when working with seemingly empty tables.