Writing robust data pipelines with AILivestream July 22

Skip to main content

3 - Sharing Your Database

In this section, you'll learn how to share your databases with colleagues and collaborate effectively using MotherDuck's sharing features.

👈 Go back to Part 2: Loading Your Dataset

Creating and sharing your data​

Let's create a table with sample data in your playground database, then share it with others. The docs_playground database is automatically created when you connect, so you can start experimenting right away!

First, let's populate your playground database with some currency exchange data:

SQL editor
CREATE TABLE docs_playground.currency_rates AS
SELECT
  'USD' AS currency_code,
  'US Dollar' AS currency_name,
  1.0 AS rate_to_usd,
  '2024-01-15' AS rate_date
UNION ALL
SELECT
  'EUR',
  'Euro',
  0.85,
  '2024-01-15'
UNION ALL
SELECT
  'GBP',
  'British Pound',
  0.75,
  '2024-01-15'
UNION ALL
SELECT
  'JPY',
  'Japanese Yen',
  110.0,
  '2024-01-15';
SQL Editor loading...
Login to connect

Sharing your database​

With your database and sample data in place, you can share this dataset with others. MotherDuck shares create a point-in-time snapshot of your database that can be accessed by specified users or groups.

When creating a share, the most important parameters control access scope, visibility, and update behavior. By default, shares use ACCESS ORGANIZATION (only your organization members can access), VISIBILITY DISCOVERABLE (appears in your organization's shared database list), and UPDATE MANUAL (creates a static snapshot that doesn't auto-update).

The syntax to create a share visible to everyone in your Organization is CREATE SHARE <share name> from <database name>.

SQL editor
CREATE SHARE IF NOT EXISTS currency_data_share
FROM
  docs_playground (ACCESS ORGANIZATION, VISIBILITY DISCOVERABLE);
SQL Editor loading...
Login to connect

Once created, all members of your organization will be able to view this share in the MotherDuck UI under "Shared with me".

Learn more about sharing in MotherDuck.

Understanding share configuration​

When creating shares, you can control three key aspects: who can access the data, how users discover the share, and when the data updates. Each parameter has specific options that determine the sharing behavior.

ACCESS - who can access the share​

  • ACCESS ORGANIZATION (default): Only members of your organization can access the share
  • ACCESS UNRESTRICTED: All MotherDuck users in the same cloud region as your Organization can access the share
  • ACCESS RESTRICTED: Only the share owner has initial access; additional users must be granted access through GRANT commands

VISIBILITY - how users discover the share​

  • VISIBILITY DISCOVERABLE (default): The share appears in your organization's "Shared with me" section for easy discovery
  • VISIBILITY HIDDEN: Share can only be accessed through a direct URL; not listed in any user interface
Important Visibility Rules
  • Organization and Restricted shares default to DISCOVERABLE
  • Unrestricted shares can only be HIDDEN
  • Hidden shares can only be used with ACCESS RESTRICTED

UPDATE - when share data updates​

  • UPDATE MANUAL (default): Share content only updates when you run UPDATE SHARE command
  • UPDATE AUTOMATIC: Share automatically reflects database changes within ~5 minutes

Example share configurations​

SQL editor
-- Organization share (most common)
CREATE SHARE IF NOT EXISTS team_currency_analysis
FROM
  docs_playground (
    ACCESS ORGANIZATION,
    VISIBILITY DISCOVERABLE,
    UPDATE MANUAL
  );
SQL Editor loading...
Login to connect
SQL editor
-- Restricted share for selective access
CREATE SHARE IF NOT EXISTS private_analysis
FROM
  docs_playground (
    ACCESS RESTRICTED,
    VISIBILITY HIDDEN,
    UPDATE AUTOMATIC
  );
SQL Editor loading...
Login to connect

Querying shared data​

After creating a share, authorized users can access the shared database in two ways: by using the share URL directly or by attaching it as a database alias:

-- Attach a shared database
ATTACH 'md:_share/docs_playground/b556630d-74f1-435c-9459-cfb87d349cb3' AS shared_currency;

-- Query the shared data
SELECT * FROM shared_currency.currency_rates 
WHERE rate_to_usd < 1.0
ORDER BY rate_to_usd DESC;

Managing Shares​

You can also manage your existing shares:

SQL editor
SELECT
  name,
  source_db_name,
  access,
  visibility
FROM
  MD_INFORMATION_SCHEMA.OWNED_SHARES
WHERE
  name LIKE '%currency%';
SQL Editor loading...
Login to connect

Going further​

Now that you've mastered the basics, here are some next steps to explore: