Professional Data Engineer · 20% of the exam

Storing the data: free practice questions

5 sample questions from our 50-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Google Cloud exam.

1. You are designing the row-key strategy for a Bigtable table that stores user activity logs. The table will be used for both real-time queries (looking up specific users' recent activities) and batch analytics (scanning activity by time range). User IDs are randomly distributed UUIDs, and timestamps are from a continuous stream. Which row-key design minimizes hotspotting and supports both query patterns?

A. Use timestamp as the row-key prefix, followed by user ID (e.g., ts#userID). This ensures chronological ordering and prevents hotspot concentration on recent data.
B. Reverse the timestamp bytes and prepend user ID (e.g., userID#reversed_ts). This distributes writes evenly across tablets while allowing efficient range scans by time.
C. Use a hash of user ID as a prefix followed by timestamp (e.g., hash(userID)#ts). This distributes load across tablets and allows both user-specific and time-range queries.✓ Correct
D. Use user ID as the sole row-key with timestamp stored as a column family qualifier. This optimizes real-time lookups but will cause hotspotting during batch time-range scans.

Explanation

The correct answer is option 3: hashing the user ID prefix ensures writes are distributed evenly across Bigtable tablets (avoiding hotspots), while the timestamp suffix still allows efficient range scans by time within each user's partition. This balances both query patterns. Option 1 fails because timestamps increase monotonically; recent activity will concentrate writes on a single tablet (hotspotting). Option 2 reverses the timestamp but doesn't solve hotspotting if writes cluster around current time. Option 4 optimizes only for user lookups and causes severe hotspotting on recent timestamps during batch scans.

2. SELECT TWO statements that are accurate regarding BigQuery slots and on-demand pricing trade-offs.

A. BigQuery slots provide a fixed monthly cost regardless of query volume, making budgeting predictable✓ Correct
B. On-demand pricing scales linearly with TB scanned; a query scanning 1 TB costs twice as much as a 500 GB query
C. Slots are most cost-effective when annual query volume is stable and predictable✓ Correct
D. On-demand pricing is always cheaper for bursty, unpredictable workloads
E. Slots automatically pause unused capacity to reduce costs when queries aren't running
F. Switching from on-demand to slots requires full dataset migration and schema changes

Explanation

A and C are correct. A is true—slots provide a fixed commitment cost, making budget predictable. C is true—slots offer better ROI for steady-state workloads with consistent query volume. B is misleading in framing ('twice as much' phrasing is accurate but incomplete); on-demand is simpler but not directly compared to slots this way. D overgeneralizes; on-demand can be competitive for true bursty loads, but slots offer better unit economics for many sustained patterns. E is false—idle slots still incur their commitment cost; there's no automatic pausing. F is false—slots and on-demand are billing models, not requiring schema changes.

3. SELECT TWO or THREE statements that correctly describe BigQuery materialized view behavior and trade-offs.

A. Materialized views automatically refresh on a schedule (default 24 hours) and BigQuery seamlessly uses them to accelerate queries matching their structure✓ Correct
B. Querying a materialized view always costs less than querying the base table because results are pre-computed
C. Materialized views consume storage and incur refresh costs; they should only be created for queries that run frequently or at large scale✓ Correct
D. If a materialized view refresh fails, BigQuery automatically falls back to querying the base table, and the query still completes
E. Materialized views support custom refresh schedules (hourly, every 6 hours, etc.) and manual refresh via the API✓ Correct
F. Materialized views are incompatible with streaming inserts into the base table

Explanation

A is correct—materialized views refresh on a schedule (customizable, not just 24 hours) and BigQuery's query planner automatically uses them if the query matches their structure. C is correct—materialized views have storage and refresh costs; they're ROI-positive only for frequent or large-scale queries. E is correct—custom refresh schedules and manual refresh via API are fully supported. B is misleading; while materialized views can reduce query cost, they don't always (overhead of refresh, storage, and base table scans can exceed savings for low-frequency queries). D is partially true but overstates behavior—if a refresh fails, the view may become stale, and the planner might not use it, potentially failing the query or falling back to the base table (not seamlessly). F is false—materialized views work with streaming inserts; the view simply refreshes periodically to include new data.

4. A data engineer notices that a partitioned BigQuery table's partition count has grown to over 4,000 daily partitions, and queries that span more than 90 days are hitting the 10,000-partition scan limit warning. The table also has a large number of very small partitions (under 10 MB each) from low-volume historical days. Which TWO actions should the engineer take to address these issues? (Select TWO)

A. Convert the daily partitioning to monthly partitioning using a DATE_TRUNC expression to reduce partition count and merge small partitions.✓ Correct
B. Enable partition expiration to automatically delete partitions older than the query lookback window, reducing total partition count.✓ Correct
C. Increase the number of slots assigned to the project to handle cross-partition query execution.
D. Add a LIMIT clause to all queries to reduce the number of partitions scanned per query.
E. Enable automatic partition clustering to merge small partitions below the minimum size threshold.
F. Set a partition filter requirement (requirePartitionFilter) on the table to prevent unfiltered full-table scans.

Explanation

Option A is correct: switching from daily to monthly partitioning (DATE_TRUNC to MONTH) reduces partition count by ~30×, directly resolving both the partition limit warning and the small-partition problem, as monthly volumes will exceed the 10 MB minimum threshold. Option B is correct: if historical data beyond a defined retention window is not needed for queries, setting a partition expiration policy is the most automated way to reduce total partition count over time. Option C is wrong — slot count governs compute throughput, not the number of partitions that can be scanned; it does not address the root cause. Option D is wrong — LIMIT affects row output, not partition pruning; BigQuery still scans all referenced partitions before applying LIMIT. Option E is wrong — there is no 'automatic partition clustering to merge small partitions' feature in BigQuery; clustering is a sorting mechanism within partitions. Option F is useful for preventing costly full-table scans but does not reduce partition count or fix small partitions.

5. A manufacturing company ingests 50 GB of sensor data daily into Cloud Storage as compressed Parquet files. These files are processed immediately via Dataflow pipelines and rarely accessed again. The company has strict cost optimization requirements and plans to delete files after 90 days. Which Cloud Storage class(es) should be used, and why?

A. Standard class for all files—it offers the best cost-per-GB when files are accessed frequently enough to justify storage cost over deletion.
B. Standard class initially, then transition to Coldline after 30 days, and delete after 90 days—balancing low retrieval costs during the processing window with reduced storage cost for aged data.✓ Correct
C. Archive class—the lowest storage cost and acceptable for data that will be deleted within a short retention window.
D. Standard class initially, then transition to Nearline after 30 days—Nearline offers better durability guarantees than Archive for mission-critical sensor data.

Explanation

The correct answer is option 2 (Standard → Coldline transition). Manufacturing sensor data requires immediate processing (high access during initial 30 days, justifying Standard), then aging to Coldline (lower storage cost for data awaiting deletion at day 90). Standard class has no minimum storage duration and offers fast retrieval for the Dataflow pipeline. After 30 days, when access drops, transitioning to Coldline reduces storage cost substantially (roughly 50% of Nearline) while maintaining acceptable retrieval latency if reprocessing is needed. Option 1 is wrong because keeping all data in Standard for 90 days is wasteful when access patterns drop after initial processing. Option 3 (Archive) is wrong because Archive has a 365-day minimum storage duration—the company's 90-day retention would incur charges for the full year. Option 4 is wrong because Nearline does not offer better durability than Archive, and Nearline is more expensive than Coldline for this use case.

45 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free