Skip to main content

Stripe

Stripe is a payments platform for online businesses. Load Stripe customers, subscriptions, invoices, and balance transactions into MotherDuck on a schedule with a Flight that runs dlt's Stripe source.

Stripe is a payments platform for online businesses, and its API holds the customer, subscription, invoice, and transaction records behind revenue reporting. To analyze that data in MotherDuck, run dlt's Stripe source and load it into a MotherDuck database.

How it works with MotherDuck​

dlt ships a Stripe verified source that wraps the Stripe Python SDK and handles pagination and typing for you, and you can run it in a Flight, so MotherDuck runs the pipeline on a schedule with no infrastructure of your own.

The source splits into two entry points, and most setups need both:

Entry pointDefault endpointsWrite behavior
stripe_source()Subscription, Account, Coupon, Customer, Invoice, Product, PriceReplaces the table on each run, because these objects change in place.
incremental_stripe_source()Event, BalanceTransactionAppends only records created since the last run, because these objects are immutable.

Prerequisites​

  • A MotherDuck account on a plan that includes Flights.
  • A Stripe restricted API key with read permission on the objects you want. A restricted key is preferable to a secret key: ingestion never needs write access.
  • A target database in MotherDuck. The examples use stripe.

Store the Stripe key as a Flight secret​

The key is a credential, so it belongs in a Flight secret rather than the Flight's config map. Name the key after dlt's own config variable so dlt resolves it without any glue code in your Flight.

The secret has to exist before you create the Flight, otherwise MD_CREATE_FLIGHT rejects the reference with user_secret not found.

The quickest way is a pre-filled dialog. This link opens Add secret with the type, name, and parameter row already set, so you only paste the key:

Create the stripe Flight secret in your own MotherDuck account.

You can also open Settings > Secrets and add it by hand with type Flights, or use SQL from a write-enabled connection:

CREATE SECRET stripe IN motherduck (
    TYPE flights,
    PARAMS MAP {
        'SOURCES__STRIPE_ANALYTICS__STRIPE_SECRET_KEY': '<your_restricted_api_key>'
    }
);

To keep the literal key out of your SQL and shell history, run that statement from the duckdb CLI, where getenv() resolves client-side:

CREATE SECRET stripe IN motherduck (
    TYPE flights,
    PARAMS MAP {
        'SOURCES__STRIPE_ANALYTICS__STRIPE_SECRET_KEY': getenv('STRIPE_API_KEY')
    }
);

Create the Flight​

The Stripe source isn't a single file, so install it as a dependency instead of pasting it into source_code. See Use a dlt verified source for how this works and what to watch for.

Stripe is the one source of the three that needs an extra package: without stripe, the import fails with ModuleNotFoundError: No module named 'stripe'.

duckdb==1.5.5
dlt[motherduck]==1.30.0
stripe==15.6.0
dlt-verified-sources @ https://github.com/dlt-hub/verified-sources/archive/3957506893a7da821dbcc6acd51c7ca4475d1f53.tar.gz

That commit is a known-good pin. Check the commit history for a newer one, and keep a SHA rather than master.tar.gz: a Flight reinstalls its dependencies on every run, so an unpinned URL can change the connector between two runs of a Flight you haven't touched.

The Flight runs both entry points into the same dataset. MOTHERDUCK_TOKEN is injected for you, so dlt's MotherDuck destination picks up the credential without configuration.

import os

import dlt
import duckdb

from sources.stripe_analytics import incremental_stripe_source, stripe_source

DB = "stripe"


def main():
    os.environ.setdefault("HOME", "/tmp")
    os.environ["DESTINATION__MOTHERDUCK__CREDENTIALS__DATABASE"] = DB

    # dlt attaches the database but never creates it, so make sure it exists.
    duckdb.connect("md:").execute(f'CREATE DATABASE IF NOT EXISTS "{DB}"')

    pipeline = dlt.pipeline(
        pipeline_name="stripe_analytics",
        destination="motherduck",
        dataset_name="stripe_raw",
    )

    # Mutable objects: replaced on every run.
    print(pipeline.run(
        stripe_source(endpoints=("Customer", "Subscription", "Invoice", "Price", "Product")),
        loader_file_format="parquet",
    ))

    # Immutable objects: only records created since the last run.
    print(pipeline.run(
        incremental_stripe_source(endpoints=("Event", "BalanceTransaction")),
        loader_file_format="parquet",
    ))


if __name__ == "__main__":
    main()

Create the Flight with MD_CREATE_FLIGHT, passing that Python as source_code, the pinned dependencies as requirements_txt, and flight_secret_names := ['stripe'] so the key reaches the run. Leave schedule_cron off until a manual MD_RUN_FLIGHT succeeds, then add a schedule with MD_UPDATE_FLIGHT.

Query the result​

dlt creates one table per endpoint in the stripe_raw schema, with its own tables for load history:

SELECT
    date_trunc('month', created) AS month,
    count(*) AS new_subscriptions
FROM stripe.stripe_raw.subscription
GROUP BY ALL
ORDER BY month DESC;

Known limitations​

  • stripe_source() reloads everything on each run. Its endpoints cover mutable objects, so there's no incremental cursor. On a large account, keep the endpoint list narrow and lean on incremental_stripe_source() for the high-volume history.
  • start_date and end_date need pendulum datetime objects, not strings. pendulum installs with dlt, so import it in the Flight when you want to bound a backfill.
  • Rate limits apply per account. A wide first load can take a while. Run the initial backfill once with a bounded date range rather than letting a scheduled run do it.
  • The connector isn't editable when installed as a dependency. To change extraction logic, use dlt init stripe_analytics motherduck in a local project.
  • Stripe's own object schemas evolve. dlt handles new fields through schema evolution, but a renamed field surfaces as a new column rather than a migration of the old one.

Managed alternatives​

If you'd rather not run the pipeline yourself, Fivetran and Airbyte both offer a Stripe source and a MotherDuck destination.

Related content​