# Shopify > Load Shopify orders, customers, and products into MotherDuck on a schedule with a Flight that runs dlt's Shopify source. The Shopify Admin API holds the orders, customers, and product records behind sales reporting. To analyze that data in MotherDuck, run [dlt](https://dlthub.com/)'s Shopify source and load it into a MotherDuck database. ## How it works with MotherDuck Dlt ships a [Shopify verified source](https://dlthub.com/docs/dlt-ecosystem/verified-sources/shopify) that reads the Admin API with cursor pagination and incremental date filtering that you can run in a **[Flight](/concepts/flights)**, so MotherDuck runs the pipeline on a schedule with no infrastructure of your own. `shopify_source()` provides three resources, all loaded incrementally on `updated_at`: | Resource | Contents | |---|---| | `orders` | Transactions placed in the store, with nested line items and addresses. | | `customers` | Accounts created in the store. | | `products` | Items available for sale, with nested variants. | A separate `shopify_partner_query()` resource runs arbitrary GraphQL against the Shopify Partner API. That's a different credential and audience, so treat it as a separate pipeline. ## Prerequisites - A [MotherDuck account](https://app.motherduck.com) on a plan that includes Flights. - A Shopify app created in the [Dev Dashboard](https://shopify.dev/docs/apps/build/dev-dashboard), installed on your store. - The app's **Client ID** and **Client secret** from the Dev Dashboard. The secret starts with `shpss_`. There is no permanent Admin API token to copy: you exchange these two values for a short-lived token, as shown below. - The app and the store must belong to the **same Shopify organization**. This is what the client credentials grant requires, and a mismatch fails with `shop_not_permitted`. - Your store URL, in the form `https://.myshopify.com`. - A target database in MotherDuck. The examples use `shopify`. - Read scopes for the resources you load, set on an app version in the Dev Dashboard. The example below reads three Admin API endpoints with three scopes: | Resource | Scope | |---|---| | `products` | `read_products` | | `orders` | `read_orders` | | `customers` | `read_customers` | Grant only the ones matching the resources you pass to `with_resources()`. If you manage the app with the Shopify CLI, these go in the [`access_scopes`](https://shopify.dev/docs/apps/build/cli-for-apps/app-configuration#access_scopes) block of `shopify.app.toml`. :::note `orders` and `customers` are [protected customer data](https://shopify.dev/docs/apps/launch/protected-customer-data). Public apps need Shopify's review to read them; custom apps have both access levels available without review. ::: :::warning Shopify's Admin API returns only the last 60 days of orders. To load history beyond that, select [`read_all_orders`](https://shopify.dev/docs/api/usage/access-scopes#orders-permissions) **in addition to** `read_orders`. Without it, a backfill succeeds and silently returns short. ::: ## Create the Shopify app In the [Dev Dashboard](https://dev.shopify.com/dashboard), open **Apps** and choose **Create an app**. Name it, then use **Start from Dev Dashboard** rather than the CLI: it generates API credentials without scaffolding a local app project, which is all an ingestion pipeline needs. ![Shopify Dev Dashboard "Create an app" page with the "Start from Dev Dashboard" option and app name field highlighted](./img/shopify-dev-dashboard-create-app.png) Go to **Versions**, create a version, and pick the read scopes for the resources you load. Typing `read_` filters the list. `read_all_orders` appears here too, as **All orders**. ![Shopify "Select scopes" dialog filtered by "read_", listing Admin API scopes with checkboxes](./img/shopify-select-scopes.png) Release the version, then install the app on your store with **Install app** on the app's **Overview** page. ![Shopify app Overview page with the Install app button in the Installs card](./img/shopify-install-app.png) Open **App settings** to copy the **Client ID** and reveal the **Secret**. Shopify masks the secret behind an eye toggle, and **Rotate** replaces it if it ever leaks. ![Shopify app settings Credentials card showing the Client ID field and a masked Secret with reveal, copy, and Rotate controls](./img/shopify-app-credentials.png) ## Store the credentials Put the client ID and secret in a [Flight secret](/key-tasks/flights/flights-authentication-config-and-secrets#secrets-sensitive-environment-variables). The Flight exchanges them for an access token at the start of each run, so no token is stored anywhere. 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 both parameter rows already set, so you only paste the two values: **[Create the `shopify` Flight secret in your own MotherDuck account](https://app.motherduck.com/settings/secrets?action=create&type=flights&name=shopify¶ms=CLIENT_ID,CLIENT_SECRET)**. You can also open [Settings > Secrets](https://app.motherduck.com/settings/secrets) and add it by hand with type **Flights**, or use SQL from a write-enabled connection: ```sql CREATE SECRET shopify IN motherduck ( TYPE flights, PARAMS MAP { 'CLIENT_ID': '', 'CLIENT_SECRET': '' } ); ``` The store URL isn't sensitive, so pass it in the Flight's `config` argument: ```sql config := MAP { 'SHOP_URL': 'https://.myshopify.com' } ``` ## Mint an access token in the Flight The [client credentials grant](https://shopify.dev/docs/apps/build/authentication-authorization/client-credentials-grant) trades the client ID and secret for an Admin API token, with no redirect and no merchant prompt: ```python def get_access_token(shop_url, client_id, client_secret): response = httpx.post( f"{shop_url}/admin/oauth/access_token", data={ "grant_type": "client_credentials", "client_id": client_id, "client_secret": client_secret, }, timeout=30, ) response.raise_for_status() return response.json()["access_token"] ``` The token lasts 24 hours (`expires_in` is `86399`). That's a poor fit for a long-lived config value but a good fit for a Flight: each run mints its own token, and a run finishes well inside the window. :::note dlt's argument for this value is `private_app_password`, legacy naming from Shopify's retired private apps. Pass the token you just minted, not the `shpss_` client secret. ::: ### If you already have a static token An admin-created custom app from before 2026 still works, and its `shpat_` Admin API token doesn't expire. In that case skip the exchange, drop `httpx`, and hand dlt the token directly through a secret param named `SOURCES__SHOPIFY_DLT__PRIVATE_APP_PASSWORD`, which dlt resolves. ## Create the Flight The Shopify 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](/key-tasks/flights/packages-and-runtime#use-a-dlt-verified-source) for how this works and what to watch for. Beyond dlt, the Flight needs an HTTP client for the token exchange: ```text duckdb==1.5.5 dlt[motherduck]==1.30.0 httpx==0.28.1 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](https://github.com/dlt-hub/verified-sources/commits/master) 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. Set `api_version` explicitly. The source's default trails Shopify's supported window, and Shopify removes versions about a year after release. `MOTHERDUCK_TOKEN` is injected for you, so dlt's MotherDuck destination picks up the credential without configuration. ```python import os import dlt import duckdb import httpx from dlt.common.configuration.container import Container from dlt.extract.incremental.context import TimeIntervalContext from sources.shopify_dlt import shopify_source DB = "shopify" def get_access_token(shop_url, client_id, client_secret): response = httpx.post( f"{shop_url}/admin/oauth/access_token", data={ "grant_type": "client_credentials", "client_id": client_id, "client_secret": client_secret, }, timeout=30, ) response.raise_for_status() return response.json()["access_token"] 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}"') # Every shopify_dlt resource sets allow_external_schedulers=True, which makes # dlt require an Airflow-style interval. Turn that off for all of them at # once and leave dlt's own incremental state in charge. Container()[TimeIntervalContext] = TimeIntervalContext( allow_external_schedulers=False ) shop_url = os.environ["SHOP_URL"].rstrip("/") access_token = get_access_token( shop_url, os.environ["shopify_CLIENT_ID"], os.environ["shopify_CLIENT_SECRET"], ) pipeline = dlt.pipeline( pipeline_name="shopify", destination="motherduck", dataset_name="shopify_raw", ) source = shopify_source( private_app_password=access_token, shop_url=shop_url, start_date="2024-01-01", api_version="", ).with_resources("orders", "customers", "products") print(pipeline.run(source, loader_file_format="parquet")) if __name__ == "__main__": main() ``` Create the Flight with [`MD_CREATE_FLIGHT`](/sql-reference/motherduck-sql-reference/flights/md-create-flight), passing that Python as `source_code`, the pinned dependencies as `requirements_txt`, `flight_secret_names := ['shopify']` so the client ID and secret reach the run, and the `config` map with the store URL. Leave `schedule_cron` off until a manual [`MD_RUN_FLIGHT`](/sql-reference/motherduck-sql-reference/flights/md-run-flight) succeeds, then add a schedule with [`MD_UPDATE_FLIGHT`](/sql-reference/motherduck-sql-reference/flights/md-update-flight). ## Query the result dlt creates one table per resource in the `shopify_raw` schema, plus child tables for nested arrays. Order line items land in `orders__line_items`: ```sql SELECT items.title, sum(items.quantity) AS units, sum(items.quantity * items.price::DECIMAL(12, 2)) AS revenue FROM shopify.shopify_raw.orders AS orders JOIN shopify.shopify_raw.orders__line_items AS items ON items._dlt_parent_id = orders._dlt_id WHERE orders.created_at >= current_date - INTERVAL 30 DAY GROUP BY ALL ORDER BY revenue DESC LIMIT 20; ``` ## Source options | Argument | Default | Effect | |---|---|---| | `api_version` | `2023-10` | Admin API version. Set this explicitly, since the default ages out. | | `start_date` | `2000-01-01` | Lower bound for the first incremental load. | | `end_date` | `None` | Upper bound. Set both to run a bounded backfill. | | `created_at_min` | `2000-01-01` | Filters on creation date rather than the incremental `updated_at` cursor. | | `items_per_page` | `250` | Page size, which is also Shopify's maximum. | ## Known limitations - **The source expects an external scheduler.** Every resource declares `allow_external_schedulers=True`, which tells dlt to take its load window from an orchestrator rather than from its own state. Despite the name, dlt treats it as a requirement: with no Airflow context and no `DLT_INTERVAL_START`/`DLT_INTERVAL_END` pair, a run fails with `ExternalSchedulerNotAvailable`. The `TimeIntervalContext` override above switches it off for every resource at once. Setting the two interval variables also clears the error, but then each run loads a fixed window instead of resuming where the last one stopped. - **The client credentials grant needs one organization.** The app and the store must sit in the same Shopify organization, or the token request fails with `shop_not_permitted`. Across organizations, use the [authorization code grant](https://shopify.dev/docs/apps/build/authentication-authorization/access-tokens/authorization-code-grant) to get a long-lived offline token and pass that instead. - **Minted tokens expire after 24 hours.** Fine for a Flight that mints one per run, but don't cache the token in `config` or a secret between runs. - **The default `api_version` is stale.** The source defaults to `2023-10`, and Shopify removes API versions roughly a year after release. Pass a [supported version](https://shopify.dev/docs/api/usage/versioning) and revisit it when you update the pinned commit. - **Orders are limited to 60 days without `read_all_orders`.** This is a Shopify scope restriction, not a dlt one, and it fails quietly by returning fewer rows rather than raising an error. - **Incremental loading tracks `updated_at`.** A record edited in Shopify reappears in the next load, which is what you want, but it means row counts per load don't equal new records. - **Money fields arrive as strings.** Shopify returns amounts as decimal strings, so cast them in SQL, as in the query above, rather than assuming a numeric type. - **Only three Admin API resources are covered.** Inventory, fulfillments, discounts, and payouts aren't included. For those, use dlt's [REST API source](https://dlthub.com/docs/dlt-ecosystem/verified-sources/rest_api) against the endpoints you need. - **The connector isn't editable when installed as a dependency.** To change extraction logic, use `dlt init shopify_dlt motherduck` in a local project. ## Managed alternatives If you'd rather not run the pipeline yourself, [Fivetran](/integrations/ingestion/fivetran) and [Airbyte](/integrations/ingestion/airbyte) both offer a Shopify source and a MotherDuck destination. ## Related content - [Load data with dlt from Shopify to MotherDuck](https://dlthub.com/docs/pipelines/shopify_dlt/load-data-with-python-from-shopify_dlt-to-motherduck) - [dlt Shopify verified source reference](https://dlthub.com/docs/dlt-ecosystem/verified-sources/shopify) - [Run a dlt ingest pipeline in a Flight](/key-tasks/flights/run-dlt-ingest-pipeline) - [Packages and recommended libraries](/key-tasks/flights/packages-and-runtime) - [Shopify Admin API versioning](https://shopify.dev/docs/api/usage/versioning) --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fintegrations%2Fingestion%2Fshopify%2F&page_title=Shopify&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.