Organizing BigQuery resources

Like other Google Cloud services, BigQuery resources are organized in ahierarchy. You can use this hierarchy to manage aspects of yourBigQuery workloads such as permissions, quotas, slotreservations, and billing.

Resource hierarchy

BigQuery inherits theGoogle Cloud resource hierarchyand adds an additional grouping mechanism calleddatasets, which are specificto BigQuery. This section describes the elements of thishierarchy.

Datasets

Datasets are logical containers that are used to organize and control access toyour BigQuery resources. Datasets are similar to schemas in otherdatabase systems.

Most BigQuery resources that you create — including tables,views, functions, and procedures — are created inside a dataset.Connections and jobs are exceptions; these are associated with projects ratherthan datasets.

A dataset has alocation. When you create a table,the table data is stored in the location of the dataset. Before you createtables for production data, think about yourlocation requirements. You cannotchange the location of a dataset after it is created.

Projects

Every dataset is associated with a project. To use Google Cloud, you mustcreate at least one project. Projects form the basis for creating, enabling, andusing all Google Cloud services. For more information, seeResource hierarchy.A project can hold multiple datasets, and datasets with different locations canexist in the same project.

When you perform operations on your BigQuery data, such asrunning a query or ingesting data into a table, you create ajob. A job is always associated with a project,but it doesn't have to run in the same project that contains the data. In fact,a job might reference tables from datasets in multiple projects. A query job,load job, or extract job always runs in the same location as the tables that itreferences.

Each project has aCloud Billing account attached toit. The costs accrued to a project are billed to that account. If you useon-demand pricing, your queries arebilled to the project that runs the query. If you usecapacity-based pricing, your slot reservationsare billed to the administration project used to purchase the slots. Storage ischarged to the project where the dataset resides.

Folders

Folders are an additional grouping mechanism above projects. Projects andfolders inside a folder automatically inherit the access policies of theirparent folder. Folders can be used to model different legal entities,departments, and teams within a company.

Folders as discussed here are part of theGoogle Cloud resource hierarchy.They shouldn't be confused withBigQuery folders,which you can use to organize code assets.

Organizations

TheOrganization resourcerepresents an organization (for example, a company) and is the root node in theGoogle Cloud resource hierarchy.

You don't need an Organization resource to get started usingBigQuery, but we recommend creating one. Using an Organizationresource allows administrators to centrally control your BigQueryresources, rather than individual users controlling the resources they create.

The following diagram shows an example of the resource hierarchy. In thisexample, the organization has a project inside a folder. The project isassociated with a billing account, and it contains three datasets.

Considerations

When choosing how to organize your BigQuery resources, considerthe following points:

• Quotas. Many BigQueryquotas areapplied at the project level. A few apply at the dataset level. Project-levelquotas that involve compute resources, such as queries and load jobs, arecounted against the project that creates the job, rather than the storageproject.
• Billing. If you want different departments in your organization to usedifferent Cloud Billing accounts, then create different projects foreach team. Create the Cloud Billing accounts at the organizationlevel and associate the projects to them.
• Slot reservations. Reservedslots are scoped tothe Organization resource. After you purchase reserved slot capacity, you canassign a pool of slots to any project or folder within the organization, orassign slots to the entire Organization resource. Projects inherit slotreservations from their parent folder or Organization. Reserved slots areassociated with anadministration project,which is used to manage the slots. For more information, seeWorkload management using Reservations.

• Permissions. Consider how your permissions hierarchy affects the people inyour organization who need to access the data. For example, if you want togive an entire team access to specific data, you might store that data in asingle project to simplify access management.

  Tables and other entities inherit the permissions of theirparent dataset. Datasets inherit permissions from their parent entities in theresource hierarchy (projects, folders, organizations). To perform an operationon a resource, a user needs both the relevant permissions on the resource, andalso permission to create a BigQuery job. The permission tocreate a job is associated with the project that is used for that job.

Patterns

This section presents two common patterns for organizing BigQueryresources.

• Central data lake, department data marts. The organization creates aunified storage project to hold its raw data. Departments within theorganization create their own data mart projects for analysis.

• Department data lakes, central data warehouse. Each department createsand manages its own storage project to hold that department's raw data.The organization then creates a central data warehouse project for analysis.

There are advantages and tradeoffs to each approach. Many organizations combineelements of both patterns.

Central data lake, department data marts

In this pattern, you create a unified storage project to hold yourorganization's raw data. Your data ingestion pipeline can also run in thisproject. The unified storage project acts as a data lake for your organization.

Each department has its own dedicated project, which it uses to query the data,save query results, and create views. These department-level projects act asdata marts. They are associated with the department's billing account.

Advantages of this structure include:

• A centralized data engineering team can manage the ingestion pipeline in asingle place.
• The raw data is isolated from the department-level projects.
• With on-demand pricing, billing for running queries is charged to thedepartment that runs the query.
• With capacity-based pricing, you can assign slots to each department based ontheir projected compute requirements.
• Each department is isolated from the others in terms of project-level quotas.

When using this structure, the following permissions are typical:

• The central data engineering team is granted the BigQuery Data Editor and BigQuery Job Userroles for the storage project. These allow them to ingest and edit data in thestorage project.
• Department analysts are granted the BigQuery Data Viewer rolefor specific datasets in the central data lake project. This allows them toquery the data, but not to update or delete the raw data.
• Department analysts are also granted the BigQuery Data Editorrole and Job User role for their department's data mart project. This allowsthem to create and update tables in their project and run query jobs, in orderto transform and aggregate the data for department-specific usage.

For more information, seeBasic roles and permissions.

Department data lakes, central data warehouse

In this pattern, each department creates and manages its own storage project,which holds that department's raw data. A centraldata warehouse projectstores aggregations or transformations of the raw data.

Analysts can query and read the aggregated data from the data warehouse project.The data warehouse project also provides an access layer for businessintelligence (BI) tools.

Advantages of this structure include:

• It is simpler to manage data access at the department level, by using separateprojects for each department.
• A central analytics team has a single project for running analytics jobs,which makes it easier to monitor queries.
• Users can access data from a centralized BI tool, which is kept isolated fromthe raw data.
• Slots can be assigned to the data warehouse project to handle all queriesfrom analysts and external tools.

When using this structure, the following permissions are typical:

• Data engineers are granted BigQuery Data Editor andBigQuery Job User roles in their department's data mart. Theseroles allow them to ingest and transform data into their data mart.
• Analysts are granted BigQuery Data Editor andBigQuery Job User roles in the data warehouse project. Theseroles allow them to create aggregate views in the data warehouse and runquery jobs.
• Service accounts that connect BigQuery to BI tools are grantedthe BigQuery Data Viewer role for specific datasets, which canhold either raw data from the data lake or transformed data in the datawarehouse project.

For more information, seeBasic roles and permissions.

You can also use security features such asauthorized views andauthorized user-defined functions(UDFs) to make aggregated data available to certain users without granting thempermission to see the raw data in the data mart projects.

This project structure can result in many concurrent queries in the datawarehouse project. As a result, you might hit theconcurrent query limit. If you adopt thisstructure, consider raising this quota limit for the project. Also considerusing capacity-based billing, so that you can purchase a pool of slots to run thequeries.