Advanced
Prepare and test your skills
Advanced
Prepare and test your skills
Using Native UDFs and MAX_BY in BigQuery
Free sample question, answered and explained below. The Professional Data Engineer practice bank has 1,353 more like it. Try 10 free: instant score, no account.
A data engineering team is designing a high-throughput batch transformation pipeline in BigQuery to process billions of customer interaction records daily. The pipeline needs to parse complex semi-structured payloads, determine the customer's most recent interaction event attributes per session without complex joins, and return structured objects containing arrays of historical actions.
The solution must run natively at scale within BigQuery, maximize query execution performance, avoid external concurrency throttling, and support caching and complex types like ARRAY and STRUCT.
Which declarative processing approach should the team implement?
Explanations
Extract all raw tables into Cloud Storage in JSON format, perform payload parsing in Dataproc Spark, and load the enriched data back into BigQuery
Extract all raw tables into Cloud Storage in JSON format, perform payload parsing in Dataproc Spark, and load the enriched data back into BigQuery
What the Option Is
This approach uses an external ETL pipeline where BigQuery tables are exported to Cloud Storage, ingested and transformed using an Apache Spark cluster on Cloud Dataproc, and subsequently reloaded into BigQuery.
Why It Doesn't Meet the Requirement
- Violates native execution requirement: The scenario specifically mandates executing declarative transformations natively within BigQuery without transferring massive volumes outside the data warehouse.
- Massive I/O and network overhead: Exporting and re-importing billions of rows between BigQuery storage and Cloud Storage introduces substantial network latency, storage costs, and pipeline execution time.
- Operational complexity: Requires provisioning, sizing, tuning, and monitoring a dedicated Spark/Dataproc cluster instead of utilizing serverless BigQuery SQL execution.
Key Characteristics and Limitations
- High data movement latency compared to in-place SQL processing.
- Increases storage footprint by retaining intermediate staging files in Cloud Storage.
- Bypasses BigQuery's built-in query optimizer, declarative SQL engine, and slot management.
This Answer Would Be Correct If
The workload required heavy non-SQL distributed machine learning algorithms or complex graph analytics that cannot be executed in SQL or BigQuery ML.
Use legacy SQL User-Defined Functions (UDFs) written in JavaScript to parse data and construct window partitions across the dataset
Use legacy SQL User-Defined Functions (UDFs) written in JavaScript to parse data and construct window partitions across the dataset
What the Option Is
Legacy SQL UDFs represent the older SQL dialect in BigQuery, which allowed embedding JavaScript functions into query definitions prior to the introduction of standard GoogleSQL.
Why It Doesn't Meet the Requirement
- Strict concurrency limits: BigQuery enforces a strict quota limit of 6 concurrent legacy SQL queries with UDFs per project (including interactive and batch jobs), creating a severe bottleneck for production batch workloads.
- Deprecated features: Legacy SQL lacks modern analytical capabilities, including native
MAX_BY, richJSONfunctions, and standardized analytic window functions available in GoogleSQL. - Inferior performance: JavaScript UDF execution incurs V8 JavaScript engine evaluation overhead compared to native compiled GoogleSQL scalar logic.
Key Characteristics and Limitations
- Legacy SQL is deprecated for new development and lacks support for modern BigQuery storage and schema optimizations.
- Highly restrictive query concurrency limits compared to the 1,000+ concurrent multi-statement or interactive queries supported in GoogleSQL.
- Incompatible with modern BigLake, Analytics Hub, and fine-grained data governance features.
This Answer Would Be Correct If
Maintaining an existing, unmigrated legacy data pipeline that has not yet been modernized to standard GoogleSQL.
Implement native GoogleSQL User-Defined Functions (UDFs) for parsing logic and use analytical aggregate functions like MAX_BY to extract the latest event attributes directly within the query
Implement native GoogleSQL User-Defined Functions (UDFs) for parsing logic and use analytical aggregate functions like MAX_BY to extract the latest event attributes directly within the query
What the Solution Is
Native GoogleSQL User-Defined Functions (UDFs) allow developers to write reusable scalar and table-valued transformation logic in standard SQL. When combined with advanced analytical and aggregate functions such as MAX_BY (or ANY_VALUE(... HAVING MAX ...)), BigQuery can efficiently evaluate complex row-level transformations and time-based attribute selections in a distributed fashion.
How It Meets the Requirements
- Native processing at scale: Native GoogleSQL UDFs and aggregate functions execute directly inside BigQuery's distributed Dremel execution engine, processing billions of rows in parallel without data movement.
- Support for complex types: GoogleSQL UDFs fully support complex data types, including nested
STRUCTand repeatedARRAYfields. - Determining latest event attributes: The built-in
MAX_BYfunction retrieves the value of a target column corresponding to the maximum value of another column (such asevent_timestamp) within an aggregation or window frame, eliminating the need for self-joins or nested ranking subqueries. - Avoids concurrency throttling and supports caching: Native GoogleSQL queries with SQL UDFs are deterministic by default, allow full result caching, and are not bound by the restrictive concurrency limits associated with external services.
Key Advantages
- Zero external compute dependency: Eliminates network latency, serialization overhead, and HTTP request batching.
- Optimized slot utilization: Native SQL routines are compiled directly into BigQuery execution stages, allowing full query plan optimization.
- Rich type ecosystem: Native support for
STRUCT,ARRAY,JSON, and native date/time data types. - Cost-effective execution: Avoids paying separate infrastructure fees for external compute engines like Cloud Run.
Why This Is Optimal
Native GoogleSQL UDFs combined with advanced aggregate functions execute entirely in-engine with native slot allocation, supporting full SQL type hierarchies (STRUCT, ARRAY) and deterministic query result caching.
Deploy a BigQuery Remote Function backed by a Cloud Run HTTP service to parse payloads, select the latest event attributes, and return nested STRUCT and ARRAY values
Deploy a BigQuery Remote Function backed by a Cloud Run HTTP service to parse payloads, select the latest event attributes, and return nested STRUCT and ARRAY values
What the Option Is
BigQuery Remote Functions extend SQL capabilities by allowing GoogleSQL queries to call external HTTP endpoints hosted on Cloud Run or Cloud Run functions to run logic implemented in languages such as Python, Java, or Go.
Why It Doesn't Meet the Requirement
- Unsupported data types: Remote functions do not support complex nested and repeated types such as
STRUCT,ARRAY,INTERVAL, orGEOGRAPHYas input arguments or return types. - Query concurrency bottlenecks: Projects are constrained to a default quota of only 10 concurrent queries containing remote functions, which presents severe scaling limits for high-throughput batch pipelines.
- Performance and caching limitations: Remote function calls introduce network latency, HTTP serialization/deserialization overhead, and are treated as non-deterministic, disabling BigQuery's query result cache.
Key Characteristics and Limitations
- Supports only primitive scalar data types and JSON (
STRING,BYTES,NUMERIC,DATETIME,JSON, etc.). - Requires managing external compute infrastructure, Cloud Resource connections, and IAM roles.
- Subject to HTTP payload timeouts and request batching limitations.
This Answer Would Be Correct If
The pipeline required third-party proprietary libraries, external machine learning model inference, or integration with external REST APIs that cannot be compiled or expressed in SQL or JavaScript.
Try 10 interactive questions like this one, free
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
- 1,353 practice questions across all 5 exam domains
- Every answer explained: why it is right, and why the others are not
- Scout, an AI tutor that answers your follow-up questions
- Readiness tracking by domain, so you know when to book the exam
Related questions
Keep the momentum going with these hand-picked practice scenarios
AdvancedProfessional Data Engineer
Cloud Storage gRPC Client and Broadcast Hash Join
Processing logic
AdvancedProfessional Data Engineer
Spanner INSERT_OR_UPDATE Mutation Idempotency
Processing logic
AdvancedProfessional Data Engineer
BigQuery SCD Denormalization with Nested STRUCTs
Designing the data model
AdvancedProfessional Data Engineer
Transfer Appliance vs DMS vs BigQuery DTS
Planning migration and validation to Google Cloud (e.g., BigQuery Data Transfer Service, Database Migration Service, Transfer Appliance, Google Cloud networking, Datastream)
AdvancedProfessional Data Engineer
BigQuery Multi-Project Architecture
Mapping current and future business requirements to the architecture
AdvancedProfessional Cloud Security Engineer
Cloud DLP FPE with KMS Wrapped Keys
Applying encryption methods to various use cases.
Last updated Validated