Google Associate Data Practitioner Exam Practice Test Instant Access

Question 1

Your company has several retail locations. Your company tracks the total number of sales made at each location each day. You want to use SQL to calculate the weekly moving average of sales by location to identify trends for each store. Which query should you use?

AOption A

BOption B

COption C

DOption D

Answer : C

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:

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).

Question 2

Your company is setting up an enterprise business intelligence platform. You need to limit data access between many different teams while following the Google-recommended approach. What should you do first?

ACreate a separate Looker Studio report for each team, and share each report with the individuals within each team.

BCreate one Looker Studio report with multiple pages, and add each team's data as a separate data source to the report.

CCreate a Looker (Google Cloud core) instance, and create a separate dashboard for each team.

DCreate a Looker (Google Cloud core) instance, and configure different Looker groups for each team.

Answer : D

Comprehensive and Detailed In-Depth

For an enterprise BI platform with data access control across teams, Google recommends Looker (Google Cloud core) over Looker Studio for its robust access management. The 'first' step focuses on setting up the foundation.

Option A: Looker Studio reports are lightweight but lack granular access control beyond sharing. Creating separate reports per team is inefficient and unscalable.

Option B: One Looker Studio report with multiple pages and data sources doesn't enforce team-level access control natively---users could access all pages/data.

Option C: Creating a Looker instance with separate dashboards per team is a step forward but skips the foundational access control setup (groups), reducing scalability.

