Google Professional Data Engineer BigQuery and Analytics

Correct A: A . Now it is feasible to provide table level access to user by allowing user to query single table and no other table will be visible to user in same dataset.

D should be the answer

Why Option D is the Best Fit
In BigQuery, simply creating a view isn't enough to grant access to the underlying data. If a user has access to a view but not the source table, the query will fail. An Authorized View solves this by allowing the view itself to "authorize" access to the source data, even if the user doesn't have direct access to those tables.

obviously D. A is not cumbersome and difficult to manage

The question was created at the time when it was not possible to share data on table level (dataset was the only option). At that time D was possible only. Right now A is feasible as well.

Authorized views provide a centralized way to manage access. You define the data each team can see in a view and then grant access to that view. This is much easier to maintain and update than managing permissions on individual tables.
Why not D? - Option D suggests creating separate datasets for each team and using authorized views within those datasets. This adds unnecessary complexity and overhead.
You would need to manage multiple datasets.You would need to grant the authorized views access to the original dataset.

Table level access could be done in bigquery.

Recommended approach

Should be D.

https://cloud.google.com/solutions/migration/dw2bq/dw-bq-data-governance
When you create the view, it must be created in a dataset separate from the source data queried by the view. Because you can assign access controls only at the dataset level, if the view is created in the same dataset as the source data, your users would have access to both the view and the data.
https://cloud.google.com/bigquery/docs/authorized-views
This approach aligns with the Google Cloud best practices for data governance, ensuring that users can only access the data intended for them without having direct access to the source tables. Authorized views serve as a secure interface to the underlying data, and by placing these views in separate datasets per team, you can manage permissions effectively at the dataset level.

but the question said that all data are copied into one dataset. so it should be C

A is the best answer for security as stated in the documentation - https://cloud.google.com/bigquery/docs/row-level-security-intro#comparison_of_authorized_views_row-level_security_and_separate_tables

A is a better fit than D for this case

Authorized Views: Authorized views in BigQuery allow you to control access to specific rows and columns within a table. This means you can create views for each team that restrict access to only the data relevant to that team.
Single Dataset: Keeping all the authorized views and the underlying data in the same dataset simplifies management and access control. It avoids the need to create multiple datasets, making the permission management process more straightforward.

Option A (assigning data viewer access at the table level) would not provide the granularity you need, as it would allow users to see all tables in the dataset. This does not align with the requirement to restrict access based on team membership.

https://cloud.google.com/bigquery/docs/share-access-views#:~:text=the%20source%20data.-,Authorized%20views,-should%20be%20created

For best practice, Option D is bettern than others.

[A] is correct if it is for individual table
However, in practice we normally do [C] as most of the time, the view is a JOIN of a few tables or a subset of the table (some columns removed)

Answer A, Trick here is, if question is not asking for data level Access such as some rows or columns, don't go for authorized view in that case i would go for C. If it's Table level request only in question, then A is simple answer

[C]
I found the correct answer based on a real case, where Google's Solutions Architect team decided to move an internal process to use BigQuery.
The related doc is here: https://cloud.google.com/blog/products/data-analytics/moving-a-publishing-workflow-to-bigquery-for-new-data-insights

Vote B - "Some recently streamed rows might not be available for table copy typically for a few minutes. In rare cases, this can take up to 90 minutes"
https://cloud.google.com/bigquery/docs/streaming-data-into-bigquery#dataavailability

Option a. You shouldn´t really delete from staging and append only means BQ doesn´t have to worry about updates etc.

3 hours so the streaming buffer is definitely written to disk

Read the MaxNRG comment

deleting data from my point of view is not a good practice to build datawarehouse solutions. So, C and D are excluded.
according to the official documentation, the updating/merging process could last till 90 minutes. 3 hours could be enough.

An append-only staging table ensures that all incoming data is captured without risk of data loss or overwrites, which is crucial for maintaining data integrity in a streaming ingestion scenario.
Three-Hour Update Interval:

Updating the production table every three hours strikes a good balance between minimizing the latency of data availability for reporting and reducing the frequency of potentially resource-intensive update operations.
This interval is frequent enough to keep the production table relatively up-to-date for reporting purposes while ensuring that the performance of both ingestion and reporting processes is not significantly impacted.
Frequent updates (like every ninety minutes or every thirty minutes) could introduce unnecessary overhead and contention, especially if the dataset is large or if there are complex transformations involved.

Not C nor D. Moving and deleting:
Deleting data from the staging table every 3 or 30 minutes could lead to data loss if the production table update fails, and it also requires more frequent and potentially resource-intensive operations.

Options C and D cause rebuilding of the staging table, which slows down ingestion, and may lose data if errors occur during recreation.

A or B

Neither. In the current scenario, DataStream (a new google resource) captures the CDC data and uses Dataflow to Replicate the changes to big query.

Vote B
C : the doc says streaming data can be used up to 90 minutes not 3 hours
B : correct , insert staging table first with append
and use merge from staging into production table

C
Following common extract, transform, load (ETL) best practices, we used a staging table and a separate production table so that we could load data into the staging table without impacting users of the data. The design we created based on ETL best practices called for first deleting all the records from the staging table, loading the staging table, and then replacing the production table with the contents.

When using the streaming API, the BigQuery streaming buffer remains active for about 30 to 60 minutes or more after use, which means that you can’t delete or change data during that time. Since we used the streaming API, we scheduled the load every three hours to balance getting data into BigQuery quickly and being able to subsequently delete the data from the staging table during the load process.
Building a script with BigQuery on the back end
https://cloud.google.com/blog/products/data-analytics/moving-a-publishing-workflow-to-bigquery-for-new-data-insights

D : read more on Streaming inserts and timestamp-aware queries as the following link
it is the same as this question exactly, but it is quite similar.
https://cloud.google.com/blog/products/bigquery/performing-large-scale-mutations-in-bigquery

