From Data to Insights with Google Cloud
Course 1469
3 DAY COURSE

Explore ways to derive insights from data at scale using BigQuery, Google Cloud’s serverless, highly scalable, and cost-effective cloud data warehouse. This course uses lectures, demos, and hands-on labs to teach you the fundamentals of BigQuery, including how to create a data transformation pipeline, build a BI dashboard, ingest new datasets, and design schemas at scale.

From Data to Insights with Google Cloud Benefits

• In this course, you will:

  • Derive insights from data using the analysis and visualization tools on Google Cloud.
  • Load, clean, and transform data at scale with Dataprep.
  • Explore and visualize data using Looker Studio.
  • Troubleshoot, optimize, and write high performance queries.
  • Practice with pre-built ML APIs for image and text understanding.
  • Train classification and forecasting ML models using SQL with BigQuery ML.

• Prerequisites

  Fundamental expertise in ANSI SQL.

From Data to Insights with Google Cloud Training Outline

Learning Objectives

Module 1) Introduction to Data on Google Cloud

• Compare data infrastructure on-premises versus on Google Cloud.

Module 2) Analyzing Large Datasets with BigQuery

• Identify data analyst tasks and challenges and introduce Google Cloud data tools.
• Explore nine fundamental BigQuery features.
• Compare the differences in roles and toolsets between data analysts, data scientists, and data engineers. •
• Access the BigQuery web UI and explore a public dataset with basic SQL.

Module 3) Exploring your Public Dataset with SQL

• Compare common data exploration techniques.
• Identify the key components of a basic SQL SELECT statement and common pitfalls.
• Discuss the basics of SQL functions and how they create calculated fields with input parameters.
• Explore BigQuery public datasets.
• Troubleshoot dataset quality issues by analyzing duplicate records with SQL in the BigQuery Web UI

Module 4) Cleaning and Transforming your Data with Dataprep

• Characterize different dataset shapes and potential skew.
• Clean and transform data using SQL.
• Clean and transform data using Dataprep.

Module 5) Visualizing Insights and Creating Scheduled Queries

•     Compare data visualizations and make recommendations for improvement.

•     Create dashboards and visualizations with Looker Studio.

Module 6) Storing and Ingesting New Datasets

• Differentiate between permanent and temporary data tables.
• Identify what types and formats of data BigQuery can ingest.
• Differentiate between native BigQuery table storage and external data source connections.
• Load new data into BigQuery.

Module 7) Enriching your Data Warehouse with JOINs

• Explain when to use UNIONs and when to use JOINs.
• Identify the key pitfalls when joining and merging datasets.
• Differentiate between join types visually.
• Explain how union wildcards work and when to use them.
• Write SQL JOINs and UNIONs against a dataset in the BigQuery web UI.

Module 8) Advanced Features and Partitioning your Queries and Tables for Advanced Insights

• Identify the available statistical approximation functions and user-defined functions.
• Apply large-scale record estimation with approximate aggregation functions.
• Deconstruct an analytical window query and explain when to use RANK () and PARTITION.
• Explain when to use Common Table Expressions (WITH) to break apart complex queries.

Module 9) Designing Schemas that Scale: Arrays and Structs in BigQuery

• Differentiate between BigQuery and traditional data architecture.
• Work with ARRAYs and STRUCTs as part of nested fields in data schemas.

Module 10) Optimizing Queries for Performance

• Identify BigQuery performance pitfalls.
• Discuss the Query Explanation map and how to interpret MAX and AVG processing times per stage.
• Describe how to analyze and troubleshoot broken queries.

Module 11) Controlling Access with Data Security Best Practices

• Review data access roles within Google Cloud and BigQuery.
• Highlight key data access pitfalls and how to avoid them.

Module 12) Predicting Visitor Return Purchases with BigQuery ML

• Explain how ML on structured data drives value.
• Describe how customer LTV can be predicted with an ML model.

Module 13) Deriving Insights from Unstructured Data Using Machine Learning

• Discuss how ML is able to drive business value.
• Explain how ML on unstructured data works.
• Differentiate between pre-built ML models, custom models, and new models when considering an AI application strategy.
• Configure traffic management.