Master Data Engineering using GCP Data Analytics

Set up a data engineering environment for GCP data analytics on Windows or Mac, including VS Code, Python 3.9, path configuration, and cloning the repository to build your workspace.

Configure the Windows environment by updating the system path to include Python 3.9 so the Python CLI launches from Windows PowerShell, then validate with Hello World.

Download the data engineering on gcp repository as a zip, extract it locally, and set up a VS Code workspace to access notes, notebooks, scripts, and data.

Review the data engineering on GCP workspace setup, including markdown materials, notebooks, datasets, apps, and scripts, to configure a VS Code workspace for GCP data analytics services.

Set up a Python 3.9 virtual environment and integrate it with the VS Code workspace using the terminal. Create venv, verify Python 3.9, and select the interpreter to activate it.

Identify the prerequisite skills for this course, including required SQL and programming knowledge (Python preferred), with highly desired data engineering and data warehousing fundamentals and command line tool familiarity.

Explore cloud platforms and their core features, including quick setup, marketplace access, pay-as-you-go models, and scalable infrastructure, with emphasis on AWS, Azure, and GCP.

Explore the Google Cloud Platform marketplace of native and third-party services, managed via CLI or the Google Cloud SDK, and learn to select storage, databases, and serverless options.

Sign up for GCP with a valid Google account, claim $300 credits for three months, and set up a project while reviewing billing.

Learn how to create a Google account with a non Gmail id and sign up for Google Cloud Platform, including email and phone verification steps.

Sign up for GCP using a Google account, start free with $300 credit, enter email and password, add card details, and complete setup with data management and analytics in mind.

Activate the $300 GCP credit and use it for up to 90 days, with free-tier limits per service (e.g., BigQuery 1 TB, storage 5 GB) before charges apply.

Learn to manage a GCP project from signup to naming, noting that the project ID cannot be changed after creation, and review billing, credits, and gcloud setup on Windows.

Master data engineering with GCP data analytics by installing and initializing the Google Cloud SDK and gcloud CLI, authenticating, selecting a project, and validating access to Google storage with gsutil.

Get started with data engineering on Google Cloud Platform by setting up the Google Cloud SDK, validating your Google Cloud account, and exploring analytics services.

Learn to use the Google Cloud Storage web UI to create buckets, manage objects, choose multi-region placement, select storage class, and apply versioning, retention, and encryption.

Use gsutil to list buckets and objects in gcs via cloud shell, handle authorization prompts, view bucket URLs, and prepare for programmatic object management with Python.

Clone the data repository in the Google Cloud Shell home folder and use gsutil to manage files and buckets in GCS, then explore retail_db and retail_db_json datasets.

Master gsutil to manage google cloud storage buckets and files. Use mb to create and rb to delete buckets, emptying before deletion, and copy local files to GCS.

Create a new GCS bucket and copy the retail_db dataset into it using gsutil from a notebook, then validate with gsutil ls.

Discover how to manage files in Google Cloud Storage using Python, configure a virtual environment, install the Google Cloud Storage library, and handle credentials for notebook and script usage.

Authenticate external python applications to Google Cloud storage by setting up credentials with gcloud auth application-default login, then create a GCS client to manage buckets and files.

Upload all files and folders from a local data folder to Google Cloud Storage using Python, with glob and os, leveraging the google.cloud.storage module to address the problem statement.

Review the Get_file_names function to recursively collect data files with glob and os, filter out SQL scripts, and prepare a list for uploading to GCS in parquet format.

Learn to upload files to Google Cloud Storage as blobs using Python, preserving folder hierarchy under the python demo bucket and constructing blob names with split and join.

Validate that the nine local files are uploaded to GCS as blobs by listing local data, using gsutil ls -r, and programmatically listing blobs with Python.

Read and validate data stored in Google Cloud Storage using pandas and GCSFS, leveraging read_csv and optional column names. Write processed data to GCS in parquet format, following notebook examples.

Process local data with pandas, convert to a dataframe, and write to Google Cloud Storage in parquet format using pyarrow and gcsfs, applying a schema and disabling index.

Load schemas.json into a Python dict, extract column names by sorting on column position, and validate across datasets. Next lecture covers pandas read and write to GCS.

Validate that parquet files copied to GCS match the source data by reading with pandas read_parquet, comparing shapes and row counts across six datasets.

