Prepare and test your skills

Prepare and test your skills

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 architect is migrating an enterprise e-commerce reporting workload from a normalized relational database to BigQuery. The existing relational model contains a parent Orders table and a child Order_Items table related by an order_id foreign key (a 1-to-many relationship).
The analytical workload frequently aggregates line items per order, but queries that join these multi-billion-row tables across compute slots suffer from significant data shuffling and high slot consumption. The architect has the following requirements:
JOIN operations.How should the architect model this schema in BigQuery?
Flat denormalization consolidates normalized tables into a single wide table by repeating parent attributes (such as order_id, customer_id, order_date, and billing_address) on every single child line-item record.
COUNT(DISTINCT) or window deduplication logic to prevent double-counting order header amounts.The target storage engine lacked support for complex types (such as legacy SQL databases without array or record support) or if downstream BI tools strictly demanded flat two-dimensional tabular data without UNNEST capabilities.
While flat denormalization eliminates multi-table joins, BigQuery's nested and repeated fields (ARRAY) provide the same join-elimination benefits without repeating parent attributes across multiple rows.
Orders and Order_Items) and cluster both tables on order_id to optimize relational JOIN operations.This design retains a traditional relational third-normal-form (3NF) design, keeping Orders and Order_Items as separate independent tables and applying table clustering on the order_id column to physically order and group records on storage blocks.
JOIN operations to combine order headers with line items.The parent and child entities were frequently modified independently with high-concurrency point updates, or if the relationship between orders and items was many-to-many across arbitrary dimensions.
Although clustering improves filter performance and co-locates blocks with similar keys, nested records inherently eliminate the join mechanism altogether by embedding child items in the parent row.
Orders table with a JSON type column to hold line item objects, and query the items using JSON_EXTRACT_ARRAY.The JSON data type in BigQuery is designed for storing semi-structured, schema-less, or evolving payloads where attributes are not known upfront or vary between rows.
JSON_EXTRACT_ARRAY or JSON path expressions requires runtime parsing and dynamic type casting, which is computationally more expensive than querying strongly typed STRUCT elements.STRUCT and ARRAY definitions allow Capacitor to enforce strict columnar encoding, compression, and fine-grained leaf column projection during reads.order.items.price) and standard array functions.The structure of the line items were arbitrary, constantly changing with polymorphic attributes, or if the ingestion pipeline required loading schema-free document payloads without schema maintenance.
When the child object's schema is well-defined and stable, standard ARRAY types are significantly preferred over JSON strings or JSON objects for performance, typing, and analytics.
Orders table with a column defined as an ARRAY of STRUCT (repeated RECORD) types to contain the line item details.In BigQuery, combining the ARRAY (repeated mode) and STRUCT (RECORD type) data types allows you to store multiple structured child objects directly inside a single parent row. This pattern creates a semi-normalized or nested schema that natively represents one-to-many parent-child relationships.
STRUCT into its own storage column. Queries referencing only order headers (e.g., order_date, customer_id) bypass the nested line item columns completely, minimizing bytes scanned and reducing query costs.LEFT JOIN UNNEST() or comma cross joins.This approach eliminates the performance bottlenecks of normalized relational joins in distributed MPP environments while preventing the storage bloat and duplicate aggregation errors associated with flat denormalization.
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