How to Build an Open-Source Data Pipeline

#1: Data Pipeline Summer: A step-by-step transformation pipeline for subscription changes in Trail Trekker

Welcome to the first email of this 6-week series, Data Pipeline Summer! In the next 6 weeks, we will learn how to build a data pipeline from start to finish, using some of the most popular open-source data tools.

By the end of the challenge, you will have hands-on experience setting up DuckDB, creating a SQLMesh project, building a dimensional data model, and orchestrating the models using SQLMesh.

If you know someone who would benefit from this challenge, be sure to send this to them! It’s never too late to join 😉

The benefits of following along with the challenge include:

• Growing confidence in your analytics engineering skills

• Building a project to add to your resume (I’ll tell you exactly how to share and structure this)

• Gaining hands-on experience with data modeling, testing, and orchestration best practices

• Learning how to use the hottest open-source tools like DuckDB and SQLMesh

• Pushing through frustration and landing on a solution you are proud of

---

Here’s what you can expect over the next 6 weeks:

• Week #1: Introduction (today’s newsletter)

• Week #2: How to Set Up DuckDB (sponsored by MotherDuck)

• Week #3: SQLMesh: The Next Generation Open Source Transformation Tool (sponsored by SQLMesh)

• Week #4: 4-Step Dimensional Data Modeling Process

• Week #5: Orchestration with SQLMesh (sponsored by SQLMesh)

• Week #6: Creating a portfolio project

I want to give a special shout-out to SQLMesh and MotherDuck for sponsoring parts of this challenge, making those weeks free to all newsletter subscribers!

Otherwise, the other weeks will be available to paid newsletter subscribers only. If you want access to the entire challenge, be sure to upgrade to a paid subscription.

---

Why do analytics engineers need to know how to build data pipelines?

Analytics engineers own data from the time it is generated to the time it is absorbed in the form of a dashboard or report by stakeholders. They truly own the entire analytics pipeline.

The analytics engineer is the one who acts as the middle-man between data engineers and data analysts. If there’s a data quality issue at the end of the pipeline, its the job of the analytics engineer to fix it. If there’s a data quality issue at the beginning (in the source data), it’s the job of the analytics engineer to work with data engineers to get to the root of it.

They need to understand what is happening at every piece and how that affects the quality of the data. If they don’t, it’s going to be a lot harder to offer reliable data that stakeholders can depend on.

This is why it’s so important that they can own the entire pipeline process from data warehouse architecture and governance strategy to how often data models run and replenish metrics. The more of it they own, the more of it they can control.

Data Pipeline Pieces

Before we start building our data pipeline, we need to understand the purpose of a data pipeline in its entirety and its components. Understanding each piece will help you better construct a pipeline that works as expected and produces high-quality data.

Piece #1: Data Warehouse (storage + processing)