ETL, warehouse, and BI: the data tooling map for developers

⏱️ Lectura: 16 min

You get assigned to a data project and in the first meeting you hear ETL, warehouse, dbt, and reverse ETL in the same sentence. Welcome to the daily vocabulary of the data tools that any modern data team uses.

📑 En este artículo

1. TL;DR
2. The four roles of the data tooling ecosystem
   1. Analytical: the one who interprets
   2. Scientific: the one who predicts
   3. Engineering: the one who builds the pipe
   4. ML engineering: the one who puts models into production
3. The data lifecycle: from source to dashboard
4. Practical examples: from SQL query to orchestrated pipeline
   1. Extract and explore with Python
   2. Transform with SQL directly in the warehouse
   3. Model with dbt: versioned, testable SQL
   4. Orchestrate the full pipeline with Airflow
5. How to get started: build your own mini data stack
6. Real-world use cases
   1. From a churn pipeline to a retention campaign
   2. Reverse ETL: when data goes back to the operational tools
7. Common mistakes and best practices
8. Comparison: ETL, ELT, and reverse ETL
9. Going deeper: lakehouse, data mesh, and streaming
10. Frequently Asked Questions
    1. What’s the difference between a data analyst and a data scientist?
    2. What is a data warehouse and how does it differ from a data lake?
    3. Do I need to know Python to work with data tools?
    4. Why did dbt replace so much loose SQL?
    5. What exactly is reverse ETL, and why isn’t it simply ETL in reverse?
    6. Where do I start if I’m a developer and I get assigned to a data project?
11. References

This article maps the data tools you’ll run into: who does what on the team, where the information comes in, where it lives, and how it makes its way to a dashboard or a machine learning model. With code examples in SQL, Python, and dbt so you can follow along from day one.

TL;DR

• You’ll tell apart the 4 roles on a data team (analyst, scientist, engineer, ML engineer) and which tool each one uses.
• You’ll understand the difference between ETL, ELT, and reverse ETL, and when each approach fits.
• You’ll know when to use a data warehouse, a data lake, or a lakehouse depending on the type of data.
• You’ll write your first dbt model and your first Airflow DAG with real examples.
• You’ll spin up a local mini data stack with DuckDB in minutes, no cloud infrastructure.
• You’ll spot common mistakes when designing pipelines: schema drift, missing tests, and non-idempotent jobs.
• You’ll be able to read a data architecture diagram and place where each tool your colleagues mention fits in.

The four roles of the data tooling ecosystem

At a mid-sized company, the data team is rarely a single person with one profile. These are roles that overlap, especially on small teams, and understanding the difference saves you confusion when someone tells you “the data engineer should handle that” or “that question is for the analyst.”

Analytical: the one who interprets

The analytical profile turns raw data into answers for the business. It’s strong in SQL and spreadsheets, and works with business intelligence (BI) tools like Tableau, Looker, or Metabase. A typical day: pull customer data with a SQL query, calculate the churn rate by region, build a Tableau dashboard, and present it to the marketing team with a concrete recommendation.

Scientific: the one who predicts

The scientific profile goes beyond reporting: it applies statistics, builds models, and runs experiments. It usually codes in Python with pandas and scikit-learn, and lives inside notebooks (Jupyter, Deepnote, Google Colab). A typical day: take the same customer data, find which variables correlate with churn, train a model that estimates each customer’s likelihood of leaving, and design an A/B test for a retention campaign.

Engineering: the one who builds the pipe

The data engineer makes it possible for the other two profiles to have clean, accessible data. They build and maintain the pipelines that extract information from different sources (transactional databases, APIs, product events), standardize it, and load it into a warehouse or a lake. They also administer databases and other pieces of data infrastructure, and typically work with Python, Apache Spark, cloud warehouses, and orchestrators like Airflow.

ML engineering: the one who puts models into production

A fourth profile, increasingly common, is the ML engineer: they take the model the data scientist trained in a notebook and turn it into a reliable, versioned, monitored service in production. They rely on MLOps: model registry, retraining pipelines, and observability of metrics like data drift.

The data lifecycle: from source to dashboard

Every data tool you’ll hear named fits into some stage of a single cycle: extraction, loading, transformation, and consumption. That order, or its variant with transformation before loading, defines whether you’re looking at an ETL or an ELT pipeline.

