- BigQuery - Home
- BigQuery - Overview
- BigQuery - Initial Setup
- BigQuery vs Local SQL Engines
- BigQuery - Google Cloud Console
- BigQuery - Google Cloud Hierarchy
- What is Dremel?
- What is BigQuery Studio?
- BigQuery - Datasets
- BigQuery - Tables
- BigQuery - Views
- BigQuery - Create Table
- BigQuery - Basic Schema Design
- BigQuery - Alter Table
- BigQuery - Copy Table
- Delete and Recover Table
- BigQuery - Populate Table
- Standard SQL vs Legacy SQL
- BigQuery - Write First Query
- BigQuery - CRUD Operations
- Partitioning & Clustering
- BigQuery - Data Types
- BigQuery - Complex Data Types
- BigQuery - STRUCT Data Type
- BigQuery - ARRAY Data Type
- BigQuery - JSON Data Type
- BigQuery - Table Metadata
- BigQuery - User-defined Functions
- Connecting to External Sources
- Integrate Scheduled Queries
- Integrate BigQuery API
- BigQuery - Integrate Airflow
- Integrate Connected Sheets
- Integrate Data Transfers
- BigQuery - Materialized View
- BigQuery - Roles & Permissions
- BigQuery - Query Optimization
- BigQuery - BI Engine
- Monitoring Usage & Performance
- BigQuery - Data Warehouse
- Challenges & Best Practices
BigQuery - Data Warehouse
For many organizations, BigQuery is a natural candidate for a data warehouse. A data warehouse is a business system and central repository to store data for analysis and then downstream reporting.
Note Typically, data stored within a data warehouse is structured or semi-structured, as opposed to a data lake, which stores unstructured data.
BigQuery's ability to connect to visualization platforms like Looker or Tableau make it ideal for serving as the engine powering enterprise dashboards and ad hoc reporting. The ability to apply storage optimizations like partitioning and clustering means data teams can confidently and efficiently store data for years or decades without worrying about compromised performance. Being able to integrate with scripting languages like Python and JavaScript allows professionals like software engineers, data architects and data engineers to create automated, recurring load jobs.
Integrating BigQuery with other applications like Google Sheets makes data stored in BigQuery more visible and accessible to non-technical stakeholders who might prefer or exclusively work on spreadsheets.
Leveraging the BigQuery API allows developers to combine BigQuery SQL with programmatic logic to generate custom insights.
The ability to add slots, upgrade storage and accelerate queries on-demand with tools like BI Engine is an attractive proposition for organizations initially building or growing their data infrastructure.
The downside to any of these features is cost. However, with both fixed and per-use pricing models, business users and decision makers are likely to find the cost-benefit of using a BigQuery-oriented data warehouse worth it.
The diagram below, provided by Google Cloud, illustrates how to build and implement a basic data warehouse-oriented solution.
(Source: https://cloud.google.com/architecture/big-data-analytics/data-warehouse)
In addition to data sources, application and data analysis, this diagram can also be broken down into more specific categories and interpreted as −
- Upstream source (a third-party API or external data source).
- Intermediary/staging storage (Cloud Storage buckets).
- Permanent/long-term storage (BigQuery).
- ML/AI applications (Vertex AI, Cloud Functions and Compute Engine Virtual Machines).
- Downstream users: Business users accessing the output of models and queries through visualization platforms like Looker.
In either interpretation, BigQuery is the central repository or "data warehouse" that contains data before it can generate business value for downstream users.