The One Thing No One Teaches You About Data Pipelines

It All Started with a Simple dbt Model

---

---

What a Data Pipeline Is:

A data pipeline moves data from where it originates to where it gets used and transforms it along the way.

Raw data arrives from APIs, databases, files, or event streams.

The pipeline cleans it, reshapes it, and delivers it to a destination, for example, a data warehouse, a CSV file, a dashboard, or a machine learning model.

Today I will show you how these pipelines work and how to build your first one.

---

Data Pipeline architecture:

The Medallion Architecture:

Medallion architecture is the design to refine raw data into clean, business-ready datasets.

It organizes data pipelines into three layers:

Bronze Layer:

The bronze layer is the raw data landing zone. Source systems send records exactly as they arrive, without any cleaning, transformation, or deduplication.

Silver layer:

The silver layer is the curated and cleaned zone. Engineers clean, standardize, and deduplicate raw Bronze data with schema enforcement and transform the data into standardized datasets.

Gold layer:

The gold layer is the business-ready zone. Analysts aggregate, enrich, and model curated Silver data into datasets for reporting, dashboards, and advanced analytics.

Data pipeline elements:

Extract:

Pull data from APIs, databases, files, or streams.

Transform:

Clean, normalize, aggregate, and enrich the raw data.

Load:

Transfer data into a cloud data warehouse or data lake, where it can be stored or further transformed.

Transformation Types:

ETL

ETL (Extract, Transform, Load) applies transformations before loading.

ELT

ELT (Extract, Load, Transform) loads raw data first and then transforms it inside the destination.

ELT preserves raw source records for reprocessing when business rules change. It is the current default starting point for cloud analytics.

Reverse ETL:

Moves processed data from the warehouse back into operational tools, closing the analytics loop.

---

Build Your First Data Pipeline:

Install Python libraries:

In our case:

• Pandas: For data manipulation.

• SQLAlchemy: To connect and interact with databases.

Extract the data:

SQLAlchemy can connect to a database, and Pandas will load the query result into a DataFrame.

import pandas as pd
from sqlalchemy import create_engine