read carefully in the content below.
When using timestamps to keep track of updated and deleted records, it’s a good idea to periodically delete stale entries. To illustrate, the following pair of DML statements can be used to remove older versions as well as deleted records.

You’ll notice that the above DELETE statements don’t attempt to remove records that are newer than 3 hours. This is because data in BigQuery’s streaming buffer is not immediately available for UPDATE, DELETE, or MERGE operations, as described in DML Limitations. These queries assume that the actual values for RecordTime roughly match the actual ingestion time.

Either C or D But When will we delete stale data on staging table ? Every xxx????
https://cloud.google.com/architecture/database-replication-to-bigquery-using-change-data-capture#prune_merged_data

D. Have a staging table that moves the staged data over to the production table and deletes the contents of the staging table every thirty minutes.

Ans D
D. Have a staging table that moves the staged data over to the production table and deletes the contents of the staging table every thirty minutes.

D. Have a staging table that moves the staged data over to the production table and deletes the contents of the staging table every thirty minutes.

Option A is the correct choice because it retains all the original columns and specifically addresses the issue of null values in ‘feature1’ by replacing them with zeros, without altering any other columns or performing unnecessary calculations. This makes the data ready for use in BigQueryML without losing any important information.

Option C is not the best choice because it includes the EXCEPT clause for the price and square_feet columns, which would exclude these columns from the results. This is not desirable since you need these columns for the machine learning model to predict the price per square foot

Correct answer is C.
It both replace NULL with 0 and pass price per square foot of real estate.

You shouldn't remove features from the dataset based on an assumption of what the ML Engineer wants. Clean the column that's needed and allow the ML Engineer to do any further processing if he so desires.

"You are preparing data that your machine learning team will use to train a model using BigQueryML" this detail makes answer C more eligible than answer A

I would go with "C" becuse of this: "Another feature" so that implies there is a first feaure which would be doin calculation to prdict the price.

🎯 Final Answer: A is the most correct answer based on what is explicitly required.

Option C would only be correct if the question said something like:
“You need to prepare data that includes the price_per_sqft target column...”

But it doesn’t — it only states what the model will predict, not what you are to calculate.

It's the only one that:
- computes the price per square foot of real estate. Note that "the training data has a column for the price and a column for the number of square feet" only.
- fills NAs with zeros

Not worded well but the best answer I would think would be C since it has price per square foot but I understand the argument for A.

Let's step away from GCP for a minute. If price & square feet are already features, then there is no need for BigQuery ML to do any prediction. You'd want to predict price per square foot based on other fields like location, weather, etc. The first sentence in the question indicates that a machine learning team is preparing the data for model training. Therefore, C's query a fantastic preparation step. If this query for any other use case, then A would've been the answer.

To people saying you need price and square footage to predict price/sq. feet: you do not need an ML model then, you need a calculator. C is the correct answer because you want to predict price/sq. feet from the features EXCLUDING price and sq. footage.

Gemini told me C

Here's why it's the best of the limited choices:

Calculates price_per_sqft: It includes the calculation for the target variable your model needs.
Handles Nulls: It uses IFNULL(feature1, 0) to replace nulls in feature1 with 0, similar to COALESCE.
Most Comprehensive: While it excludes the original price, square_feet, and feature1 columns, it still retains any other columns that might be present in the training_data table.

it should be C.

This must be C, though the wording isn't great. If price and square foot are included in the data, they are either intended to be the target, in which case you need to create that target as per C, or if they are genuinely features, you DO NOT need a machine learning model. If you already know price and square feet, price per square foot is just price/ft2. You don't need ML to predict that, it's just a division. The only context this makes sense in is if they mean "price and square foot are the target, and feature1 is the predictive feature", which means C is correct. The removing nulls from feature1 and the creation of price per square foot is C.

Font Cloude 3.5 and GPT 4o, in theoy is better to keep the less amount of features, then price_per_sqft and feature1 cleaned is the best option

EXCEPT means it won't select that column.

C is not a valid answer. You are introducing a redundant variable, that could be valid, but removing from the dataset 2 variables that exactly influence in the predictions you are trying to make.

C is not a valid answer. You are introducing a redundant variable, that could be valid, but removing from the dataset 2 variables that exactly influence in the predictions you are trying to make.

A. Disable caching by editing the report settings.

By default, Google Data Studio 360 caches data to improve performance and reduce the amount of queries made to the data source. However, this can cause visualizations to not show data that is less than 1 hour old, as the cached data is not up-to-date.

To resolve this, you should disable caching by editing the report settings. This can be done by following these steps:

Open the report in Google Data Studio 360.
Click on the "File" menu in the top left corner of the screen.
Select "Report settings" from the dropdown menu.
In the "Report settings" window, scroll down to the "Data" section.
Toggle off the "Enable cache" option.
Click the "Save" button to apply the changes.
Disabling caching ensures that the data shown in the visualizations is always up-to-date, but it may increase the query load on the data source and affect the report's performance. Therefore, it's important to consider the trade-off between performance and data accuracy when making this change.

A. Disable caching by editing the report settings.

By default there's a cach in google data studio (looker studio). So you should disable it

To ensure that Google Data Studio visualizations show the most recent data, you should disable caching within Data Studio's report settings.

A. Disable caching by editing the report settings.

By default, Google Data Studio 360 caches data to improve performance and reduce the amount of queries made to the data source. However, this can cause visualizations to not show data that is less than 1 hour old, as the cached data is not up-to-date.

Disabling caching in the report settings will ensure that the visualizations are not using cached data and will reflect the most up-to-date information from your Google BigQuery data source. This will allow your report to show data that is less than 1 hour old. Caching is often used for performance optimization, but it can result in delays in displaying real-time or near-real-time data, so disabling it is the appropriate action in this case.

