Top 12 Data Engineering Projects for Hands-On Learning

Intermediate Data Engineering Projects

These projects focus on skills like being a better programmer and mixing different data platforms. These technical skills are essential for your ability to contribute to an existing tech stack and work as part of a larger team.

Project 4: Performing a code review

This project is all about reviewing the code of another data engineer. While it may not be as hands-on with the technology as some other projects, being able to review others’ code is an important part of growing as a data engineer. 

Reading and reviewing code is just as important of a skill as writing code. After understanding foundational data engineering concepts and practices, you can apply them to reviewing others’ code to ensure that it follows best practices and reduces any potential bugs in the code.

Resources

Here are some valuable resources, including projects and articles, that provide step-by-step guidance:

Guided projects:

• Performing a Code Review: This guided project offers hands-on experience in code review, simulating the code review process as if you were a senior data professional. It’s an excellent way to practice identifying potential bugs and ensuring best practices are followed.

Articles:

• How to Do a Code Review: This resource provides recommendations on conducting code reviews effectively, based on extensive experience, and covers various aspects of the review process.

Skills developed

• Reading and evaluating code written by other data engineers
• Finding bugs and logic errors when reviewing code
• Providing feedback on code in a clear and helpful manner

Project 5: Building a retail data pipeline

In this project, you'll build a complete ETL pipeline with Walmart's retail data. You'll retrieve data from various sources, including SQL databases and Parquet files, apply transformation techniques to prepare and clean the data, and finally load it into an easily accessible format.

This project is excellent for building foundational yet advanced data engineering knowledge because it covers essential skills like data extraction from multiple formats, data transformation for meaningful analysis, and data loading for efficient storage and access. It helps reinforce concepts like handling diverse data sources, optimizing data flows, and maintaining scalable pipelines.

Resources

Here are some valuable resources, including guided projects and courses, that provide step-by-step guidance:

Guided projects:

• Building a Retail Data Pipeline: This guided project takes you through constructing a retail data pipeline using Walmart’s retail data. You’ll learn to retrieve data from SQL databases and Parquet files, transform it for analysis, and load it into an accessible format.

Courses:

• Database Design: A solid understanding of database design is essential when working on data pipelines. This course covers the basics of designing and structuring databases, which is valuable for handling diverse data sources and optimizing storage.

Skills developed

• Designing data pipelines for real-world use cases.
• Extracting data from multiple sources and different formats.
• Cleaning and transforming data from different formats to improve its consistency and quality.
• Loading this data into an easily accessible format.

Project 6: Factors influencing student performance with SQL

In this project, you'll analyze a comprehensive database focused on various factors that impact student success, such as study habits, sleep patterns, and parental involvement. By crafting SQL queries, you'll investigate the relationships between these factors and exam scores, exploring questions like the effect of extracurricular activities and sleep on academic performance.

This project builds data engineering skills by enhancing your ability to manipulate and query databases effectively. 

You'll develop skills in data analysis, interpretation, and deriving insights from complex datasets, essential for making data-driven decisions in educational contexts and beyond.

Resources

Here are some resources, including guided projects and courses, that provide step-by-step guidance:

Guided projects:

• Factors that Fuel Student Performance: This guided project enables you to explore the influence of various factors on student success by analyzing a comprehensive database. You’ll use SQL to investigate relationships between study habits, sleep patterns, and academic performance, gaining experience in data-driven educational analysis.

Courses:

• Data Manipulation in SQL: A strong foundation in SQL data manipulation is key for this project. This course covers SQL techniques for extracting, transforming, and analyzing data in relational databases, equipping you with the skills to handle complex datasets.

Skills developed

• Writing and optimizing SQL queries to retrieve and manipulate data effectively.
• Analyzing complex datasets to identify trends and relationships.
• Formulating hypotheses and interpreting results based on data.

Project 7: High-performance local analytics with DuckDB

While the previous project focused on writing queries, this project focuses on performance and architecture. You will use DuckDB, a modern "in-process" database, to analyze a dataset that would be too slow or heavy for standard tools like Excel or Pandas.

You will take a large public dataset (like the NYC Taxi Trip Data or Citibike Data), convert it into the industry-standard Parquet format, and run complex aggregation queries. You will learn how "Columnar Storage" allows you to query millions of rows in a fraction of a second on your own laptop, without needing to install a server.

This project is impressive to employers because it shows you are keeping up with the latest trends in the "Modern Data Stack."

Resources

Here are resources to help you build this high-performance project:

• Data Sources:
  • NYC Taxi & Limousine Commission: Use the "Yellow Taxi Trip Records" for a robust, real-world dataset that is perfect for testing speed.
