How to Build an AI-Powered Data Pipeline
#1: Data Pipeline Summer: A step-by-step transformation pipeline built with the help of AI
Welcome to the first email of this 5-week series, Data Pipeline Summer! In the next 5 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 building dimensional data models in dbt, working with Claude Code as a coding assistant, and building a context layer to power AI-enabled BI.
If you know someone who would benefit from this challenge, be sure to send this to them! It’s never too late to join 😉
Here’s what you can expect over the next 5 weeks:
Week #1: Introduction (today’s newsletter)
Week #2: How to build an AI-ready Data Warehouse
Week #3: Components of a Dimensional Data Model
Week #4: Orchestration with Claude Code
Week #5: How to build a context layer
Why do you need to know how to build data pipelines?
Data pipelining has always been viewed as the responsibility of the data engineer or analytics engineer. However, with AI allowing us to expand our responsibilities, data pipelining is becoming the responsibility of all data practitioners.
Data analysts now need to know how to model data. Analytics engineers need to know how to orchestrate these models and generate the schemas that power them. Not to mention, you also need to understand how you can leverage AI to speed this all up.
By understanding how to build a data pipeline, you control the entire data lifecycle. You know exactly what data came from where, how it was transformed, what schedule it runs on, and what context lives alongside it in the BI tool. Having this much knowledge of every step of the pipeline empowers you to act faster and more strategically.
You no longer need to depend on a data engineer for the source data you need or the stakeholder for the backstory on a business process.
The future of data teams is analytics and data engineers that understand the entire data pipeline, can implement it, and then can use it to solve the business’s problems.
Pieces of a Data Pipeline
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)
Data warehouses are your central hub to store data from different sources, allowing you to query them all in one place.
While a data warehouse is technically a database, it differs from PostgreSQL and MySQL in that it is highly structured and optimized for analytics. This makes it highly performant and ideal for enforcing data quality standards.
When working in a data warehouse, it’s important to consider the architecture and how your development and production environments are set up. It’s also necessary to keep track of costs and performance by reading query plans and optimizing any bottlenecks you come across.
With AI, it’s becoming more important now than ever to ensure you set things up right from the beginning to protect your data but also keep track of how much compute you are using and how much this is costing.
In this challenge, we will learn how to protect our raw and transformed data from AI tooling and access scope. We will also learn how to set things up so you can closely manage costs and performance, two things necessary when AI is querying your data.
Piece #2: Data Ingestion (origin —> destination)
Data ingestion tools move data from various sources into a central location like a data warehouse. They make it so all of your data is in one place, creating a source of truth to start your data modeling from and reference when there are data discrepancies.
When considering how you want to ingest data, it’s important to keep in mind:
the resources available to maintain your method for data ingestion, whether that is a tool like Airbyte or Fivetran, or a Python script
cost
number of data sources being ingested
volume of data being ingested
criticality of the data
All of these factors will lead you to a more enterprise data ingestion tool like Fivetran or an open-source tool like Airbyte. They could also lead you to work directly with APIs via Python scripts.
In this challenge, we will discuss best practices for protecting your raw data and determining who/what should have access to it.
Piece #3: Data Transformation (modeling)
Data transformation takes your raw data and turns it into a dataset that is more easily used for analytics. While data transformation is not the same thing as data modeling, they often get confused.
Data modeling is the technique used to transform your data into robust facts and dimensions that help answer business questions. This is typically done using SQL and a framework like dbt, which makes it easier than ever to produce modular, scalable data models.
In this challenge, we will transform our data using dimensional data modeling techniques and dbt. You’ll learn how to work with Claude Code to follow best practices and achieve a design that is scalable and helps answer our most pressing business questions.
Piece #4: Data Orchestration (scheduling)
Data orchestration tools allow you to run the various parts of your data pipeline from one scheduler. You can think of an orchestration tool as the orchestrator of a symphony, telling each musician when they should begin playing, and helping the symphony create a cohesive, beautiful song.
When orchestrating multiple tools, the first step in your pipeline runs on a cron job, but the following steps depend on the previous piece of the pipeline finishing to run.
Orchestrators allow your pipeline pieces to communicate seamlessly, letting the data flow from one tool to the next without gaps in the records.
Of course, like with anything, there is a scale of complexity. Hosted tools like dbt Cloud are some of the simplest forms, as they only run objects in their own projects, including tests, models, and seeds. Airflow, Dagster, and Prefect are more complex, as they allow you to run different tools together in one location.
Piece #5: Context Layers
Context layers aren’t a “traditional” piece of the data pipeline. However, with AI, I believe they are becoming more important. We are headed towards a world where businesses will have endless access to data, but in order to get the right answers, they will need to have context alongside their data models.
Without context, AI models have no idea how a business operates, what its goals are, how a certain product line functions, or the nuances between different vocabulary. This means that it will make its own best educated guess, giving stakeholders wrong answers.
Context allows stakeholders to use AI to answer their questions using the data models we spent so long building. By building up a context layer alongside our data models, we are eliminating ourselves as a bottleneck and allowing them to dive deep into a clean set of data.
🏆 The project: modeling weekly orders and recipe selection for Aloha Fresh
You will build a data pipeline for a company called Aloha Fresh that uses data from ClickHouse, transforms it into dimensional data models with dbt, and lives alongside a rich business context layer. All of this will be done with the assistance of Claude Code!
Aloha Fresh is a meal prep company that delivers ingredients for making fresh poke bowls along with a recipe on how to cook the meal using the ingredients. Each kit comes with a recipe, fresh fish, herbs, 2 vegetables, a fruit, and any ingredients needed to make the sauce. They have different recipes subscribers can choose from each week based off dietary preferences.
The business is looking to understand things like:
How does the user’s plan affect their AOV and churn rate?
Are certain cohorts more valuable than others?
What types of behavior increases churn risk?
You will be building a data pipeline that allows the business to confidently answer these questions themselves with the help of AI.
🏆 At the end of every newsletter, there will be a challenge for you to complete based on the topic we reviewed. If you have any questions along the way, I will be diligently helping out in the comments and in the subscriber chat. The goal is for you to have a functioning data pipeline at the end of the summer to present in your portfolio!
We get started next week!
⬇️ Drop any questions down below or reply directly to this email.
Madison
The decentralization of data engineering is here. Once you understand how to build and orchestrate the pipeline yourself, your ability to move fast and act strategically skyrockets. Excited to follow along with this summer series!