Skip to main content

Where code runs

MotherDuck workloads run in one of three environments. Choose the environment before choosing an authentication method or an extension: the same SQL text can have different capabilities depending on where it runs.

EnvironmentUse it forCommunity extensionsCredentials available to the code
Local* DuckDB clientInteractive development, one-off loads and Dual ExecutionInstall and load community extensions like bigqueryLocal environment variables, Application Default Credentials (ADC), and local secrets
MotherDuck DucklingSQL editor, remote MCP queries, and direct cloud-storage readsCannot install or load community extensionsMotherDuck storage secrets, such as TYPE GCS or TYPE S3
Flight Python runtimeScheduled ingestion and Python workflowsInstall and load extensions in the Flight's in-process DuckDBTYPE flights secrets attached to that Flight, plus the injected MotherDuck token
note

Local means a non-MotherDuck environment in this case. That could be your own machine, a third-party ETL service or even a browser (WASM).

Interfaces, runtimes, and secrets​

The interface where you write code and the runtime where that code executes are usually different places. Each interface hands work to one of the three runtimes, and each runtime reaches its own set of credentials. When a query cannot find a credential look at its runtime to understand where it can fail.

Flights and Ducklings run on MotherDuck infrastructure. The local DuckDB client is the one interface that is also a runtime: your SQL executes in the same process, and only the work that touches an attached md: database moves to a Duckling (including related secrets). Every other interface is remote from its runtime. The UI notebook and a chat client (through the MCP server) send SQL to a Duckling, and a Flight has no interactive interface at all: it starts from its schedule or a manual run, and its in-process DuckDB connects back to cloud SQL like any other client unless it's explicitly told to run local to the Flight runtime only.

Credentials do not cross runtimes on their own. A Duckling cannot read your laptop's environment variables, a local extension cannot authenticate with a TYPE flights secret, and a Flight's in-process DuckDB sees only its own environment variables, though the cloud side of its md: connection uses stored secrets like any other Duckling session.

Four caveats worth knowing.

  • Write access can be prevented in multiple ways A Duckling can only write when accessed with a read-write token. Even with a read-write token the MCP server's query tool rejects write statements before they reach MotherDuck, unless the query_rw is enabled and used instead.

  • A read-only connection can still use a stored secret. A secret you saved with CREATE SECRET ... IN MOTHERDUCK is available to the query tool and to a read scaling connection, so a read-only path can select from s3:// or gs://. What it cannot do is create or drop one: CREATE SECRET is a write statement. Storing a secret also needs the create-secrets permission from your role, which Admin and Builder have and Explorer does not.

  • A Flight runs read-write by default. Flights run with a default read-write token injected into each run that inherits your identity and can write. To narrow that, create the Flight with access_token_name pointing at a token with fewer privileges, such as a service account scoped to one database. See Authentication, config, and secrets.

  • A share does not carry secrets. Stored secrets are scoped to the user who created them. A consumer of your share queries it on their own Duckling with their own stored secrets, so a view that reads from a private S3 bucket with s3:// works for you and fails for them until they store their own secret for that bucket.

Unsupported extensions and connectors​

Some connectors and community extension are not supported on Ducklings or even by DuckDB. These come in two flavors.

  • Community DuckDB extensions. The bigquery extension is the well-known case. A Duckling does not install or load community extensions, so a read-only MCP query cannot call INSTALL bigquery, ATTACH ... TYPE bigquery, bigquery_scan, or bigquery_query. Use a local DuckDB client for interactive work, or run DuckDB in a Flight for scheduled ingestion.
  • Vendor Python clients. For sources without a first-class DuckDB extension, such as Snowflake or Databricks, ingestion runs through the vendor's Python SDK, typically fetching results as Arrow and registering them with DuckDB. The Snowflake ingestion Flight is the reference for this pattern.

Core DuckDB extensions such as postgres and mysql load in Ducklings, so they do not need this workaround. Cloud storage (s3://, gs://) also works from Ducklings directly with a stored secret, which covers Delta and Iceberg tables on S3, GCS, or Azure and by extension most Databricks or lakehouse data reachable through its object store.

Third-party ETL tools​

Managed pipelines like dlt, Fivetran, or Airbyte are often not an execution environment of their own: they are clients that write to MotherDuck. Where they run decides which credentials they see.

  • Run the tool inside a Flight to reuse Flight scheduling, logs, and TYPE flights secrets for source credentials. See the dlt ingestion Flight and the Postgres ingestion Flight for the in-Flight pattern.
  • Run the tool on its own infrastructure and point it at MotherDuck through a DuckDB SDK or the Postgres endpoint. MotherDuck is the destination and the tool's own runtime holds source credentials.

Secret types match the environment​

A TYPE GCS, TYPE S3, or TYPE AZURE secret is a cloud-storage provider secret. MotherDuck cloud SQL can select from a gs://, s3://, or abfss:// path and connects to the storage service remotely. See Google Cloud Storage for the GCS example.

A TYPE flights secret is different: it stores key-value parameters for a Flight. Attach its name with flight_secret_names when creating the Flight. The Flight runtime exposes each parameter as an environment variable named <secret_name>_<parameter_name>. A SQL editor or MCP query session cannot read those parameters or use the secret to authenticate a community extension or a vendor SDK.

Use CREATE OR REPLACE SECRET with a complete replacement definition when a secret needs a different type. Changing a cloud-storage secret to a Flights secret also changes its parameter model, so replace the provider fields with a PARAMS MAP and attach the resulting secret to the relevant Flight.

Choose a path by source shape​

  • Object storage (Parquet, CSV, JSON, Delta, Iceberg on S3, GCS, or Azure). Store a TYPE S3, TYPE GCS, or TYPE AZURE secret and query from cloud SQL. No extra runtime needed.
  • A source with an in-process DuckDB extension, such as BigQuery. Use the extension in a local DuckDB client for interactive exploration, or in a Flight for scheduled ingestion. See the BigQuery integration and the BigQuery ingestion Flight; the GA4 recipe specializes the latter for daily export shards.
  • A source reachable only through a vendor SDK, such as Snowflake or Databricks. Run ingestion in a Flight and land data into MotherDuck through its md: connection. See the Snowflake ingestion Flight.
  • A managed ETL tool. Point it at MotherDuck as a destination, either from inside a Flight or from the tool's own runtime, per Third-party ETL tools above.

Related content​