disable caching in report setting will get the issue resolved

The solution from the site is perfect.

what is relevant here is to uncache Data Studio

A. Disable caching by editing the report settings.

Data Studio 360 uses caching to speed up report loading times. When caching is enabled, Data Studio 360 will only show the data that was present in the data source at the time the report was loaded. To ensure that the visualizations in your report are always up-to-date, you should disable caching by editing the report settings. This will force Data Studio 360 to retrieve the latest data from the data source (in this case BigQuery) every time the report is loaded.

Option B is incorrect as it would only disable caching in BigQuery, but it wouldn't affect the caching in Data Studio 360, so the visualizations would still not show the latest data.

Option C and D will not help as the data is not being updated in Data Studio 360, it's just the cache that needs to be updated.

I'm confused, is it possible that the cache is in BigQuery level? and the looker just get the cache from bigquery

Based on the doc, you can refresh the report using refresh button on the report, not the browser's refresh button. So the answer is A.

On my opinion it's C, because Data Studio doesn't support the Real-Time dashboard updates, so it means that if caching will be disabled, user will be forced to update the dashboard manually, otherwise report will be stuck on the data from the last update. According to documentation https://support.google.com/looker-studio/answer/7020039?hl=en#zippy=%2Cin-this-article, if we want to keep the data fresh we need to setup caching with minimum value of 15 minites - it means that data in the report will be updated automatically wvery 15 minutes, if cache will be disabled completely then the report will be stuck until we will manually update it. So, tbh for me it doesn't make sense to disable cache.

C.
Since the data is not always stale. When it is, click on refresh button. Document also says the same
Refresh report data manually
Report editors can refresh the cache at any time:

View or edit the report.
In the top right, click More options. and then click RefreshRefresh data .
This refreshes the cache for every data source added to the report.

https://support.google.com/looker-studio/answer/7020039?hl=en#zippy=%2Cin-this-article

A. should be correct. after disabled the cache, it will retrieve data every time.

Same question from a Cloud guru and answer was C. The wording is slightly different in the documentation but still, the idea is that you can trigger a manual refresh

None, the actual `bigquery-public-data.noaa_gsod.gsod*`
with back ticks at the beginning and at the end.

A very common reason for SQL statements with wildcard tables to fail in BigQuery is not enclosing the table name pattern in backticks (`)

Focus only on what the question asks. This question contains several errors in the query, but the question addresses only one of them. The table name. Option D.

just read this guys : https://cloud.google.com/bigquery/docs/querying-wildcard-tables

B This is the correct syntax. The wildcard * is outside any quotes or string delimiters. This tells BigQuery to query all tables that match the pattern gsod* within the noaa_gsod dataset.

Big Query does not use quotations while fetching data from the table :
example: select * from project-id.dataset_name.table_name; is the syntax

let's forget the fact that in BQ is used ` instead than ' which retrieves an error in any case. ` is called backquote, backtick, or left quote while ' is simply an apostrophe. Let's consider ' to be ` in every answer, since moderators could have not been aware of such when they had received the question.

Option D (assuming to have backticks)

Refer: https://cloud.google.com/bigquery/docs/querying-wildcard-tables
The following query is NOT valid because it isn't properly quoted with backticks:
```
#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
max
FROM
# missing backticks
bigquery-public-data.noaa_gsod.gsod*
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX = '1929'
ORDER BY
max DESC
```

Reference: https://cloud.google.com/bigquery/docs/querying-wildcard-tables
The wildcard table name contains the special character (*), which means that you must enclose the wildcard table name in backtick (`) characters. For example, the following query is valid because it uses backticks:


#standardSQL
/* Valid SQL query */
SELECT
max
FROM
`bigquery-public-data.noaa_gsod.gsod*`
WHERE
max != 9999.9 # code for missing data
AND _TABLE_SUFFIX = '1929'
ORDER BY
max DESC

Few might go with the Option B which will be a blunder because of the below reason.

While querying the tables or views with the name, it is optional to surround with the backticks. But while querying the list of tables with Wild card character, it is must to surround with the backticks.

We can get the Syntax error: Expected end of input but got "*" with the below query

SELECT * FROM bigquery-public-data.noaa_gsod.gsod*
WHERE _TABLE_SUFFIX = "2024"

So, option D might be the correct one, provided if there is a typo.

IN option D, there is differert ' ` on first and last. That's why right option is second.

bigquery-public-data.noaa_gsod.gsod* also works

Agree with others - Option D

D. 'bigquery-public-data.noaa_gsod.gsod*` is the right answer with 1 typo

Answer is 'D'
Reference : https://cloud.google.com/bigquery/docs/wildcard-table-reference

