Exam Professional Cloud Database Engineer All Questions

View all questions & answers for the Professional Cloud Database Engineer exam

Exam Professional Cloud Database Engineer topic 1 question 15 discussion

Actual exam question from Google's Professional Cloud Database Engineer

Question #: 15
Topic #: 1

[All Professional Cloud Database Engineer Questions]

Your company uses Cloud Spanner for a mission-critical inventory management system that is globally available. You recently loaded stock keeping unit (SKU) and product catalog data from a company acquisition and observed hotspots in the Cloud Spanner database. You want to follow Google-recommended schema design practices to avoid performance degradation. What should you do? (Choose two.)

A. Use an auto-incrementing value as the primary key.
B. Normalize the data model.
C. Promote low-cardinality attributes in multi-attribute primary keys.
D. Promote high-cardinality attributes in multi-attribute primary keys.
E. Use bit-reverse sequential value as the primary key.

Show Suggested Answer

Suggested Answer: DE 🗳️

by fredcaram at Dec. 19, 2022, 4:11 p.m.

Comments

Submit Cancel

PrtkKA

Highly Voted 1 year, 10 months ago

Selected Answer: DE

Spanner needs high cardinality primary key to avoid hotspotting.

upvoted 7 times

...

PKookNN

Most Recent 12 months ago

Selected Answer: DE

I would go with D and E

upvoted 3 times

...

juliorevk

1 year, 3 months ago

Selected Answer: DE

https://cloud.google.com/spanner/docs/schema-design D because high cardinality means you have more unique values in the collumn. That's a good thing for a hot-spotting issue. E because Spanner specifically has this feature to reduce hot spotting. Basically, it generates unique values https://cloud.google.com/spanner/docs/schema-design#bit_reverse_primary_key

upvoted 3 times

...

nescafe7

1 year, 5 months ago

Selected Answer: BD

I agree with Hilab's comment below.

upvoted 2 times

...

felipeschossler

1 year, 8 months ago

Selected Answer: DE

D and E, the docs are below. D: https://cloud.google.com/bigtable/docs/schema-design#row-keys-avoid E: https://cloud.google.com/spanner/docs/schema-design#bit_reverse_primary_key

upvoted 4 times

...

Carpediem78

1 year, 8 months ago

high-cardinality

upvoted 1 times

...

PATILDXB

1 year, 9 months ago

Correct answers are D,E. Refer to the link which is self explanatory. https://cloud.google.com/spanner/docs/schema-design

upvoted 3 times

...

BenMS

1 year, 9 months ago

Selected Answer: DE

A - incrementing values are an explicitly documented antipattern B - normalising the schema does not specifically address hotspotting C - low cardinality values in the primary key will also cause hotspotting D - promoting high cardinality values in the primary key (i.e. moving them nearer the front of the value) is a recommended approach to reduce hotspotting E - bit-reversed keys are an explicitly recommended best practice

upvoted 3 times

...

dynamic_dba

1 year, 10 months ago

D, E. A is wrong because that will promote hotspots. C is wrong because low cardinality attributes being part of the key (particularly at the front multi-attribute keys) will also promote hotspots. That makes D correct by definition. This leave B or D at the other correct answer. The fact the new data has already been added to the database suggests the data model is already properly normalized. In addition, one of the techniques to reduce or eliminate hotspots is to bit reverse sequential values. It’s in Google’s docs here: https://cloud.google.com/spanner/docs/schema-design

upvoted 3 times

...

Hilab

1 year, 10 months ago

B. Normalize the data model. D. Promote high-cardinality attributes in multi-attribute primary keys. When designing a schema for Cloud Spanner, it is important to follow best practices to avoid hotspots and ensure optimal performance. Hotspots occur when too many requests are targeted at a single node or group of nodes, causing them to become overloaded and potentially impacting performance.

upvoted 2 times

Hilab

1 year, 10 months ago

Normalization is a recommended best practice in database schema design, including in Cloud Spanner. It involves breaking down large tables into smaller, more manageable tables that are linked together by relationships. This can help reduce duplication of data and improve performance by reducing the amount of data that needs to be read or written to the database. Promoting high-cardinality attributes in multi-attribute primary keys is also recommended in Cloud Spanner schema design. High-cardinality attributes are those that have a large number of distinct values, such as product IDs or customer IDs. Including these attributes in the primary key can help distribute data more evenly across nodes, reducing the likelihood of hotspots. Using an auto-incrementing value as the primary key or a bit-reverse sequential value as the primary key can result in hotspots, particularly if new data is being added at a high rate. These approaches can cause all new data to be inserted into a single node, leading to performance issues.

upvoted 2 times

...

Nirca

1 year, 10 months ago

Selected Answer: DE

"hotspots" in a database means that many IOPS (usually writes/updates) are happening on the same data-block; usually due to calling the same DATA. low cardinality => same value in the column ==> hotspots. High cardinality => different values in the column ==> avoiding hotspotting.

upvoted 4 times

...

zanhsieh

1 year, 11 months ago

Selected Answer: CE

CE A and D: WRONG. Anti-pattern Since the question specifically stated the hotspots cause by new SKUs and product catalog data added, so the goal would be: 1. The old data keeps distributed without any extra work needed. 2. Resolving the new data hot spots problem. It seems to me that SKU and product catalog are already normalized, so further normalize might touch the old data. This means B is out. If the new data already normalized, then it must have some high-cardinality attributes, e.g. SKU_id, and some low-cardinality attributes, e.g. category_id. So I picked low-cardinality attibutes in multi-attribute primary keys as C. I agreed with E as already Google recommended practice. Reference: https://cloud.google.com/spanner/docs/schema-design

upvoted 3 times

...