The sources are transactional databases (Postgres, MySQL), third-party APIs (Stripe, HubSpot), product events (clicks, sessions), or flat files. Tools like Fivetran, Airbyte, or Meltano connect to those sources and replicate the data to a central destination without you having to hand-write a connector for each API.

That central destination is the warehouse (Snowflake, BigQuery, Redshift) or, if the data is less structured, a data lake on top of object storage (S3, GCS) with formats like Parquet. That’s where transformation comes in: turning raw data into clean, documented tables ready for an analyst to build a dashboard without guessing what each column means.

flowchart TD
    A["Fuentes: APIs, bases transaccionales, eventos"] --> B["Ingestion: Fivetran, Airbyte, Meltano"]
    B --> C[("Data warehouse o data lake")]
    C --> D["Transformación: dbt"]
    D --> E["BI: Tableau, Metabase, Looker"]
    D --> F["ML: notebooks, modelos en producción"]

The last leg is consumption: BI dashboards for the analytical profile, notebooks and feature pipelines for the scientific profile, and in many cases a trip back to the operational tools (CRM, email marketing) known as reverse ETL. That pattern comes up again in the use cases section.

Practical examples: from SQL query to orchestrated pipeline

Extract and explore with Python

The simplest entry point for any developer is a Python script that reads a file and computes something. You don’t need a warehouse to start thinking like an analyst.

import pandas as pd

df = pd.read_csv("customers.csv")
churned = df[df["status"] == "churned"]
churn_rate = len(churned) / len(df) * 100

print(f"Tasa de abandono: {churn_rate:.2f}%")

This script reads a local CSV with pandas, filters the customers marked as churned, and calculates the percentage of the total. With a sample dataset of 5,000 rows, the output would be something like Tasa de abandono: 4.32%. It’s the same calculation an analyst would do in Tableau, only in code instead of a visual interface.

Transform with SQL directly in the warehouse

When the data already lives in a warehouse, the simplest transformation is a SQL query with aggregations. There’s no ETL involved: it’s a view that runs over data another pipeline already loaded.

SELECT
    region,
    COUNT(*) AS total_clientes,
    SUM(CASE WHEN status = 'churned' THEN 1 ELSE 0 END) AS clientes_perdidos,
    ROUND(100.0 * SUM(CASE WHEN status = 'churned' THEN 1 ELSE 0 END) / COUNT(*), 2) AS tasa_abandono
FROM analytics.customers
GROUP BY region
ORDER BY tasa_abandono DESC;

The query groups customers by region, counts the total, and calculates what percentage ended up marked as churned. The result is a table with one row per region and its churn rate, sorted from highest to lowest, ready to drop into a dashboard.

Model with dbt: versioned, testable SQL

The problem with writing loose SQL queries is that nobody versions or tests them. dbt (data build tool) solves that: you treat each transformation as a SQL model with explicit references to other models, version control in Git, and automatic data quality tests.

-- models/marts/customer_churn.sql
with customers as (
    select * from {{ ref('stg_customers') }}
),

orders as (
    select
        customer_id,
        max(order_date) as ultima_compra
    from {{ ref('stg_orders') }}
    group by customer_id
)

select
    c.customer_id,
    c.region,
    o.ultima_compra,
    case
        when o.ultima_compra < current_date - interval '90 days' then true
        else false
    end as en_riesgo_de_churn
from customers c
left join orders o using (customer_id)

This model combines two staging tables (customers and orders) and adds a calculated column, en_riesgo_de_churn, which flags as true any customer with no purchases in the last 90 days. When you run dbt run, dbt compiles the final SQL, resolves the references with {{ ref(...) }}, and creates or updates the customer_churn table in the warehouse.

Orchestrate the full pipeline with Airflow

Neither the extraction nor the transformation runs on its own: someone has to trigger them in order and on a schedule. That’s the job of an orchestrator like Apache Airflow, which defines the full pipeline as a graph of tasks (a DAG).

from airflow import DAG
from airflow.operators.bash import BashOperator
from datetime import datetime

with DAG(
    dag_id="pipeline_customers",
    schedule="0 6 * * *",
    start_date=datetime(2026, 1, 1),
    catchup=False,
) as dag:

    extraer = BashOperator(
        task_id="extraer_customers",
        command="airbyte-cli sync --connection customers-postgres-to-warehouse",
    )

    transformar = BashOperator(
        task_id="transformar_dbt",
        command="dbt run --select customer_churn",
    )

    extraer >> transformar

