Google Professional Data Engineer Storage and Data Modeling

None, rowkey should be Device_Id+Date(reverse)

think is A, since “The most common query is for all the data for a given device for a given day”, rowkey should have info for both devcie and date.

the only option that follows the correct design principle is the C; the one that starts with device_id. If device_id#date were an option, it would be perfect.

Its very confusing but what I found is timebucket concept and day can be used instead of timestamp.

https://cloud.google.com/bigtable/docs/schema-design-time-series#time-buckets

The correct option should be device_id#Date as it will distribute the load while writing and also be performant while reading. C is the second best option in my understanding as device Id will ensure that data sent by all the devices on a day is distributed between nodes and will not create hotspot.

I would go to date#device_id. However, i don't find this combination. A should be then chosen.

A-https://cloud.google.com/bigtable/docs/schema-design-time-series?hl=es-419#use_tall_and_narrow_tables

showed up in my exam. picked A. passed the exam. still not sure it's correct though

A. Rowkey: date#device_id Column data: data_point

Explanation:

Optimized for Most Common Query: The most common query is for all data for a given device on a given day. This schema directly matches the query pattern by including both date and device_id in the row key. This enables efficient retrieval of the required data using a single row key prefix scan.
Scalability: As the number of devices and data points increases, this schema distributes the data evenly across nodes in the Bigtable cluster, avoiding hotspots and ensuring scalability.
Data Organization: By storing data points as column values within each row, you can easily add new data points or timestamps without modifying the table structure.

Answer C:
https://cloud.google.com/bigtable/docs/schema-design#time-based:~:text=Don%27t%20use%20a%20timestamp%20by%20itself%20or%20at%20the%20beginning%20of%20a%20row%20key%2C

c without any doubt

The right answer should be Reverse A, but since we don't have that, the best answer is C.

C. This schema is best suited for historical analysis of device data over time when the most common query is to retrieve all data for a **specific device** on a **given day**.

* **Row Key as `device_id`:** This allows for efficient retrieval of all data points related to a particular device in a single operation. Bigtable sorts data lexicographically by row key, so all data for a single device will be stored together.

* **Column with `date` and `data_point`:**
- Using `date` as a column name or part of the column qualifier allows you to quickly filter and retrieve data for specific date ranges.
- Storing `data_point` as the column value provides the actual data associated with each timestamp.

**Example:**

With this schema, a query to get all data for `device_12345` on `2023-12-20` would efficiently target the specific row key `device_12345` and fetch the relevant columns (with dates around `2023-12-20`).

C - the answer should the right answer.
Key is "all the data for a given device for a given day"
as in, Device first, and all the data + data points after.
This has nothing to do with Date-based search.

A - Key should be less granular item first to more granular item, there are more devices than date key (every 15 min)

the closest match to this in the provided options is:

C. Rowkey: device_id Column data: date, data_point

Thus, option C would be the best choice from the given option

It all comes down to the most common query

The best answer is "A".
Answer "D" is not as efficient or error-proof due to two reasons
1. You need to calculate hash at sender as well as at receiver end to do the comparison. Waste of computing power.
2. Even if we discount the computing power, we should note that the system is sending inventory information. Two messages sent at different can denote same inventory level (and thus have same hash). Adding sender time stamp to hash will defeat the purpose of using hash as now retried messages will have different timestamp and a different hash.
if timestamp is used as message creation timestamp than that can also be used as a UUID.

Transmitted data includes fields and timestamp of transmission.
So, hash value changes with re-transmission ==> Option B & D are wrong.

Why not the others:
A. GUIDs: You can only assign GUIDs if the source system supports it. Here, the system retransmits without changing the payload, so GUIDs won't help unless added upstream.
B. Compute hash and compare with all historical data: This is inefficient because you'd need to scan all previous entries every time.
C. Store each data entry as the primary key: Payloads can be large and variable, making them unsuitable as primary keys. Also, indexing on large text fields is costly.

Option D correctly specifies that the hash value must be stored in a database table (or a high-speed key/value store like Redis/Memcached) with an index applied to it. This allows the ingestion service to perform a single, fast primary key lookup using the hash.

The correct answer is: Assign global unique identifiers (GUID) to each data entry. This method allows highly efficient deduplication because each entry carries a unique ID, making it easy to identify and ignore duplicates during ingestion or later analysis.

Assigning a GUID to each entry ensures that every record—even across re-transmissions—has a unique identifier. At ingestion, the deduplication system can simply filter by GUID, drastically reducing the need for costly comparisons or hash storage. This approach avoids computational overhead and is scalable for frequent data transmissions.

Alternative methods like comparing hashes with all historical data, indexing the full data payload, or maintaining tables of hashes and metadata are less efficient due to increased computational, storage, or operational complexity. Using GUIDs is the simplest and most scalable solution for deduplication in this scenario.

This question is bullsh*t, because the correct answer would be "A", but for that you have to imagine that this GUID gets added to the original message. Instead, it says "assign". For me that sounds like when the message arrives, you assign it an ID, which is utterly useless for deduplication.

D is not efficient, but is the most efficient of the bunch here, plus, it's not that different from what "A" does. In fact, once you do "A", you still have to store that in a database and look it up, but it doesn't say that anywhere in the answer.

Me? I believe "A" it's a trap. But as we'll never get official confirmation, feel free to believe it's just a question where you assume "A" has all the magic things that make deduplication happen.

Maintain a table with hash can be painfull and we can write it in hystorical data.

timestamp may be changed while re-transmitting the same message

Answer-D , A- Assign GUIDs: This only works if the source system can guarantee unique IDs per original transmission. But if re-transmissions recreate the GUID, or if the system doesn't assign one, this fails.

Assigning a GUID (Globally Unique Identifier) at the SOURCE system to each payload ensures:
Idempotency: You can identify if a record has already been processed.
Efficiency: A simple lookup on the GUID is much faster than comparing hashes or checking entire payloads.
Scalability: Works well even when millions of records are transmitted.

