Skip to content

About

Multi-tenant ELT pipeline with dlt and dbt Core: 10 platform connectors, per-tenant schema isolation on PostgreSQL 16, Pydantic contracts, and Metabase dashboards.

Topics

Resources

Code of conduct

Contributing

Security policy

Stars

2 stars

Watchers

0 watching

Forks

Latest commit

Β 

History

102 Commits

Folders and files

NameName
Last commit message
Last commit date
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 

Repository files navigation

Developmi multitenant-elt

Multitenant ELT | Developmi

Eliminate manual reporting across 10 marketing platforms - one pipeline, one dashboard, zero spreadsheets.

Version Python Docker CI Tests FinOps Security Status License Maintainer Role

Multi-tenant marketing data pipeline β€” sovereign data stack (dlt + dbt + PostgreSQL 16 / ClickHouse + Metabase): extract data from 10 ad and social platforms, transform it into clean metrics, and visualize it in Metabase. Built for digital agencies managing multiple clients.

mindmap
  ((multitenant-elt))
    Ads
      Meta Ads
      TikTok Ads
      Google Ads
    Organic & Social
      Facebook
      Instagram
      TikTok Organic
      YouTube
      Pinterest
    Analytics & Tagging
      GA4
      GTM
    Stack
      dlt
      dbt Core
      PostgreSQL 16 / ClickHouse
      Metabase
Loading

Table of contents


🎯 Features

  • 10 data connectors: Meta Ads, TikTok Ads, Google Ads, Facebook Pages, Instagram Business, TikTok Organic, YouTube Data, Pinterest, GA4, GTM
  • Honest status tracking: See Connector status for which integrations are tested live (8/10) vs. code-complete only (2/10)
  • Multi-tenant by design: per-client raw namespaces and dbt output schemas (raw_<connector>_<client_id> / client_<client_id>), YAML client config, shared observability
  • Data contracts & Schema integrity: Multi-layer contract validation β€” YAML client configurations validated via strict type and schema contracts (client_config.py, providing Pydantic-compatible data contracts with zero external overhead), raw ingestion schema typing enforced by dlt, and relational models verified by 75+ dbt tests
  • No-code connector config: Add/remove platforms per client via YAML, no code changes
  • Pipeline orchestration: Native Python engine (pipeline_runner.py), dynamic client execution plans, database run step metrics, health checks, and Telegram alerts
  • dbt transformations: 30 staging models β†’ intermediate β†’ marts (35 models total)
  • Quality gate: 505 tests (504 passed, 1 skipped), ruff linting, mypy type checking, Trivy security scan
  • Docker native: Isolated services, two Docker networks, no vendor lock-in

πŸ’° FinOps & Cost Efficiency

Sovereign data pipelines run at a fraction of cloud SaaS costs.

Architecture Tier Managed Cloud (Fivetran + BigQuery + Looker) Sovereign (multitenant-elt on VPS / Bare Metal) Monthly Savings
Ingestion & ELT ~$400 - $1,200 / mo $0 (Self-hosted dlt + PostgreSQL 16) 100%
Data Warehouse ~$150 - $400 / mo Included in VPS 100%
BI & Visualization ~$300 / user / mo $0 (Metabase Open Source) 100%
Compute Infrastructure AWS ECS / Cloud Run (~$120 / mo) Hetzner / Contabo VPS (~$15 - $30 / mo) ~75% - 85%
Estimated Total ~$970 - $2,300+ / mo ~$20 - $35 / mo ~98% OpEx Reduction (modeled estimate)

Note

Modeled Estimate Methodology: The ~98% OpEx reduction is an engineering modeled estimate comparing a typical agency workload across 10 platforms and 2–5 active clients (~100k–500k monthly rows ingested):

  • Managed Cloud baseline: Combines commercial ELT pricing (Fivetran tier pricing based on Monthly Active Rows), managed cloud warehouse (Google BigQuery storage & on-demand query compute), and enterprise BI seat licensing (Looker/Tableau at $300/user/mo for agency analysts), plus serverless compute (AWS ECS / Cloud Run).
  • Sovereign stack baseline: Replaces per-row and per-seat fees by running self-hosted dlt extraction, PostgreSQL 16 / ClickHouse warehouse storage, dbt Core, and Metabase Open Source on dedicated VPS / Bare Metal infrastructure (e.g., Hetzner Cloud or Contabo at $15–$35/month).
  • Boundary: Direct recurring SaaS/cloud infrastructure run-rate only; excludes internal staff development and maintenance hours.

