Load Data into MotherDuck

Use this skill when the job is getting data into MotherDuck correctly and efficiently, not just writing one ad hoc import query.

Source Of Truth

• Prefer current MotherDuck loading, cloud-storage, and Postgres-endpoint loading docs first.
• Use

  CREATE SECRET

  and cloud-storage docs for protected-object-store workflows.
• Keep the loading advice aligned with MotherDuck's documented posture:
  • batch over streaming
  • Parquet over CSV when you control the format
  • dataframe,

    COPY

    , CTAS, or

    INSERT ... SELECT

    over row-by-row inserts
  • native MotherDuck storage first unless DuckLake is explicitly required

Default Posture

• Start by classifying the source: object storage or HTTPS, local file or local DuckDB, in-memory rows, or an external database.
• Prefer

  CREATE TABLE AS SELECT

  for first loads and

  INSERT INTO ... SELECT

  for appends.
• Use Parquet for durable bulk movement whenever you control the source format.
• Treat the Postgres endpoint as a thin-client path for server-side remote reads, not for local-file or extension-driven ingestion.
• Bootstrap the target MotherDuck database first when the ingestion tool does not create it automatically.
• Keep source storage close to the MotherDuck region when you control placement.

Workflow

1. Identify where the source data actually lives.
2. Choose the loading path:
   • object storage or HTTPS: remote read into MotherDuck
   • local file or local DuckDB: use a DuckDB client path
   • in-memory rows: Arrow or dataframe bulk load first, batched inserts only as a fallback
   • external database: use the appropriate scan or replication path from a DuckDB-capable environment
3. Land the data into a raw or staging table with minimal transformation.
4. Validate row counts, types, and a few business aggregates immediately after the load.
5. Promote into modeled tables only after the landing step is correct.

Open Next

• references/INGESTION_PATTERNS.md

  for format-specific options, cloud-storage secrets, Postgres-endpoint loading tradeoffs, Python dataframe paths, and advanced ingestion patterns

Related Skills

• motherduck-connect

  for choosing between the Postgres endpoint and a DuckDB client path

• motherduck-explore

  for inspecting destination databases and validating landed tables

• motherduck-query

  for writing CTAS, append, and validation SQL

• motherduck-model-data

  for promoting landed data into staging and analytics tables

• motherduck-ducklake

  only when object-storage-backed lakehouse storage is an explicit requirement