MotherDuck: Access Management and Scalable Analytics Overview

MotherDuck's architecture for storage management and user access is built on several key design principles that shape how data is organized and shared. To understand how MotherDuck manages access control, you need to understand three key concepts: organizations, shares and Ducklings. First of all, MotherDuck uses organizations to group users. This is the top-level entity that handles administrations, data sharing, billing, and security, to name a few. Users can create organizations or join an existing one when signing up. Databases can be shared inside the organization with other users or outside via share URLs.

Creating the share works via the UI or SQL commands. Below is an SQL code snippet that explains the necessary steps.

1ATTACH "md:";
2
3USE flights;
4CREATE SHARE IF NOT EXISTS flights FROM flights
5   (ACCESS ORGANIZATION , VISIBILITY DISCOVERABLE, UPDATE MANUAL);

In this example, everyone in the user’s organization can see the share and read the data. The ACCESS parameter controls who can see the share, VISIBILITY determines if it appears in the UI, and UPDATE controls how data refreshes are handled. The other scopes for sharing are restricted and unrestricted, allowing either read access to a limited group of users or unlimited read access for every user who is signed into MotherDuck and has the shared URL. Share URLs are constructed as follows md:_share/<source_database_name>/<share_token>.

To consume shared data a user must attach the share to their workspace. This creates a read-only zero-copy clone of the source database. Attaching the database is possible in two ways. The UI offers all available shares in an overview when the creators make their shares discoverable. The share can easily be attached via the press of a button. However, hidden shares must be attached via the share URL, which must be sent to the consumer by the data provider. Public shares can also be attached via the respective share URL if the consumer is not using the UI.

The UPDATE keyword determines if adding new data is reflected for the share consumers automatically. If it is set to MANUAL, the data provider must refresh the data via an SQL statement

1UPDATE SHARE flights;

Consumers on the other site can use the REFRESH statement to check for new data manually. Auto-updating shares are refreshed periodically every minute.

Read-Only only?

This sharing model naturally leads to an important question: why are so many of these connections read-only? This is a limitation of the underlying technology for MotherDuck, which is DuckDB. DuckDB allows multiple parallel read-only connections but only one read-write connection at a time per database file. Each user has their own Duckling, a compute instance that has a very fast cold-start and shuts down as quickly when idle. To allow shared storage, the DuckDB instance (the Duckling) must be in read-only mode to realize the access. Consumers can decide to create a full replica of shared data and use the write access for their own data to manipulate the copy, however updates from the original share can no longer be reflected in this dataset automatically.

There are actually two types of Ducklings available. One is the standard read-write Duckling that is assigned to every user by default, providing full access to create tables, modify structures, and perform data transformations. The second type is Read Scaling Ducklings, which are used specifically for read operations that could cause a bottleneck if only one Duckling served requests from multiple users.

Read Scaling

The challenge MotherDuck identified was that Business Intelligence tools typically use a single shared database connection across an entire organization, making all queries appear as if they come from one user. Within Motherduck this would mean all requests route to the same Duckling, potentially causing performance issues when dozens or hundreds of people use dashboards simultaneously. For instance, if your marketing team of 50 people all opened the same dashboard at 9 AM Monday morning, without Read Scaling that single Duckling could be overwhelmed. Read Scaling solves this by automatically spinning up additional Ducklings as needed when clients connect using read-only tokens. These replicas are clones of the original Duckling that have access to the same data and distribute the query load across multiple instances.

Read Scaling can be enabled when creating an access token in MotherDuck. The other available token type offers read-write access and does not scale since it is using the default Duckling and multiple read-write connections are not available. This bundling of scaling capabilities with access management is an unconventional approach. In most data warehouses, scaling is controlled through separate compute configuration or warehouse sizing settings, independent of the authentication. MotherDuck ties these concerns together: choosing read-only access automatically enables the scaling mechanism, while write access inherently limits users to a single instance. When a client connects using a Read Scaling token, it is directed to one of the read scaling replicas, each powered by its own dedicated duckling. As more users connect via read scaling tokens, the flock of ducklings expands, aiming to give each user their own dedicated duckling up to the configured limit, which defaults to 16 replicas but can be adjusted by contacting MotherDuck support. If this limit is exceeded, new connections share existing ducklings while maintaining user-to-duckling affinity where possible.

MotherDuck intelligently routes queries to maintain session affinity. Applications can provide a session_hint parameter in the connection string to ensure all queries from a specific end user route to the same duckling replica. This improves caching effectiveness and provides a more consistent view of data across queries for that user. The session hint can be set to a user session ID, user ID, or hashed value for privacy. Additionally, DuckDB integrations now support instance caching with a configurable time-to-live parameter (dbinstance_inactivity_ttl) that helps maintain session affinity even across separate queries or short connection gaps. Read scaling replicas are eventually consistent, meaning read operations might see data that slightly lags behind the very latest writes made to the primary instance, typically syncing within a few minutes. For applications requiring stricter synchronization, users can manually create snapshots on the writer connection and refresh databases on read-scaling connections. This architecture has delivered significant performance improvements, with MotherDuck's own BI dashboards loading much faster after switching to Read Scaling tokens, making it a lightweight solution compared to traditional data warehouse scaling approaches.

Conclusion

MotherDuck's access management centers on understanding the interplay between shares, Ducklings, and tokens. Shares enable zero-copy data distribution across your organization but lock you into read-only access due to DuckDB's single-writer constraint. In practice, this means only one user, often a technical service account, can provide and update data while all the other users consume it read-only. This aligns with common data warehouse patterns where most data remains immutable to prevent users from breaking shared datasets. Standard read-write Ducklings work well for individual users, but shared BI connections can bottleneck without Read Scaling tokens. The key consideration is eventual consistency: Read Scaling replicas may lag behind primary instances by a few minutes, which matters for real-time scenarios but is acceptable for most analytics workflows. Plan your architecture around these limitations, particularly if your use case requires immediate data synchronization or frequent write operations on shared datasets.

A look into the future: DuckLake-based storage will mitigate the limitation of single-account write access in a future release. Currently, write permissions are limited to one account per database, which can perform multiple concurrent writes as long as they are append-only.

To see these concepts in action, enroll in our on-demand Hands-on Workshop: Introduction to MotherDuck for a complete practical walkthrough.