Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Developer practice bank has an additional 608 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
You are designing a BigQuery data pipeline that ingests e-commerce transaction events to feed an analytics dashboard and an ML feature pipeline. Currently, transactions and line items are stored in separate normalized tables, causing expensive joins and high query latency.
You need to evolve the BigQuery schema while meeting these requirements:
Which schema design and evolution strategy should you implement?
Flat denormalization duplicates parent transaction columns across every child line-item row in a single table. Meanwhile, the ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE DDL statement is intended only for widening compatible scalar column types or updating existing STRUCT fields without altering their structure.
GROUP BY clauses. This triggers extensive network shuffling across BigQuery slots, degrading query performance compared to inline nested and repeated columns.RECORD (STRUCT) column. Running ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE with new subfields produces an Incompatible field change within RECORD column error.ARRAY<STRUCT> columns avoid.SET DATA TYPE requires STRUCT columns to retain the exact same subfields in the exact same order.bq update with a JSON schema file or the tables.patch API.The data had a simple one-to-one relationship without hierarchical child arrays, and schema evolution only involved widening compatible scalar column types (such as INT64 to NUMERIC) without adding nested subfields or requiring decoupled migrations.
A star schema stores metrics in a central fact table linked by foreign keys to normalized dimension tables. SELECT DISTINCT and EXCEPT DISTINCT are GoogleSQL operators used to deduplicate rows or compute set differences across query results.
RECORD (STRUCT) type is incompatible with SELECT DISTINCT, EXCEPT DISTINCT, INTERSECT, and UNION (UNION DISTINCT). Attempting to deduplicate or compare RECORD columns directly with these operators causes query failures.SELECT DISTINCT and EXCEPT DISTINCT cannot be applied directly to RECORD columns.You were migrating an existing relational warehouse in its initial lift-and-shift phase before denormalization, and your queries avoided applying DISTINCT or set operators directly to RECORD columns.
An ARRAY column in BigQuery stores a repeated list of elements of a single data type, while CREATE OR REPLACE TABLE overwrites an existing table definition and its contents in place with a new schema and query result or load definition.
ARRAY to contain another ARRAY directly (ARRAY<ARRAY<...>>). To model repeated multi-attribute entities like line items, you must wrap repeated values inside a RECORD (STRUCT) type, such as ARRAY<STRUCT<...>>, or nest arrays inside a STRUCT.CREATE OR REPLACE TABLE in place to alter schemas breaks the requirement for independent, zero-downtime migration. Overwriting production tables can invalidate active streaming writers, risk data availability, and break downstream ML pipelines expecting the prior schema.STRUCT containers (up to 15 levels of RECORD nesting).You were performing a one-time offline batch rebuild of a non-production table using valid ARRAY<STRUCT> types where no active upstream writers or downstream ML consumers depended on uninterrupted access.
Combining nested and repeated fields (RECORD with REPEATED mode, represented as ARRAY<STRUCT> in GoogleSQL) with facade views and API or CLI-based schema updates creates a resilient, high-performance BigQuery architecture. Internally, BigQuery organizes data using the Dremel model and Capacitor columnar storage, where nested and repeated columns store parent-child hierarchies inline within a single table.
ARRAY<STRUCT> keeps all child records colocated with their parent transaction in the same BigQuery slot. This eliminates costly table joins and avoids the network shuffling associated with GROUP BY operations on completely flattened tables.ALTER TABLE) cannot add new subfields inside an existing RECORD column. Using a JSON schema definition with bq update or the tables.patch API safely appends new optional (NULLABLE) subfields to an existing RECORD without rebuilding the table.UNNEST operations.Unlike flat tables, star schemas, or direct in-place table replacements, this strategy aligns directly with BigQuery's columnar architecture while protecting downstream ML feature consumers from breaking changes during schema evolution.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated