Data Engineering Project SQL, Python, Airflow, Docker, CI/CD

Introduce data extraction and foundational concepts, including what an API is and how to interact with an API.

Learn what an API is through a restaurant analogy, showing how the client, waiter, server, and kitchen interact for data extraction, and introduce obtaining a YouTube API key.

Learn to create and secure a YouTube Data API v3 key via the Google Developer Console, set up a project, restrict the key to public data, and manage credentials.

activate google cloud shell sandbox, use the built-in editor like VS Code, understand quotas and storage limits, and set up a local IDE; next lecture covers the YouTube API.

Explore the YouTube API with API explorer and Postman; build and test requests to fetch video statistics (view, like, and comment counts) using part and id parameters.

Create a dedicated project directory, set up a GitHub repository, initialize git, add and commit a readme, rename the main branch, and push changes to link local and remote repos.

Set up a virtual environment to isolate Python projects and avoid conflicts between versions like 3.4 and 3.10. Activate, install with pip, and use gitignore to exclude venv and pycache.

Extract seven video variables from the MrBeast YouTube channel using the YouTube API, including video ID, title, published at, duration, views, likes, and comment counts.

Develop a Python script to fetch YouTube channel playlist ID via the YouTube API using requests, handle errors with try-except, and build modular code ready for ELT, docker, and airflow.

Learn to secure code changes by using a dot env file to store API keys, load them from the env, and add to .gitignore to prevent exposure.

Develop a Python function to fetch unique video IDs from a playlist using the playlist item resource, handling pagination with next page tokens and robustly parsing contentDetails.videoId.

Implement a batch-based function mapping video IDs to seven variables via snippet, content details, and statistics; batch IDs, build the API URL, fetch data, and accumulate results.

Plan to load data into the data warehouse by saving extracted video data as json in the data folder with a daily dated file and utf-8 encoding.

Use Docker to run Airflow DAGs for development and small-scale production, and learn to build a Dockerfile and docker-compose file while comparing Kubernetes and Google Cloud Composer options.

Create a dockerfile based on the official airflow image, pin airflow 2.9.2 and python 3.10, set airflow home to /opt/airflow, and install requirements with no-cache.

Learn to build a Docker image, push it to a registry like Docker Hub, and create a repository; login, tag with a version, and verify the pushed image.

Explore airflow architecture, including the scheduler, executors, docker compose deployment, and production readiness with a web UI, while noting dag, metadata database, trigger, and message broker.

Explore airflow directories, set up docker volumes to mount local dags and logs into containers, and review config and plugins, tests, data, include for data quality yaml resources.

amend the env file to reference environment-specific variables, including docker hub credentials, postgres connection details for three databases, airflow parameters, and a fernet key; do not change variable names.

Learn to configure and deploy Airflow with Docker Compose, defining multi-container services, environment variables, volumes, and a Postgres backend, plus health checks and restart policies.

Spin up the airflow environment with docker compose in detached mode, verify containers with docker ps, and access the scheduler container via docker exec.

Explore how Airflow runs inside Docker containers and create DAGs to execute code, building on prior Docker concepts in the course.

Refactor your data pipeline by turning a Python script into an Airflow dag using task decorators, manage variables, and schedule a daily json output.

Continuing the ELT workflow, this lecture covers loading data from the API into the Postgres data warehouse and applying transformations to produce the final data.

Define schemas and tables for a data warehouse, implement Python functions to insert, update, and delete data, and transform raw data from the staging to the refined core layer.

Establish a Postgres data warehouse connection in Airflow using the Postgres hook, focusing on connection and cursor management. Build modular Python utilities to create schemas and tables and manage queries.

Create staging and core schemas and their YouTube_API tables, with Python functions to create schemas, create tables, and fetch video IDs.

Build a Python data loading script to read json api data from the data directory with a load_path function, using json parsing and logging for robust error handling.

Transform staging data into the core layer by parsing ISO 8601 video durations into hours, minutes, and seconds, and classify videos as shorts or normal with a Python script.

Build and populate the staging and core schema layers in the data warehouse. Use python and airflow to implement upsert logic with insert, update, delete, logging, and data transformations.

Define and debug a data warehouse dag to process a json file, insert data into staging and core schemas, adjust schedule, and troubleshoot with docker and airflow logs.

Learn to view data in a data warehouse using SQL in a PostgreSQL Docker container or the DBeaver UI, and query the YouTube API table in the ELT database.

Explore data quality checks and functional tests essential for data engineers, including duplicates and missing value checks, and learn how unit, integration, and end-to-end tests ensure data accuracy.

Learn to implement data quality tests using soda core, define checks in YAML, and run soda scan against Postgres in a dockerized Airflow setup.

Master Airflow integration for data quality tests by using the Airflow batch operator to run batch commands, manage YAML checks and configuration, and validate staging and core schemas.

Learn functional testing in data engineering by implementing unit, integration, and end-to-end tests with pytest in Python, using a base Docker image and validating with Docker commands.

Learn to write unit tests with pytest, using fixtures to mock credentials, databases, and API keys, and validate DAGs and ELT processes in isolation.

Execute end-to-end tests of the ELT route using Airflow dag test to validate API extraction, JSON output, and data quality checks across produce_json and update_db dags.

Explore continuous integration and continuous development (CI/CD) and learn what, how, and why to implement CI/CD. We will answer the how by using GitHub actions workflows.

Commit and push all changes before the ci/cd section to version your work, add any untracked changes with a meaningful commit message, and push to update the GitHub workflow.

Create a GitHub actions CI/CD workflow that triggers on push, PR, or manual dispatch, builds and pushes Docker images, and manages Docker Hub secrets.

Coordinate GitHub Actions ci-cd testing by using needs to run the build before pytest and Airflow dag tests, with secrets and env variables wired into docker compose.

Explore manually triggering GitHub Actions with workflow dispatch, adjust if conditions for changed files, and verify docker builds, image tags, and CI/CD pipeline success.

You have successfully finished this data engineering course; celebrate your achievement and apply what you learned in the workplace, then consider leaving a rating about your experience.