Modern Data Pipeline with Mage, dbt, PostgreSQL and Metabase

This project is a small data engineering lab built to learn how a modern analytics pipeline works from ingestion to dashboarding.

It extracts movie data from public APIs, stores the raw data in PostgreSQL, transforms it with dbt, orchestrates the flow with Mage, and exposes the final outputs in a Metabase dashboard.

The current pipeline uses:

• TMDB as the main source for popular movies
• Trakt as a second source for trending movie activity
• PostgreSQL as the warehouse
• Mage as the orchestrator
• dbt as the transformation layer
• Metabase as the reporting layer

The goal is not to build a massive platform. The goal is to create a clean, understandable and reproducible data project that shows the full path from API data to analytics output.

Architecture

flowchart LR
    TMDB[TMDB API] --> MageExtractTMDB[Mage Python loader]
    Trakt[Trakt API] --> MageExtractTrakt[Mage Python loader]

    MageExtractTMDB --> RawTMDB[(PostgreSQL lab.tmdb_popular_movies_raw)]
    MageExtractTrakt --> RawTrakt[(PostgreSQL lab.trakt_trending_movies_raw)]

    RawTMDB --> DbtStgTMDB[dbt staging model]
    RawTrakt --> DbtStgTrakt[dbt staging model]

    DbtStgTMDB --> DbtMart[dbt mart model]
    DbtStgTrakt --> DbtMart

    DbtMart --> Metabase[Metabase dashboard]

Loading

The pipeline follows a simple layered structure:

lab schema
  Raw data loaded by Mage

analytics schema
  Clean staging models and final mart models created by dbt

Metabase
  Dashboard built from the analytics schema

Technology stack

Docker Compose

Docker Compose is used to run the local data platform with three services:

• PostgreSQL
• Mage
• Metabase

PostgreSQL

PostgreSQL stores both raw and transformed data.

Current schemas:

lab
  Raw ingestion tables

analytics
  dbt staging and mart models

public
  Not used intentionally

Mage

Mage orchestrates the pipeline.

It handles:

• API extraction
• Raw data export to PostgreSQL
• dbt model execution

The Python loaders run inside the Mage Docker container.

dbt

dbt is used to transform raw data into clean analytics models.

Current model layers:

models/staging
  Cleaned and typed source models

models/marts
  Business ready tables used by Metabase

Metabase

Metabase is used to explore the warehouse and build the final dashboard.

It connects directly to PostgreSQL and reads the final dbt models from the analytics schema.

Data sources

TMDB

TMDB is used to extract popular movies.

Current extraction:

Endpoint
/movie/popular

Purpose
Get a list of popular movies with metadata such as title, release date, popularity score, vote average and vote count.

Output table
lab.tmdb_popular_movies_raw

The loader fetches multiple pages, deduplicates movies by TMDB ID and keeps the top 100 movies.

Trakt

Trakt is used to extract trending movie activity.

Current extraction:

Endpoint
/movies/trending

Purpose
Get trending movies with activity metrics such as watchers.

Output table
lab.trakt_trending_movies_raw

This source is used to compare TMDB popularity with Trakt trending activity.

Project structure

.
├── docker-compose.yml
├── README.md
├── mage/
│   ├── data_lab/
│   │   ├── data_loaders/
│   │   ├── data_exporters/
│   │   ├── pipelines/
│   │   ├── dbt/
│   │   │   └── analytics/
│   │   │       ├── dbt_project.yml
│   │   │       ├── models/
│   │   │       │   ├── staging/
│   │   │       │   └── marts/
│   │   │       └── profiles.yml
│   │   └── io_config.yaml
│   └── mage_data/
└── .gitignore

mage/mage_data/ contains local Mage state and should not be committed.

Environment variables and secrets

The pipeline needs API credentials for TMDB and Trakt.

Current secrets used by Mage:

TMDB_API_TOKEN
TRAKT_CLIENT_ID

These values should not be committed to Git.

In Mage, add them through the secrets interface or your preferred secret management method.