Set up a Postgres database server using Cloud SQL on Google Cloud Platform by enabling the API and creating a Postgres instance in development with Postgres 14.

Configure network access for Cloud SQL Postgres, validate connectivity with Telnet, and manage authorized networks by adding your IPv4 address and handling IP changes.

Connect to the postgres server on Windows using pgAdmin to manage databases and tables via the GUI, and optionally use the PSQL CLI for operations.

Connect to a cloud sql postgres database using psql, create the database and user, grant permissions, and run scripts to set up tables and datasets.

Learn how to connect Python to a Cloud SQL Postgres instance using psycopg2-binary, install via pip in a virtual environment, specify host and port 5432, run queries with a cursor.

Explore integrating GCP Cloud SQL Postgres with pandas to build lightweight data pipelines, using pandas with SQLAlchemy and psycopg2-binary to interact with Postgres for read and write ETL.

Process data using the pandas DataFrame API to group by order date and status, compute counts via agg, and reset the index for loading results into a Postgres database.

Validate data in Postgres databases by loading data with pandas read_sql into a dataframe, query with SQL or read_sql_table, and verify daily_status_count against expectations.

Discover how to use GCP Secret Manager to replace hardcoded credentials, enable the API, create secrets with JSON payloads for database connections, and prepare Python apps to use them.

Configure Google Cloud secret manager access for external applications by assigning the secret manager secret access role via iam, enable the api, and prepare for programmatic secret use.

Install the Google Cloud Secret Manager Python library and verify your environment is set up; then learn to retrieve secret details to connect to the database in upcoming lectures.

Learn to connect to a postgres database using credentials retrieved from GCP secret manager, building a dynamic connection uri from secret details and avoiding hard-coded credentials with pandas.

Create a dataset and an empty orders table in Google BigQuery using the UI, selecting project ID, location, and a four-field schema: order_id integer, order_date timestamp, order_customer_id integer, auto_status string.

Learn to load data from GCS to a BigQuery table with Python, using the Google Cloud BigQuery library, creating a client, and running a query.

Learn to run BigQuery queries from Python to validate loaded data. Create a BigQuery client, run a query job, and read order_id, order_date, order_customer_id, and order_status.

Explore the SQL capabilities of Google BigQuery, a GCP data warehouse, including standard SQL features, predefined functions, joins, aggregations, windowing, and JSON processing for data engineering, BI, and AI/ML applications.

Learn to compute daily product revenue in Google BigQuery by inner joining orders and order items, summing order items subtotal, rounding, and sorting by date asc and revenue desc.

Learn to compute daily revenue in Google BigQuery by joining orders and order items, then build monthly cumulative revenue using partition by month and cumulative sum with a CTE.

Explore key Google BigQuery integrations, including GCS data loading and external tables, Cloud SQL and ETL pipelines, and Spark on Dataproc to feed BigQuery-backed applications in Python, Java, or Scala.

Explains how to connect pandas with Google Cloud BigQuery using the pandas-gbq plugin, install required libraries, run queries via read_gbq, and load results into a dataframe, enabling write-back to BigQuery.

Set up a Cloud SQL Postgres server, configure BigQuery external queries, validate the retail database and its tables, and use pandas with SQLAlchemy to run queries.

Configure a static IP for the Dataproc master node VM by reserving the IP from ephemeral addresses, ensuring it remains static for the cluster, and enabling VS code development.

Connect visual studio code to a dataproc vm via the remote window, using ssh configuration and the remote development extension pack to set up the development environment.

Set up a VS Code workspace on the Dataproc master node by cloning data engineering on GCP repository with Git. Open notebooks, scripts, and datasets to learn Hadoop and SPARC.

Review local datasets in the data folder, including retail_db, and copy data into HDFS from the Dataproc master node VM using HDFS commands, with sql scripts and schemas.json guidance.

Create the /public folder on the hdfs root, copy the local retail_db folder there with hdfs dfs -put, and verify via hdfs dfs -ls /public.

Launch Spark Shell to validate the Spark Scala CLI on Dataproc, create dataframes from orders and order_items, and compute daily revenue with sum, round, and dollar.

Validate Spark SQL CLI on a Dataproc cluster by creating temporary views orders and order_items, joining them to compute daily product revenue, and reviewing view definitions and descriptions.