Use hashing (answer B) for efficient, scalable, and fast deduplication — it's a well-established best practice in streaming and batch data pipelines.

Even If you defien GUID. How will the source know about it? he hold no such ID.

A is incorrect. how can you find duplicates if you assign a unique id to every record? The answer is D.

The most efficient way to deduplicate your inventory data would be:
D. Maintain a database table to store the hash value and other metadata for each data entry.
This approach is optimal because:

It creates a lightweight reference table that stores just the hash values and essential metadata (like timestamps) rather than the full payload data
Hash values can be quickly compared to identify duplicates without expensive full-data comparisons
The metadata can help with auditing and troubleshooting transmission issues
This solution scales well as your data volume grows

Option A (using GUIDs) doesn't address the retransmission scenario well, as new GUIDs might be generated each time. Option B requires comparing against all historical data, which becomes increasingly inefficient over time. Option C creates unnecessary storage overhead by using entire data entries as primary keys when only a hash value is needed for comparison.

Deduplicate data with retransmissions. Use a database table with hash

most obvious answer

Option D: Maintain a database table to store the hash value and other metadata for each data entry.

This approach is efficient and scalable. By storing a computed hash value (as a compact representation of the data) along with metadata, deduplication can be performed by comparing new entries with the stored hashes. This minimizes storage requirements and improves lookup efficiency.

Correct: A
It asks for scaling up which can be done in cloud sql, horizontal scaling is not possible in cloud sql
Automatic storage increase
If you enable this setting, Cloud SQL checks your available storage every 30 seconds. If the available storage falls below a threshold size, Cloud SQL automatically adds additional storage capacity. If the available storage repeatedly falls below the threshold size, Cloud SQL continues to add storage until it reaches the maximum of 30 TB.

Answer could be : A

Both Cloud Spanner and Cloud SQL does not have auto scaling feature natively. You need to build automation around it based on metrics.

Both Cloud SQL and Cloud Spanner supports SQL.

With Cloud SQL, you can go up to 10 TB of storage which also satisfies the other requirement.

Consistency - Of course, with Cloud SQL, you have single master and read replicas. So technically the data will be consistent across all instances so to speak.

The reason why I didn't choose Spanner is - There is no requirement for HA DR, multi region, secondary indexes, etc.. So I choose "A"

Cloud Spanner can automatically scale in both storage and compute.

Cloud SQL can scale up to 64To

Correct: C - Cloud Spanner
hint is automatically scale, CloudSQL dont scale

Cloud SQL
Cloud SQL can store up to 30 TB of data.
It offers limited scalability as per the lesser load.
You can easily work with MySQL code in Cloud SQL.
Cloud SQL is a cost-effective service.

Cloud Spanner:
Cloud Spanner is used to store more than 30 TB of data.
It provides better scalability and SLOs.
Cloud Spanner is an expensive service.
It provides strong transactional consistency.
It is built on Google Cloud’s dedicated network that ensures low latency, security, and reliability.

C. Cloud Spanner and an explanation of the other options:
Why Cloud Spanner is the best fit:
Fully managed service: Cloud Spanner is fully managed by Google, simplifying database administration.
Automatic scaling It handles scaling seamlessly, both horizontally and vertically.
Transactional consistency: Cloud Spanner is known for its strong transactional consistency, including globally distributed transactions.
Scalable up to 6 TB (and beyond): It easily accommodates your 6 TB requirement and can scale much larger if needed.
SQL Support: Cloud Spanner offers a familiar SQL interface.

Although the type of automatic scaling isn't specified, Cloud SQL does not allow for outright dynamic capacity auto scaling, so I believe the answer would be C

C. Cloud Spanner

Here's why:

Fully Managed: Cloud Spanner is a fully managed database service provided by Google Cloud.

Automatic Scaling: It automatically scales horizontally to handle increased workloads and data volumes.

Transactional Consistency: Cloud Spanner provides strong transactional consistency with support for ACID transactions.

Scalability: It can easily scale up to and beyond 6 TB while maintaining performance and consistency.

SQL Queries: Cloud Spanner supports SQL queries, making it compatible with existing SQL-based analytics and applications.

I would go to C.

All the arguments below can essentially be boiled down to two questions. 1) is Cloud SQL fully managed? (yes), 2) Does it autoscale? It depends, is the answer. The question is horrifyingly worded, as it comes down to an ambiguity coinflip. I'm going with C, it feels like it's a better fit.

Did not refer to global, thus not spanner. A is correct

Transactional consistency: spanner provides strong consistency across rows, regions, and continents.

Answer : A
Cloud SQL and Cloud Spanner are the options for the questions. But here as per requirement they don't need horizontal scaling, they want manager SQL instance and it should support 6 TB of storage. Cloud SQL can support up to 64 TB of storage
https://cloud.google.com/sql/docs/quotas#:~:text=Cloud%20SQL%20storage%20limits,core%3A%20Up%20to%203%20TB.

Not horizontal scaling is required, cloud SQL will work for 10 TB

i will go with A, since the question doesnt specifically say it should be scaling horizontally

"Able to scale up to 6 TB" -seems to be the key
it looks like autoscaling is related to storage - possible in case of Cloud SQL

B.
The question is focused on designing storage for very large files, with support for compression, ANSI SQL queries, and parallel loading from the input locations. This can be met using GCS for storage and Bigquery permanent tables with external data source in GCS.

Should be A

It's not B because Permanent linked tables it's not recommanded for a large data

The task here is to design a storage for very big FILES, not tables. And only then, these big files should be queries.
So it's B.

