Your understanding is pretty solid! MotherDuck databases are indeed owned by accounts (human or service), and the pattern of using service accounts for production workloads + shares for read access is the encouraged direction.
One thing worth noting coming from Snowflake/BigQuery is that MotherDuck doesn't have a roles layer like SYSADMIN/ACCOUNTADMIN or IAM bindings on datasets. Access control is at the database level: a user either has access to a database or doesn't. Within an org, users are either Admins (can manage users, billing, invites) or Members. The building blocks for access separation are service accounts, shares, and token types.
For production workloads, especially for production runs, we'd recommend using separate user accounts or service accounts with dedicated compute for updating and maintaining core tables. So the pattern would be:
1. Create a production service account
This account owns the production databases and runs your dbt/pipeline jobs with its read/write token.
2. Create a staging service account
This gives you compute isolation, since every duckling (compute instance) is isolated from each other.
3. Share production data with the org for read access
The production service account creates shares from its databases. They have three access levels to choose from:
• ACCESS ORGANIZATION (default) where only members of your organisation can access the share
• ACCESS RESTRICTED where only the share owner and users specified with GRANT commands can access the share
• ACCESS UNRESTRICTED where any user signed into any MotherDuck organisation in the same cloud region can access the share
3. For your use case (let the whole org discover it), ACCESS ORGANIZATION with VISIBILITY DISCOVERABLE is the right default. If you want to limit staging access to just the data team, ACCESS RESTRICTED + GRANT READ ON SHARE to specific usernames can give you that control.
4. BI tools and read-heavy consumers use read-scaling tokens
You generate a read_scaling token for your BI tool that only permits read operations, and a standard read_write token for your ETL script.
We have a nice service account walkthrough here, a DWH overview here (with broader patterns on how to structure a warehouse on MotherDuck), and an org management guide.
Would this help? 🙂
.png){kind=logo}
