Comprehensive and Detailed in Depth

Why D is correct:Creating a BigQuery external table directly from the Google Sheet allows for real-

time updates.

Joining the external table with the actual cost table in BigQuery performs the calculation.

Connected Sheets allows the team to access and analyze the results directly in Google Sheets, with

the data being updated.

Why other options are incorrect:A: Saving as a CSV file loses the live connection and daily updates.

B: Downloading and uploading as a CSV file adds unnecessary steps and loses the live connection.

C: Same issue as B, losing the live connection.

Reference:

BigQuery External Tables: https://cloud.google.com/bigquery/docs/external-tables

Connected Sheets: https://support.google.com/sheets/answer/9054368?hl=en

Creating a materialized view with the limited data in a new dataset and granting the data analyst the

BigQuery Data Viewer role on the dataset and the BigQuery Job User role in the project ensures that

the analyst can query only the non-sensitive data without access to sensitive datasets. Materialized

views allow you to predefine what subset of data is visible, providing a secure and efficient way to

control access while maintaining compliance with data governance policies. This approach follows

the principle of least privilege while meeting the requirements.

Comprehensive and Detailed in Depth

Why A is correct:Storage Transfer Service with agent-based transfer allows for online migrations and

provides the ability to set bandwidth limits.

Agents are installed on-premises and can be configured to respect network constraints.

Why other options are incorrect:B: The --daisy-chain option is not related to bandwidth control.

C: Transfer Appliance is for offline migrations and is not suitable for online transfers with bandwidth

constraints.

D: The --no-clobber option prevents overwriting existing files but does not control bandwidth.

Reference:

Storage Transfer Service: https://cloud.google.com/storage-transfer-service/docs

Storage Transfer Service Agents: https://cloud.google.com/storage-transfer-service/docs/agentoverview

gcloud storage cp: https://cloud.google.com/storage/docs/gsutil/commands/cp

Comprehensive and Detailed in Depth

Why C is correct:Setting the Shared folder to "View" ensures everyone can see the content.

Creating Looker groups simplifies access management.

Subfolders allow granular permissions for each team.

Granting "Manage Access, Edit" allows teams to modify only their own content.

Why other options are incorrect:A: Grants View access only, so teams can't edit.

B: Moving content to personal folders defeats the purpose of sharing.

D: Grants edit access to all members of the team, not the team as a whole, which is not ideal.

Reference:

Looker Access Control: https://cloud.google.com/looker/docs/access-control

Looker Groups: https://cloud.google.com/looker/docs/groups

Setting the table partition expiration period to one year using the ALTER TABLE statement is the most

efficient and cost-effective approach. This automatically deletes data in partitions older than one

year, reducing storage costs without requiring manual intervention or additional queries. It

minimizes administrative overhead and ensures compliance with your data retention policy while

optimizing storage usage in BigQuery.

Extract from Google Documentation: From "Managing Partitioned Tables in BigQuery"

