- 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 vs Local SQL Engines
With SQL being around for 40+ years, BigQuery was not the first SQL environment. Prior to BigQuery's release, SQL developers worked primarily with on-premises or "on-prem" databases.
The systems that allow developers to manage and interact with databases are called database management systems (DBMS).
In the days before the "cloud engineer" or "data engineer" job title gained popularity and widespread use, those who worked with DBMS tools held titles like "architect." Often, this title was used quite literally, as those who worked with databases maintained both physical and digital infrastructure.
Data Modeling
As database technology evolved, an architect's responsibilities primarily concerned a task called data modeling.
While functions like INSERT() can make it seem easy to take data from a source and add it to a database, in organizations that embrace data modeling, there is a lot of thought involved in how to store and mold the data.
Popular data modeling concepts include phrases like "star schema" or "normalization" and "normal forms."
How is BigQuery Different from any Traditional DBMS?
While it is still advised to follow best practices when creating BigQuery tables, BigQuery is a bit more out-of-the-box than traditional DBMS interfaces.
BigQuery differs from a more "traditional" DBMS in the following ways −
- Scalability − BigQuery can scale to meet nearly any storage or querying need, thanks to the staggering amount of cloud storage made available by Google's data centers.
- API integration − BigQuery's SQL engine can be leveraged programmatically, while DBMS like Postgre can only run native SQL queries.
- ML/AI capabilities, integrating with Vertex AI.
- More specific data types available for long-term storage.
- A Google-specific SQL dialect, Google Query Language (GQL) that includes more specialized functions than more legacy SQL dialects.
From a user's perspective, BigQuery also offers more visibility regarding execution stats and user activity −
- Allowing users to see if a query is acceptable before executing accompanying SQL
- Providing execution plans and query lineage
- Expanded metadata stores to provide insight into query usage, storage and cost
BigQuery truly is the standard for cloud-based SQL querying, which begins in The Google Cloud Console.