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.
An analytics team is troubleshooting severe latency regressions in analytical aggregation queries running on a Cloud Spanner Enterprise instance. Operational transactions continue to write updates, while reporting queries scan millions of rows.
The team investigates the performance diagnostics and observes the following:
SPANNER_SYS.QUERY_STATS_TOP_* shows that the AVG_COLUMNAR_READ_SHARE metric for these queries is near 0%.SELECT *.Which actions should the team take to diagnose and mitigate this retrieval bottleneck?
Cloud Bigtable is a NoSQL database optimized for high-throughput operational and time-series writes. While Bigtable supports HDD storage for cost-effective cold data storage, HDD delivers significantly lower read throughput (up to 500 rows per second per node) compared to SSD (up to 17,000 rows per second per node).
The scenario involved archiving tens of terabytes of append-only, latency-insensitive log data accessed only through batch processing jobs.
Cloud Spanner Enterprise Edition provides integrated hybrid transactional and analytical processing (HTAP) using its built-in columnar engine, eliminating the need to offload data to third-party storage layers.
LIMIT clause on all aggregation queries and disable background compaction to conserve CPU utilization.LIMIT Clauses and Background Compaction DoA LIMIT clause restricts the maximum number of rows returned to a client. Background compaction in Cloud Spanner is an essential database maintenance subsystem responsible for merging mutations, cleaning deleted rows, and building columnar block representations.
LIMIT to an aggregation query (such as SUM or COUNT) without subquery filtering either produces incorrect statistical results or fails to stop the underlying table scan if the grouping operation must process all rows before limiting.AVG_COLUMNAR_READ_SHARE near 0%.LIMIT does not reduce the bytes scanned by the aggregation operator.The application required sampling only the first few matching operational rows from a sorted index lookup where early termination was acceptable.
To optimize analytical queries on Spanner, the engine requires background compaction to convert row-based PAX data into column-wise structures so subsequent scans can achieve maximum parallelism.
A secondary index in Cloud Spanner creates an alternate sorting key for fast lookups. When an index does not include all requested columns (a non-storing index), Spanner must execute a Cross Apply operator to join each row from the index back to the base table to fetch missing columns.
STORING clause) are ineffective for wide analytical range scans.The workload consisted of highly selective, point-lookup queries retrieving a tiny fraction of rows matching a specific non-primary key filter predicate.
Query hints like @{FORCE_INDEX=...} should be avoided unless verified against query execution plans, as forcing an index on scan-bound aggregations often degrades performance.
Cloud Spanner's columnar engine provides accelerated scan performance for analytical workloads on operational data by storing single-column runs across blocks instead of colocating full rows. When diagnosing queries using SPANNER_SYS.QUERY_STATS_TOP_* and the query execution plan, an AVG_COLUMNAR_READ_SHARE near 0% indicates that Spanner is falling back to scanning row-oriented PAX (Ressi) storage rather than columnar storage. Furthermore, a high-latency Table scan node confirms that the query is scan-bound.
SELECT * forces Spanner to retrieve every column in the schema. In columnar layouts, reading all columns diminishes columnar scan advantages because multiple individual column files must be scanned and reconstructed. Restricting projections to specific columns (e.g., SELECT customer_id, SUM(order_total)) allows Spanner to read strictly the necessary contiguous column blocks.AVG_COLUMNAR_READ_SHARE indicates the newly loaded or heavily churned data has not yet completed compaction into columnar runs. Allowing automatic compaction to complete will naturally raise the columnar read share and improve scan speed by up to 200x.Because the diagnostic metrics clearly show a scan-bound profile with unpopulated columnar blocks, pruning projection lists and letting background compaction finalize columnar structures aligns precisely with the database engine's architecture without introducing operational overhead.
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