Option D: Setting up a Looker instance and configuring groups aligns with Google's recommendation for enterprise BI. Groups allow role-based access control (RBAC) at the model, Explore, or dashboard level, ensuring teams see only their data. This is the scalable, foundational step per Looker's 'Access Control' documentation. Reference: Looker Documentation - 'Managing Users and Groups' (https://cloud.google.com/looker/docs/admin-users-groups).

Question 3

You are working with a large dataset of customer reviews stored in Cloud Storage. The dataset contains several inconsistencies, such as missing values, incorrect data types, and duplicate entries. You need to clean the data to ensure that it is accurate and consistent before using it for analysis. What should you do?

AUse the PythonOperator in Cloud Composer to clean the data and load it into BigQuery. Use SQL for analysis.

BUse BigQuery to batch load the data into BigQuery. Use SQL for cleaning and analysis.

CUse Storage Transfer Service to move the data to a different Cloud Storage bucket. Use event triggers to invoke Cloud Run functions to load the data into BigQuery. Use SQL for analysis.

DUse Cloud Run functions to clean the data and load it into BigQuery. Use SQL for analysis.

Answer : B

Using BigQuery to batch load the data and perform cleaning and analysis with SQL is the best approach for this scenario. BigQuery provides powerful SQL capabilities to handle missing values, enforce correct data types, and remove duplicates efficiently. This method simplifies the pipeline by leveraging BigQuery's built-in processing power for both cleaning and analysis, reducing the need for additional tools or services and minimizing complexity.

Question 4

Your company uses Looker as its primary business intelligence platform. You want to use LookML to visualize the profit margin for each of your company's products in your Looker Explores and dashboards. You need to implement a solution quickly and efficiently. What should you do?

ACreate a derived table that pre-calculates the profit margin for each product, and include it in the Looker model.

BDefine a new measure that calculates the profit margin by using the existing revenue and cost fields.

CCreate a new dimension that categorizes products based on their profit margin ranges (e.g., high, medium, low).

DApply a filter to only show products with a positive profit margin.

Answer : B

Defining a new measure in LookML to calculate the profit margin using the existing revenue and cost fields is the most efficient and straightforward solution. This approach allows you to dynamically compute the profit margin directly within your Looker Explores and dashboards without needing to pre-calculate or create additional tables. The measure can be defined using LookML syntax, such as:

measure: profit_margin {

type: number

sql: (revenue - cost) / revenue ;;

value_format: '0.0%'

}

This method is quick to implement and integrates seamlessly into your existing Looker model, enabling accurate visualization of profit margins across your products.

Question 5

You work for a healthcare company. You have a daily ETL pipeline that extracts patient data from a legacy system, transforms it, and loads it into BigQuery for analysis. The pipeline currently runs manually using a shell script. You want to automate this process and add monitoring to ensure pipeline observability and troubleshooting insights. You want one centralized solution, using open-source tooling, without rewriting the ETL code. What should you do?

ACreate a direct acyclic graph (DAG) in Cloud Composer to orchestrate a pipeline trigger daily. Monitor the pipeline's execution using the Apache Airflow web interface and Cloud Monitoring.

BConfigure Cloud Dataflow to implement the ETL pipeline, and use Cloud Scheduler to trigger the Dataflow pipeline daily. Monitor the pipelines execution using the Dataflow job monitoring interface and Cloud Monitoring.

CUse Cloud Scheduler to trigger a Dataproc job to execute the pipeline daily. Monitor the job's progress using the Dataproc job web interface and Cloud Monitoring.

DCreate a Cloud Run function that runs the pipeline daily. Monitor the functions execution using Cloud Monitoring.

Answer : A

Comprehensive and Detailed in Depth

Why A is correct:Cloud Composer is a managed Apache Airflow service, which is a popular open-source workflow orchestration tool.

DAGs in Airflow can be used to automate ETL pipelines.

Airflow's web interface and Cloud Monitoring provide comprehensive monitoring capabilities.

It also allows you to run existing shell scripts.

Why other options are incorrect:B: Dataflow requires rewriting the ETL pipeline using its SDK.

C: Dataproc is for big data processing, not orchestration.

D: Cloud Run functions are for stateless applications, not long-running ETL pipelines.

Cloud Composer: https://cloud.google.com/composer/docs

Apache Airflow: https://airflow.apache.org/

Question 6

You have a BigQuery dataset containing sales dat

a. This data is actively queried for the first 6 months. After that, the data is not queried but needs to be retained for 3 years for compliance reasons. You need to implement a data management strategy that meets access and compliance requirements, while keeping cost and administrative overhead to a minimum. What should you do?

AUse BigQuery long-term storage for the entire dataset. Set up a Cloud Run function to delete the data from BigQuery after 3 years.

BPartition a BigQuery table by month. After 6 months, export the data to Coldline storage. Implement a lifecycle policy to delete the data from Cloud Storage after 3 years.

CSet up a scheduled query to export the data to Cloud Storage after 6 months. Write a stored procedure to delete the data from BigQuery after 3 years.

DStore all data in a single BigQuery table without partitioning or lifecycle policies.

Answer : B

Partitioning the BigQuery table by month allows efficient querying of recent data for the first 6 months, reducing query costs. After 6 months, exporting the data to Coldline storage minimizes storage costs for data that is rarely accessed but needs to be retained for compliance. Implementing a lifecycle policy in Cloud Storage automates the deletion of the data after 3 years, ensuring compliance while reducing administrative overhead. This approach balances cost efficiency and compliance requirements effectively.

Question 7

You want to process and load a daily sales CSV file stored in Cloud Storage into BigQuery for downstream reporting. You need to quickly build a scalable data pipeline that transforms the data while providing insights into data quality issues. What should you do?

ACreate a batch pipeline in Cloud Data Fusion by using a Cloud Storage source and a BigQuery sink.

BLoad the CSV file as a table in BigQuery, and use scheduled queries to run SQL transformation scripts.

CLoad the CSV file as a table in BigQuery. Create a batch pipeline in Cloud Data Fusion by using a BigQuery source and sink.

DCreate a batch pipeline in Dataflow by using the Cloud Storage CSV file to BigQuery batch template.

Answer : A

Using Cloud Data Fusion to create a batch pipeline with a Cloud Storage source and a BigQuery sink is the best solution because:

Scalability: Cloud Data Fusion is a scalable, fully managed data integration service.

Data transformation: It provides a visual interface to design pipelines, enabling quick transformation of data.

Data quality insights: Cloud Data Fusion includes built-in tools for monitoring and addressing data quality issues during the pipeline creation and execution process.

Google Cloud Associate Data Practitioner Exam Practice Test