• Documentation:
  • DuckDB "SQL on Parquet": Read the official guide on how to query Parquet files directly. This is the core skill of this project.

Skills developed

• Understanding Columnar Storage (Parquet) vs. Row Storage (CSV).
• Using DuckDB for serverless, high-speed SQL.
• Benchmarking query performance.
• Working with "larger-than-memory" datasets on a local machine.

Advanced Data Engineering Projects

One hallmark of an advanced data engineer is the ability to create pipelines that can handle a multitude of data types in different technologies. These projects focus on expanding your skill set by combining multiple advanced data engineering tools to create scalable data processing systems.

Project 8: Cleaning a dataset with Pyspark

Using an advanced tool like PySpark, you can build pipelines that take advantage of Apache Spark's capabilities. 

Before you attempt to build a project like this, it's important to complete an introductory course to understand the fundamentals of PySpark. This foundational knowledge will enable you to fully utilize this tool for effective data extraction, transformation, and loading.

Resources

Here are some valuable resources, including guided projects, courses, and tutorials, that provide step-by-step guidance:

Guided projects:

• Cleaning an Orders Dataset with PySpark: This guided project walks you through cleaning an e-commerce orders dataset using PySpark, helping you understand how to extract, transform, and load data in a scalable way with Apache Spark.

Courses:

• Introduction to PySpark: This course provides an in-depth introduction to PySpark, covering essential concepts and techniques for effectively working with large datasets in Spark. It's an ideal starting point for building a strong foundation in PySpark.

Tutorials:

• PySpark Tutorial: Getting Started with PySpark: This tutorial introduces the core components of PySpark, guiding you through the setup and fundamental operations so you can confidently start building data pipelines with PySpark.

Skills developed

• Expanding experience with PySpark
• Cleaning and transforming data for stakeholders
• Ingesting large batches of data
• Deepening knowledge of Python in ETL processes

Project 9: Data modeling with dbt and BigQuery

A popular and powerful modern tool for data engineers is dbt (Data Build Tool), which allows data engineers to follow a software development approach. It offers intuitive version control, testing, boilerplate code generation, lineage, and environments. dbt can be combined with BigQuery or other cloud data warehouses to store and manage your datasets. 

This project will allow you to create pipelines in dbt, generate views, and link the final data to BigQuery.

Resources

Here are some valuable resources, including courses and video tutorials, that provide step-by-step guidance:

YouTube videos:

• End to End Modern Data Engineering with dbt: In this video, CodeWithYu provides a comprehensive walkthrough of setting up and using dbt with BigQuery, covering the steps for building data pipelines and generating views. It’s a helpful guide for beginners learning to combine dbt and BigQuery in a data engineering workflow.

Courses:

• Introduction to dbt: This course introduces the fundamentals of dbt, covering basic concepts like Git workflows, testing, and environment management. It’s an excellent starting point for using dbt effectively in data engineering projects.

Skills developed

• Learn about dbt
• Learn about BigQuery
• Understand how to create SQL-based transformations
• Use software engineering best practices in data engineering (version control, testing, and documentation)

Project 10: Airflow and Snowflake ETL using S3 storage and BI in Tableau

With this project, we’ll look at using Airflow to pull in data using an API and transfer that data into Snowflake using an Amazon S3 bucket. The purpose is to handle the ETL in Airflow and the analytical storage in Snowflake. 

This is an excellent project because it connects to multiple data sources through several cloud storage systems, all orchestrated with Airflow. This project is very complete because it has many moving parts and resembles a real-world data architecture. This project also touches on business intelligence (BI) by adding visualizations in Tableau.

Resources

Here are some valuable resources, including courses and video tutorials, that provide step-by-step guidance:

YouTube videos:

• Data Pipeline with Airflow, S3, and Snowflake: In this video, Seattle Data Guy demonstrates how to use Airflow to pull data from the PredictIt API, load it into Amazon S3, perform Snowflake transformations, and create Tableau visualizations. This end-to-end guide is ideal for understanding the integration of multiple tools in a data pipeline.

Courses:

• Introduction to Apache Airflow in Python: This course provides an overview of Apache Airflow, covering essential concepts such as DAGs, operators, and task dependencies. It's a great foundation for understanding how to structure and manage workflows in Airflow.
• Introduction to Snowflake: This course introduces Snowflake, a powerful data warehousing solution. It covers managing data storage, querying, and optimization. It’s perfect for gaining foundational knowledge before working with Snowflake in data pipelines.
• Data Visualization in Tableau: This course covers essential Tableau skills for data visualization, allowing you to turn data into insightful visuals—a core step for interpreting data pipeline outputs.

