Fundamentals & Positioning
What is BigQuery, and how does it differ fundamentally from a traditional relational database or data warehouse?
Explain the key advantages of using BigQuery for analytical workloads.
Is BigQuery a database or a data warehouse?
Why is BigQuery a poor choice for OLTP or high-frequency transactional workloads?
Is BigQuery a true serverless architecture? Elaborate on what that means for users.
When would you use BigQuery versus Bigtable, Spanner, or Cloud SQL?
What are the differences between BigQuery and traditional ETL tools?
Why might BigQuery be more expensive than Redshift?
Can Athena replace BigQuery, and what are the use cases for each?
When choosing between a managed cloud OLAP service like BigQuery and a self-managed columnar cluster, how do they compare across performance predictability, TCO, administrative overhead, query concurrency, and vendor lock-in, and which would you recommend for a team with 20 analysts and occasional heavy ad-hoc queries?
Which scales better, BigQuery or Redshift, and why?
Storage Objects & Data Modeling
What are datasets, tables, and views in BigQuery, and when would you use each?
What are ARRAY and STRUCT data types in BigQuery?
What is the difference between a table and a view in BigQuery?
What are native tables, external tables, and BigLake tables in BigQuery, and what are their primary use cases?
Explain the UNNEST operator in BigQuery and how it is used to flatten ARRAYs or STRUCTs.
How does BigQuery handle primary and foreign keys, and what are the implications compared to a traditional RDBMS that enforces these constraints?
Does BigQuery support all regions, and why is dataset location important?
How do you model nested and repeated data, and when do you UNNEST vs normalize into separate tables?
Explain ARRAYs and STRUCTs in BigQuery. Why is denormalizing data into nested and repeated fields often considered idiomatic and performant?
What are the best practices for schema design in BigQuery for analytical workloads?
What does the term lakehouse mean for BigQuery, and how do BigLake tables move it in that direction?
Data Types & Schema
What data types does BigQuery support?
What are the key data types supported by BigQuery, and how might choosing the right data type impact storage, performance, and cost?
How does schema auto-detection work when loading data into BigQuery, and when should you provide an explicit schema instead?
How does BigQuery support schema evolution, and what types of schema changes are allowed without data loss?
When would you use NUMERIC versus BIGNUMERIC versus FLOAT64 in BigQuery, and why does precision matter for financial data?
How does the native JSON data type in BigQuery differ from storing JSON as a string, and what are the advantages?
Architecture & Query Execution
When data is loaded into BigQuery, where is it physically stored?
Can I see or access the actual files behind a BigQuery table?
What format does BigQuery store data in internally?
Can you explain the high-level architecture of BigQuery?
What are BigQuery slots, and how do they represent the unit of computational capacity?
What happens to the data if a BigQuery node fails?
Describe the high-level architecture of BigQuery, specifically how it separates storage and compute. Why is this separation beneficial?
Explain the roles of Dremel, Colossus, and Jupiter in BigQuery's architecture and query execution.
Explain what a columnar database is and how BigQuery leverages columnar storage (Capacitor format) for analytical performance.
When you submit a SQL query in BigQuery, how does it physically execute end-to-end, and what factors affect its speed?
What is the persistence layer in BigQuery (Colossus), and how does it ensure durability?
Explain BigQuery's architecture, including columnar storage and slot-based compute, and how it fundamentally differs from traditional databases.
What is the in-memory shuffle tier in BigQuery, and what role does it play during query execution?
Can you describe Dremel's tree architecture — the roles of the root, mixer, and leaf nodes — during a query?
How are slots scheduled and shared among concurrent queries and users in BigQuery?
What is the difference between a broadcast join and a hash join in BigQuery, and how does the engine decide?
What transaction and consistency guarantees does BigQuery provide, including multi-statement transactions and the streaming buffer?
Partitioning & Clustering
What is partitioning in BigQuery, and how does it help performance?
Describe how partitioning and clustering work in BigQuery and how they contribute to optimizing query performance and cost.
What is clustering in BigQuery, and how does it differ from partitioning?
What is the difference between ingestion-time partitioning and column-based partitioning?
Explain partitioning in BigQuery. What are the different types of partitioning available, and when would you choose one over another?
When should you use partitioning vs clustering, and how do they impact cost and performance?
How does partition pruning work, and what are the requirements for a query to effectively leverage it?
How would you apply partitioning on a table that only has string columns like id, name, and address?
Explain the concept of clustering in BigQuery and its benefits for query performance.
What is require_partition_filter, and why would you enable it on a table?
What considerations should be taken into account when changing a partitioning column on an existing BigQuery table?
What is the partition limit in BigQuery, and what problems arise when partitions are too small or too numerous?
What is automatic reclustering in BigQuery, and how does it keep clustered tables performant over time?
Data Loading Ingestion & Export
Describe the common methods for batch loading data into BigQuery from Google Cloud Storage.
How do you export data out of BigQuery, and what are the differences between EXPORT DATA statements and extract jobs?
Explain the concept of BigQuery federated queries and when you would use them versus loading data.
Why is Google Cloud Storage often used as an intermediary storage layer when loading data into BigQuery?
What are the common ways to load data into BigQuery, and when would you pick each?
What is the BigQuery Data Transfer Service, and how does it simplify data ingestion?
How would you handle late-arriving or duplicated data when performing streaming inserts into BigQuery?
Explain the different options for streaming data into BigQuery (legacy streaming insert API vs. Storage Write API) and their characteristics regarding quotas, cost, and deduplication.
Why is it recommended to use the Storage Read API for reads and the Storage Write API for writes for best performance in BigQuery?
Cost Model & Optimization
Can you explain BigQuery's cost model?
Why is SELECT * considered a bad practice in BigQuery, especially considering its columnar storage and pricing model?
How do you estimate the cost of a BigQuery query before running it?
Does the LIMIT clause reduce query cost in BigQuery?
How does table and partition expiration work in BigQuery, and why would you configure it?
Explain the difference between BigQuery's on-demand pricing and capacity-based (slot) pricing models. When would an organization choose one over the other?
How can you optimize data storage costs in BigQuery?
What is query caching in BigQuery, and under what conditions does it apply or not apply?
How can you reduce bytes processed in BigQuery to optimize costs?
What is the difference between active storage and long-term storage pricing, and how does a table qualify for long-term storage?
How would you put guardrails on a runaway BigQuery bill using maximum_bytes_billed and custom quotas?
What are approximate aggregate functions like APPROX_COUNT_DISTINCT, and when would you trade exactness for cost and speed?
Explain BigQuery's pricing model, including on-demand versus flat-rate, and discuss strategies to control spiraling costs.
What is the difference between logical and physical storage billing in BigQuery, and how do you decide which to use?
Sql Features & Dml
What is the difference between Standard SQL and Legacy SQL in BigQuery?
What are the various ways to access BigQuery once configured?
Explain the concept of window functions in BigQuery and how they differ from aggregate functions.
Describe the different types of User-Defined Functions available in BigQuery (SQL, JavaScript, Remote Functions) and when you would use each.
What are wildcard tables in BigQuery, and when are they useful?
Should you use TRUNCATE TABLE or DELETE to remove all rows in BigQuery, and what are the implications of each?
What does the QUALIFY clause do in GoogleSQL, and why is it useful?
What are stored procedures and scripting in BigQuery, and when would you reach for them?
Why can CROSS JOINs be dangerous in BigQuery, and how do you avoid accidental ones?
What are the INFORMATION_SCHEMA views in BigQuery, and what kinds of metadata and operational questions can you answer with them?
How does the MERGE statement work in BigQuery, and how would you use it to implement upserts or slowly changing dimensions?
What are the cost and quota characteristics of DML operations (UPDATE/DELETE/MERGE) in BigQuery, and how do they differ from a traditional RDBMS?
Views & Materialization
What is the difference between a standard view and a materialized view in BigQuery, and when would you use each?
Can you explain the concept of materialized views in BigQuery?
What is the difference between temporary, permanent, and materialized views in BigQuery?
What are table functions (TVFs) in BigQuery, and how do they differ from views and scalar UDFs?
When do you use materialized views vs scheduled queries vs persisted tables for dashboards?
Query Performance Tuning
What are the best practices for optimizing query performance and cost in BigQuery?
Why is the ORDER BY clause expensive in BigQuery?
What are query stages in a BigQuery execution plan, and how do you read them to understand what the engine did?
How would you design a BigQuery table for a large volume of daily event logs (e.g., 5 TB), considering performance and cost?
How do you analyze a BigQuery query execution plan to identify performance bottlenecks and optimize query performance?
Describe strategies to handle data skew in BigQuery queries during joins or aggregations.
What happens if you increase SQL query concurrency in BigQuery?
How would you optimize a slow dashboard query running on billions of rows in BigQuery?
Security Governance & Compliance
How does access control work in BigQuery, including IAM roles and granular features like row-level and column-level security?
Explain authorized views and authorized datasets as mechanisms for sharing data securely in BigQuery.
What is the BigQuery Data Catalog, and how does it contribute to data governance?
How does BigQuery ensure data encryption at rest and in transit?
What is the difference between the jobUser and dataViewer roles in BigQuery, and why is that distinction important?
What is the best approach to guarantee GDPR compliance when storing data in BigQuery?
What are policy tags and dynamic data masking in BigQuery, and how do they enforce column-level governance?
What is CMEK in the context of BigQuery, and when would you use customer-managed encryption keys instead of Google-managed keys?
What do VPC Service Controls protect against for BigQuery, and why would an organization set them up?
Orchestration Monitoring & Operations
How can you schedule and automate jobs in BigQuery?
How do you analyze BigQuery audit logs and INFORMATION_SCHEMA.JOBS for usage analysis and chargeback?
How do you track pipeline performance, debug issues, and optimize query costs in BigQuery?
How do you orchestrate BigQuery pipeline workflows using tools like Cloud Composer, Cloud Functions, or Cloud Scheduler?
Capacity Recovery & Data Sharing
Explain the concept of BigQuery's time travel feature and how it can be used for data recovery.
What is BigQuery BI Engine, and how does it accelerate dashboard and reporting performance?
Explain the difference between BigQuery slots and slot reservations.
What is the difference between a table snapshot and a table clone in BigQuery, and when would you use each?
What is the difference between time travel and the fail-safe period in BigQuery?
What is Analytics Hub, and how does it enable data sharing and data exchanges across organizations in BigQuery?
Why does a cross-region join fail in BigQuery, and how do you move data between regions to work around it?
What are BigQuery Editions (Standard, Enterprise, Enterprise Plus), and how does slot autoscaling work within them?
What is the difference between slot commitments and autoscaling reservations, and how are idle slots shared across reservations?
What is BigQuery Omni, and what problem does it solve for multi-cloud analytics?
Machine Learning & Ai
What is BigQuery ML, and what types of machine learning models can you train and deploy directly within BigQuery using SQL?
What do ML.PREDICT, ML.EVALUATE, and ML.FORECAST do in BigQuery ML, and how do they fit the model lifecycle?
When would you choose to train a model using BigQuery ML versus exporting data to a service like Vertex AI?
What is the TRANSFORM clause in BigQuery ML, and why is it valuable for preprocessing?
What generative-AI and vector-search capabilities does BigQuery offer, and what use cases do they enable?