How to build a data pipeline to Metabase

Metabase turns data in a database into dashboards anyone can read. But most of your data lives in SaaS tools — Stripe, Linear, Zendesk, and the rest — that Metabase can't read directly. A data pipeline is the bridge: it copies data from those tools into a database on a schedule, so Metabase has something to chart. This guide is the source-agnostic playbook — the same shape works for almost any tool, and every integration guide links back here.

Why do you need a pipeline at all?

You could look up a number in a tool's own UI, or ask an AI assistant to fetch it live. That's fine for a one-off question. But for dashboards people rely on, you need three things a live lookup can't give you:

• History. APIs return the current state; they don't remember last quarter. A pipeline accumulates snapshots and change events so you can chart trends.
• Joins. Real questions span tools — revenue vs. support load, deploys vs. incidents. That only works when the data sits together in one database.
• Reliability. A scheduled pipeline produces the same numbers every time, isn't subject to API rate limits at read time, and doesn't depend on an assistant's interpretation.

What are the four stages of every pipeline?

Whatever tools you use, a pipeline is always the same four stages. Naming them makes it easy to swap any one piece without rethinking the whole thing.

1. Extract — pull records from the source's API (REST, GraphQL, or a bulk export).
2. Load — write those raw records into a database, ideally with incremental upserts so re-runs don't duplicate rows.
3. Model — transform raw tables into clean, typed, deduplicated ones that are safe to chart.
4. Visualize — point Metabase at the modeled tables and build questions and dashboards.

Extract and load are usually handled together by one tool. Modeling happens in SQL (or in Metabase models). Visualization is Metabase.

Which approach should you pick?

There are three honest ways to do extract-and-load. None is "best" — it's a trade of money against maintenance.

What tools handle extract and load?

Free / open-source

• dlt (code) — a Python library that handles pagination, schema inference, and incremental loading into Postgres, BigQuery, Snowflake, DuckDB, and more. The lightest way to a maintainable, no-vendor sync. Best default for most teams.
• Airbyte (open source) — hundreds of prebuilt connectors and a UI, free when self-hosted. Good when you have many sources and don't want to write extractors, but you take on the ops of running it.
• Meltano — an open-source framework built on Singer taps; a config-driven alternative to Airbyte.
• The raw API — write your own script against the source's REST or GraphQL endpoints. Most control, and an AI assistant can scaffold it fast; you own pagination, retries, and schema.

Managed (paid)

• Fivetran, Airbyte Cloud, and Stitch — fully hosted connectors. You paste credentials, pick a schedule, and they keep the sync running. Cost scales with data volume.

Native "free-ish" shortcuts

• BigQuery Data Transfer Service — ingests Google-owned sources (Google Ads, GA4, YouTube, Search Ads) into BigQuery for free; you pay only for storage and queries. Only covers Google's own products, not third-party SaaS.
• Vendor exports — many tools can drop scheduled CSVs to S3 or email; you load those on a schedule. Low-tech but zero connector cost.

Where do you run the sync?

Writing the script is the easy part now. The question people actually get stuck on is hosting: where does the job run on a schedule, reliably, without you babysitting it? An AI can write the code, but it can't be the thing that's up at 3am running your cron job. Options, from lowest to highest maintenance:

• GitHub Actions (cron) — built-in scheduler, encrypted secrets, and free minutes that comfortably cover daily or hourly syncs. The simplest "run my script every day" answer. Not for sub-minute intervals.
• Serverless functions — Google Cloud Functions, AWS Lambda, or Modal on a timer. Generous free tiers and nothing to patch; scales cleanly.
• Managed cron platforms — Railway or Render cron jobs for a few dollars a month, with logs and retries in a UI.
• Database-native schedulers — if you land in Supabase or plain Postgres, pg_cron plus an edge function can keep the whole thing in one place.
• An always-on VM + cron — a $4–6/month box with a crontab. Full control, but you own uptime and patching.

Where should the data land?

Three things need a home, and people usually only plan for one:

• The database (destination). Start with Postgres — Neon or Supabase offer free tiers, and Metabase works great with it. Move to BigQuery, Snowflake, or Redshift once volumes grow.
• The sync job (compute). One of the hosting options above.
• Metabase itself. Either Metabase Cloud (managed) or self-hosted next to your database.

A complete free build, end to end

Here's a working reference for the free path: dlt → Postgres, scheduled by GitHub Actions. Swap the API details for your source and it works the same way. An assistant can generate the source-specific parts; the pieces below are the skeleton.

1. The pipeline (pipeline.py) — extract and load:

import dlt
from dlt.sources.helpers.rest_client import RESTClient
from dlt.sources.helpers.rest_client.paginators import HeaderLinkPaginator


# One resource per entity. "merge" + a primary key gives you idempotent,
# incremental upserts so re-runs don't create duplicates.
@dlt.resource(name="records", write_disposition="merge", primary_key="id")
def records(updated_at=dlt.sources.incremental("updated_at")):
    client = RESTClient(
        base_url="https://api.yourtool.com",
        headers={"Authorization": f"Bearer {dlt.secrets['api_token']}"},
        paginator=HeaderLinkPaginator(),
    )
    for page in client.paginate(
        "/v1/records",
        params={"updated_since": updated_at.last_value},
    ):
        yield page


pipeline = dlt.pipeline(
    pipeline_name="yourtool_to_metabase",
    destination="postgres",
    dataset_name="raw",
)

if __name__ == "__main__":
    print(pipeline.run(records))

2. Dependencies (requirements.txt):

dlt[postgres]>=1.0

3. The scheduler (.github/workflows/sync.yml) — this is your hosting. Add API_TOKEN and POSTGRES_URL under the repo's Settings → Secrets and variables → Actions:

name: sync-to-metabase

on:
  schedule:
    - cron: "0 * * * *" # top of every hour (UTC)
  workflow_dispatch: {} # lets you trigger a run by hand

jobs:
  sync:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - run: pip install -r requirements.txt
      - run: python pipeline.py
        env:
          # dlt reads these as secrets/config at runtime
          API_TOKEN: ${{ secrets.API_TOKEN }}
          DESTINATION__POSTGRES__CREDENTIALS: ${{ secrets.POSTGRES_URL }}

4. Connect Metabase. In Metabase, go to Admin → Databases → Add database, choose PostgreSQL, and paste the same host, database, user, and password from your Postgres URL. Once it syncs, your raw tables are queryable — then build models on top (next section).

How do you model the raw data?

Don't build dashboards directly on the raw tables a connector loads — they're often nested, untyped, or full of duplicates from re-runs. Add a thin modeling layer: one clean table per concept, with consistent types and names. You can do this as a SQL view, a scheduled table, or a Metabase model.

-- A thin model on top of a raw table: typed, deduplicated, chart-ready.
-- Point Metabase questions at this, not at the raw JSON dlt loads.
SELECT DISTINCT ON (id)
  id,
  created_at::timestamptz AS created_at,
  updated_at::timestamptz AS updated_at,
  lower(status)           AS status,
  amount_cents / 100.0    AS amount
FROM raw.records
ORDER BY id, updated_at DESC;

• Define shared concepts (like "active" or "completed") once and reuse them.
• Convert cents to currency, strings to timestamps, and codes to labels here — not in every question.
• Keep raw and modeled tables in separate schemas (e.g. raw vs. analytics) so it's obvious which to chart.

How do you handle scheduling and freshness?

• Match the interval to the need. Finance dashboards rarely need more than hourly; many are fine daily. More frequent means more compute and API calls for little gain.
• Load incrementally. Filter the source by an updated_at cursor and upsert on a primary key (dlt's merge write disposition does this) so runs stay fast and idempotent.
• Capture history when you need trends. If a metric depends on how a record changed over time (status transitions, plan changes), store change events or periodic snapshots — a single current-state table can't reconstruct them later.
• Alert on failure. Have the job fail loudly (a Slack ping, an email, a GitHub Actions failure notification) so a silent break doesn't quietly stale your dashboards.

Common mistakes

FAQ

Related