Skills developed

• Practice creating DAGs in Airflow
• Practice connecting to an API in Python
• Practice storing data in Amazon S3 buckets
• Moving data from Amazon to Snowflake for analysis
• Simple visualization of data in Tableau
• Creating a comprehensive, end-to-end data platform

Project 11: Hacker News ETL in AWS using Airflow

This project tackles a complex data pipeline with multiple steps using advanced data processing tools in the AWS ecosystem.

Instead of dealing with restricted social media APIs, you will use the Hacker News API, which is completely free and open. You will set up Apache Airflow to extract top stories and comments, transform the data to flatten the nested JSON structures, and load it into the cloud.

The architecture follows a standard "Modern Data Stack" pattern:

1. Extract: Airflow triggers a Python script to fetch data from the Hacker News API.
2. Load: The raw JSON data is dumped into an Amazon S3 bucket (your "Data Lake").
3. Transform: You will use AWS Glue to crawl the data and create a schema.
4. Analyze: Finally, you will use Amazon Athena to run SQL queries directly on your S3 data (serverless analysis) or load it into Amazon Redshift for warehousing.

Resources

Here are some resources, including courses and video tutorials, that provide step-by-step guidance:

Documentation:

• Hacker News API: The official documentation is simple and hosted on GitHub. It teaches you how to traverse the "Item IDs" to find stories and comments.

GitHub Repositories:

• News Data Pipeline with Airflow & AWS: Look for repositories that demonstrate "Airflow to S3" pipelines. You can adapt these easily by simply changing the API endpoint from "NewsAPI" to "Hacker News."
• dlt (Data Load Tool) Hacker News Demo: The team at dltHub has a great blog post and repo specifically about pulling Hacker News data into data warehouses. This is a great modern alternative reference.

Courses and tutorials:

• AWS Concepts: This course provides a solid foundation in AWS, covering essential concepts and tools. Understanding the basics of AWS services like S3, Glue, Athena, and Redshift will be crucial for successfully implementing this project.
• AWS Glue & Athena: Look for tutorials specifically on "crawling JSON data in S3 with Glue" to understand how to turn your raw files into queryable tables.

Skills developed

• Orchestration: creating complex DAGs in Airflow to manage dependencies.
• API Interaction: recursively fetching nested data (comments within stories) from a public API.
• Data Lake: Storing raw partition data in Amazon S3.
• Serverless SQL: Using AWS Glue to catalog data and AWS Athena to query it without a database server.
• Infrastructure: Managing AWS permissions (IAM) to allow Airflow to talk to S3.

Project 12: Building a real-time data pipeline with PySpark, Kafka, and Redshift

In this project, you’ll create a robust, real-time data pipeline using PySpark, Apache Kafka, and Amazon Redshift to handle high data ingestion, processing, and storage volumes. 

The pipeline will capture data from various sources in real time, process and transform it using PySpark, and load the transformed data into Redshift for further analysis. Additionally, you’ll implement monitoring and alerting to ensure data accuracy and pipeline reliability.

This project is an excellent opportunity to build foundational skills in real-time data processing and handling big data technologies, such as Kafka for streaming and Redshift for cloud-based data warehousing.

Resources

Here are some resources, including courses and video tutorials, that provide step-by-step guidance:

YouTube videos:

• Building a Real-Time Data Pipeline with PySpark, Kafka, and Redshift: This video by Darshir Parmar guides you through building a complete real-time data pipeline with PySpark, Kafka, and Redshift. It includes steps for data ingestion, transformation, and loading. The video also covers monitoring and alerting techniques to ensure pipeline performance.

Courses:

• Introduction to Apache Kafka: This course covers the basics of Apache Kafka, a crucial component for real-time data streaming in this project. It provides an overview of Kafka’s architecture and how to implement it in data pipelines.
• Streaming Concepts: This course introduces the fundamental concepts of data streaming, including real-time processing and event-driven architectures. It’s an ideal resource for gaining foundational knowledge before building real-time pipelines.

Summary Table of Data Engineering Projects

Here is a summary of the data engineering projects from above to give you a quick reference to the different projects:

Conclusion

This article presented excellent projects to help you practice your data engineering skills. 

Focus on understanding the fundamental concepts behind how each tool works; this will enable you to use these projects in your job search and explain them successfully. Be sure to review any concepts you find challenging.

Along with building a project portfolio, I recommend taking the Professional Data Engineer in Python track and working towards obtaining a data engineering certification. This can be a valuable addition to your resume, as it demonstrates your commitment to completing relevant coursework.