Data Engineering with Google BigQuery & Google Cloud

Explore how data warehouses function as digital storage for diverse data sources, and how cloud solutions like Google BigQuery and Data Studio enable ingest, prepare, store, analyze, and present insights.

Register for BigQuery with a valid Google account and start in the sandbox to learn storage and query basics within the free tier, with an option to upgrade to billing.

Explore the BigQuery web user interface layout, including the left navigation panel, details panel, and the query editor. Learn to run, save, schedule, and format queries with shortcuts.

Learn to query data in Google BigQuery with standard SQL, inspect tables via preview to avoid charges, and optimize queries with selective columns, limits, and validation, including legacy SQL awareness.

Explore BigQuery basics, including enabling standard SQL, viewing education dataset tables, and crafting efficient queries with select, limit, and proper object naming.

Use built-in BigQuery functions to format numeric values with the FORMAT function for thousand separators, apply common aggregations, and group by country and year with having clause, order, and aliases.

Explore BigQuery data types, from numeric and boolean to string, date, time, datetime, and timestamp, with guidance on when to use each and how null values are handled.

Several alternative queries can yield the same result, such as converting hours to seconds. In this course, off-scope or custom queries may not be answered in the Q&A.

Explore numeric, string, date, time, and timestamp data types in BigQuery with hands-on examples from public datasets like austin bikeshare and incidents.

Explore converting data types in BigQuery, using cast and safe_cast to convert string end_station_id to integer, handle non numeric values like 'Event', and prevent query failures.

Assess data quality by preparing, cleaning, and transforming data to ensure valid, accurate, complete, consistent, and uniform data for reliable insights; garbage in, garbage out.

Clean and transform data in BigQuery using SQL and Dataprep, applying business rules to handle nulls, bad data, and defaults like 'Unknown' and 'Unknown station'.

Store query results in temporary or permanent BigQuery tables; create dataset course_dataset; save results to a permanent table for processing, with expiration and formats like csv, json, or Google Sheet.

Upgrade from sandbox to enable billing for full Google Cloud features and hands-on practice, or view the lecture videos without practicing; reference sections guide you on upgrading.

Learn to clean and transform data with Dataprep by building a multi-step recipe that filters, trims, fills nulls with unknowns, removes duplicates, and adds duration_description column, then loads into BigQuery.

Learn to automate data cleaning and transformation with BigQuery scheduled queries after enabling billing. Create and manage scheduled jobs to overwrite and refresh the clean_austin_bikeshare_trips_sql table on a set schedule.

Analyze clean data from the clean_austin_bikeshare_trips table to derive insights, including top subscriber types by trip counts and top routes by start and end stations.

Master data visualization by turning tables into interactive charts and graphs. Learn to choose the right visuals—donut charts vs line charts—for clear, impactful insights.

Explore essential BigQuery syntax, including select-except, replace, subqueries, and aliases, then work with arrays and structs, unnesting nested data from json and avro.

Load data into BigQuery using the web UI with csv and ndjson files, using auto-detected schemas, handling headers, and importing from Google storage, Google drive, and Google sheets.

Learn to generate mock data in CSV, JSON, or via API using accessible online tools like Mockaroo, free tier available, to speed learning and cut development timelines.

Learn to load data into BigQuery and handle common errors by validating source data, avoiding overreliance on autodetect, and using DDL-defined schemas, thresholds for errors, and ignoring unknown values.

Optimize BigQuery data loads by comparing csv, json, and avro file sizes and compression, and use avro with logical types via cloud shell for faster parallel imports.

Move data from your data center into BigQuery using csv, json, or avro, with streaming options, and explore tools like dataflow, embulk, logstash, and kafka connect.

Explore loading data into BigQuery from HTTP API or Kafka in a microservice architecture using Cloud Functions and BigQuery client libraries, with sample weather data and near real time streaming.

Explore how a messaging system enables application-to-application data exchange through a centralized message broker like RabbitMQ or Kafka, decoupling systems and enabling near real-time, JSON-based integrations.

Learn how to schedule recurring data loads into BigQuery using tools like cron, Cloud Scheduler, and Cloud Functions, loading from Google Cloud Storage into a sample_loan table.

Explore how to use BigQuery views to hide sensitive data by creating sample_employee_view in the course_dataset from sample_employee, and compare standard versus materialized views, noting materialized views cannot be joined.

Learn how to present BigQuery data to non-technical users using Google Sheets and Google Data Studio, with G Suite enterprise edition, and explore the Austin bikeshare dataset from public datasets.

Connect Google Sheets to BigQuery to query and visualize BigQuery data without SQL; requires G Suite enterprise edition and permissions. Create calculated columns, charts, pivot tables, and auto-refresh in Sheets.

Learn to build interactive dashboards in Google Data Studio from BigQuery and external data sources, create charts and filters, add calculated fields, and schedule report delivery.

Explore how to join data in BigQuery using cross join, inner join, and outer joins—left, right, and full—using join columns to combine master supplier and transaction data.

Explore join operations in BigQuery using contractor data: perform inner, outer (left, right, full), and cross joins across profile, project, awards, merchandise, and portfolio, handling json arrays and missing matches.

Explore union and intersect in google bigquery, comparing union all and union distinct while uploading consultant_profile.json and joining consultant_profile with contractor_profile, then select company name, contact_email, and tax identification number.

Explore BigQuery basic statistics with loan_default.json sample data. Compute mean, median via percentile_cont and percentile_discrete, standard deviation (sample and population), and correlation with over() for grouping.