Data Warehousing with BigQuery: Storage Design, Query Optimisation, and Administration

Name: Data Warehousing with BigQuery: Storage Design, Query Optimisation, and Administration
Price: 1000.00 GBP

Learn about the internal workings of BigQuery and best practices for designing, optimising, and administering your data warehouse.

3 day course

Supporting material

Google Cloud Partner of the Year

Private

A private training session for your team. Groups can be of any size, at a location of your choice including our training centres.

On this three-day course, you’ll master BigQuery architecture and discover how to design optimal storage and schemas for data ingestion and changes.

Jellyfish has recently been named a Google Cloud Specialisation Partner of the Year. This title recognises our commitment to provide world-leading Cloud-based Training solutions that help our clients succeed. All of our trainers are experienced practitioners, so you can learn with total confidence.

Through a combination of lectures, demos, and labs, you’ll cover techniques to improve read performance, optimise queries, manage workloads, and use logging and monitoring tools.

You’ll also learn about the different pricing models. Finally, you’ll go over various methods to secure data, automate workloads, and build machine learning models with BigQuery ML.

This Data Warehousing with BigQuery: Storage Design, Query Optimisation, and Administration course is offered as a private training session that can be delivered via Virtual Classroom or at a location of your choice in the UK.

Course overview

Who should attend:

Data analysts, data scientists, data engineers, and developers who perform work on a scale that requires advanced BigQuery internal knowledge to optimise performance.

Walk away with the ability to:

Describe BigQuery architecture fundamentals
Implement storage and schema design patterns to improve performance
Use DML and schedule data transfers to ingest data
Apply best practices to improve read efficiency and optimise query performance
Manage capacity and automate workloads
Understand patterns versus anti-patterns to optimise queries and improve read performance
Use logging and monitoring tools to understand and optimise usage patterns
Apply security best practices to govern data and resources
Build and deploy several categories of machine learning models with BigQuery ML

Prerequisites

To get the most out of this course, participants should complete Google Cloud Fundamentals: Big Data and Machine Learning.

Course agenda

Module 1: BigQuery Architecture Fundamentals

Introduction
BigQuery Core Infrastructure
BigQuery storage
BigQuery query processing
BigQuery data shuffling

Module 2: Storage and Schema Optimisations

BigQuery storage
Partitioning and clustering
Nested and repeated fields
ARRAY and STRUCT syntax
Best practices

Module 3: Ingesting Data

Data ingestion options
Batch ingestion
Streaming ingestion
Legacy streaming API
BigQuery storage write API
Query materialisation
Query external data sources
Data transfer service

Module 4: Changing Data

Managing change in data warehouses
Handling slowly changing dimensions (SCD)
DML statements
DML best practices and common issues

Module 5: Improving Read Performance

BigQuery's cache
Materialised views
BI engine
High throughput reads
BigQuery storage read API

Module 6: Optimising and Troubleshooting Queries

Simple query execution
SELECTs and aggregation
JOINs and skewed JOINs
Filtering and ordering
Best practices for functions

Module 7: Workload Management and Pricing

BigQuery slots
Pricing models and estimates
Slot reservations
Controlling costs

Module 8: Logging and Monitoring

Cloud monitoring
BigQuery admin panel
Cloud audit logs
INFORMATION_SCHEMA
Query path and common errors

Module 9: Security in BigQuery

Secure resources with IAM
Authorised views
Secure data with classification
Encryption
Data discovery and governance

Module 10: Automating Workloads

Scheduling queries
Scripting
Stored procedures
Integration with Big Data products

Module 11: Machine Learning in BigQuery

Introduction to BigQuery ML
How to make predictions with BigQuery ML
How to build and deploy a recommendation system with BigQuery ML
How to build and deploy a demand forecasting solution with BigQuery ML
Time-series model with BigQuery ML
BigQuery ML explainability

Book this course

Call our sales team today

020 7993 4556