MotherDuck

About

Polytomic can run ETL and sync workloads with MotherDuck in both directions. The following workloads are supported:

• Ingesting data from SaaS platforms like Stripe, Salesforce, Intercom, and many others .
• High-scale ingest CDC (Change Data Capture) streaming from storage systems like PostgreSQL, MySQL, PlanetScale, MongoDB, Amazon DynamoDB, and Amazon S3.
• Reverse ETL: sync from MotherDuck to SaaS platforms, databases, data warehouses, spreadsheets, webhooks, and cloud storage buckets like S3.

Connecting Polytomic to MotherDuck

Polytomic connects to MotherDuck using a MotherDuck Access Token:

1. Generate a MotherDuck Access Token by following MotherDuck's instructions.

2. In Polytomic, go to Connections → Add connection → MotherDuck.

3. Enter the MotherDuck Database for Polytomic to use and paste your Access token.

4. Optional: if you're writing to MotherDuck, enter an S3 bucket name and S3 bucket region. Also enter an AWS access key ID and AWS secret access key with access to the bucket (see below). The bucket name may include a folder, e.g. s3://my-bucket/polytomic.

5. Click Save.

S3 bucket write requirement

Writing to MotherDuck requires an S3 bucket as a staging area for temporary files. Polytomic does not use it as a permanent data lake. To write permanent files to S3, sync directly to Polytomic's S3 connection.

Polytomic writes Parquet files to the bucket and MotherDuck reads them. The access key needs the following access to the bucket:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::your-bucket-name",
        "arn:aws:s3:::your-bucket-name/*"
      ]
    }
  ]
}

📘

Polytomic stores an S3 secret in your MotherDuck account

Polytomic creates a persistent S3 secret named polytomic_<connection ID> in your MotherDuck account so that MotherDuck can read the staged files. The secret is scoped to the staging bucket and contains the access key above. Polytomic updates it at the start of each sync.

If you rotate the key or delete the connection, you can remove the secret with DROP PERSISTENT SECRET "polytomic_<connection ID>";.

Syncing to MotherDuck

Use Bulk Syncs to sync to MotherDuck from your databases, data warehouses, SaaS applications, and cloud storage buckets.

Use Model Syncs to sync to MotherDuck from custom SQL queries (e.g. from a custom SQL query on PostgreSQL).

Syncing from MotherDuck

Use Model Syncs to sync from MotherDuck to SaaS applications like Salesforce, spreadsheets like Google Sheets and Airtable, and webhooks.