BigQuery - Advanced Features
Partitioning, clustering, materialized views, UDFs, nested queries, STRUCT, ARRAY
1What is the primary role of partitioning in BigQuery?
What is the primary role of partitioning in BigQuery?
Answer
Partitioning divides a table into segments based on a column (often a date), which reduces the amount of data scanned during queries. When a query filters on the partition column, BigQuery only reads the relevant partitions instead of scanning the entire table. This improves performance and reduces query costs, which are billed based on the volume of data scanned.
2What types of partitioning are available in BigQuery?
What types of partitioning are available in BigQuery?
Answer
BigQuery offers three types of partitioning: by DATE, TIMESTAMP, or DATETIME column (most common), by integer range (INTEGER RANGE), and by ingestion time (_PARTITIONTIME). Date-based partitioning is the most widely used because most analyses filter on time periods. Integer range partitioning is useful for numeric identifiers.
3What is a STRUCT in BigQuery?
What is a STRUCT in BigQuery?
Answer
A STRUCT (or RECORD) is a data type that groups multiple named fields of potentially different types into a single column. For example, a STRUCT can contain a name (STRING), an age (INT64), and an email (STRING). STRUCTs allow modeling hierarchical data directly within a table, avoiding costly joins. Fields are accessed using dot notation (struct_col.field).
What is an ARRAY in BigQuery?
What is the purpose of the UNNEST function in BigQuery?
+17 interview questions
Other Data Analytics interview topics
Google Sheets - Fundamentals
Google Sheets - Advanced Formulas
SQL - Fundamentals
SQL - Aggregations and Grouping
SQL - Joins
BigQuery - Fundamentals
Data Cleaning
KPIs and Business Metrics
Descriptive Statistics
Zapier and No-Code Automation
Data Visualization Principles
Python & Pandas - Fundamentals
Google Sheets - Automated Dashboards
SQL - Subqueries and CTEs
SQL - Window Functions
Data Modeling
Funnel and Conversion Analysis
Cohort and Retention Analysis
Google Tag Manager and Tracking
APIs and Webhooks
dbt - Fundamentals
AB Testing and Applied Statistics
Looker Studio (Google Data Studio)
Power BI - Fundamentals
SQL - Advanced Analytical Queries
dbt - Advanced Features
Power BI - DAX and Advanced Dashboards
Python Analytics - Advanced Analysis and ML
Master Data Analytics for your next interview
Access all questions, flashcards, technical tests, code review exercises and interview simulators.
Start for free