Here's why:
Cloud Dataflow is a fully managed service for stream and batch data processing. It’s ideal for transforming large text files into a more efficient format like Avro, which supports schema evolution and is optimized for BigQuery ingestion.
Avro is a row-based storage format that supports compression and is well-suited for BigQuery.
BigQuery is Google Cloud’s serverless, highly scalable, and cost-effective multi-cloud data warehouse that supports ANSI SQL.
Using compressed Avro allows for parallel loading into BigQuery, which is a Google-recommended best practice for performance and cost-efficiency.
Why not the others?
B: Using Cloud Storage with permanent external tables in BigQuery is possible, but it’s less performant and flexible than loading the data into native BigQuery storage.
C: Gzip-compressed text files are not as efficient for parallel processing or schema enforcement as Avro.
D: Cloud Bigtable is not designed for SQL queries; it’s a NoSQL wide-column store, and not suitable for ANSI SQL workloads.

Avro is a Google-recommended format for BigQuery because it supports schema evolution, efficient compression, and parallel processing. Using Cloud Dataflow ensures scalable transformation, and storing the data in BigQuery allows for optimized ANSI SQL queries.

Storing large text files with SQL support**

Avro in BigQuery (A) supports compression and efficient queries.

For me it would make more sense to store the data directly in BQ, but A does not make sense, because why compress to Avro if you dont store the avro files and directly saves the data to BQ?
You are not using the compression

BigQuery now has physical storage, which makes storage cost fairly cheap on BigQuery with compression. I would go with A.

Gros fichier + sql = GCS + Bigquery

copy to gcs and use external tble in bq

Should be A.

Check this link for the advantage of load Avro data to BigQuery https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro#advantages_of_avro

"""The Avro binary format:
* Is faster to load. The data can be read in parallel, even if the data blocks are compressed.
* Doesn't require typing or serialization.
* Is easier to parse because there are no encoding issues found in other formats such as ASCII.
When you load Avro files into BigQuery, the table schema is automatically retrieved from the self-describing source data."""

B makes sense

1. Store Avro files in GCS
2. Query them in BigQuery (federated tables)

Answer is B.
The requirements are:
- storage for compressed text files
- parallel loads to SQL tool

AVRO is a compressed format for text files, which makes it possible to load chunks of a very large file in parallel to BigQuery.

gzip files are seamless in GCS though, but cannot load in parallel to BQ.

Correct Answer:
A. Transform text files to compressed Avro using Cloud Dataflow. Use BigQuery for storage and query.

This option offers several advantages:

- Transforming the text files to compressed Avro using Cloud Dataflow allows for parallel processing of the input data, improving the efficiency of the pipeline.

- Compressing the data in Avro format further reduces the storage space required and improves data transfer performance.

- Storing the data in BigQuery supports ANSI SQL queries and allows for easy querying of the data.

- BigQuery is a fully-managed data warehousing solution, it's scalable and can handle large datasets and concurrent queries, so it's suitable for large text files.

Designing storage solution, not data warehousing -> So Cloud Storage.

Support compression -> just use Avro
Parallel load -> refers to upload from input locations, NOT download.

Load in parallel using -m flag for gsutil cp

https://cloud.google.com/storage/docs/uploads-downloads#console

A. Use Cloud Spanner to configure a single region instance initially, and then configure multi-region Cloud Spanner instances after securing funding.

When you create a Cloud Spanner instance, you must configure it as either regional (that is, all the resources are contained within a single Google Cloud region) or multi-region (that is, the resources span more than one region).

You can change the instance configuration to multi-regional (or global) at anytime.

Although A is good, but concerning about the cost. Then D will be much more suitable

Native JDBC → eliminates Bigtable (B & C)
Cost optimization initially → eliminates Spanner (A)
Global expansion later → Cloud SQL can add read replicas

I go to D. Cloud SQL is usually used for web application (CRM) (https://cloud.google.com/blog/topics/developers-practitioners/your-google-cloud-database-options-explained?hl=en)

Although A is good, but concerning about the cost. Then D will be much more suitable

Although A is good, but concerning about the cost. Then D will be much more suitable

I think is D

The best for Web app is Cloud SQL, and Spanner is the best for data more than 30GB

A - This option allows for optimization for cost initially with a single region Cloud Spanner instance, and then optimization for global presence and performance after funding with multi-region instances.
Cloud Spanner supports native JDBC drivers and is horizontally scalable, providing very high performance. A single region instance minimizes costs initially. After funding, multi-region instances can provide lower latency and high availability globally.
Cloud SQL does not scale as well and has higher costs for multiple high availability regions. Bigtable does not support JDBC drivers natively. Therefore, Spanner is the best choice here for optimizing both for cost initially and then performance and availability globally post-funding.

Spanner has some limitations with JDBC. Maybe the quetion wants to help us tp choose Cloud SQL

Answer D:
Cost effective transactional database Cloud SQL. Spanner is good case for data more than 30 GB

B and C has no sense because of the driver.
D looks like a good option, but HA it's not to improve performance or global presence:
The purpose of an HA configuration is to reduce downtime when a zone or instance becomes unavailable. This might happen during a zonal outage, or when an instance runs out of memory. With HA, your data continues to be available to client applications.
So the best option is A.

https://cloud.google.com/spanner/docs/jdbc-drivers
Ans A
https://cloud.google.com/spanner/docs/instance-configurations#tradeoffs_regional_versus_multi-region_configurations
The last part of the question makes it easy

Yes Spanner is expensive , but the question expresslty states that "after securing funding you want to have a global presence" the word globally is repeatedly stated there.
Answer is A.

Spanner is expensive, they haven't mentioned the size of db... optimize for cost then option is Cloud SQL which cost effective and highly available in case of multi region.

A is the best option. It is globally scalable and it also meets the cost goal as it says that initially it will be configurated as single region wich is cheaper than multi region.

Spanner is expensive can't be A

Would choose D

Spanner still support JDBC
https://cloud.google.com/spanner/docs/jdbc-drivers

BCD makes more sense to me. Its for sure not unsupervised, since locations are in the data already. Reinforcement also doesn't fit, as there no AI and no interactions with data from the observer.

As wrote by RP123
B - Not labelled as Fraud or not. So Unsupervised.
C - Clustering can be done based on location, amount etc.
D - Location is already given. So labelled. Hence supervised.

No sense in predicting the location in a dataset that already contains it.

The three most applicable machine learning applications for analyzing bank transactions are

BCD makes sense, but I now agree that BCE is the correct answer.
Say the model predict a location, guessing US or Sweden are both wrong when the answer is Canada. But US is closer, the distance from the correct location can be used to calculate a reward. Through reinforcement learning (E) the model could guess a location with better accuracy than supervised (D).

Ans: B, C and D
i) Fraudulent transaction, is nothing but anomaly detection which falls under Unsupervised.
ii) All transactions can be categorized using type etc - clustering algorithm.
iii) Using location as a label, supervised classification can be developed to predict location.

