Data Engineering
Data Warehousing with BigQuery: Storage Design, Query Optimisation, and Administration
Delve into the internal workings of BigQuery in this comprehensive course. Learn best practices for designing efficient storage, optimizing queries, and administering your data warehouse for peak performance. Ideal for professionals ready to enhance their BigQuery expertise
3 Days
Target Audience
This course is designed for data professionals, including analysts, scientists, engineers, and developers, who work on large-scale datasets and need advanced knowledge of BigQuery internals to optimize performance and efficiency
What you'll learn
BigQuery Essentials:
Understand architecture, storage design, and data ingestion techniques.
Optimize and Automate:
Improve query performance, manage capacity, and automate workloads.
Secure and Innovate:
Ensure data security, monitor usage, and build ML models with BigQuery ML.
COURSE AGENDA
BigQuery Architecture Fundamentals
- Introduction to BigQuery’s core infrastructure.
- Explore storage architecture, query processing, and data shuffling.
Storage & Schema Optimizations
- Learn advanced storage techniques, including partitioning and clustering.
- Work with nested and repeated fields using ARRAY and STRUCT syntax.
- Apply best practices for schema design to maximize performance.
Ingesting Data
- Understand data ingestion options: batch and streaming.
- Use the legacy streaming API and BigQuery storage write API.
- Query external data sources and utilize the Data Transfer Service.
Changing Data
- Manage change in data warehouses using DML statements.
- Handle slowly changing dimensions (SCD) effectively.
- Apply best practices and troubleshoot common issues with DML.
Improving Read Performance
- Leverage BigQuery’s cache, materialized views, and BI Engine.
- Implement high-throughput reads using the BigQuery storage read API.
Optimizing & Troubleshooting Queries
- Execute efficient queries with SELECTs, aggregations, and JOINs.
- Address skewed JOINs, filtering, and ordering.
- Follow best practices for query functions.
Workload Management & Pricing
- Understand BigQuery slots and pricing models.
- Reserve slots and control costs effectively.
Logging & Monitoring
- Monitor BigQuery performance using Cloud Monitoring and the admin panel.
- Leverage Cloud Audit Logs and INFORMATION_SCHEMA for insights.
- Understand query paths and resolve common errors.
Security in BigQuery
- Secure resources with IAM and authorized views.
- Implement data classification, encryption, and governance.
Automating Workloads
- Schedule queries and use scripting for automation.
- Develop stored procedures and integrate with Big Data products.
Machine Learning in BigQuery
- Get started with BigQuery ML.
- Build and deploy machine learning models, including:
- Recommendation systems.
- Demand forecasting solutions.
- Time-series models.
- Explore model explainability features.