Skip to main content
A motherduck database entry can be added by specifying type: motherduck in your ~/.config/oxy/config.yml file. This integration allows Oxy to query databases hosted in MotherDuck, the cloud-based DuckDB platform, using your authentication token.

​
Required fields

Each motherduck entry requires the following fields:
  • token_var – The name of the environment variable that stores your MotherDuck authentication token.
  • type – Must be set to motherduck.

​
Optional fields

  • database – The name of the specific MotherDuck database to connect to. If omitted, connects to your default database.
  • schemas – A map of schema names to table patterns for filtering which schemas/tables to sync. Each key is a schema name, and the value is an array of table patterns (use ["*"] for all tables in that schema). If omitted, all schemas will be synced.

​
Authentication

Oxy connects to MotherDuck using an authentication token. You should store this token as an environment variable and reference it via token_var. For example, if your variable is MOTHERDUCK_TOKEN, make sure it’s exported in your environment before running any queries: 
export MOTHERDUCK_TOKEN="your_motherduck_token_here"
Alternatively, you can add it to your .env file: 
echo MOTHERDUCK_TOKEN=your_motherduck_token_here >> .env
To obtain a MotherDuck token, visit the MotherDuck console and generate an API token from your account settings.

​
Sample config entries

​
Connect to default database

databases:
  - name: motherduck
    type: motherduck
    token_var: MOTHERDUCK_TOKEN

​
Connect to specific database

databases:
  - name: motherduck_analytics
    type: motherduck
    token_var: MOTHERDUCK_TOKEN
    database: my_analytics_db  # replace with your database name

​
Filter by specific schemas

databases:
  - name: motherduck_filtered
    type: motherduck
    token_var: MOTHERDUCK_TOKEN
    database: my_analytics_db
    schemas:
      main: ["*"]              # sync all tables from 'main' schema
      analytics: ["users", "events"]  # sync only specific tables from 'analytics' schema

​
Usage notes

  • MotherDuck uses the DuckDB SQL dialect, so all DuckDB features and functions are available.
  • Queries are executed in the cloud and results are streamed back to Oxy.
  • You can query both cloud-based tables and local files using DuckDB’s hybrid execution model.
  • MotherDuck provides automatic caching and optimization for better query performance.
  • Schema sync: Use oxy sync to automatically generate semantic models from your MotherDuck database schema.

​
Syncing your schema

You can automatically generate semantic models from your MotherDuck database schema using the oxy sync command: 
oxy sync
This will:
  1. Query your MotherDuck database’s information_schema to discover all tables and columns
  2. Generate semantic model YAML files in your project’s configured location
  3. Include table names, column names, and data types
To control which schemas and tables are synced, use the schemas field in your config: 
databases:
  - name: motherduck
    type: motherduck
    token_var: MOTHERDUCK_TOKEN
    database: exercise
    schemas:
      main: ["*"]           # sync all tables from main schema
      staging: ["orders"]   # sync only orders table from staging schema
You can also sync specific databases using command-line options: 
# Sync only the motherduck database
oxy sync --database motherduck

# Sync with overwrite flag to replace existing files
oxy sync --overwrite

​
Troubleshooting

  • Token issues: Ensure your MOTHERDUCK_TOKEN environment variable is set correctly and contains a valid token.
  • Database not found: Verify the database name in your config matches an existing database in your MotherDuck account.
  • Connection errors: Check your internet connectivity and verify you can access MotherDuck through their web console.
  • Sync issues: If oxy sync fails, ensure your token has read permissions on the database schema.