Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 37 ยท Getting Started

Projects, Datasets and Tables in BigQuery

Understand how BigQuery organizes data into projects, datasets, tables, views and routines, how to name them, what a location means, and how to create a dataset and table with SQL.

Every object in BigQuery lives in a small hierarchy. If you understand the hierarchy, table names, permissions, billing and regions all stop being mysterious. This lesson covers the four levels you will meet every day, how to create your own, and the naming rules that trip up beginners.

The hierarchy

flowchart TD
  O["Organization (optional)"] --> P["Project: billing and permissions boundary"]
  P --> D1["Dataset: groups tables, has a location"]
  P --> D2["Dataset"]
  D1 --> T["Table"]
  D1 --> V["View"]
  D1 --> M["Model (BigQuery ML)"]
  D1 --> R["Routine: UDF or procedure"]

A table's full name is project, dataset and table joined by dots.

  • A project is a Google Cloud project. It owns the bill, the enabled APIs and the top level of access control. Queries are billed to the project that runs them, not the one that stores the data. This is why you can query a public dataset: the data sits in bigquery-public-data, but the work is billed to your project (within your free allowance).
  • A dataset is a folder for tables. It also sets the data location (such as US, EU or a single region like europe-west3) and default settings such as table expiration. Access can be granted per dataset.
  • A table holds rows and columns with a fixed schema.
  • A view is a saved query that behaves like a table. A materialized view also stores precomputed results.
  • Routines are user-defined functions and stored procedures; models belong to BigQuery ML.

Table names

A fully qualified table name has three parts:

SQL
SELECT * FROM `my-project.shop.orders` LIMIT 5;

Rules to remember:

Part Allowed characters Notes
Project ID Lowercase letters, digits, hyphens Globally unique, cannot change
Dataset Letters, digits, underscores Case-sensitive by default
Table Letters, digits, underscores, some Unicode, dashes Up to 1,024 characters

Because project IDs contain hyphens, the whole name is usually wrapped in backticks. Dataset and table names are case-sensitive unless the dataset was created with case-insensitivity enabled, so Orders and orders are different tables. Pick lowercase snake_case names and avoid the problem.

Creating a dataset and a table

You can click through the console, or use DDL (data definition language). This is BigQuery syntax, so it is read-only here:

SQL
CREATE SCHEMA IF NOT EXISTS `my-project.shop`
OPTIONS (
  location = 'EU',
  description = 'Demo online shop'
);

CREATE TABLE `my-project.shop.customers` (
  id         INT64 NOT NULL,
  email      STRING,
  country    STRING,
  signed_up  DATE
);

SCHEMA is BigQuery's word for what the console calls a dataset. Note the types: INT64, STRING and DATE (the full list is in Data Types).

You can also create a table from a query result, which is how most derived tables are built:

SQL
CREATE OR REPLACE TABLE `my-project.shop.top_customers` AS
SELECT customer_id, SUM(total) AS revenue
FROM `my-project.shop.orders`
GROUP BY customer_id;

The same pattern, CREATE TABLE AS SELECT (CTAS), works in SQLite, so we can practise it:

SQL Stand-in: create a table from a query ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Views

A view stores a query, not data. Every time someone selects from the view, BigQuery runs the stored query:

SQL Stand-in: a view ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In BigQuery you write CREATE VIEW `my-project.shop.expensive_products` AS .... Views save typing and let you hide columns, but they do not save money: querying a view scans whatever its underlying query scans. A materialized view (CREATE MATERIALIZED VIEW) stores results and is refreshed automatically, so it can reduce both time and bytes for repeated aggregations.

Table types you will meet

Type What it is
Standard (native) table Data stored in BigQuery's own columnar storage
External table Data stays in Cloud Storage, Drive or Bigtable; BigQuery reads it on demand
View / materialized view A saved query / a saved and cached query result
Snapshot A read-only copy of a table at a point in time
Clone A writable, space-efficient copy of a table
Wildcard tables Many tables with a shared prefix queried as one, such as events_*

Table expiration and housekeeping

Datasets and tables can carry an expiration. After it passes, BigQuery deletes the object. The sandbox sets 60 days by default. In a normal project you decide. Expiration is excellent for scratch tables and a quiet source of "where did my table go?" surprises.

Labels (key-value tags) help with cost reports, and a table description is read by every future analyst: write one.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which project is billed when you query a public dataset?

    Query cost belongs to the project that runs the job. Public data storage is paid by Google.
  2. 2. What does a dataset define besides grouping tables?

    Location is set per dataset and cannot be changed later. A query cannot join tables from different locations.
  3. 3. Does querying a plain view scan less data than its underlying query?

    A normal view stores SQL, not data. Only materialized views store precomputed results.
  4. 4. Why use lowercase snake_case for dataset and table names?

    Consistent lowercase names prevent hard-to-spot errors.

Summary

  • The hierarchy is project, dataset, table (plus views, routines and models).
  • Projects hold billing and permissions; datasets hold location and defaults.
  • Reference tables as `project.dataset.table`; names can be case-sensitive.
  • Create objects with CREATE SCHEMA, CREATE TABLE and CTAS.
  • Views save SQL, not bytes; materialized views store results.

Next: BigQuery Architecture: Dremel, Colossus and Slots