Build and Optimize Data Warehouses with BigQuery: Challenge Lab

Join Sign in

Build and Optimize Data Warehouses with BigQuery: Challenge Lab

1 hour 30 minutes 5 Credits


Google Cloud self-paced labs logo


In a challenge lab you’re given the scenarios and tasks. Instead of following step-by-step instructions, you will use the skills learned from the labs in the quest to figure out how to complete the tasks on your own! An automated scoring system (shown on this page) will provide feedback on whether you have completed your tasks correctly.

When you take a challenge lab, you will not be taught new Google Cloud concepts. You are expected to extend your learned skills, like changing default values and reading and researching error messages to fix your own mistakes.

To score 100% you must successfully complete all tasks within the time period!

This lab is recommended for students enrolled in the Build and Optimize Data Warehouses with BigQuery Quest.


Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

  • Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.
  • Time to complete the lab---remember, once you start, you cannot pause a lab.
Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account. Note: Once the lab environment has been fully provisioned, the tasks will become visible. The tasks that are assigned to you are independent, so you are free to perform the tasks in any order you want.

Challenge scenario

You are part of an international public health organization which is tasked with developing a machine learning model to predict the daily case count for countries during the Covid-19 pandemic. As a junior member of the Data Science team you've been assigned to use your data warehousing skills to develop a table containing the features for the machine learning model.

You are expected to have the skills and knowledge for this task, so don't expect step-by-step guides to be provided.

Note: You've been assigned random tasks from the set of tasks. Please reference this Form ID: and respective Task Number while reporting any issues, requesting assistance with these tasks or providing any feedback.

Task 1

Click Check my progress to verify the objective.

Verify Task

Task 2

Click Check my progress to verify the objective.

Verify Task

Task 3

Click Check my progress to verify the objective.

Verify Task

Task 4

Click Check my progress to verify the objective.

Verify Task

Tips and tricks

  • Tip 1. Remember that if you are instructed to create table you must exclude the United Kingdom (GBR), Brazil (BRA), Canada (CAN) and the United States (USA) data.
  • Tip 2. If you are updating the schema for a BigQuery table you can use the console to add the columns and record elements or you can use the command line bqutility to update the schema by providing a JSON file with all of field definitions as explained in the BigQuery Standard SQL reference documentation.
  • Tip 3. The covid19_ecdc table in the European Center for Disease Control COVID 19 public dataset contains a population column that you can use if you are populating the populationcolumn based on your task description.
  • Tip 4. The country_names_area table from the Census Bureau International public dataset does not contain a three letter country code column, but you can join it to table provided in task description using the full text country_name column that exists in both tables.
  • Tip 5. If you are updating the mobility record remember that you must select (and average) a number of records for each country and date combination so that you get a single average of each child column in the mobility record. You must join the resulting data to your working table using the same combination of country name and date that you used to group the source mobility records to ensure there is a unique mapping between the averaged source mobility table results and the records in your table that have a single entry for each country and date combination.
  • TIP 6. The UNION option followed by the ALL keyword combines the results of two queries where each query lists distinct results without combining duplicate results that arise from the union into a single row.



Earn your next skill badge

This self-paced lab is part of the Build and Optimize Data Warehouses with BigQuery skill badge. Completing this skill badge earns you the badge above, to recognize your achievement. Share your badge on your resume and social platforms, and announce your accomplishment using #GoogleCloudBadge.

This skill badge is part of Google Cloud’s Data Engineer learning path. If you have already completed the other skill badges in this learning path, search the catalog for 20+ other skill badges in which you can enroll.

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated February 03, 2024

Lab Last Tested December 22, 2023

Copyright 2024 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.