- A
Clustering can be used to improve query performance on frequently filtered columns.
Clustering sorts data within partitions for better filter performance.
- B
Partitioning in BigQuery can be based on a DATE, TIMESTAMP, or INTEGER column.
BigQuery supports these partitioning types.
- C
BigQuery requires explicit indexes on columns used in WHERE clauses.
Why wrong: BigQuery uses columnar storage and automatic pruning; no indexes needed.
- D
Materialized views in BigQuery are automatically refreshed based on base table changes.
BigQuery materialized views are incremental and server-managed.
- E
BigQuery supports unique constraints and foreign keys for data integrity.
Why wrong: BigQuery does not enforce constraints; it's schema-on-read.
Question 77 of 1,000
BigQuery Schema Considerations When Migrating from Oracle
This PCDE practice question tests your understanding of design and implement database schemas. Read the scenario carefully and evaluate each option against the stated constraints before committing to an answer. A key principle to apply: clustering. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.
A company is migrating a large Oracle Data Warehouse to BigQuery. The source schema includes many partitioned tables and materialized views. Which THREE considerations are important when designing the BigQuery schema?
Quick Answer
The answer is that BigQuery clustering, not indexing, is the key schema consideration when migrating from Oracle. This is correct because BigQuery uses a columnar storage model where clustering organizes data based on specified column values, reducing the amount of data scanned for filtered queries—effectively replicating the performance benefits of Oracle indexes without manual index management. On the Google Professional Cloud Database Engineer exam, this tests your understanding that BigQuery relies on clustering and partitioning for performance, not traditional indexing, and a common trap is assuming you can directly recreate Oracle indexes or that materialized views refresh instantly. Remember the memory tip: "Cluster, don't index—BigQuery scans less when you sort and slice."
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Clustering can be used to improve query performance on frequently filtered columns.
Options A, B, and D are correct. Clustering (A) improves query performance on frequently filtered columns by colocating data, similar to Oracle's partitioning but without manual index management. Partitioning in BigQuery (B) supports DATE, TIMESTAMP, or INTEGER columns, which is crucial for migrating Oracle's partitioned tables. Materialized views in BigQuery (D) are automatically refreshed when base tables change, aligning with Oracle's materialized view refresh mechanisms but with less administrative overhead. Options C and E are incorrect because BigQuery does not require explicit indexes and does not enforce unique or foreign key constraints, reflecting its schema-on-read approach.
Key principle: Clustering
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Clustering can be used to improve query performance on frequently filtered columns.
Why this is correct
Clustering sorts data within partitions for better filter performance.
Related concept
Clustering
- ✓
Partitioning in BigQuery can be based on a DATE, TIMESTAMP, or INTEGER column.
Why this is correct
BigQuery supports these partitioning types.
Related concept
Clustering
- ✗
BigQuery requires explicit indexes on columns used in WHERE clauses.
Why it's wrong here
BigQuery uses columnar storage and automatic pruning; no indexes needed.
- ✓
Materialized views in BigQuery are automatically refreshed based on base table changes.
Why this is correct
BigQuery materialized views are incremental and server-managed.
Related concept
Clustering
- ✗
BigQuery supports unique constraints and foreign keys for data integrity.
Why it's wrong here
BigQuery does not enforce constraints; it's schema-on-read.
Common exam traps
Common exam trap: answer the scenario, not the keyword
Candidates often assume BigQuery requires traditional database features like indexes (C) or constraint enforcement (E), but BigQuery relies on partitioning, clustering, and automatic materialized view refresh for performance and manageability.
Detailed technical explanation
How to think about this question
BigQuery's clustering automatically sorts data within partitions based on cluster column values, allowing for efficient block pruning and min/max metadata elimination. Unlike Oracle's partitioning, BigQuery partition pruning works on ingestion-time partitioning (DATE, TIMESTAMP, INTEGER columns) and can reduce query costs by scanning only relevant partitions. Materialized views in BigQuery use incremental refresh by default, updating automatically when base tables change, but they have limitations such as no support for self-joins or certain aggregate functions.
KKey Concepts to Remember
- Clustering
- Partitioning
- Materialized Views
TExam Day Tips
- Watch for words such as best, first, most likely and least administrative effort.
- Review why wrong options are wrong, not only why the correct option is correct.
Key takeaway
Clustering
Real-world example
How this comes up in practice
A cloud solutions architect for a retail company is evaluating services for a new workload. The correct answer here reflects best practice for the specific scenario described — not a general cloud recommendation. Clustering Cloud exam questions reward reading the constraint carefully: the same technology can be right or wrong depending on the use case.
What to study next
Got this wrong? Here's your next step.
Review clustering, then practise related PCDE questions on the same topic to reinforce the concept.
- →
Design and implement database schemas — study guide chapter
Learn the concepts, then practise the questions
- →
Design and implement database schemas practice questions
Targeted practice on this topic area only
- →
All PCDE questions
1,000 questions across all exam domains
- →
Google Professional Cloud Database Engineer study guide
Full concept coverage aligned to exam objectives
- →
PCDE practice test guide
How to use practice tests most effectively before exam day
Related practice questions
Related PCDE practice-question pages
Use these pages to review the topic behind this question. This is how one missed question becomes focused revision.
Building and Implementing CI/CD Pipelines for a Service practice questions
Practise PCDE questions linked to Building and Implementing CI/CD Pipelines for a Service.
Bootstrapping a Google Cloud Organisation for DevOps practice questions
Practise PCDE questions linked to Bootstrapping a Google Cloud Organisation for DevOps.
Applying Site Reliability Engineering Practices to a Service practice questions
Practise PCDE questions linked to Applying Site Reliability Engineering Practices to a Service.
Implementing Service Monitoring Strategies practice questions
Practise PCDE questions linked to Implementing Service Monitoring Strategies.
Optimising Service Performance practice questions
Practise PCDE questions linked to Optimising Service Performance.
Plan and manage database infrastructure practice questions
Practise PCDE questions linked to Plan and manage database infrastructure.
Define data structures and implement SQL for Business Intelligence practice questions
Practise PCDE questions linked to Define data structures and implement SQL for Business Intelligence.
Design and implement database schemas practice questions
Practise PCDE questions linked to Design and implement database schemas.
Monitor and optimize database performance practice questions
Practise PCDE questions linked to Monitor and optimize database performance.
PCDE fundamentals practice questions
Practise PCDE questions linked to PCDE fundamentals.
PCDE scenario practice questions
Practise PCDE questions linked to PCDE scenario.
PCDE troubleshooting practice questions
Practise PCDE questions linked to PCDE troubleshooting.
Practice this exam
Start a free PCDE practice session
Short sessions build daily habit. Longer sessions build exam-day stamina. Try a timed session to simulate real conditions.
FAQ
Questions learners often ask
What does this PCDE question test?
Design and implement database schemas — This question tests Design and implement database schemas — Clustering.
What is the correct answer to this question?
The correct answer is: Clustering can be used to improve query performance on frequently filtered columns. — Options A, B, and D are correct. Clustering (A) improves query performance on frequently filtered columns by colocating data, similar to Oracle's partitioning but without manual index management. Partitioning in BigQuery (B) supports DATE, TIMESTAMP, or INTEGER columns, which is crucial for migrating Oracle's partitioned tables. Materialized views in BigQuery (D) are automatically refreshed when base tables change, aligning with Oracle's materialized view refresh mechanisms but with less administrative overhead. Options C and E are incorrect because BigQuery does not require explicit indexes and does not enforce unique or foreign key constraints, reflecting its schema-on-read approach.
What should I do if I get this PCDE question wrong?
Review clustering, then practise related PCDE questions on the same topic to reinforce the concept.
What is the key concept behind this question?
Clustering
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Keep practising
More PCDE practice questions
- A company stores sensor data in BigQuery. They have a table 'sensor_readings' with columns: sensor_id, reading_time, val…
- Which THREE are valid considerations when designing BigQuery tables for BI reporting?
- A company uses Cloud Build to build Docker images. They want to cache intermediate layers to speed up subsequent builds.…
- A company is adopting GitOps for their GKE clusters using Config Sync. They need to meet the following requirements: (1)…
- A team is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The existing schema uses a large num…
- A DevOps engineer is setting up Docker credential helper for Artifact Registry on a Cloud Build worker. They want the bu…
Last reviewed: Jun 30, 2026
This PCDE practice question is part of Courseiva's free Google Cloud certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PCDE exam.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.