πŸ”Œ Connector status

Transparency over hype. All 10 connectors are implemented with unit tests and dbt staging models. 8 have been tested against live APIs with production client schemas (Meta Ads, Facebook Pages, Instagram Business, TikTok Organic, YouTube Data, Google Ads, GA4, GTM) β€” the remaining 2 (TikTok Ads, Pinterest) have complete code and mock unit tests ready for live validation.

Connector Type Tested live Production ready Notes
Meta Ads Ads βœ… Yes βœ… Yes v25.0, 250+ rows verified
Facebook Page Organic βœ… Yes βœ… Yes v25.0, requires Page Access Token (not User Token)
Instagram Business Organic βœ… Yes βœ… Yes v25.0, daily history marts and total_value metrics verified
TikTok Organic Organic βœ… Yes βœ… Yes Profile stats, user info & refresh tokens verified live
YouTube Organic βœ… Yes βœ… Yes API v3, OAuth consent flow & daily video analytics verified live
Google Ads Ads βœ… Yes βœ… Yes API v17/v18, OAuth 2.0 refresh flow verified live (2026-11-05)
GA4 Analytics βœ… Yes βœ… Yes Data API v1beta, OAuth & service account reports verified live (2026-11-05)
GTM Analytics βœ… Yes βœ… Yes API v2, container/tag hierarchy verified live (2026-11-05)
TikTok Ads Ads ❌ No ⚠️ Code + tests API v1.3, mock tested, pending live ad account
Pinterest Organic ❌ No ⚠️ Code + tests API v5, mock tested, pending ad account access

Instagram diagnostics probe (read-only)

tools/diagnostics/ig_probe.py is a read-only diagnostic CLI for the Instagram Business connector (never part of the scheduled pipeline). A backward-compatible shim is maintained at agency_analytics.ig_probe. It answers which account-insights metrics respond today and with which metric_type, the real retention horizon per trailing window, which breakdown axes work per metric, and eligibility below 100 followers β€” the data that drives the insights_daily / insights_totals split and backfill decisions.

docker exec -w /app/src agency_pipeline \
  python -m agency_analytics.ig_probe --client example --read-only

# Local run with tools/diagnostics/ig_probe.py:
uv run python tools/diagnostics/ig_probe.py --client example --read-only \
  --skip-breakdowns --output /tmp/ig_probe_report.json

It makes no destructive calls and writes to no table (spec IG4). Full CLI reference, report schema and call budget live in the module docstring (tools/diagnostics/ig_probe.py). The probe is a manual diagnostics entry point, not a pipeline step.


⚑ Quick start

Prerequisites

1. Clone and configure

git clone https://github.com/Developmi/multitenant-elt.git
cd multitenant-elt
cp .env.example .env
# Edit .env with your API tokens, database passwords, and Telegram credentials

2. Start infrastructure

make setup-networks   # Create Docker networks
make docker-up-all     # Start Postgres, Pipeline, and Metabase (opt-in profile)

3. Run the pipeline

make setup-env        # Ensure .env exists
make test             # 504 tests should pass

# Run full Python E2E pipeline:
make pipeline
# Or execute via the native CLI for a single client:
uv run multitenant-elt pipeline --client acme

# For host cron automation (thin wrapper around container pipeline):
./scripts/pipeline.sh

4. Access Metabase (opt-in)

Metabase is not part of the default stack: it is gated behind the metabase compose profile (enabled by make docker-up-all / make docker-metabase). Before first start, set METABASE_READER_ENABLED=true in services/db/.env and make METABASE_READER_PASSWORD match MB_DB_PASS in services/metabase/.env; the init bootstrap then creates the metabase_reader role. Open http://localhost:3000 and connect the metabase_reader Postgres user.