BCD makes more sense. B, C should not be controversial. For D vs E, in this use case D fits better than usage of reinfocement learning

BCD makes more sens to me

Anwer: BCD
Things to understand:
Supervised learning will only predict the column that is labeled. In this case, there is not Fraud or not Fraud column inside which he will train on. So Option A, wrong.
option D: Supervised learning for column (transaction location) is possible as column exist to train on.
Option C: Custering N-type is possible and also an unsupervised learning to make cluster of similar pattern.
Option B: Its a weaker point here, User should be able to know which clusters are fraud in history. As it doesn't give enough information about past analysis whether user knows potential frauds or not. Ignore this option, if question asked for 2 right options only.

Why would you need to predict a location...

C and D are good for sureCliquez pour utiliser cette solution et E, F wrong for sure.

Then, to choose between A and B. Both options indicate that we know which transactions are fraudulent and which are not. Indeed, in order to use unsupervised classification to determine the characteristics of fraudulent transactions, we must already know which ones are fraudulent, either because all transactions in the dataset are fraudulent, or because a variable allows us to identify them. If all transactions were fraudulent, this would probably have been specified in the statement. It is therefore more likely that the "type of transaction" variable can be used to distinguish fraudulent transactions from others.

In this case, we have a target variable to predict, enabling us to build interpretable supervised models to understand the typology of fraudulent transactions. I therefore opt for A, C and D

Options B, E, and F are not as suitable for the given scenario:

B. Unsupervised learning to determine which transactions are most likely to be fraudulent.

Unsupervised learning, while useful for anomaly detection, might not be as effective for fraud detection without labeled data indicating which transactions are fraudulent.
E. Reinforcement learning to predict the location of a transaction.

Reinforcement learning is more suitable for scenarios where an agent learns to make decisions through trial and error, which doesn't seem to align with predicting transaction locations.
F. Unsupervised learning to predict the location of a transaction.

Unsupervised learning typically doesn't involve predicting specific values (like location) without labeled data for training.
In summary, A, C, and D are the most appropriate machine learning applications for investigating the provided bank transactions dataset.

Answer: BCD

Location is already given as attribite so what value is served with predicting location?

A, B: Data features without the definition of fraudulent, so we can not obtain the answer even if using the unsupervise learning.
C: Kmeans solve this.
D: logistic regression. Just put the location into target.
E: Give the positive reward when the model predicts correct location.
F: Same as C. Use all features but locations, and use similarity to predict new data.

Absolutely

makes more sense

Serve the last state data directly from Cloud SQL to the API.
Here's why this option is most suitable:

BigQuery for Analytics: BigQuery is an excellent choice for storing and analyzing large datasets like your 10 PB of historical product data. It is designed for handling big data analytics efficiently and cost-effectively.

Cloud SQL for Last State Data: Cloud SQL is a fully managed relational database that can effectively handle the storage of the last known state of products. Storing this subset of data (about 10 GB) in Cloud SQL allows for optimized and faster query performance for your API needs. Cloud SQL can comfortably handle the requirement of up to 1000 QPS with sub-second latency.

Separation of Concerns: This approach separates the analytics workload (BigQuery) from the operational query workload (Cloud SQL). This separation ensures that analytics queries do not interfere with the operational performance of the API and vice versa.

D. 1. Store the historical data in BigQuery for analytics.
2. In a Cloud SQL table, store the last state of the product after every product change.
3. Serve the last state data directly from Cloud SQL to the AP

This approach leverages BigQuery's scalability and efficiency for handling large datasets for analytics. BigQuery is well-suited for managing the 10 PB of historical product data. Meanwhile, Cloud SQL provides the necessary performance to handle the API queries with the required low latency. By storing the latest state of each product in Cloud SQL, you can efficiently handle the high QPS with sub-second latency, which is crucial for the API's performance. This combination of BigQuery and Cloud SQL offers a balanced solution for both the large-scale analytics and the high-performance API needs.

Why not A? Because BQ API 100 reqs per second for API method. Other possible limits do not meet the 1000 QPS requirement. Yes, max number of tabledata.list reqs per second is 1000 but we won't always call tabledata.list all time.
https://cloud.google.com/bigquery/quotas#api_request_quotas

A is the most plausible option - Cloud SQL can not retrieve results out with 1 second latency as the requirement here is, with BQ MV"s that could be a possibility as its pre-computed.

Why A? Because:

Materialized View for API: A materialized view in BigQuery pre-computes the last known state of each product. This ensures that your API can quickly retrieve the latest product information without needing to query the entire historical dataset.
BigQuery for API Serving: BigQuery can handle high query volumes with low latency, meeting your requirement of 1000 QPS with sub-second latency.
Cost-Effectiveness: This solution avoids the need for a separate database like Cloud SQL, minimizing costs and management overhead.

Why not D:
While Cloud SQL is a good option for transactional workloads, it's not as cost-effective or scalable as BigQuery for analytical queries on 10 PB of data. It might also not be the ideal choice for serving high-volume API requests with low latency.