This DAG runs every day at 6 AM: it first syncs customer data from Postgres into the warehouse with Airbyte, and only when that task finishes (through the >> operator) does it trigger the customer_churn dbt model. If the extraction step fails, Airflow doesn’t run the transformation and you can configure an alert.

How to get started: build your own mini data stack

You don’t need a Snowflake account or a Spark cluster to practice. DuckDB is an analytical SQL engine that runs embedded, serverless, and reads CSV or Parquet directly from disk. It’s the perfect pocket warehouse for understanding the flow without paying anything.

1. Install DuckDB

• macOS (Homebrew): brew install duckdb
• Linux: curl https://install.duckdb.org | sh
• Windows (winget): winget install DuckDB.cli

2. Verify the installation

duckdb --version

It should print something like v1.x.x. If the command isn’t recognized, close and reopen the terminal so the PATH updates.

3. Query a CSV without loading it into any database

duckdb -c "SELECT region, COUNT(*) AS total FROM read_csv_auto('customers.csv') GROUP BY region;"

DuckDB infers the CSV schema automatically with read_csv_auto and runs the aggregation in memory. It’s the same mental pattern as a cloud data warehouse, but running on your laptop in seconds.

4. Take the next step: dbt on DuckDB

pip install dbt-duckdb
dbt init mi_proyecto_datos

With the dbt-duckdb adapter you can write the same SQL models from the previous section and run them locally, without depending on a paid warehouse, before migrating the project to Snowflake or BigQuery in production.

💡 Tip: DuckDB also reads Parquet straight from S3 with read_parquet('s3://bucket/archivo.parquet'), handy for exploring a data lake without standing up any infrastructure.

Real-world use cases

Two examples connect everything above to a real business problem: customer churn analysis and the data’s return trip to the operational tools.

From a churn pipeline to a retention campaign

The data engineer maintains the pipeline that brings customer data from Postgres, Stripe, and the CRM into the warehouse every night. The data analyst uses that table to calculate the churn rate by region in a Tableau dashboard. The data scientist takes the same base table, trains a model that predicts which active customers have a high probability of canceling next month, and that result (a score per customer) is written back into the warehouse as just another table.

Reverse ETL: when data goes back to the operational tools

The churn risk score is useless sitting in the warehouse: the marketing team needs to see it inside the CRM to launch a campaign. That’s where reverse ETL comes in: tools like Hightouch or Census take a warehouse table and sync it to an operational system (CRM, email tool, ads).

sequenceDiagram
    participant W as Warehouse
    participant R as Reverse ETL
    participant C as CRM
    W->>R: exporta score de riesgo de churn
    R-->>C: sincroniza campo custom por cliente
    Note over W,C: el equipo de marketing ve el score sin tocar SQL

It’s the same data pipe, but traveled in the opposite direction: instead of the analyst going to fetch the data from the warehouse, the data travels on its own to where the business team already works every day.

Common mistakes and best practices

• Silent schema drift: a source changes a column’s name or type and the pipeline keeps running, but with corrupted data. Mitigate it with schema tests on every load.
• Unversioned SQL: queries copied and pasted across dashboards that nobody knows who wrote or why. A dbt project in Git solves this at the root.
• Non-idempotent jobs: running the same pipeline twice duplicates rows instead of overwriting them. Design your loads so that running the job N times gives the same result as running it once.
• Confusing ETL with ELT: transforming before loading makes sense when the volume is small and the destination can’t process heavy SQL; with a modern warehouse, it’s almost always better to load raw and transform afterward (ELT).
• Over-engineering the first pipeline: you don’t need Spark or Kafka for a 200,000-row dataset. Start with DuckDB or a Python script and scale when the volume truly demands it.

Comparison: ETL, ELT, and reverse ETL

Approach	What it does	Typical tools	When to use it
ETL (extract, transform, load)	Transforms the data before loading it into the destination	Talend, Informatica, custom Python scripts	Small volumes or destinations with no compute of their own
ELT (extract, load, transform)	Loads the raw data and transforms it inside the warehouse	Fivetran or Airbyte + dbt	Modern warehouses with elastic compute (Snowflake, BigQuery)
Reverse ETL	Syncs already-transformed data back to operational tools	Hightouch, Census	When a business team needs the data inside its CRM or ads, not in a dashboard
CDC / streaming	Captures changes in near real time instead of in batches	Debezium, Kafka, Fivetran CDC	Cases where the hours-long latency of a batch job isn’t acceptable

