Exam Professional Data Engineer topic 1 question 59 discussion

B. Cloud SQL would be the most appropriate choice for the online retailer in this scenario. Cloud SQL is a fully-managed relational database service that allows for easy management and analysis of data using SQL. It is well-suited for applications built on Google App Engine and can handle the transactional workload of an e-commerce application, as well as the analytical workload of a BI tool.

A. "They want to use only a single database for this purpose" is a key requirement. You can use BigQuery for transactions, though it is not efficient. You can not use CloudSQL for analytics. So it is probably BQ.

Cloud SQL seems to fit the best. It supports transactions and can be used to run queries and do analytics. BigQuery is good for the analysis part but it's not good for managing transactions. If the question needed a database just to store the data for analysis it would be ok. But if we want to update single transactions or add them row by row, then it's not good. BigQuery is not made to support an application. It's a DW. BigTable is can not carry transactions over multiple rows and is better for large scale analytics jobs. Also we should pick it for use-cases with high throughput/low latency requirements. Seems redundant.

Big Query because of analysis

Should be bigtable

I think BigQuery makes sense here. It works for transactions too.

Transactional Data need to written first by application before it could be analysed so cloudsql.

Both BigQuery and Cloud Bigtable are valid options for this use case, but BigQuery is better suited for this specific scenario where the retailer needs to manage and analyze large amounts of data from multiple datasets using a BI tool. BigQuery is a fully-managed, cloud-native data warehouse that enables super-fast SQL queries using the processing power of Google's infrastructure. It can handle large, complex datasets and is well-suited for both transactional and analytical workloads. It can also handle data from multiple datasets and can be integrated with other Google Cloud services, such as Dataflow, Dataproc and Looker for BI analysis. While Cloud Bigtable is also a good option for this use case as it is a highly scalable and performant NoSQL database that is well-suited for handling large amounts of data and high-write loads. It is not as good as BigQuery for analytical workloads and it may not be as well-suited for this specific scenario where the retailer needs to manage and analyze large amounts of data from multiple datasets using a BI tool.

The Community is choosing Answer B - Cloud SQL, as per the question. However when they explain - they're speaking about BQ[????] So is it BigQuery or Cloud SQL?

https://cloud.google.com/bigquery/docs/partitioned-tables

It needs support for transaction so cloud sql is the choice of database and with Bigquery we can still analyze cloud sql data via federated queries https://cloud.google.com/bigquery/docs/reference/legacy-sql

Most important part of question is transaction (RDBMS) strong ACID property database. Second part analysis of data, yes possible using any BI tool its possible with RDBMS db.

B is the answer. https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries BigQuery Cloud SQL federation enables BigQuery to query data residing in Cloud SQL in real time, without copying or moving data. Query federation supports both MySQL (2nd generation) and PostgreSQL instances in Cloud SQL.

C and D are not able to work with BI directly, so discard. A: It's the best option for BI for awful for transactions B: it's the best option for transaction, and works for BI, so this must be the answer

BigQuery for Analytics and BI

Cloud Sql is Used to store Transactional Data and supports Sql Transactions. Where as Big Query is used for Analytics.

Cloud SQL supports transactions as well as analysis through a BI tool. Firestore/Datastore does not support SQL syntax typically needed to do analysis done by a BI tool. BigQuery is not suitable for transactional use case. BigTable does not support SQL. It's A.