πŸ—οΈ Architecture

  • Ingestion: Each platform has a standalone dlt script with exponential backoff, rate-limit handling, and token-expiry detection; every run targets a per-client raw dataset (raw_<connector>_<client_id>, full replace per tenant)
  • Data contracts: Client configuration and tenant schemas follow strict contract definitions (client_config.py per-connector schema specifications with Pydantic-compatible data contracts), preventing invalid client configs before ingestion starts
  • Storage & Warehouse: Dual-engine analytical design β€” currently deployed with PostgreSQL 16 (per-client raw schemas raw_<connector>_<client_id>, staging, and transformed client_<id> marts) with architectural alignment for ClickHouse analytical warehouse ingestion via dlt destination adapters, matching Developmi's sovereign data platform stack (dlt + dbt + ClickHouse + Metabase)
  • Transformation: dbt with multi-tenant routing - generate_schema_name sends tenant models to client_<id> and sources.yml jinja resolves each client's own raw namespace
  • Visualization: Metabase (opt-in compose profile) connected as metabase_reader, a read-only Postgres role that only exists when METABASE_READER_ENABLED=true
  • Orchestration: Native Python orchestrator (multitenant-elt pipeline) executed directly, via make pipeline, or nightly via cron wrapper ./scripts/pipeline.sh; validates Docker health, dynamically executes client plans, tracks run steps in PostgreSQL, and sends Telegram summaries

See ARCHITECTURE.md for full design.

Project structure

β”œβ”€β”€ main.py                         # CLI entry point
β”œβ”€β”€ Makefile                        # Quality & Docker targets
β”‚
β”œβ”€β”€ services/                       # Docker Compose stacks
β”‚   β”œβ”€β”€ db/                         #   PostgreSQL 16
β”‚   β”œβ”€β”€ pipeline/                   #   dlt + dbt worker
β”‚   └── metabase/                   #   Metabase dashboards
β”‚
β”œβ”€β”€ src/                            # Application source
β”‚   β”œβ”€β”€ connectors/                 #   10 dlt connector scripts
β”‚   β”œβ”€β”€ dbt_project/                #   35 dbt models + macros
β”‚   └── agency_analytics/           #   CLI module
β”‚
β”œβ”€β”€ clients/                        # Multi-tenant YAML config
β”‚   β”œβ”€β”€ _template.yml
β”‚   β”œβ”€β”€ acme.yml
β”‚   └── nike.yml
β”‚
β”œβ”€β”€ scripts/                        # Shell orchestration
β”‚   β”œβ”€β”€ pipeline.sh
β”‚   └── setup-networks.sh
β”‚
β”œβ”€β”€ tests/                          # 505 unit & integration tests (504 passed, 1 skipped)
β”œβ”€β”€ .env.example
β”œβ”€β”€ pyproject.toml
└── .gitignore

🐳 Docker deployment

Build the pipeline image

make docker-build

This uses a multi-stage build:

  1. Builder stage: installs Python dependencies via uv sync - caches on pyproject.toml + uv.lock
  2. Runtime stage: Python 3.12-slim, non-root user (app, UID 1000), only the virtual env and source code

Start all services

make docker-up-all     # Postgres 16 β†’ Pipeline worker β†’ Metabase

Service isolation

Each service runs in its own Compose stack with separate .env files:

  • services/db/.env - database credentials
  • services/pipeline/.env - API tokens, dlt/dbt config
  • services/metabase/.env - Metabase credentials

Docker networks (agency_analytics_net, agency_internal_net) isolate traffic. Metabase attaches to both networks to reach Postgres, so it shares a bridge with the pipeline container β€” its guard is a dedicated read-only DB role (metabase_reader, opt-in), not network isolation (see ARCHITECTURE.md β†’ Honest Network Posture).

Use a published image (vX tags)