Going deeper: lakehouse, data mesh, and streaming

The line between warehouse and data lake got blurry with the lakehouse: cheap object storage, like a lake, with transactions, schema versioning, and fast SQL queries, like a warehouse. Open table formats like Apache Iceberg, Delta Lake, or Apache Hudi make that combination possible: they add a transactional layer on top of Parquet files in S3 or GCS.

flowchart TD
    subgraph Warehouse
    W1["Datos estructurados"] --> W2["Motor SQL propietario"]
    end
    subgraph "Data lake"
    L1["Datos crudos: JSON, logs, Parquet"] --> L2["Almacenamiento de objetos"]
    end
    subgraph Lakehouse
    H1["Almacenamiento de objetos"] --> H2["Capa transaccional: Iceberg, Delta"]
    H2 --> H3["Motor SQL"]
    end

Data mesh, on the other hand, isn’t a tool but a way of organizing teams: instead of a single central data team owning the entire warehouse, each product team owns its own datasets and exposes them as a data product with a contract and documentation. It solves an organizational scaling problem more than a technical one, when a central team becomes a bottleneck for dozens of product teams.

Streaming, with tools like Kafka or Kinesis, replaces the batch pattern (running a pipeline every hour) with a continuous flow of events. It makes sense when the business decision depends on seconds, not hours: fraud detection at the moment of payment, for example. For most business dashboards, a nightly batch job is still simpler and cheaper to maintain.

Finally, data quality has become its own discipline within the data tooling ecosystem: projects like Great Expectations validate that a table meets rules (non-null columns, expected ranges, uniqueness) as part of the pipeline, instead of discovering the problem when the dashboard already shows an impossible number.

⚠️ Watch out: migrating to streaming or a lakehouse before you have the real scaling problem they solve adds operational complexity with no measurable benefit.

📖 Summary on Telegram: View summary

Your next step: download a public CSV from Kaggle, install DuckDB, and run a GROUP BY query over it in your terminal before writing your first dbt model.

Frequently Asked Questions

What’s the difference between a data analyst and a data scientist?

The analyst interprets existing data with SQL and BI to answer business questions. The scientist goes further: they apply statistics and models to predict or explain, and usually work in notebooks with Python.

What is a data warehouse and how does it differ from a data lake?

A warehouse stores structured data optimized for fast SQL queries (Snowflake, BigQuery). A data lake stores any kind of data, structured or not, in cheap object storage, without imposing a schema at write time.

Do I need to know Python to work with data tools?

Not for every role. A data analyst can handle most of their work with SQL and a BI tool. Python becomes necessary as soon as you step into the scientific or engineering profile, where you’ll transform data with pandas or write pipelines.

Why did dbt replace so much loose SQL?

Because it gives transformations what SQL copied and pasted into a dashboard lacks: version control in Git, explicit references between models, and automatic tests that fail the pipeline if a piece of data breaks a rule.

What exactly is reverse ETL, and why isn’t it simply ETL in reverse?

Technically it does move data in the opposite direction from the traditional pipeline, from the warehouse toward an operational tool like a CRM. It gets its own name because it solves a different problem: not analyzing data, but operationalizing it so a business team can act on it without touching SQL.

Where do I start if I’m a developer and I get assigned to a data project?

Install DuckDB, get a real CSV (from your own product or a public dataset), and walk through the four stages of the cycle with your own hands: read it with pandas, query it with SQL, transform it with a dbt model, and, if your team uses Airflow, look at an existing DAG before writing your own.

References

• Sinja.io: Guide to data tools landscape for developers: the original article this guide is based on, written by a software engineer who joined a data team.
• Official dbt documentation: full reference for models, tests, and the ref() reference system.
• Apache Airflow: documentation for the pipeline orchestrator used in the DAG example.
• DuckDB: the embedded analytical SQL engine used in the “How to get started” section.
• Apache Iceberg: the open table format behind most lakehouse implementations.

📱 Enjoying this content? Follow @programacion on Telegram for daily tech content in Spanish: quick summaries, fresh content every day. @programacion

Imagen destacada: Foto de Luke Chesser en Unsplash