Setup

1. Clone the repository

git clone git@github.com:YOUR_USERNAME/YOUR_REPOSITORY.git
cd YOUR_REPOSITORY

2. Start the Docker environment

docker compose up -d

Check that the containers are running:

docker ps

Expected services:

data_lab_postgres
data_lab_mage
data_lab_metabase

3. Access Mage

Mage runs on port 6789.

4. Access Metabase

Metabase runs on port 3000.

On first launch, Metabase asks you to create an admin account.

Then connect it to PostgreSQL with:

Host: postgres
Port: 5432
Database: warehouse
Username: data
Password: data

Database setup

PostgreSQL is created by Docker Compose.

The main database is:

warehouse

The expected schemas are:

lab
analytics

If needed, create them manually:

create schema if not exists lab;
create schema if not exists analytics;

Running the pipeline manually

Open Mage and run the main pipeline.

Expected flow:

extract_tmdb_popular_movies
-> export_tmdb_popular_movies_raw
-> extract_trakt_trending_movies
-> export_trakt_trending_movies_raw
-> dbt staging models
-> dbt mart models

The raw tables should be created in the lab schema:

lab.tmdb_popular_movies_raw
lab.trakt_trending_movies_raw

The dbt models should be created in the analytics schema:

analytics.stg_tmdb_popular_movies
analytics.stg_trakt_trending_movies
analytics.mart_movie_popularity

Running dbt manually

You can also run dbt from inside the Mage container.

docker exec -it data_lab_mage bash
cd /home/src/data_lab/dbt/analytics
dbt debug
dbt build

dbt debug checks the project and database connection.

dbt build runs the models and their tests.

To run a specific model:

dbt build --select stg_tmdb_popular_movies

Inspecting the database

From PostgreSQL:

docker exec -it data_lab_postgres psql -U data -d warehouse

List tables and views:

select
    table_schema,
    table_name,
    table_type
from information_schema.tables
where table_schema in ('lab', 'analytics', 'public')
order by table_schema, table_name;

Check row counts:

select count(*) from lab.tmdb_popular_movies_raw;
select count(*) from lab.trakt_trending_movies_raw;
select count(*) from analytics.stg_tmdb_popular_movies;
select count(*) from analytics.stg_trakt_trending_movies;
select count(*) from analytics.mart_movie_popularity;

Quality assurance checklist

After running the pipeline, check the following:

Raw layer

lab.tmdb_popular_movies_raw exists
lab.trakt_trending_movies_raw exists
Raw tables contain rows
No unexpected tables are created in public

Staging layer

analytics.stg_tmdb_popular_movies exists
analytics.stg_trakt_trending_movies exists
Column names are clean
Data types are correct
Primary IDs are not null
Duplicate IDs are handled

Mart layer

analytics.mart_movie_popularity exists
The table is readable by Metabase
The values look reasonable
The dashboard updates after a new pipeline run

dbt tests

Run:

dbt build

The build should finish without failing tests.

Viewing the dashboard

Open Metabase.

Go to:

Database -> the name of the database you configured -> analytics

The dashboard should be based on the final mart model:

analytics.mart_movie_popularity

Useful commands

Start the platform:

docker compose up -d

Stop the platform:

docker compose down

Check running containers:

docker ps

Read Mage logs:

docker logs data_lab_mage --tail 100

Open a shell inside Mage:

docker exec -it data_lab_mage bash

Open PostgreSQL:

docker exec -it data_lab_postgres psql -U data -d warehouse

Run dbt:

docker exec -it data_lab_mage bash
cd /home/src/data_lab/dbt/analytics
dbt build

Git hygiene

Do not commit local state or secrets.

Recommended .gitignore entries:

.env
.venv/
__pycache__/
*.pyc
.DS_Store
mage/mage_data/

Commit source files such as:

docker-compose.yml
README.md
Mage pipeline files
Mage loader and exporter files
dbt models
dbt schema.yml files

---

Made with ❤️ by Nicolas