You are migrating an application that tracks library books and information about each book, such as author or year published, from an on-premises data warehouse to BigQuery. In your current…

Question

Options

• AKeep the schema the same, maintain the different tables for the book and each of the attributes, and query as you are doing today
• BCreate a table that is wide and includes a column for each attribute, including the author's first name, last name, date of birth, etc
• CCreate a table that includes information about the books and authors, but nest the author fields inside the author column
• DKeep the schema the same, create a view that joins all of the tables, and always query the view

• A
  9% (2)
• B
  4% (1)
• C
  83% (19)
• D
  4% (1)

Explanation

Option C is correct because BigQuery's recommended practice for denormalization is to use nested fields (STRUCTs) rather than traditional relational joins. BigQuery is a columnar, distributed system where JOINs are costly - nesting author data as a STRUCT inside the book table keeps related data co-located in storage, allowing queries to retrieve book and author information in a single scan without shuffling data across nodes.

Option A is wrong because maintaining separate tables and joining them replicates a relational pattern that performs poorly in BigQuery - distributed JOINs are expensive and go against Google's denormalization guidance. Option B (a fully flat wide table) moves in the right direction with denormalization, but doesn't leverage BigQuery's native STRUCT/ARRAY support, which provides better logical organization and handles one-to-many relationships more cleanly. Option D is wrong because a view is just a saved query - it doesn't change how data is physically stored, so the underlying JOIN cost remains unchanged at query time.

Memory tip: Think "BigQuery = NEST, not JOIN." When you see a relational schema migration question, the answer almost always involves flattening or nesting data - and when there's a clear parent-child relationship (book → author), nested fields beat a flat wide table because they preserve structure while eliminating the JOIN.

No community discussion yet for this question.