In this tutorial, we'll show you how to:

  • Connect your motherduck account to Faraday using a connection.

Let's dive in.

  1. You'll need a Faraday account — signup is free!

Prerequisites

You'll need the following details to create your connection to motherduck:

  • MotherDuck token requiredtextAccess token from MotherDuck Settings. Used to authenticate with MotherDuck.
  • Database requiredtext

Granting access

First, you'll need Faraday access to your motherduck account.

MotherDuck is a serverless analytics platform built on DuckDB. Faraday can connect to MotherDuck to read data (datasets) and write data (targets) directly from/to your MotherDuck database.

Setup process

Setting up MotherDuck with Faraday is straightforward and requires only a token and database name:

  1. Get your MotherDuck token:
  • Log into MotherDuck
  • Navigate to Settings
  • Generate or copy your access token
  • Keep this token secure - it provides full access to your MotherDuck databases
  1. Identify your database name:
  • In MotherDuck, note the name of the database you want Faraday to access
  • The database must already exist in your MotherDuck account
  • For datasets (reading data), ensure the tables you want to access exist in this database
  • For targets (writing data), ensure you have write permissions to this database
  1. Create your Faraday connection:
  • Use the API or UI to create a MotherDuck connection
  • Provide your token (from step 1)
  • Provide your database name (from step 2)
  1. Create datasets or targets:
  • Specify the table_name you want to read from or write to
  • Optionally specify a schema (defaults to 'main' if not provided)

Faraday suggests that you use an unguessable string somewhere in the path to your data. This avoids what is called the Confused deputy problem

For example, let's say you were using S3. Instead of naming an S3 bucket s3://faraday-acme/, name it s3://faraday-acme-pwiiprz162ez. This guarantees that malicious actors cannot guess the name and request that Faraday import data from it into their account. The same logic applies to any path that is used to locate data.

Supported data types

Faraday supports the following DuckDB/MotherDuck data types:

  • BIGINT
  • INTEGER
  • SMALLINT
  • TINYINT
  • DOUBLE
  • FLOAT
  • REAL
  • VARCHAR
  • TEXT
  • STRING
  • BOOLEAN
  • DATE
  • TIMESTAMP
  • TIMESTAMPTZ
  • UUID
  • JSON

Complex types like ARRAY and STRUCT are converted to JSON for compatibility.

Additional notes

  1. Schema defaults: If you don't specify a schema, MotherDuck will use the 'main' schema by default.

  2. Incremental syncs: For datasets, you can specify an incremental_column (typically a timestamp or date column) to sync only new or updated rows.

  3. Table creation: For targets, Faraday will create the table if it doesn't exist. If the table already exists, it will be replaced with the new data (CREATE OR REPLACE TABLE pattern).

  4. Security: Faraday uses saas_mode=true when connecting to MotherDuck to prevent any access to local filesystems, ensuring secure cloud-only operations.

  5. Permissions: Your MotherDuck token needs:

    • READ permissions on tables you want to use as datasets
    • WRITE permissions on schemas where you want to create targets

Connecting

API via cURL
Dashboard

Use a POST /connections request:

curl https://api.faraday.ai/connections --json '{
  "name": "motherduck",
  "options": {
    "type": "motherduck",
    "token": "...",
    "database": "..."
  }
}'
  1. Wait briefly while Faraday establishes your connection. It shouldn't take long.

Your new connection is now ready to use.