Why not A:
Serving data directly from BigQuery to the API may not meet the low latency requirements for high QPS operations, as BigQuery is optimized for analytical queries rather than transactional workloads.

Materialized views are precomputed views that periodically cache the results of a query for increased performance and efficiency. Materialized views can optimize queries with high computation cost and small dataset results. https://cloud.google.com/bigquery/docs/materialized-views-intro#use_cases
https://cloud.google.com/bigquery/docs/materialized-views-intro

Why D is the best choice:

Cost-Effective Analytics: BigQuery excels at handling large datasets (10 PB) and complex analytical queries. Its columnar storage and massively parallel processing make it ideal for analyzing historical product data.
High-Performance API: Cloud SQL provides a managed relational database service optimized for transactional workloads. It can easily handle the 1000 QPS requirement with low latency, ensuring fast API responses.
Separation of Concerns: Storing historical data in BigQuery and the last known state in Cloud SQL separates analytical and transactional workloads, optimizing performance and cost for each use case.

Option D

BigQuery = data warehouse that is optimized for querying and analyzing large datasets using SQL. Can easily process petabytes of data.
Cloud SQL = designed for transactional workloads and traditional relational database use cases, such as web applications, e-commerce platforms, and content management systems.

Option D is the right one, compared to option A, Cloud SQL is more efficient and cost effective for the amount of time the data needs to be accessed by the api

A. 1. Store the historical data in BigQuery for analytics.
2. Use a materialized view to precompute the last state of a product.
3. Serve the last state data directly from BigQuery to the API.

- New Key Creation: A new Cloud KMS key ensures a secure replacement for the compromised one.
- New Bucket: A separate bucket prevents potential conflicts with existing objects and configurations.
- Default CMEK: Setting the new key as default enforces encryption for all objects in the bucket, reducing the risk of unencrypted data.
- Copy Without Key Specification: Copying objects without specifying a key leverages the default key, simplifying the process and ensuring consistent encryption.
- Old Key Deletion: After copying, the compromised key can be safely deleted.

Wrong:
A - rotating external key doesn't trigger re-encryption of data already in GCS: https://cloud.google.com/kms/docs/rotate-key#rotate-external-coordinated
C - Setting key during copy doesn't take care of objects that are later uploaded to the bucket, that will still use the default key

passing the cloud KMS key in the command is more secure and explicit
https://cloud.google.com/storage/docs/encryption/customer-managed-keys?utm_source=chatgpt.com#key-replacement

If no key is specified, and the bucket's default CMEK key is used, there's a risk that some objects might fall back to Google-managed encryption, especially if misconfigured

Option D

The correct answer is D. Rotating the key does not seem to re-encrypt:

In the event that a key is compromised, regular rotation (!!) limits the number of actual messages vulnerable to compromise (!!).
If you suspect that a key version is compromised, disable it and revoke access to it as soon as possible.
Source: https://cloud.google.com/kms/docs/key-rotation#why_rotate_keys

I don't understand why only Matt select A

https://cloud.google.com/sdk/gcloud/reference/kms/keys/update

This seems to do the job, am I wrong ?

Definitely A

Minimize cost. https://cloud.google.com/alloydb?hl=en

AlloyDB offers superior performance, 4x faster than standard PostgreSQL for transactional workloads. That does not come without cost.

D is clean

D is clean

AlloyDB for PostgreSQL is a fully managed, PostgreSQL-compatible database service offered by Google Cloud. It's designed for high-performance transactional and analytical workloads and offers performance and scalability benefits over standard PostgreSQL. While it meets the requirement of not changing the database system in a broad sense (as it's PostgreSQL-compatible), it's a different service than standard PostgreSQL and might introduce a level of complexity and cost beyond simply migrating to Cloud SQL for PostgreSQL.

The correct answer is B. Migrate your workloads to AlloyDB for PostgreSQL.
Here's why:
Your requirements are:

