nerdexam
Google

PROFESSIONAL-DATA-ENGINEER · Question #256

Your company is implementing a data warehouse using BigQuery, and you have been tasked with designing the data model. You move your on-premises sales data warehouse with a star data schema to BigQuery

The correct answer is D. Partition the data by transaction date. Explanation/Reference: Star schema is supported by Big Query but is not the most efficient form, if you should design a schema from scratch google recommend to use nested and repeated fields. In this case, you already have done a migration of the schema and data, so it sounds goo

Submitted by kevin_r· Mar 30, 2026Designing data processing systems

Question

Your company is implementing a data warehouse using BigQuery, and you have been tasked with designing the data model. You move your on-premises sales data warehouse with a star data schema to BigQuery but notice performance issues when querying the data of the past 30 days Based on Google's recommended practices, what should you do to speed up the query without increasing storage costs?

Options

  • ADenormalize the data
  • BShard the data by customer ID
  • CMaterialize the dimensional data in views
  • DPartition the data by transaction date

How the community answered

(20 responses)
  • A
    5% (1)
  • B
    5% (1)
  • C
    10% (2)
  • D
    80% (16)

Explanation

Explanation/Reference: Star schema is supported by Big Query but is not the most efficient form, if you should design a schema from scratch google recommend to use nested and repeated fields. In this case, you already have done a migration of the schema and data, so it sounds good and with less effort to do partitioning by transaction date than to redesign the schema. And other aspect to consider is that this is a data warehouse, so is sure that there is an ETL process and if you change the schema you must adapt the ETL

Topics

#BigQuery partitioning#star schema#data warehouse optimization#query performance

Community Discussion

No community discussion yet for this question.

Full PROFESSIONAL-DATA-ENGINEER Practice