(https://cloud.google.com/bigquery/docs/partitioned-tables#expiration): "Set a partition expiration

time using ALTER TABLE to automatically remove partitions older than a specified duration, reducing

storage costs efficiently for ingestion-time partitioned tables."

Reference: Google Cloud Documentation - "BigQuery Partitioned Tables"

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

Comprehensive and Detailed in Depth

Why A is correct:Analytics Hub allows you to share datasets with external partners while maintaining

control over access.

Subscriptions allow granular control.

Why other options are incorrect:B: Cloud storage is for files, not bigquery datasets.

C: IAM roles do not allow for granular row and column level control.

D: Creating a separate project for each partner is complex and not scalable.

Reference:

Analytics Hub: https://cloud.google.com/analytics-hub/docs

To calculate the weekly moving average of sales by location: The query must group by store_id (partitioning the calculation by each store). The ORDER BY date ensures the sales are evaluated chronologically. The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW specifies a rolling window of 7 rows (1 week if each row represents daily data). The AVG(total_sales) computes the average sales over the defined rolling window. Chosen query meets these requirements: PARTITION BY store_id groups the calculation by each store. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/GOOGLE_Cloud_Associate-Data-Practitioner_/page_6_img_1.jpg ORDER BY date orders the rows correctly for the rolling average. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/GOOGLE_Cloud_Associate-Data-Practitioner_/page_6_img_2.jpg ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ensures the 7-day moving average. https://kxbjsyuhceggsyvxdkof.supabase.co/storage/v1/object/public/file-images/GOOGLE_Cloud_Associate-Data-Practitioner_/page_6_img_3.jpg Extract from Google Documentation: From "Analytic Functions in BigQuery" (https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts): "Use ROWS BETWEEN n PRECEDING AND CURRENT ROW with ORDER BY a time column to compute moving averages over a fixed number of rows, such as a 7-day window, partitioned by a grouping key like store_id." Reference: Google Cloud Documentation - "BigQuery Window Functions" (https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls).

Comprehensive and Detailed in Depth

Why C is correct:Materialized views in BigQuery are precomputed views that periodically cache the

results of a query. This ensures up-to-date results automatically.

A UNION is the correct operation to combine the data from multiple regional sales tables.

RANK() function is correct to rank the sales regions. ROW_NUMBER() would create a unique number

for each row, even if sales amount is the same, this is not the desired function.

Why other options are incorrect:A and B: Standard tables do not provide automatic updates.

D: A CROSS JOIN would produce a Cartesian product, which is not appropriate for combining regional

sales data.

Cross join is used when you want every combination of rows from tables, not a aggregation of data.

Reference:

BigQuery Materialized Views: https://cloud.google.com/bigquery/docs/materialized-views

BigQuery Window Functions: https://cloud.google.com/bigquery/docs/reference/standardsql/window-functions

BigQuery UNION: https://cloud.google.com/bigquery/docs/reference/standard-sql/querysyntax#union_all

BigQuery CROSS JOIN: https://cloud.google.com/bigquery/docs/reference/standard-sql/querysyntax#cross_join

Comprehensive and Detailed In-Depth

The solution must support SQL-based ELT, be serverless and cost-effective, and include advanced

features like version control and quality checks. Let’s dive in:

Option A: Cloud Data Fusion is a visual ETL tool, not SQL-centric (uses plugins), and isn’t fully

serverless (requires instance management). It lacks native source code control and parameterization.

Option B: Dataform is a serverless, SQL-based ELT platform for BigQuery. It uses SQLX scripts,

integrates with Git for version control, supports environment variables (parameterization), and offers

assertions for data quality—all meeting the requirements cost-effectively.

Option C: Dataproc is for Spark/MapReduce, not SQL ELT, and requires cluster management,

contradicting serverless and cost goals.

Option D: Cloud Composer orchestrates workflows (Python DAGs), not SQL pipelines natively. It’s

managed but not optimized for ELT within BigQuery alone.

Why B is Best: Dataform leverages your team’s SQL skills, runs in BigQuery (no extra infrastructure),

and provides Git integration (e.g., GitHub), parameterization (e.g., DECLARE env STRING DEFAULT

'prod';), and quality checks (e.g., assert 'no_nulls' AS SELECT COUNT(*) FROM table WHERE col IS

NULL). It’s the perfect fit.

Extract from Google Documentation: From "Dataform Overview"

(https://cloud.google.com/dataform/docs): "Dataform is a fully managed, serverless solution for

building SQL-based ELT pipelines in BigQuery, with built-in Git version control, environment

parameterization, and data quality assertions for robust data warehouse management."

Reference: Google Cloud Documentation - "Dataform" (https://cloud.google.com/dataform).

Comprehensive and Detailed in Depth

Why C is correct:Dataflow is the recommended service for real-time stream processing on Google

Cloud.

It provides scalable and reliable processing with low latency and high throughput.

Dataflow's streaming API is optimized for Pub/Sub integration and BigQuery streaming inserts.

Why other options are incorrect:A: Cloud Composer is for batch orchestration, not real-time

streaming.

B: Dataproc and Spark streaming are more complex and not as efficient as Dataflow for this task.

D: Cloud Run functions are for stateless, event-driven applications, not continuous stream

processing.

Reference:

Dataflow Streaming: https://cloud.google.com/dataflow/docs/concepts/streaming-pipelines

Pub/Sub to BigQuery with Dataflow: https://cloud.google.com/dataflow/docs/tutorials/pubsub-tobigquery