Run both transactional and analytics workloads in a single database
Stay with PostgreSQL (don't change database systems)
Minimize cost and complexity

AlloyDB for PostgreSQL is specifically designed for this scenario - it's fully PostgreSQL-compatible but optimized for both transactional and analytical workloads. It offers:

PostgreSQL compatibility (minimizing migration complexity)
Enhanced analytics capabilities with column store indexes
Better performance for mixed workloads

While B looks good too but It’s more expensive than Cloud SQL and better suited when you need advanced analytics and heavy transactional performance. I think it is an Overkill if you're looking to minimize cost/complexity which the question states.

The question specifies that the analytical needs need to reside in a single database. This can't be done using Cloud SQL. The database that supports all of that is AlloyDB that's why I will go with the B answer.

"support analytics needs" -> columnar storage -> AlloyDB

Cloud SQL natively supports PostgreSQL
AlloyDB for PostgreSQL is a great option if you're specifically looking for high performance in both transactional and analytical workloads. However, it might be more complex and costly than Cloud SQL

B
AlloyDB es la mejor opción para modernizar el entorno, mantener compatibilidad con PostgreSQL y manejar tanto cargas transaccionales como analíticas en un único sistema, minimizando costos y complejidad.

In real life clearly how performant it needed to be would be a massive factor. AlloyDB is more expensive (see https://cloud.google.com/alloydb/pricing, vs https://cloud.google.com/sql/pricing), but when they say "minimise cost" is that per query, or is it per year assuming similar instance size. There's no way for us to know, we have to guess. I'm guessing AlloyDB, as the question seem to be telegraphing that, but it could just as easily be CloudSQL postgres based on the cheaper costs. We simply cannot know.

Because AlloyDB is optimised for hybrid transactional and analytical processing (HTAP), meaning you can run both transactional workloads and analytics on the same database with excellent performance.

AlloyDB

It's a little complicated, considering it says minimize costs (Cloud SQL) and run transactional workloads and support analytics needs (AlloyDB). I consider B. because you can minimize costs in the long-term instead of doing it immediately with possible extra costs in the long-term. Think about it

AlloyDB is for large scale and more expensive. We want to minimize cost and complexity, so the answer is D.

B. Migrate your workloads to AlloyDB for PostgreSQL.

They currently have transactional data stored on-premises in a PostgreSQL database and they want to modernize their database that supports transactional workloads and analytics .If they select cloud Sql (postgreSQL) it will minimize the cost and complexity.
and for analytics purpose they can create federated queries over cloudSql(postgreSql)
https://cloud.google.com/bigquery/docs/federated-queries-intro
This approach will minimze the cost

You want to optimize the data schema + Machine Learning --> Bigquery. So A

A; ML should be BQ; stored all transactional data (not use for transactional) so should BQ

BQ for storing ML tables

bq storing for machine learning

The docs for Datastore call out user profiles as a use case. https://cloud.google.com/appengine/docs/legacy/standard/go111/datastore#what_its_good_for

"for a machine learning-based"
This is the why. So, the objective of storege here is not (ACID) opeartion at all. there is only two requirment here:
Store the data.
Do machine learning.

Cloud SQL is perfect for ACID opeartion which the question didn't hint. You only want to store the data, improve data schame for ML. I think alot of people here are misunderstanding the question.

Cloud SQL doesn't support ML so the right answer will be BigQuery

Transactional db -> CloudSQL

Since they need transactional data i would say B
However thinking on machine learning is better A

The answer should be Cloud SQL because it is a relational database suitable for transactional data.

BigQuery is for analytics and querying - not suitable for transactional workload.
Bigtable is for unstructured and time series data.
Datastore is a nosql document database for semi structured data.

For a machine learning-based food ordering service that requires optimised storage of transactional data, Google Cloud BigQuery is a suitable choice

ithin Google Cloud, the database that most readily allows for data schema optimization is BigQuery; it provides features like schema auto-detection, columnar storage, and the ability to manually define your schema to tailor it for efficient querying and analysis of large datasets.

Cloud SQL is the best choice for your application as it provides relational database management and is optimized for storing transactional data with SQL querying capabilities. It is well-suited for managing user profiles, account information, and orders, ensuring data integrity, and supporting complex queries necessary for the food ordering service.

Use BigQuery for analyzing aggregated data (e.g., predicting food trends or training ML models).
Use Cloud Bigtable for large-scale real-time recommendation engines if needed in the future.
Use Firestore for dynamic, semi-structured data with real-time updates if you need flexibility over transactional consistency.
Cloud SQL strikes the right balance for this use case due to its support for structured data, transactions, and easy integration with other GCP services.
So B. CloudSQL is the right answer

Cloud SQL can store transactional data not Big Query. Big Query is an analytical service.

Easy implement data schema + Machine Learning model in Big Query

database will be used to storage all transactional data.... I think that you need a relational database for that, then federated tables to bigquery to analysis.

Answer should be D.

Obviously the answer is D

D is the right answer

Google Cloud Datalab is a powerful interactive tool for data exploration, analysis, and machine learning.

My answer is Google Cloud Datalab, but since that service has already been discontinued, I question whether a problem like this would actually be asked on the actual exam.

D sounds good for me

Hash Value for Deduplication: By computing a hash value for each data entry, you create a unique identifier based on the content of the data. This allows you to efficiently identify duplicates, as entries with identical content will have the same hash value.

Storing Hash Value and Metadata: Maintaining a database table that includes the hash value and other relevant metadata (like the timestamp of transmission) allows for quick lookups and comparisons. This way, when new data is received, you can check if an entry with the same hash value already exists.
Assign global unique identifiers (GUID) to each data entry: While GUIDs are unique, they do not inherently identify duplicate content. Two transmissions of the same data would have different GUIDs.

D sounds good for me

Agree with D

D. Deploy Google Cloud Datalab to a virtual machine (VM) on Google Compute Engine.

Google Cloud Datalab is a powerful interactive tool for data exploration, analysis, and machine learning. By deploying it to a VM on Google Compute Engine, you can provide her with a robust and scalable environment where she can work with large datasets, create labeled datasets, and perform data analyses efficiently.

Option A (running a local version of Jupyter on her laptop) might not be sufficient for very large datasets, and her laptop's limited power could still be a bottleneck.

Option B (granting access to Google Cloud Shell) is useful for running command-line tools but may not provide the interactive and visual capabilities she needs.

Option C (hosting a visualization tool on a VM on Google Compute Engine) is beneficial for visualization tasks but does not cover the full spectrum of data analysis and machine learning tasks that Google Cloud Datalab offers.

D - as it is a FULL set up, not a shell that is needed...

Nowadays it should be similar to D, deploy a Vertex workbench

As per Options , Correct Answer should be D. ie Datalab
However Datalab is no longer used in GCP (Deprecated in Sep2022), It is Vertex AI or Deep Learning VM Images

I think.
Answer is D

Datalab is deprecated. This question should appear in the exam.

Should be D, because Cloud shell alone does not provide access to what they need.
Nowadays is Vertex AI, but still, correct answer is D

Google Cloud Datalab is now Vertex AI. So, letter D make more sense.

Vote for 'D' - Store multiple delimited values in each row key. (But avoid starting with Timestamp)

"Row keys to avoid"
https://cloud.google.com/bigtable/docs/schema-design

A. Use a row key of the form <timestamp>.
---> Incorrect, because google says don't use a timestamp by itself or at the beginning of a row key.
B. Use a row key of the form <sensorid>.
--->Incorrect, because google says Include a timestamp as part of your row key.
C. Use a row key of the form <timestamp>#<sensorid>.
---> Incorrect, because google says don't use a timestamp by itself or at the beginning of a row key.
D. Use a row key of the form >#<sensorid>#<timestamp>.
---> Correct answer, because of option A,B,C reasons.
- Timestamp isn't by itself, neither at the beginning.
- Timestamp is included.

Reference: https://cloud.google.com/bigtable/docs/schema-design#row-keys

D is correct

Looks like D is the best option
Reference: https://cloud.google.com/bigtable/docs/schema-design#time-based

D. Use a row key of the form <sensorid>#<timestamp>.

By using the sensor ID as the prefix in the row key, you can achieve better distribution of data across Bigtable tablets. This can help balance the workload and prevent hotspots in the table. Additionally, placing the timestamp after the sensor ID allows you to perform range scans for a specific sensor and retrieve data efficiently within a time frame.

Option C (using a row key of the form <timestamp>#<sensorid>) can work for some use cases but may not be as efficient for range scans when you want to retrieve data for a specific sensor within a time range.

Option A (using a row key of the form <timestamp>) may lead to hotspots and inefficient range scans because it doesn't consider sensor IDs.

Option B (using a row key of the form <sensorid>) is not optimal because it doesn't allow for efficient time-based filtering and could lead to uneven data distribution in Bigtable.

D is right
Best practices of bigtable states that rowkey should not be only timestamp or have timestamp at starting. It’s better to have sensorid and timestamp as rowkey.

Reference:
https://cloud.google.com/bigtable/docs/schema-design

#<sensorid>#<timestamp> ------> low cardinality # high cardinality
This is current Bigtable Best Practice (to avoid Hotspots on the inserts)

Discard:
A -> timestamp unique id could not be unique in the case that sensors transmit data at the same time.
B -> sensorId repeated id for messages coming from the same sensor
C -> a bad performance choice

D -> BEST CHOICE. Each time BigTable looks for data in a table it does a scan and sort operations. By starting each unique id by sensorId it will make it easier to group and sort data since it has the lowest cardinality
https://cloud.google.com/bigtable/docs/schema-design#general-concepts

as I look at https://cloud.google.com/bigtable/docs/schema-design#row-keys
asia#india#bangalore
asia#india#mumbai
they didn't have # ahead of this first value.
asia#india#bangalore OR #asia#india#bangalore
Are both valid?

ANSWER: D

Answer is D

Ans: D

Correct D

Should be D
Reverse of timestamp even better but no options for that.
Also changing sensor ID if they are in sequential to hash or changing data to bits even better.
Idea is not to use timestamp or sequential ID as first key.

The correct answer is D.
Refer to the link https://cloud.google.com/bigtable/docs/schema-design for Big table schema design.

C is not the right answer becuase
Timestamps
If you often need to retrieve data based on the time when it was recorded, it's a good idea to include a timestamp as part of your row key. Using the timestamp by itself as the row key is not recommended, as most writes would be pushed onto a single node. For the same reason, avoid placing a timestamp at the start of the row key.

For example, your application might need to record performance-related data, such as CPU and memory usage, once per second for a large number of machines. Your row key for this data could combine an identifier for the machine with a timestamp for the data (for example, machine_4223421#1425330757685).

answer would be D to avoid hotspoting..

correct: D
why not C? Using the timestamp by itself as the row key is not recommended, as most writes would be pushed onto a single node. For the same reason, avoid placing a timestamp at the start of the row key. https://cloud.google.com/bigtable/docs/schema-design#row-keys

A. Cloud SQL (30TB)

A as limit is now 30 TB for Cloud SQL

C. Cloud Spanner
📌 Breakdown of Requirements:
20 TB transactional data → A large-scale, transactional, and reliable database is needed.

From an operational system → Implies high consistency, availability, and performance.
Moving to GCP → Should use a cloud-native, fully managed GCP service.

Cloud SQL storage limit: dedicated core up to 64 TB.

two keywords: Transactional data, 20 TB

Scalability: Cloud Spanner is designed to handle large volumes of data, making it suitable for a 20 TB database. It can scale horizontally and vertically to accommodate growing data needs.

Global Distribution: Cloud Spanner allows you to distribute data globally for low-latency access across regions, which can be advantageous for operational systems.

Strong Consistency: It provides strong transactional consistency, which is important for operational systems that require ACID compliance.

SQL Support: Cloud Spanner supports SQL, which is a familiar query language for developers.

While Cloud SQL, Cloud Bigtable, and Cloud Datastore have their use cases, Cloud Spanner is better suited for larger databases with strong consistency requirements, making it a suitable choice for migrating a 20 TB operational system database to GCP.

Cloud SQL, upto 64 TB now, you can always call GCP for increasing the quota though !!

Cloud SQL is generally better for OLTP, and Cloud SQL is up to 64 TB now.
https://cloud.google.com/sql/docs/quotas#storage_limits

"move its operational system transaction data from an on-premises database to GCP". Cloud SQL may be plug-and-play

Not 100% in favour of A, Should i recommend my client Cloud SQL, when they are coming to me with 20TB already 30TB is limit, its transactional data, which i can't compromise. I will propose cloud spanner. There is nothing mentioned that they want to save cost.

A. Cloud SQL

With the given requirements A. Cloud SQL is more than sufficient. Don't try to overthink scenarios like what if it grows.. what if there's additional requirement in future.. what if this what if that.. just look at the question and see the stated requirement. If there are more than one answer try to see which is simple and doesn't come with extra frills.

A
65 TB now in Nov 2022

it is already 20 TB at the moment, and you probably want to change the database because the capacity of your current storage solution is not enough. Then you decide to change it to Cloud SQL(up to 30 TB) which may not increase much capacity? I am not sure about the answer but A looks weird imho.

Cloud SQL can store 64 Tb, but in the intial set up the operation are 20tb. It will reach the limitation soon if you choose Cloud SQL

Depends.. I mean, C is correct if the exam is not updated. A is correct if the exam is updated. So ... kinda in catch 22 situation ...

Hi everyone, Can i purchase this exam? is it worthable?

This is special case , plese Take a look carefully the below link and read at last paragraph at the bottom of this comment, let everyone share idea, We will go with B, C
https://cloud.google.com/bigtable/docs/schema-design#time-based

Don't use a timestamp by itself or at the beginning of a row key, because this will cause sequential writes to be pushed onto a single node, creating a hotspot.

If you usually retrieve the most recent records first, you can use a reversed timestamp in the row key by subtracting the timestamp from your programming language's maximum value for long integers (in Java, java.lang.Long.MAX_VALUE). With a reversed timestamp, the records will be ordered from most recent to least recent.

Option B using reverse timestamp only, this is not the answer.
the right answer should be using the index and revers timestamp as the row key.

So, Option D is the only answer, because not A,B,C .

When designing for Bigtable, particularly for time-series data like stock prices, your row key design directly impacts read efficiency and data locality. You want to:

Access latest data quickly

Avoid hotspots

Keep queries simple and fast

Vote A, as explained by Augustax:

Agree A is the best option because:
1. Multi-tenancy solution
2. As with any timestamp, avoid starting a row key with a reversed timestamp so that you don't cause hotspots.

Agree A is the best option because:
1. Multi-tenancy solution
2. As with any timestamp, avoid starting a row key with a reversed timestamp so that you don't cause hotspots.

I don't think any answer is correct.
A lot people upvote for B, but based on https://cloud.google.com/bigtable/docs/schema-design#time-based, "As with any timestamp, avoid starting a row key with a reversed timestamp so that you don't cause hotspots."

Why other options are not as suitable:

A and B (One table for all indices): Storing all indices in a single table can lead to performance issues as the table grows larger. It also makes it harder to scale individual indices independently.
C (Timestamp as row key): Using a regular timestamp would place the most recent data at the end of the table, making it less efficient to retrieve the latest prices.

Option B and Option D are both from my point of view correct. It depens on the situation. If there is need to get the information from each stock index, then D is more suitable. Otherwise B.

1. Reverse Timestamp for most recent stock prices
2. Having different table for each stock is more efficient, improves the query performance and option B doesn't specify stock in row key.

Row keys that start with a timestamp (irrespective reversed or not) causes sequential writes to be pushed onto a single node, creating a hotspot. If you put a timestamp in a row key, precede it with a high-cardinality value (index in our case) to avoid hotspots.

The ideal option would be: "use the index and reversed timestamp as the row key design".

B is a correct answer because "you need to access only the most recent stock prices"

"If you usually retrieve the most recent records first, you can use a reversed timestamp in the row key by subtracting the timestamp from your programming language's maximum value for long integers (in Java, java.lang.Long.MAX_VALUE). With a reversed timestamp, the records will be ordered from most recent to least recent."
https://cloud.google.com/bigtable/docs/schema-design#time-based

B. One unique table for all indices, reverse timestamp as row key:

A single table for all indices keeps the structure simple.
Using a reverse timestamp as part of the row key ensures that the most recent data comes first in the sorted order. This design is beneficial for quickly accessing the latest data.
For example: you can convert the timestamp to a string and format it in reverse order, like "yyyyMMddHHmmss", ensuring newer dates and times are sorted lexicographically before older ones.

Correct Is B

https://cloud.google.com/bigtable/docs/schema-design#row-keys - If you usually retrieve the most recent records first, you can use a reversed timestamp
B it is.

A is the answer

the answer relieves on whether the application need to access the whole indexes at the same time or not. If yes then is B, if no is A.

in mind the answer is yes, so B makes more sense: I retrieve all the list at the same time.

https://cloud.google.com/bigtable/docs/schema-design#time-based If you usually retrieve the most recent records first, you can use a reversed timestamp in the row key by subtracting the timestamp from your programming language's maximum value for long integers (in Java, java.lang.Long.MAX_VALUE). With a reversed timestamp, the records will be ordered from most recent to least recent.

C is correct because this option provides the least amount of inconvenience over using pre-specified date ranges or one table per clinic while also increasing performance due to avoiding self-joins.
A is not correct because adding additional compute resources is not a recommended way to resolve database schema problems.
B is not correct because this will reduce the functionality of the database and make running reports more difficult.
D is not correct because this will likely increase the number of tables so much that it will be more difficult to generate reports vs. the correct option.
https://cloud.google.com/bigquery/docs/best-practices-performance-patterns
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#explicit-alias-visibility

A is incorrect because adding space won't solve the problem of query performance.
B is incorrect because there is nothing related to the report generation which is specified and sharding tables on date ranges is not a good option as it will create many tables.
C is CORRECT because the statement says "the scope of the project has expanded. The database must now store 100 times more patient records". As the data increases there would be difficulty in managing the tables and querying it. Hence creating different table is correct as per the need.
D is Incorrect as it Partitions on each clinic. We have to adjust the database design so that it performs optimally when generating reports.
Also nothing is specified for generation of reports in the required statement.

Because you can optimize the storage of each table; furthermore, you will avoid self-joins.

answer is C, the problem here is the self-join (avoid self-join if possible) on a Denormalized table. So the solution is to Normalize

Normalizing the database into separate Patients and Visits tables, along with creating other necessary tables, is the best solution for handling the increased data size while ensuring efficient query performance and maintainability. This approach addresses the root problem instead of applying temporary fixes.

C is the most suitable solution for this situation. It provides a better way for scalability and monitoring. B has a constraint on predefined date range, which is usually not suitable for reporting.

Normalization is a technique used to organize data in a relational database to reduce data redundancy and improve data integrity. Breaking the patient records into separate tables (patient and visits) and eliminating self-joins will make the database more scalable and improve query performance. It also helps maintain data integrity and makes it easier to manage large datasets efficiently.

Options A, B, and D may provide some benefits in specific cases, but for a scenario where the project scope has expanded significantly and there are performance issues with self-joins, normalization (Option C) is the most robust and scalable solution.