Subsection 3.1

Datasets vs tables

~4 min

Reading

Before you write a query, make sure you know whether you are looking at a dataset or a table.

Try this analogy

Think of a university's records system

Rosario's deck uses this one: imagine a university maintains a dataset called UniversityData. Inside that dataset, you'd find separate tables : Students, Courses, Enrollments.

The dataset is the institution's records system as a whole. The tables are the specific lists inside it. You query a table when you want actual records: who's enrolled, which courses run on Tuesday, which students are missing a grade. You don't query a dataset directly : you query the table inside the dataset.

If you ever lose the thread in BigQuery, ask yourself: am I clicked on the drawer, or am I clicked on the folder inside the drawer?

A dataset is a container. It groups related tables, views, and other data objects. It can also carry shared permissions, which means access may be managed at the dataset level.

A table is the data object you actually read from. It contains rows and columns. It has a schema. A table can be queried directly.

In BigQuery, learners often click around the interface and lose track of where they are. Use the hierarchy to stay grounded:

Google Project → Dataset → Table or View

The table is the thing you query. The dataset is the folder that holds it.

You may also see materialized views. A materialized view is a view-like object that stores precomputed results. For this course, treat views and materialized views as data objects you may read from, but focus first on ordinary tables.

Learner action

In BigQuery, click your selected dataset. Then click your selected table. Write one sentence explaining the difference between those two objects in your own words.

In Module 2 you picked a project.dataset.table path. Before you ever write SQL, it's worth knowing exactly what a dataset is and what a table is : they look similar in the Explorer panel but they do very different jobs.

From the SQL 101 deck : the university analogy

"Imagine you're managing data for a university. You could have a dataset named UniversityData. Within UniversityData, you might have several tables: Students (holding records of all students), Courses (listing all courses offered), and Enrollments (documenting which students are enrolled in which courses)."

So in plain English:

A dataset is a folder. It groups related tables together and controls who has access to them. In BigQuery, datasets are equivalent to "schemas" in databases like Redshift or Postgres.
A table is the actual rows and columns : the records you'll query. Each column has a name and a type.
A view is a saved query that behaves like a table when you read from it. You won't write views in this course, but you'll see the icon and now you know what it means.

Diagram 3.1 · One dataset, three tables. Datasets are folders that control access. Tables hold the actual rows. Each table has its own schema.

Datasets group related tables. Our dataset has voter_file, events, and donations. Each lives at a different path. The query below pulls from donations : a completely separate shape of data.

Action: In the Explorer panel, click your dataset (the folder) and then your table (the grid icon). Notice which icon is which.

Slide to complete