Enclose table names with wildcards in backticks
The wildcard table name contains the special character (*), which means that you must enclose the wildcard table name in backtick (`) characters.

bigquery-public-data.noaa_gsod.gsod* works

should be B, the backtick at D answer is wrong ' instead of `

- BigQuery allows you to specify job priority as either BATCH or INTERACTIVE.
- Batch queries are queued and then started when idle resources are available, making them suitable for non-time-sensitive workloads.
- Running ad-hoc queries as interactive ensures they have prompt access to resources.

You can query BigQuery data by using one of the following query job types:
Interactive query jobs. By default, BigQuery runs queries as interactive query jobs, which are intended to start executing as quickly as possible.

Batch query jobs. Batch queries have lower priority than interactive queries. When a project or reservation is using all of its available compute resources, batch queries are more likely to be queued and remain in the queue. After a batch query starts running, the batch query runs the same as an interactive query. For more information, see query queues.

By updating your SQL pipelines to run as batch queries you can reduce concurrency, avoid quota errors, and ensure that your analysts have the resources they need for their interactive queries.

This question has nothing to do with increasing slots, it is just confusing and misleading, therefore A and C do not make sense.
D (All interactive queries): Running all queries as interactive would prioritize speed over cost-efficiency and might not be necessary for your non-time-sensitive SQL pipelines.

You already have a 2000 slots consumption and sudden peaks, so you should use a baseline of 2000 slots and a maximum of 3000 to tackle the peak concurrent activity.
https://cloud.google.com/bigquery/docs/slots-autoscaling-intro

Why A is the best choice:

Addresses Concurrency: Increasing the maximum reservation size to 3000 slots directly addresses the concurrency issue by providing more capacity for simultaneous queries. Since the current peak usage is 1500 queries, this increase ensures sufficient headroom.
Cost Optimization: Setting the baseline to 0 means you only pay for the slots actually used, avoiding unnecessary costs for idle capacity. This is ideal for non-time-sensitive workloads where flexibility is more important than guaranteed instant availability.

Option B

B.
Update SQL pipelines to run as a batch query, and run ad-hoc queries as interactive query jobs.

I personally don't think any answer is correct,

D is the closest one but it's missing a "ORDER BY timestamp DESC" to ensure to get only the latest record based in the timestamp

D:
https://cloud.google.com/bigquery/streaming-data-into-bigquery#manually_removing_duplicates

D but it needs ORDER BY and should use QUALIFY instead of WHERE

D is closest, as there will always be at least 1 row for each ID. Would have rather used SELECT DISTINCT.

D ensures data is partitioned by the unique id and only one record is picked thereby ensuring results are de-duplicated

This approach will assign a row number to each row within a unique ID partition, and by selecting only rows with a row number of 1, you will ensure that duplicates are excluded in your query results. It allows you to filter out redundant rows while retaining the latest or earliest records based on your timestamp column.

Options A, B, and C do not address the issue of duplicates effectively or interactively as they do not explicitly remove duplicates based on the unique ID and event timestamp.

Correct answer is D. Group by column us used to check for the duplicates where you can have the count(*) for each of the unique id column. If the count is greater than 1, we will know duplicate exists.The easiest way to remove duplicates while streaming inserts is to use row_number. Use GROUP BY on the unique ID column and timestamp column and SUM on the values will not remove duplicates.
I also executed LAG function and LAG function will return NULL on unique id when no previous records with same unique id exist. Hence LAG is also not an option here.

D is correct because it will just pick out a single row for each set of duplicates.
A is not correct because this will just return one row.
B is not correct because this doesn’t get you the latest value, but will get you a sum of the same event over time which doesn’t make too much sense if you have duplicates.
C is not correct because if you have events that are not duplicated, it will be excluded.

Correct D

Row number gives the unique number ranking based on target column.

It's the only valid option, try it your self with examples in QB.

Ans is D as Row number is the clause to fetch unique record from duplicate

Answer: D

correct answer -> Use the ROW_NUMBER window function with PARTITION by unique ID along with WHERE row equals 1.

You can use the ROW_NUMBER() to turn non-unique rows into unique rows and then delete the duplicate rows.

Reference:
https://www.mysqltutorial.org/mysql-window-functions/mysql-row_number-function/

Sorry, but IMHO no response is correct, because, in addition to making the ID field unique, it occurs consider the record with most recent timestamp

Ans: D

Correct : D

Option D, reservation can't be applied to resources lower than projects (only to Org, folders or projects)

Reserve assignments
To use the slot capacity you purchased, assign projects, folders, or organizations to a reservation. When a job in a project runs, it uses slots from the assigned reservation. Resources can inherit roles from their parents in the resource hierarchy. Even if a project is not assigned to a reservation, it inherits the assignment from the parent folder or organization, if any. If a project does not have an assigned or inherited reservation, the job uses on-demand pricing. For more information about the resource hierarchy, see Organizing BigQuery Resources .

Reservations are assigned at project level https://cloud.google.com/bigquery/docs/reservations-tasks

Answer C provides the granularity you need. Answer D is for the entire project... however the question ONLY wants it for the CDC process - so service account is better.

In BigQuery, reservations are assigned to "assignments", which can be:

Project

Folder

Organization

Service account

"When you create commitments and reservations, they are associated with a Google Cloud project." - https://cloud.google.com/bigquery/docs/reservations-intro#admin-project

https://cloud.google.com/bigquery/docs/reservations-intro#assignments

Option B provides a more granularity solution

The correct answer is B. Create a BigQuery reservation at the job level.

Create a BigQuery reservation at the job level: This is the most suitable option. By creating a job-level reservation, you can allocate resources specifically to the CDC process and improve the accuracy of cost forecasting.

Steps to create and apply a BigQuery reservation:
Identify the job: Clearly identify the job that executes the CDC process.
Create a reservation: Use the BigQuery console or API to create a reservation, specifying the job's label, query text, and other details.
Apply the reservation: Assign the created reservation to the job that executes the CDC process.

Are these questions really useful andd coming in GDE Exam? Anyone appeared recently and passed ?

https://cloud.google.com/blog/products/data-analytics/manage-bigquery-costs-with-custom-quotas.
Quotas can be applied on Project or User Level

D- choose the correct project and apply the task type background:https://cloud.google.com/bigquery/docs/reservations-intro?hl=fr#assignments

Option D

D.
Reservation is on project, folder or organisation level.

C or D ??

Option C (service account) allows you to target the reservation specifically to the CDC process or any other jobs run by that service account. This is particularly useful if you have multiple processes running in the project with different performance or cost requirements.

Option D (project) applies the reservation across all jobs in the project, which is a broader approach. If the CDC process is the primary or sole job running in the project and you want all jobs to share the same reservation, then this option might be more straightforward.

D. Create a BigQuery reservation for the project.

Straight Forward

exporting the table to a bucket is the most straight forward and cost effective solution. C is not suited because it mentions enable versioning for a table that wont change and is immutable for 3 years. and it doesn't mention retention

For data keeping till last 3 years, use bucket lock with rentention policy

voted D

Option D

Option D, clearly

D.
For data keeping till last 3 years, use bucket lock with rentention policy

"E" looks better. For D, the database will be double in size (which increases the storage price) and the user has to spend some more days reloading all the data.

I would go for Option.E
Reason:
1. Question says, I want to change data type to the TIMESTAMP. So there is change required
2. To minimize migration effort without making future queries computationally expensive: No view required as I will run this query again and again. In order to convert string to Timestamp we need to use CAST function for sure. Simple copy of numeric value won't work. https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules
Option-A: I don't want to redo this and spent few days again
Option-B: Copying numeric value won't work.
Option-C: View is expensive to run again and again.
Option-D: To me this seems onetime data load which took 3 days. So, the IS_NEW seems give us reduntant data as we are adding appended mode.
Option-E: Technically it will work, although it will add more space (new table) ,cost. But no choice, because Option-B didn't mention Cast function. Else I would have gone for "B"

It's E.

C looks good because it's very easy and straightforward. But every time you query that view, a transformation will take place, making queries expensive, and it will be forever like this as the original table doesn't change.
One time effort of loading the data into a new table with a small query. Heck you can even rename it right after if you don't want to change the reference of the ingestion.

Changing DT to TIMESTAMP. Create a view casting the string. Answer C.

I was hesitating between C and E, but E seems to be the good one

Both options C (creating a view) and E (creating a new table) avoid reloading the original data. However, C is the better choice for minimizing effort and maintaining performance. Views don't store any data themselves. They simply act as a layer on top of the existing table. This means you avoid the cost of storing duplicate data, which can be significant for large tables.

Create a view no data migration easy to do but computational efficient queries not sure (?)

E. It recreates the table one time and everything is fixed. Next time you load, load to the new table, you can delete the previous one.
Definitely not C. The question says I have to minimize future query effort, which literally means "don't create a view that converts from STR to TIMESTAMP for every row."

Option E is correct.
The question is asking to consider the Query cost for future.
This is a one time job to fix the Timestamp column. no views were created.

Why Option E is the best choice:

It modifies the schema with minimal data movement.
The original table remains untouched for potential future needs.
Future data loads can directly go to the new table with the desired schema.
Queries referencing the new table (NEW_CLICK_STREAM) will benefit from the optimized data type for timestamp operations.

minimizing effort is key.

A view in Google BigQuery is a virtual table defined by a SQL query. By creating a view that casts the DT column as a TIMESTAMP, you can transform the data format without altering the underlying data in the CLICK_STREAM table. This means you don't have to reload any data, thereby minimizing migration effort.

Good point about the logical views and the desire to reduce costs. I would vote for E

The best way to minimize the migration effort without making future queries computationally expensive is to create a view and reference it instead of the table. This is because views are materialized when they are queried, so they do not incur any additional overhead.
So the answer is (C).

Option "E"
It avoids the need to delete and recreate the entire CLICK_STREAM table, which is time-consuming and requires reloading all data.

It allows you to use a simple query to cast the existing DT column as TIMESTAMP values and store the results in a new table, NEW_CLICK_STREAM.

You can gradually migrate to the new data format, and your future queries will be able to utilize the TIMESTAMP data type for more efficient processing.

Option D duplicates, not a good solution

E. E. You can use a special command to change the time on the old cards to the better type "TIMESTAMP" and create a new box called "NEW_CLICK_STREAM." From now on, you'll look at the new box whenever you want to know the time. It's like having a new and better box to keep things tidy and organized.

So, the best way to change the time on the little cards to the better type "TIMESTAMP" is option E. It's like using magic to create a new box and making sure everything is still easy to find and work with. It's a clever way to keep track of time and make your website even better!

Answer is B. Timetravel only covers for 7 days and a scheduled query is needed for creating Table snapshots for 30 days. Also table snapshot must remain in the same region as base table (please refer to limitation of table snapshot from below link) https://cloud.google.com/bigquery/docs/table-snapshots-intro

Answer is C: https://cloud.google.com/bigquery/docs/table-snapshots-intro
"Benefits of using table snapshots include the following:

Keep a record for longer than seven days. With BigQuery time travel, you can only access a table's data from seven days ago or more recently. With table snapshots, you can preserve a table's data from a specified point in time for as long as you want.

Minimize storage cost. BigQuery only stores bytes that are different between a snapshot and its base table, so a table snapshot typically uses less storage than a full copy of the table."
But the wording is foolish... It's table snapshot, NOT point in time snapshot!

https://cloud.google.com/bigquery/docs/time-travel#restore-a-table
this is point in time using time travel window - max is 7 days...

Regional - to minimize cost, scheduled backups - to enable 30 days RPO (time travel only gives you 7 days)

Regional datasets are *cheaper* than *multi-regional*, which helps minimize cost.
Scheduled queries can automate daily or hourly snapshots, ensuring you always have backup data within the 30-day RPO window.
Time-suffixed tables allow for granular restoration to a specific date.
This is a manual but cost-effective backup strategy.

I think all of these answers are wrong. None of these options safeguard against a regional GCP failure. Managed Disaster Recovery is a real DR solution to consider for this use case or at least a manually managed second backup copy of the table in a different region. Multi-region just automatically picks a region but does not replicate cross region (https://cloud.google.com/bigquery/docs/managed-disaster-recovery)

Regional dataset ✅ Lower cost than multi-regional
Scheduled backups (30 days) ✅ Meets RPO by retaining 30+ days of backup snapshots
Cost-efficient ✅ Regional storage is cheaper and backup tables are under user control
Recovery process ✅ Simply point to the latest backup or restore from backup tables

Minimizing Cost

BigQuery multi-regional is more expensive than BQ regional.

RPO is NOT the same as a PITR window. Point-in-time recovery (PITR) is a process that allows users to restore data or settings from a previous point in time. A recovery point objective (RPO) is the maximum amount of data loss that an organization can tolerate after a data loss event. So a PITR snapshot easily meets an RPO of 30 days. A regional bucket minimizes cost.

Answer is C. As question is related to highly available so this rules out A and B.

Because this option uses Multiregional & BQ Snapshot, others are not right/cumbersome

A is the right Answer

Best choice, minimized cost

Minimized Cost with Regional Storage: Regional datasets are less costly than multi-regional datasets in BigQuery. Since there is no requirement here for multi-regional availability, regional storage meets the high availability need while keeping costs lower.

RPO Compliance with Scheduled Backups: A scheduled query that periodically creates copies of the data (e.g., daily or weekly, depending on the requirements) allows for recovery within the 30-day RPO, meeting the requirement for data retention and recovery.

Point-in-Time Recovery Not Native in BigQuery: Although BigQuery provides a limited "table snapshot" feature, it’s not a true point-in-time recovery option for the last 30 days. Creating periodic backups through scheduled queries gives you control over retention, enabling you to keep backups for 30 days and reducing dependency on more costly or limited snapshot capabilities.

D.Create monthly snapshots of a table by using a service account that runs a scheduled query. Link: https://cloud.google.com/bigquery/docs/table-snapshots-scheduled

HA => multi-region
30-days RPO => manual backups as max time-travel is 7 days

This is in one of google's training practice questions and the answer for it is C.

B
The questions is specifically about organizing the data in BigQuery and storing backups.

Should be B
Using snapshot decorators , recovery is valid only for a period of 7 days. Here it says 2 weeks so "D" is ruled out.

You can undelete a table within seven days of deletion, including explicit deletions and implicit deletions due to table expiration. After seven days, it is not possible to undelete a table using any method, including opening a support ticket.
https://cloud.google.com/bigquery/docs/managing-tables

D is a distractor as it talks about snapshot DECORATORS (a technique to address Time Travel - a 7 days backup) and not about "snapshots".

Snapshot decorators in BigQuery allow you to query a table at a past point in time, but they are limited by BigQuery’s time travel window (which is typically 7 days). Since errors are sometimes only detected after 2 weeks, snapshot decorators won’t be effective for recovering data beyond their retention period.

With Storage Decorators, BigQuery only stores the differences between a snapshot and its base table, minimizing storage costs.

D is the most cost optimized solution to keep the backup. please read the link - https://cloud.google.com/bigquery/docs/table-snapshots-intro#table_snapshots

I think D is better.

The best option is D. Organize your data in separate tables for each month, and use snapshot decorators to restore the table to a time prior to the corruption.

Answer is D:
Snapshots are different from time travel. They can hold data as long as we want.
Furthermore "BigQuery only stores bytes that are different between a snapshot and its base table" so pretty cost effective as well.

https://cloud.google.com/bigquery/docs/table-snapshots-intro#table_snapshots

From : https://cloud.google.com/architecture/dr-scenarios-for-data#BigQuery
It can't be D
If the corruption is caught within 7 days, query the table to a point in time in the past to recover the table prior to the corruption using snapshot decorators.
Store the original data on Cloud Storage. This allows you to create a new table and reload the uncorrupted data. From there, you can adjust your applications to point to the new table. => D

D - this solution in integrated. No core is needed

90% of questions are having multiple answers and its very hard to get into every discussion where the conclusion is not there

The answer is B:

Why not D? Because snapshot costs can become high if a lot of small changes are made to the base table: https://cloud.google.com/bigquery/docs/table-snapshots-intro#:~:text=Because%20BigQuery%20storage%20is%20column%2Dbased%2C%20small%20changes%20to%20the%20data%20in%20a%20base%20table%20can%20result%20in%20large%20increases%20in%20storage%20cost%20for%20its%20table%20snapshot.

Since the question specifically states that the ETL pipeline is regularly modified, this means that lots of small changes are present. In combination with the requirement to optimize for storage costs, this means that option B is the way to go.

keyword: detected after 2 weeks.
only snapshot could resolve the problem.

From BigQuery documentation - Benefits of using table snapshots include the following:

- Keep a record for longer than seven days. With BigQuery time travel, you can only access a table's data from seven days ago or more recently. With table snapshots, you can preserve a table's data from a specified point in time for as long as you want.
- Minimize storage cost. BigQuery only stores bytes that are different between a snapshot and its base table, so a table snapshot typically uses less storage than a full copy of the table.

So storing data in GCS will make copies of data for each table. Table snapshots are more optimal in this scenario.

Organizing your data in separate tables for each month will make it easier to identify the affected data and restore it.
Exporting and compressing the data will reduce storage costs, as you will only need to store the compressed data in Cloud Storage.
Storing your backups in Cloud Storage will make it easier to restore the data, as you can restore the data from Cloud Storage directly

Organize in separate tables and store in GCS

should be B
https://cloud.google.com/bigquery/docs/creating-partitioned-tables#converting_date-sharded_tables_into_ingestion-time_partitioned_tables

Answer: B
Description: Google says that when you have multiple wildcard tables, best option is to shard it into single partitioned table. Time and cost efficient

partitioned table is more efficient

Sharded tables, like LOGS_yyyymmdd, are useful for managing data, but querying across a long date range with table wildcards can lead to inefficiencies and exceed the 1,000 table limit in BigQuery. Instead of using multiple sharded tables, you should consider converting these into a partitioned table.

A partitioned table allows you to store all the log data in a single table, but logically divides the data into partitions (e.g., by date). This way, you can efficiently query data across long date ranges without hitting the 1,000 table limit.

gpt: Thank you for your feedback and additional information. You are correct that partitioned tables have a limit of 4,000 partitions, so partitioning tables by date could potentially run into this limit in the future. In this case, option B, converting sharded tables into a single partitioned table, could be a reasonable solution to avoid exceeding the maximum number of tables in BigQuery.

As you mentioned, sharded tables require additional metadata and permissions verification, which can impact query performance. Converting sharded tables into a single partitioned table can improve performance and reduce query overhead.

Therefore, based on the information provided, option B seems to be the most appropriate solution for avoiding the limit of 1,000 tables in BigQuery and optimizing query performance.

The question seems pretty badly written. One important thing to remember is that partitioned tables also have a limit of 4000 partitions (https://cloud.google.com/bigquery/docs/partitioned-tables#ingestion_time), so moving everything to one table would just delay the problem. However, option A is not clear on how it will be done. One table per year with daily partitions? Best solution as no limit will be reached. One table per day? Then we have the same 1000 tables problem.
All things considered I'll stick to B, simply because the problem will definitely be solved for the next few years, so I'd say it's a reasonable solution.

Answer is B.
Table sharding is the practice of storing data in multiple tables, using a naming prefix such as [PREFIX]_YYYYMMDD.
Partitioning is recommended over table sharding, because partitioned tables perform better. With sharded tables, BigQuery must maintain a copy of the schema and metadata for each table. BigQuery might also need to verify permissions for each queried table. This practice also adds to query overhead and affects query performance.
In answer A. we still are creating tableS (even though partioned). So we still facing the issue of max 1000 tables. In B. we have only ONE table (partioned)

Why not A?
By converting all daily log tables into date-partitioned tables, you can take advantage of partition pruning to limit the number of tables that need to be scanned during a query. Partition pruning allows BigQuery to skip scanning partitions that are not within the date range specified in the query, thus reducing the number of tables that need to be scanned and can help to avoid reaching the 1,000 table limit.
A Seems like the correct answer but I can be wrong...

B. Convert the sharded tables into a single partitioned table
It was a sharded Table (format is the HINT here); converting to partition table is the option.
Also as per GCP its recommended to use Partition over Sharding

I chose option A. From all the comments I have seen, there are various things that are misunderstood.
1. Option A is a single table with multiple shards! Google does recommend to use partition rather than shard as it has a better performance (https://cloud.google.com/bigquery/docs/partitioned-tables#dt_partition_shard)
2. Option B is a single table with single partition! Single partition is a no for large table

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

Option A - already doing same loading data in separate table daily and reached 1000 table limit.
Option B - Use wild card to query the data
Option C & D - make no sense

its B.
A - Even if you have 100+ partitioned tables, you still have the limit of less than 1000 tables. So this doesn't work for this problem.
C It's a no sense. Cache its 24h for every table that has been query in the last 24 and has no changes. Also, cache is not support with wildcard multiple tables.
D Will not work because it's a recursive issue. You still will have 100+ tables, beam query
B will work, you materialize in only one table, so will be working perfectly.

Convert MANY sharded tables into a single ONE (partitioned) table

selecting for daily/monthly data from one single partition will be very expensive. I think A is the best answer

C'mon, how much time are you going to take to partition every single table you have? second point and the most important, you have a table for every SINGLE DAY "LOGS_YYYYMMDD" partitioning every table will end on scanning all the records of each table when you query them by date ranges using the wildcards, there will be no difference on time-partitioning each table versus consuming them as described.

If you follow option A, you will end up with the same amount of tables, e.g 1500 tables, though they will all be partitioned, which is not helpful.
Option B takes all the sharded tables and makes one large partitioned table.

correct option -> B. Restrict access to tables by role.
Reference: https://cloud.google.com/bigquery/docs/table-access-controls-intro

correct option -> D. Restrict BigQuery API access to approved users.
***Only approved users will have access which means other users will have minimum amount of information required to do their job.***
Reference: https://cloud.google.com/bigquery/docs/access-control

correct option -> F. Use Google Stackdriver Audit Logging to determine policy violations.
Reference: https://cloud.google.com/bigquery/docs/table-access-controls-intro#logging

A. Disable writes to certain tables. ---> Read is still available(not minimal access)
C. Ensure that the data is encrypted at all times. ---> Data is encrypted by default.
E. Segregate data across multiple tables or databases. ---> Normalization is of no help here.

Yes. Access control on table level is now possible in BigQuery : https://cloud.google.com/bigquery/docs/table-access-controls-intro

F makes sense, but only once there has been a violation.
I'd go with BDE, even though normalization across different datasets might not solve the problem.

B - Use IAM to define granular permissions.
D - Only authorised users or systems can query or manipulate BigQuery data.
E - By segregating data into different tables or datasets, specific permissions can be assigned to each data subset.

I disagree with [F]. It's too late for a "highly regulated industry" to detect access violations by audit logs.
[E] is a more reasonable answer, since it is a kind of row-level security, especially the times when BigQuery row-level security wasn't available.
It is a practice still recommended (even with row-level sec. available) for the extreme scenario that:
(Through repeated observation of query duration when querying tables with row-level access policies,) "a user could infer the values of rows that otherwise might be protected by row-level access policies"
"If you are sensitive to this level of protection, we recommend using separate tables to isolate rows with different access control requirements, instead."
Source:
https://cloud.google.com/bigquery/docs/best-practices-row-level-security#limit-side-channel-attacks

E seems more sensible to be as the question concentrates more on table access restriction than access violation, policy violations can only be determined through stackdriver how ever we cant restrict the access to tables.Probably option E should be considered as, by segregating the data into diferrent tables , we can restrict access to tables.

B. Restrict access to tables by role: You can define roles in BigQuery and grant specific permissions to these roles to control who can access particular tables.

D. Restrict BigQuery API access to approved users: You can control access to the BigQuery API and, consequently, to the underlying data by ensuring that only approved users or services can make API requests.

E. Segregate data across multiple tables or databases: You can separate data into different tables or databases based on user access requirements, which allows you to limit users' access to specific data sets.

These approaches, when used together, can help you enforce data access controls in a regulated environment. Options A, C, and F are also important considerations but are not direct methods for enforcing fine-grained access control to specific data.

B. Restrict access to tables by role.

Use IAM roles and permissions to control access to specific datasets or tables based on the user’s role.
D. Restrict BigQuery API access to approved users.

Limit API access to only those users or services that need it, ensuring that unauthorized users cannot interact with the data.
E. Segregate data across multiple tables or databases.

Organize data in a way that separates sensitive information, allowing more granular control over who has access to specific datasets.
These options directly contribute to enforcing the principle of least privilege, ensuring users can only access the data necessary for their roles.

You want to enforce this requirement with Google BigQuery -> BDE

BDF. We are fairly unanimous with options B and D. I'm going with F because it does help identifying policy violations which is also one aspect to be considered when designing access controls. Option D only indicates segregating into multiple tables and databases which may or may not help with controlling access leaving it open-ended for the architect to decide.

In Google BigQuery, you can organize and segregate data across multiple tables within the same dataset, but you cannot directly segregate data into separate databases. BigQuery uses a flat namespace structure where data is organized into datasets and tables within those datasets. Datasets are the highest level of organization within BigQuery.

So i'm sticking with BDF

B. Restrict access to tables by role.
D. Restrict BigQuery API access to approved users.
E. Segregate data across multiple tables or databases.

BDF as per other answers

Regarding E or F, opinions seem to be divided into two parts.

I think E is insufficient because it seems that appropriate conditions must be additionally described for table or dataset separation.

F is also emphasized in Google's official textbook. You need to ensure that it is operating well as set up through monitoring.

So, BDF!

Why B is correct? Access control can only be applied on dataset and views, not on partitions and tables. => So it is not possible to restrict access to table, but only to dataset. Can someone help me understand why in this scenario B is correct?

Option E says "...or databases". The data housing service in question in BigQuery and the context is to design that support BigQuery access delegation. Seems random to include moving to another database as an option. If it did not mention databases and stopped at just tables, then E would also be the right option

B. Restrict access to tables by role: This approach can be used to control access to tables based on user roles. Access controls can be set at the project, dataset, and table level, and roles can be customized to provide granular access controls to different groups of users.

D. Restrict BigQuery API access to approved users: This approach involves using IAM (Identity and Access Management) to control access to the BigQuery API. Access can be granted or revoked at the project or dataset level, and policies can be customized to control access based on user roles, IP addresses, and other factors.

E. Segregate data across multiple tables or databases: This approach involves breaking down large datasets into smaller, more manageable tables or databases. This helps to ensure that individual users have access only to the minimum amount of information required to do their jobs, and reduces the risk of data breaches or policy violations.

- BigQuery Omni: This is an extension of BigQuery that allows you to analyze data across Google Cloud, AWS, and Azure without having to manage the infrastructure or move data across clouds. It's suitable for querying data stored in AWS S3 buckets directly.
- BigLake: Allows you to create a logical abstraction (table) over data stored in Cloud Storage and S3, so you can query data using BigQuery without moving it.
- Unified Querying: By setting up BigQuery Omni to connect to AWS S3 and creating BigLake tables over both Cloud Storage and S3 data, you can query all data using BigQuery directly.

B, Biglake dont support to create table from S3

Biglake dont support create tables from aws S3

Option A

Option A - clearly explained in comments

A - BigLake tables work for S3 and GCS

Old question. It's done using IAM nowadays: bigquery.dataEditor and bigquery.dataViewer

B. Create a dataset for each department. Assign the department leads the role of WRITER, and assign the data analysts the role of READER on their dataset.

The answers was to old, Reader role its a legacy role and its equal to viewer. The answer is B, but is too old. The other answers no make sense

No writer, reader role

WRITER role is not there in roles of BigQuery table/dataset

Answer : D. There is no role called WRITER or READER as preliminary role.

both C & D violate the principle of least privilege.
A talks about OWNER and WRITER roles, and the analyst doesn't need a writer role.
So we're left with B.

https://cloud.google.com/bigquery/docs/access-control#bigquery

B - Lead needs to have the role to create tables and also Analyst only need to read

Answer B:
Why not D, mentioned in question: Data lead will create tables in dataset. Imagine, other department leads are creating unnecessory tables in shared dataset and you are struggling to find your tables as everyday there are some new tables. Headache right ? better to give them seperate dataset and do whatever you want in that dataset.

Vote B, both BD can fullfill the job requirement but B is on dataset level and D on project level. "By default, granting access to a project also grants access to datasets within it." D may issue unnecessary accesses to other content in the project.

Interestingly enough - I know believe the answer is A...
Deleting is not the same as modify...

Answer is B: https://cloud.google.com/bigquery/docs/access-control
The question ask for the lead to be able to:
CREATE, UPDATE, and SHARE with the team...

BigQuery Data Owner can do that
(roles/bigquery.dataOwner)
When applied to a table or view, this role provides permissions to:

Read and update data and metadata for the table or view.
Share the table or view.
Delete the table or view.

Editor cannot do that.

Thoughts?

It's D, because this is an outdated question, before IAM you cannot set Editor to a dataset; but the best practice is: Create a dataset for each department. Assign the department leads the role of EDITOR(NOT OWNER), and assign the data analysts the role of READER on their dataset.