Learn to run spark sql queries on dataproc against json datasets in GCS using file paths, then save daily status counts back to GCS in json format with insert overwrite.

Run a Spark SQL script to convert order items data into Parquet, validate results with unit tests, and verify the order items table and related data in the target database.

Develop a spark-sql cleanup script to drop retail_bronze_DB and retail_gold_DB with cascade, enabling repeated workflows. Validate by running show databases and gsutil ls checks on the airetail bucket.

Validate spark sql scripts in GCS by enforcing cleanup, file format conversion for orders and order items, and daily product revenue. Use local validation and unit tests.

Examine the limitations of running Spark SQL scripts in Dataproc jobs and workflows, including query text versus file, path handling for GCS/HDFS, and CLI workarounds.

Learn to run dataproc jobs with gcloud, submitting spark sequel jobs using -e or --execute with a cluster, including simple queries like select current_date, then monitor in the webui.

Learn to submit data proc jobs using spark sql scripts with gcloud and gsutil, pass parameters correctly, convert data to parquet, and validate results in retail_bronze_db.

Master dataproc job management with gcloud commands, including submission, maintenance, and instantiating workflows, and prepare for workflow templates and orchestration with tools like airflow.

Add jobs to dataproc workflow templates with gcloud data proc workflow templates add-job, using spark sql scripts and step id, parameters, and file paths for orchestrated pipelines.

Learn to run and validate an ELT pipeline on Dataproc using gcloud data proc commands, including pre and post run validation, cascade database cleanup, and pipeline review.

Learn to stop, start, or delete a Google Dataproc cluster to stop charges from GCP and retain work on the master node, using gcloud data proc commands.

Explore how Databricks on GCP, a cloud-agnostic big data processing platform available on GCP and Azure, enables orchestrated pipelines with Databricks workflows and GCS integration for end-to-end analytics.

Create a Databricks workspace on GCP by configuring the project ID, region US central, quotas, and Google Cloud storage buckets, then connect to set up clusters and deploy jobs.

Spin up a databricks cluster on google cloud platform using the ui, choose a single node with the latest runtime, name it analytics dev cluster, and set a termination time.

Explore the Databricks architecture and detail control plane, data plane, dbfs, external data sources, and how you manage notebooks, jobs, and clusters via web ui, cli, and rest apis.

Master the Databricks CLI to manage clusters, dbfs files via fs, jobs, and secrets, explore the control plane, and use --help and profile options.

Copy the retail_db from the local file system to dbfs with Databricks fs cp --recursive, creating the public/retail_db path, then verify with dbfs ls.

Create a temporary view for order_items using Spark SQL, guided by the data model diagram, and validate it with describe and select to prepare for calculating daily product revenue.

Compute product revenue by joining orders and order_items views in Spark SQL, filtering for complete or closed orders, group by date and product to sum revenue rounded to two decimals.

Write the daily revenue results to dbfs in parquet format using insert overwrite to a specific path, then verify the output by querying and sorting by date and revenue.

Orchestrate data pipelines using Databricks workflows by building jobs with dependent tasks, configuring notebooks or Python scripts, and triggering executions internally or via external APIs.

Learn how to pass parameters to SQL based notebooks in Databricks workflows, including using widgets and Python magic to pass and read arguments from external notebooks or jobs.

Create and run your first Databricks job using workflows by adding notebook tasks, configuring clusters, passing parameters, and setting task dependencies.

Orchestrate an ELT pipeline in Databricks to compute daily product revenue, clean the bronze database and parquet folders, and review notebooks with dbutils widgets for dynamic gold data paths.

Review the file format converter pyspark code that reads CSV data, applies schemas from schemas.json, and writes parquet files for orders, with schema lookup and validation via spark read.parquet.

Orchestrate an ELT pipeline in Databricks by cleaning data, converting csv to parquet, creating spark sql tables, and computing daily product revenue using a bronze to gold flow.

Build an elt pipeline with a Databricks job in workflows to compute product revenue, using tasks like cleanup_retail_datalake, convert_orders, convert_order_items, create table orders, create table order_items, with bronze_base_dir and gold_base_dir.

Run and review a Databricks ELT pipeline job by passing source_base_dir, bronze_base_dir, and gold_base_dir, then inspect task details, notebook outputs, Spark UI metrics, and logs for troubleshooting.