Once a vX tag exists on GitHub, the docker-build-scan-sign workflow publishes a multi-arch image to ghcr.io/developmi/multitenant-elt. Consumers can then pull and run it without building locally:

docker pull ghcr.io/developmi/multitenant-elt:vX
docker run --rm -d --name agency_pipeline --env-file .env \
  -v "$PWD/clients:/app/clients" ghcr.io/developmi/multitenant-elt:vX

Honest notes on the published artifact:

  • Each published vX tag is multi-arch (amd64 + arm64), cosign-signed (keyless), and carries a CycloneDX SBOM plus SLSA attestation, generated by CI. No :latest pointer is published β€” tags are the only release mechanism.
  • The image CMD is an idle worker placeholder. Full orchestration keeps using the local Compose stacks (make docker-up-all), which bind-mount ../../src and ../../clients over the baked-in copies at runtime β€” the mount shadows the image content, so dev and published layouts match.
  • Local development still uses make docker-build; nothing switches to the published image automatically.

πŸ”§ Configuration

See .env.example for the full list of environment variables.

Category Key variables
Database POSTGRES_PASSWORD, DESTINATION__POSTGRES__CREDENTIALS__*
Meta Ads META_ACCESS_TOKEN_{CLIENT}
Google Suite (Ads, GA4, GTM, YouTube) GOOGLE_OAUTH_CLIENT_ID, GOOGLE_OAUTH_CLIENT_SECRET, GOOGLE_OAUTH_REFRESH_TOKEN (or per-client _CLIENT)
Facebook FACEBOOK_ACCESS_TOKEN_{CLIENT}
Instagram INSTAGRAM_ACCESS_TOKEN_{CLIENT}
Pipeline CLIENTS_DIR, TELEGRAM_BOT_TOKEN, TELEGRAM_CHAT_ID

Security note: Never commit .env files. Use .env.example as a template and keep credentials local.


πŸ§ͺ Tests & Quality Gate

make test               # Run all tests (504 passed, 1 skipped)
make lint               # ruff check
make typecheck          # mypy
make test-security      # Trivy vulnerability & secret scan
make quality            # Full gate: lint-all + test + test-security
make release-ready      # Pre-release verification gate

All tests are mock-based - no live API calls. Each connector has tests for success paths, rate limits, token expiry, pagination, and error handling.


πŸ”‘ Authentication & Token Management

# Interactive Meta Graph API Token Renewal Wizard
# Automatically exchanges short-lived tokens for 60-day user tokens & permanent Page Access Tokens
make auth-meta
# Or for a specific client:
make auth-meta CLIENT=acme

# Interactive Google OAuth Renewal Wizard (GA4, GTM, Google Ads, YouTube)
# Generates durable Refresh Tokens and configures global agency or per-client credentials
make auth-google
# Or for a specific client:
make auth-google CLIENT=acme
# Or restrict to specific services (default: all):
make auth-google SERVICES=ga4,gtm

πŸ”’ Security

This project follows a coordinated disclosure policy. If you discover a vulnerability, do not open a public issue. See SECURITY.md for reporting instructions and response timelines.


πŸ“‹ Changelog

See CHANGELOG.md for the full version history. The project follows Keep a Changelog and Semantic Versioning.


πŸ—ΊοΈ Roadmap

See ROADMAP.md for completed features and upcoming work.


🀝 Contributing

Contributions are welcome. Please read CONTRIBUTING.md before opening a pull request. This project follows Conventional Commits and the Developmi engineering standard.


πŸ“„ License

Copyright Β© 2026 Miguel Lozano | Developmi. Licensed under the MIT License.


🀝 Contact & support

Maintained by: Miguel Lozano | Developmi


Β© 2026 Miguel Lozano | Developmi. Licensed under the MIT License.

About

Multi-tenant ELT pipeline with dlt and dbt Core: 10 platform connectors, per-tenant schema isolation on PostgreSQL 16, Pydantic contracts, and Metabase dashboards.

Topics

Resources

Code of conduct

Contributing

Security policy

Stars

2 stars

Watchers

0 watching

Forks

Releases

Packages

Contributors

Languages