Mapping Business Requirements to BigQuery Design

Aligning storage and query billing models with financial objectives means choosing separate billing controls for BigQuery (opens in a new tab) compute and storage that match how predictable the workload is. Compute is billed either by bytes scanned per query or by reserved computational capacity, while storage is billed per dataset with discounts for long-lived data. Governance with labels then assigns each cost to the team or workload that created it.

1.1Query Billing Models

BigQuery query billing models are on-demand pricing and capacity-based pricing. On-demand pricing charges per TiB of bytes processed, so cost rises and falls with the data scanned by each query. Capacity-based pricing charges for slots, which are units of computational capacity purchased as reservations with a minimum of 100 slots. By default, projects are limited to 2,000 slots, and slots used for streaming inserts and loads do not count against reservation capacity.

Choose on-demand pricing when query volume is variable, ad hoc, or low, because no capacity is reserved in advance and payment follows actual use. Choose capacity-based pricing for enterprise warehouse workloads with many concurrent reporting and extract-load-transform (ELT) queries and predictable consumption, because a fixed reservation pool gives predictable cost and guaranteed capacity. The tradeoff is flexibility versus control: on-demand needs no sizing but varies with scan volume, while capacity-based pricing stabilizes cost at scale but requires monitoring and reservation sizing.

1.2Slot Reservations and Capacity Planning

Reservations are pools of BigQuery slots that isolate capacity by team, project, or workload type. A reservation lives in one administration project and can be shared across projects, so concurrent queries from different projects draw from their assigned pool. BigQuery allocates query resources dynamically by query type and complexity with an eventual fairness scheduler, so brief uneven sharing is corrected over time, but sustained high utilization signals under-provisioning.

Size reservations from observed use rather than guessing, using yearly or three-year commitments purchased in the Google Cloud console or with the bq command-line tool. The estimation loop runs in order:

1. Monitor slot allocation: Collect slot utilization, bytes scanned, and bytes stored with Cloud Monitoring and BigQuery system tables data across jobs and reservations.
2. Analyze consumption patterns: Analyze audit logs in BigQuery and visualize slot use across queries and projects with Data Studio, Looker, or Tableau.
3. Adjust reservation size: Add slots when execution plans show high wait times and dashboards show sustained high use, as when 4,000 slots running 100 medium-complexity queries at once begin to queue.

Continuous monitoring as concurrency grows keeps total cost of ownership aligned with demand, and a cross-project monitoring utility helps attribute slot use per query when many teams share capacity-based pricing.

1.3Storage Billing and Long-Term Cost Control

The dataset storage billing model controls how storage charges accrue for the tables and views inside each BigQuery dataset. BigQuery allocates storage dynamically as data is consumed and releases it as data is removed or tables are dropped, which provides per-second billing and scaling without provisioning. Unmodified data stored for more than 90 days automatically costs half as much to store, so stable historical partitions and rarely changed dimensions become cheaper to retain.

Financial ownership is enforced with labels, which are key-value pairs on datasets, tables, and views that support chargeback by cost center. Storage, query, and streaming-insert usage are billed separately, so labels let finance trace which team drives each part of total cost. Archiving cold data, reducing unnecessary retention, and keeping reference data unmodified therefore directly lower the storage share of total cost.

Translating operational service-level agreements (SLAs) into high availability and disaster recovery architectures means converting allowed downtime and data loss into concrete BigQuery (opens in a new tab) storage and recovery designs. Recovery time objective (RTO) is the allowed time to restore service and data, while recovery point objective (RPO) is the maximum age of acceptable data loss. In BigQuery, RTO and RPO decide whether a single-region dataset with backups is enough or whether cross-region copies, redundant capacity, and dual pipelines are required.

2.1RTO and RPO as Design Drivers for Data Warehouses

RTO and RPO for a BigQuery data warehouse are driven by warehouse size, availability needs, and nature of the data. Size matters because warehouses often hold terabytes to petabytes, so restoring that volume takes time and affects whether RTO can be met. Availability needs depend on the failure domain, whether machine, zonal, or regional, because regional loss is longer and more expensive to recover than zonal failure. Nature of the data matters because warehouse data is often historic, static, and append-only, so RTO can sometimes be met by restoring only new appends rather than full history.

A nightly backup pattern shows how the objectives are calculated. If BigQuery data in us-east4 is exported nightly with extract jobs to Cloud Storage in Archive Storage class in us-central1, RPO is 24 hours because the last backup can be up to 24 hours old. RTO is potentially days, because data must be transferred to the recovery region if needed and then restored from Cloud Storage into BigQuery in us-central1. Additional delay occurs unless redundant slots were purchased in advance in the recovery region, since restore and backfill queries need compute capacity before they can run.