# Database connection setup
db_engine = create_engine(’postgresql://user:password@localhost:5432/example_db’)

# SQL query to extract data
query = “SELECT * FROM sales_data”

# Load data into a Pandas DataFrame
df = pd.read_sql(query, db_engine)

# Print result on the console
print(f”Output:\n {df}”)

create_engine establishes a connection to a PostgreSQL database, and pd.read_sql() extracts data based on the SQL query. The extracted data loads into a DataFrame for further manipulation.

Transform the data:

Data needs cleaning or transformation after extraction.

You should deal with missing values, modify data types, and create new derived columns.

Example:

# Fill missing values and change data type
df[’sales_amount’] = df[’sales_amount’].fillna(0).astype(float)

# Add a new column categorizing sales into high, medium, and low
df[’sales_category’] = pd.cut(df[’sales_amount’], bins=[0, 100, 500, float(’inf’)], labels=[’Low’, ‘Medium’, ‘High’])

# Print result on the console
print(f”After Transformation:\n {df}”)

Missing values in the sales_amount column fill with 0, and the data type converts to float.

A new column, ‘sales_category,’ categorizes sales into ‘Low,’ ‘Medium,’ and ‘High’ based on predefined ranges.

Load the data:

After transforming the data, load it into a destination: database or cloud storage.

SQLAlchemy writes the data back into a database.

# Load the transformed data into a new table in the PostgreSQL database
df.to_sql(’transformed_sales_data’, db_engine, if_exists=’replace’, index=False)

df.to_sql() loads the transformed data into a new table named transformed_sales_data in the same PostgreSQL database.

Validate and test:

Data validation tools:

1. Pandera

Pandera validates DataFrame-like objects: Pandas, Polars, Dask, PySpark.

The library supports object-based and class-based schema definitions, custom checks, cross-column validation, and statistical hypothesis testing.

Pandera integrates with ML pipelines and supports lazy validation to collect all errors at once.

Example:

import pandas as pd
import pandera as pa
from pandera import Column, DataFrameSchema, Check

# Define a schema for customer data
customer_schema = DataFrameSchema({
    “customer_id”: Column(int, Check.greater_than(0)),  # must be positive integers
    “name”: Column(str, Check.str_length(min_value=1)), # non-empty strings
    “age”: Column(int, Check.in_range(18, 99)),         # age between 18 and 99
    “email”: Column(str, Check.str_matches(r”.+@.+\..+”)), # must look like an email
    “purchase_amount”: Column(float, Check.greater_than_or_equal_to(0)) # non-negative
})

# Example DataFrame
df = pd.DataFrame({
    “customer_id”: [1, 2, 3],
    “name”: [”Alice”, “Bob”, “Charlie”],
    “age”: [25, 40, 17],  # <-- 17 will fail validation
    “email”: [”alice@example.com”, “bob@example.com”, “charlieexample.com”], # <-- invalid email
    “purchase_amount”: [100.5, 200.0, -50.0]  # <-- negative will fail validation
})

# Validate the DataFrame
try:
    validated_df = customer_schema.validate(df)
    print(”Validation passed!”)
except pa.errors.SchemaErrors as err:
    print(”Validation failed!”)
    print(err.failure_cases)  # shows which rows/columns failed

Data quality gates:

Data quality gates should run at every layer.

Here are two of the most popular tools that dominate Python validation:

1. Great Expectations uses declarative expectations to describe what data should look like.

GX runs before transformations, after transformations, and on schedules between pipeline runs.

from great_expectations.datasource.fluent import Datasource
context = gx.get_context()

batch_request = context.sources.add_pandas(”orders”).read_csv(”orders.csv”)

validator = context.get_validator(batch_request=batch_request)
validator.expect_column_values_to_not_be_null(”order_id”)
validator.expect_column_values_to_be_between(”total_usd”, min_value=0, max_value=1_000_000)
validator.expect_column_values_to_be_in_set(”status”, [”pending”, “completed”, “refunded”])
validator.save_expectation_suite(”orders_suite”)

2. dbt tests catch deterministic invariants. Built-in tests include not_null, unique, relationships, and accepted_values.

Example:

version: 2

models:
  - name: customers
    columns:
      - name: id
        tests:
          - not_null
          - unique

In the customers model, the id column must not be null and must be unique.

---

Tools:

Data Ingestion Tools:

Data ingestion is the process of selecting and pulling raw data from source systems into the pipeline. Only valuable data enters the pipeline.

dlt

The Data Load Tool library coordinates schema extraction and automated normalization and loads it across multiple targets: DuckDB, BigQuery, Snowflake, and PostgreSQL.

It evaluates generator functions, maps nested JSON documents to relational table structures, and executes schema alterations.

Airbyte

Airbyte is an open-source data replication platform with a large connector ecosystem and flexible deployment options.

It supports ETL, ELT, and reverse ETL, with dbt-based transformations.

CDC connectors process log-based replication for databases.

Fivetran

Fivetran integrates with dbt for in-warehouse transformations and supports strict governance and compliance.

Data Transformation Tools:

dbt:

DBT (Data Build Tool) does SQL transformations inside the warehouse.

The standard project layout uses staging, intermediate, and mart layers.

The best use is when the workload is SQL-heavy inside a warehouse.

SQLMesh:

SQLMesh extends dbt’s concepts with semantic understanding of transformation DAGs and true CI/CD for SQL pipelines.

It supports virtual environments for models, safe testing, and deployment across environments.

SQLMesh runs on multiple execution engines, including DuckDB, Spark, BigQuery, and Snowflake.

Orchestration Tools:

Apache Airflow:

Use when inheriting existing infrastructure or when the ecosystem breadth is important.

Dagster:

Dagster takes an asset-centric approach, tracking the data objects each pipeline produces and consumes. The development environment supports unit testing of pipeline components.

Use for greenfield projects with strong governance needs.

Prefect:

Prefect is simpler than Airflow. Pipelines are defined as Python functions decorated with @flow and @task.

The open-source server can do UI for monitoring runs, and the cloud tier adds advanced features: automatic retries, caching, and concurrency limits.

Use when simplicity is the priority and the team is small.

---

Data processing libraries:

Pandas:

Pandas is a tool for data manipulation, especially for small datasets, ML pipelines, and interactive notebooks. For large-scale ETL, Polars and DuckDB are better.

Fireducks:

FireDucks is a high-performance replacement for pandas. It is designed to accelerate data manipulation and analysis pipelines without any changes to existing pandas source code.

Polars:

Polars is a DataFrame library written in Rust with Python bindings. It supports both eager and lazy execution, query optimization and streaming for datasets larger than memory.

Polars outperforms pandas on most transformation workloads, with true multi-threading and a clean, expressive API.

PySpark:

PySpark is the Python API for Apache Spark, supporting distributed batch and streaming data processing.

Pydantic:

Raw data from APIs contains missing fields, incorrect types, and null values. Pydantic validation catches the errors before transformation.

DuckDB:

DuckDB runs SQL queries directly on Parquet, CSV, and JSON files. A Python script imports the duckdb library.

The script runs a SQL query to join a Parquet file with a CSV file, and DuckDB returns the result as a Python list or a Pandas DataFrame.

The pipeline reads raw files, transforms them with SQL, and writes the output to a clean Parquet file.

SQLAlchemy:

SQLAlchemy is a bridge between applications and databases. It runs the database connectivity, query construction, object-relational mapping (ORM).

---

Storage formats:

Apache Parquet is the columnar storage format for analytics pipelines.

Parquet Best:

• Row group size: 128–512 MB for optimal I/O and parallelism.

• Partitioning: By columns frequently used in filters, with low cardinality.

• Compression: Snappy for speed, Gzip or ZSTD for storage savings.

• Predicate pushdown: query engines can skip irrelevant data.

• Avoiding small files: Consolidate to 128 MB–1 GB files to reduce metadata overhead.

GeoParquet extends Parquet for geospatial data.

Lakehouse architectures combine flexible object storage with structured table formats, supporting direct access, versioning, and schema management.

---

Best Practices:

• Modular design: break pipelines into small, testable functions: one for extraction, one for each transformation step, and one for loading. Each function performs a single operation, making debugging and reuse easier.

• Checkpointing: store the high-water mark after each successful batch. If the pipeline fails, it restarts from the last checkpoint rather than reprocessing all data.

• Version control and testing.

• Normalize everything to UTC immediately after extraction.

• Start with a clear understanding of your pipeline’s requirements: data sources, volume, latency, transformation complexity, and compliance needs.

• Batch processing: collect data for an hour, a day, or a week, then process it all at once.

• Incremental processing: tracks what has already been processed and only handles new or changed records. It uses timestamps, sequence numbers, or change-data-capture logs.

---

Common Mistakes:

• No schema validation: Use Great Expectations and dbt schema. yml files.

• Everything in one script. Split each part into a separate script.

• Hardcoded credentials: Use python-dotenv.

• print() instead of logging makes debugging impossible. Use Python’s built-in logging module.

• Pandas on large data causes out-of-memory failures (1 GB+).

• Python loops doing heavy work: use CPython.

• Loading giant files into memory.

• Wrong data structures.

• Excessive logging: It generates large volumes of data, slows down pipeline execution, and consumes storage.

• No caching. Repeated computations without caching intermediate results lead to redundant processing.

• Mixed business + ETL logic.

---

Follow Me for more Data Quality Best Practices.

What’s Your Go-To Data Quality Validation Tool?

Let me know in the comments 👇

---