Table types in BigQuery — Explained

In BigQuery, tables are structured datasets that store your data. There are mainly three types of tables: Standard BigQuery tables, External, and Views.

• Standard BigQuery tables: structured data stored in BigQuery storage.
• External tables: tables that reference data stored outside BigQuery.
• Views: logical tables that are created by using a SQL query.

Standard BigQuery tables

These contain structured data and are stored in BigQuery storage in a columnar format. These include the following types:

• Tables, which have a schema and every column in the schema has a data type.
• Table clones, which are lightweight, writeable copies of BigQuery tables. BigQuery only stores the delta between a table clone and its base table.

example:

CREATE TABLE
myproject.myDataset_backup.myTableClone
CLONE myproject.myDataset.myTable;

• Table snapshots, which are point-in-time copies of tables. They are read-only, but you can restore a table from a table snapshot. BigQuery stores bytes that are different between a snapshot and its base table, so a table snapshot typically uses less storage than a full copy of the table.

example:

CREATE SNAPSHOT TABLE SNAPSHOT_PROJECT_ID.SNAPSHOT_DATASET_NAME.SNAPSHOT_NAME
CLONE TABLE_PROJECT_ID.TABLE_DATASET_NAME.TABLE_NAME
  OPTIONS (
    expiration_timestamp = TIMESTAMP 'TIMESTAMP_VALUE');

External Tables

These tables reference data stored outside of BigQuery, like in Google Cloud Storage (GCS) or Google Drive. They allow querying the data without having to load it into BigQuery, which can be beneficial for analyzing data that’s constantly changing or resides in other storage systems. Use cases include querying data in CSV, JSON, Parquet, or other formats stored in GCS without the need for importing it into BigQuery.

External tables include the following types:

• BigLake tables, which reference structured data stored in data stores such as Cloud Storage, Amazon Simple Storage Service (Amazon S3), and Azure Blob Storage. These tables let you enforce fine-grained security at the table level.

For information about how to create BigLake tables, see the following topics

• Cloud Storage
• Amazon S3
• Blob Storage
• Object tables, which reference unstructured data stored in data stores such as Cloud Storage.

For information about how to create object tables, see Create object tables.

• Non-BigLake external tables, which reference structured data stored in data stores such as Cloud Storage, Google Drive, and Cloud Bigtable. Unlike BigLake tables, these tables don’t let you enforce fine-grained security at the table level.

Views

Views are virtual tables defined by a SQL query. They don’t store data themselves but present the result of the query whenever they’re referenced. Views can simplify complex queries, abstract underlying data structures, and restrict access by showing only specific columns or rows. Use cases involve creating simplified or aggregated representations of underlying data for reporting or analysis purposes without altering the original dataset.

Types of views:

• Views, which are logical tables that are defined by using SQL queries. These queries define the view that is run each time the view is queried.

example:

CREATE VIEW mydataset.usa_male_names(name, number) AS (
  SELECT
    name,
    number
  FROM
    bigquery-public-data.usa_names.usa_1910_current
  WHERE
    gender = 'M'
  ORDER BY
    number DESC
);

• Materialized views, which are precomputed views that periodically cache the results of the view query. The cached results are stored in BigQuery storage.

example:

CREATE MATERIALIZED VIEW PROJECT_ID.DATASET.MATERIALIZED_VIEW_NAME AS (
  QUERY_EXPRESSION
);

Query expression sample: SELECT product_id, SUM(clicks) AS sum_clicks FROM mydataset.my_source_table

Other table types

Partitioned Tables: They are divided into segments or partitions based on a specific column’s value (e.g., date or timestamp). Each partition acts as a separate subset of the table, making it easier to manage and query large datasets efficiently. Use cases include time-series data like daily logs, where partitioning by date allows faster retrieval of specific time ranges.

Clustered Tables: These tables store data physically ordered based on the values of one or more columns, known as clustering keys. Clustering helps in optimizing query performance, especially for large tables, by reducing the amount of data scanned for queries involving those columns. Use cases involve frequently queried columns or columns used for filtering data.

If you think anything is missed, please do comment.