2.2Single-Region Versus Multi-Region Datasets

A BigQuery dataset location defines failure isolation and does not by itself create a disaster recovery copy. A regional location stores data in one specific region such as Iowa (us-central1), while a multi-region location covers a large area such as the United States (US) or European Union (EU) that contains two or more places. Selection of EU multi-region, for example, excludes certain places such as Zurich and London from that configuration.

2.3Cross-Region Backup and Replication for Regional Loss

Cross-region protection for BigQuery depends on creating a usable data copy and usable compute in a second failure domain. The documented pattern runs extract jobs from the primary BigQuery region to a Cloud Storage bucket in a different region, using low-cost Archive Storage when the copy is for catastrophic loss. Recovery flows from the primary BigQuery tables to the Cloud Storage backup objects to restored BigQuery tables in the recovery region, with an added transfer step when the restore region differs from the backup region. The workflow depends on the backup bucket and on pre-provisioned BigQuery slots in the recovery region, because without reserved capacity the restore queries must wait.

For batch workloads with strict deadlines, continuous dual-region execution gives stronger RTO than backup-and-restore alone. Running two separate instances of the entire pipeline in two regions, for example us-west1 and us-east4, creates two independent failure domains that survive extended or permanent region loss. Both pipelines write results independently, so exactly-once delivery must be reconciled by picking the first finisher, for example by notifying a Pub/Sub topic on success, or by overwriting the result and re-versioning the Cloud Storage object. If the later pipeline writes corrupt data, recovery uses the version preserved from the first finisher in Cloud Storage.

2.4Time Travel and Table Snapshots for Operational Recovery

BigQuery time travel provides self-service recovery from accidental deletion, modification, or corruption within a short window. The capability comes from BigQuery multiversion concurrency control architecture, which retains history so data can be queried from any point in time over the last seven days. Time travel works even on tables that have been deleted, which allows restoration without a separate backup when the error is found within seven days.

BigQuery table snapshots extend protection against logical errors beyond the seven-day window without copying bytes. A snapshot is purely a metadata operation within the same region and creates no additional storage bytes at creation time. Snapshots add longer protection against accidental deletion, but they do not increase durability because they remain in the same region and do not protect against regional loss.

2.5Matching Resilience Cost to Real-Time and Batch SLAs

Resilient storage layouts must weigh redundancy cost against the business cost of missing RTO or RPO. In a real-time analytics architecture, log events flow from endpoints to Pub/Sub to a streaming Dataflow pipeline to BigQuery through the Storage Write API, then serve ad hoc queries and near-real-time dashboards where ingestion and query latency matter. Protecting that pattern against extended regional unavailability would require continuous replication and slots in another region, but the ingestion path is already resilient because Pub/Sub subscriptions can buffer unacknowledged messages for up to seven days and Dataflow can resume backed-up events with low RPO. That combination often makes full dual-region BigQuery redundancy not worth the cost, so a nightly extract to Cloud Storage with 24-hour RPO and multi-day RTO is an accepted tradeoff.

In a batch regulatory-reporting architecture, the SLA is a fixed nightly deadline rather than freshness, and reliability means finishing ingestion and reports correctly before that deadline. Because missing the deadline carries high business and compliance cost, the extra cost of dual-region pipelines is justified to achieve low RTO. The tradeoff is therefore explicit: accept higher continuous compute and storage cost for two active regions when on-time delivery is critical, and accept higher RPO and RTO with backup to Cloud Storage when delayed dashboards are tolerable.

Mapping security, privacy, and regulatory compliance requirements means translating governance policies and legal obligations into specific Google Cloud technical controls for a data warehouse. A business rule such as restrict sensitive columns, keep data in-country, or meet FedRAMP (opens in a new tab) High becomes a choice of residency boundary, access grant, masking rule, and encryption setting. The design must show which controls Google operates and which controls the customer must configure and monitor.

3.1FedRAMP Authorization Boundary and Control Baselines

The FedRAMP authorization path for Google Cloud workloads is the Rev. 5 Agency Authorization to Operate (ATO), completed jointly by the customer, Google, and a third-party assessment organization (3PAO). The customer must satisfy continuous monitoring for the Google Cloud infrastructure provisioned for the workload, including infrastructure used for analytics or machine learning deployment. Google supports the process with compliance documentation provided under a non-disclosure agreement (NDA) through the sales team or Google Cloud representative, or for federal agencies through the FedRAMP package access request form.

FedRAMP Moderate and FedRAMP High share the same NIST SP 800-53 control family but differ in scope and enforcement:

3.2Enforcing Data Residency for FedRAMP High

Data residency enforcement for FedRAMP is a FedRAMP High control baseline requirement mandated by control SA-9(5), not a FedRAMP Moderate requirement. The technical implementation is the Assured Workloads Data Boundary for FedRAMP High, which constrains where protected data is stored and processed. When a warehouse such as BigQuery must meet FedRAMP High obligations, the project and data must operate inside that boundary. Choosing the boundary first determines which services, regions, and APIs are eligible to store protected data.

3.3Translating Shared Controls into Service Configurations

Shared responsibility for FedRAMP High is documented in the Customer Responsibility Matrix (CRM) and System Security Plan (SSP). The Customer Responsibility Matrix (CRM) describes in detail the customer responsibilities for implementing NIST SP 800-53 controls in the High baseline. The System Security Plan (SSP) describes the security authorization boundary, how the system is architected, and how Google Cloud implements the applicable controls. Together the CRM defines what the customer must do and the SSP defines what Google already does.

Service-specific configuration is guided by control mapping guides (CMGs) and FedRAMP High implementation guides. Control mapping guides are service specific and cover NIST controls that require technical configuration by the customer, including steps for a particular service and supporting Google Cloud and Google Workspace services. Guides are available for select services including BigQuery, Cloud Logging, Compute Engine, and Identity and Access Management (IAM). FedRAMP High implementation guides cover service-specific APIs in High scope, including impacted features and data fields suitable for protected data. Those configurations are not enforced by default and must be managed by the customer, with guides available for BigQuery, Cloud Key Management Service, Cloud Logging, Google Kubernetes Engine (GKE), and Cloud Storage.

3.4Implementing Dataset Access and Column Masking

Dataset access in BigQuery is the mechanism that maps governance rules about who can see warehouse data to explicit grants. Access can be granted to an individual Google Account by user email, to all members of a Google group by group email, to all users and groups in a Google domain, and to all Google Account holders through All Authenticated Users, which makes the dataset public. Access can also be granted to Project Owners, Project Viewers, and Project Editors, and to an Authorized View that receives access on behalf of its users.

Column-level data masking extends dataset access by selectively obscuring column data for groups of users while still allowing access to the column itself. The policy applies to the column, the query flows from the protected table to the authorized user, and the masking rule determines whether the user receives the true or masked value. This approach enforces privacy without creating separate table copies for each audience, and it combines with broader encryption controls managed through Cloud Key Management Service for FedRAMP High workloads.

3.5Relying on Google Infrastructure Controls and Auditability

Google infrastructure (opens in a new tab) controls provide the foundation that customer warehouse controls depend on, using pervasive prevention, continuous detection, and independent audit. Prevention includes strong authentication for all access, where production jobs prove identity with cryptographic credentials and communicate with application layer transport security (ALTS), and where only responsible owners such as Site Reliability Engineers (SREs) can deploy jobs with a particular identity. Prevention also includes context-aware access for zero-trust decisions based on identity, location, device ownership and configuration, and fine-grained policy, plus Binary Authorization for Borg to ensure production software is reviewed before deployment.

Auditability flows from Google infrastructure logs to customer-visible monitoring and response. Customer logs are available through Cloud Audit Logs, administrative access by Google personnel is visible through Access Transparency, and deployment integrity exceptions are monitored internally to prevent unreviewed code from running in privileged environments. Incident detection and response combines expert investigation teams with automated controls that use machine learning, data processing pipelines, and threat intelligence built from past incidents, network traffic, access logs, anomalous behavior, and offensive security exercises.

Key Facts

• BigQuery on-demand pricing bills per TiB of bytes processed, while capacity-based pricing bills for slot reservations with a minimum of 100 slots and a default project limit of 2,000 slots.
• Unmodified BigQuery data stored for more than 90 days automatically costs half as much to store.
• A BigQuery multi-region dataset stores data in a single region within that multi-region area and does not provide cross-region replication by itself.
• BigQuery time travel allows querying and restoring data from any point in time over the last seven days, even for deleted tables.
• FedRAMP High requires the Assured Workloads Data Boundary for FedRAMP High for data residency, while FedRAMP Moderate can use any FedRAMP High-authorized service inside its boundary.