Create Your Data Apps with DLH.io and MotherDuck - You’re in Luck!

Build a modern data app with DLH and the MotherDuck Destination Connector to sync, transform, and visualize your data.

DLH created one of the first modern data stack destination connectors for MotherDuck years ago and are just now writing about it. The MotherDuck Connector is used to replicate data from operational systems like Salesforce, Xero, HubSpot, even Oracle Fusion, and databases such as SQL Server, Postgres, and MySQL into your MotherDuck account database(s).

If you follow the folks at MotherDuck you might already know this architecture flow where the need for producing a customer facing data architecture, https://motherduck.com/docs/getting-started/customer-facing-analytics/, continues to grow in importance.

But the question always remains, how do we get data into MotherDuck? The answer is the DLH MotherDuck destination target connector.

If you want to build a Power BI dashboard or use a python notebook to build a process on top of MotherDuck you first need to get data into MotherDuck. Otherwise where is your data coming from? And, data engineers that are working for companies with deadlines and backlogs of tasks need to stop pretending that they can just build a reliable system to extract data from their source systems, and maintain that build. Even if they could, they need to stop and ask if they should (yes, Jurassic Park reference). Most companies are not software development companies, they are just trying to get answers for internal customers, so trying to be a product software company is not what their bosses want for them or their team. So, this is where DLH comes to the rescue to speed up your ability to get the job done. First question, What is your source system? Check DLH.io’s connectors, and chances are we have it ready to go (if we don’t just let us know and we’ll build it). Second question, do you have MotherDuck? If yes, then the rest of this article is for you. If not, sign up for a MotherDuck account, and this article is still for you.

DLH connectors are maintained by the awesome team at DLH.io and we fully support SLAs around them. Production away!

Log in or create an account on DLH.io. We work with professional data engineers and mainly only accept business accounts, so you’ll need to use your work email, because you are a professional.

Once logged in click on Targets or Destinations and find or search for MotherDuck above the icons of the connectors.

Follow the instructions shown on the right side of the window but here is the general gist:

Enter a name for your connector - keep in simple like, Demo MotherDuck Target

The Account URL and Auth Type fields are set to their defaults in the current version of the connector, so no need to change those fields

Enter in the Database field a database name that you have already created in your MotherDuck account. This is where the data from the spreadsheet will land from the Sync Bridge. If the database does not exist in your account, DLH currently does not create a new one. So you must already have the database existing in your MotherDuck account..

Enter in the Service/Access Token field, your MotherDuck token - If you aren’t sure where you get your access token in your MotherDuck account, just log in to MotherDuck and go to Settings > General. Then click the Create Token button. Copy the token and paste it back in the DLH.io connector you’re setting up.

Click on the Save & Test button at the bottom of the DLH connector form and the verification process begins. If the token is good, you’ll be all set. If not, you’ll get a message explaining the issue so you can update and retry creating your connection.

Now you need a source to prove out all can connect and load data.

You’re in luck. We have a sample of our handy Brandy’s Burritos Restaurants dataset from our DLH training course on data warehousing and AI engineering to use as a source.

In DLH click on Data Sync > Sources from the file menu in the header of the site.

In the search area for “Add a New Source Connection” search for the term, Google, and then click on Google Sheets

In the name/alias field enter a name for the connector, for example, Brandys Burritos Data (only certain special characters are allowed, so remove the apostrophe).

In the Target Schema Prefix field, enter a short schema name for the schema that will be created in the MotherDuck database, for example, Brandys.

In the Sheet URL field, enter the following sheet URL,

https://docs.google.com/spreadsheets/d/1-GGT4NTUqtDGMwmwBrlldqy_N0uB2Z-s/edit?gid=2059619545#gid=2059619545

(Note: This URL provides the reference to the spreadsheet with sample data from our data warehouse development and architecture training that use Brandy’s Burritos data. It is publicly available to anyone with the URL link to check it out and use as a reference.)

Click the Yes option for the Sync All Sheets field. By doing this all worksheets in the Brandy’s Burritos spreadsheet will become tables in your schema in your MotherDuck database.

Click the Authorize Your Account button to see the connection made and the connection saved. You may get a reference to accept some criteria from DLH (datalakehouse.io) so simply read and accept those safely. If there are any issues connecting you’ll seed them in the test connection area.

Connect to your MotherDuck Account and Database

Now, let’s find the MotherDuck target destination connector and set it up. From the header menu click on Data Sync and then on the Targets option.

On the right side or if you scroll down you will see a number of connectors. You can search by category by clicking on the tag or simply location MotherDuck and click Add Target under the MotherDuck logo.

In the connection setup form, enter the basic information that you captured towards the start of the document:

Enter a name for this connector that you’ll remember

You can leave the Account URL alone as this is the default and the accepted account link for all MotherDuck accounts

Enter the database name where you want to land the data, for example, my_db (currently the database must already exist to work with DLH)

Leave Auth Type set to the default

Paste in the Service/Account Token your MotherDuck token that you created earlier

Connect the Data to Sync to the MotherDuck Target Database

You need to explicitly tell DLH where to send and synchronize the data from the spreadsheet to your target destination, MotherDuck. In DLH we refer to this as a Sync Bridge.

Click in the header menu area in DLH and from the Data Sync menu option, select Sync Bridges.

Click on the New Sync Bridge button

In the pop-up this is where we’ll select our Google Sheet spreadsheet source and our MotherDuck target as options:

Enter a name for the Sync Bridge Name, for example, Google Sheets to MotherDuck DW

In the Select Connections, use the first dropdown to select the Google Sheets option, and the second dropdown to select the MotherDuck DW

Leave Sync Time Zone at its default which is UTC

For Sync Frequency change it to make it Every 24 hours

Leave all other options at at their default value

Click the Save Sync Bridge button to save your Sync Bridge

You’ll get a pop up message in the upper-right corner giving you the status of our Sync Bridge saving in the background. Once you see a success message you’ll see a new listing in the Sync Bridges page. Hooray!

Now run it by clicking on the Actions dropdown to the right of the window. Select the third option Run Sync Bridge Right Now to start the process. Confirm the option and give it about 1-3 minutes to complete. If all is successful you’ll get an email alert. Refresh or just keep an eye on the Sync Bridge page until it gets marked as Completed. Once completed go check your MotherDuck account and confirm the data flowed all the way through to your database.

If you checked your MotherDuck account and database prior to the sync you’d see something like this,

Afterwards, you’ll see the schema and if you expand the tables from the Brandy’s Burritos spreadsheet,

Finish with visualizing the data in MotherDuck (Extra Points)

For extra points (can we say extra credit anymore in relation to anything having to do with compute operations without being confusing? I don’t know). We’ve built a quick data visualization exercise to take your landed data in MotherDuck and surface it visually using the Observable Framework.

First clone the git repository that we’ve created to go along with our data warehouse training, https://github.com/datalakehouse/dlh-data-warehousing-for-morons-dashboard

In the cloned repository on your local machine, review the header of the script, course/dashboard/app/scripts/refresh-from-motherduck.py, to establish your MotherDuck account and token in the appropriate variables.

If you are an inexperienced python developer, please note that we are using the python virtual environment concept for this exercise. This allows these innocuous commands to not conflict with any other python work you have on your machine.

In the terminal where you will run the dashboard from the cloned repo root, first run the following commands, updating your motherduck token, database and schema names, as appropriate to the steps you did above in your own environment. Please note that you can copy and paste this into your terminal in the root folder of the repo and it should correctly set all variables and launch the dashboard:

# Create a virtual environment

python3 -m venv venv

# Activate it

source venv/bin/activate # macOS / Linux

#venv\Scripts\activate # Windows

# Prerequisites

python -m pip install duckdb pandas pyarrow

# Set your token (or add to a .env file)

export MOTHERDUCK_TOKEN="<your MD token here>"

export MOTHERDUCK_DATABASE="my_db"

export MOTHERDUCK_SCHEMA="burritos_google_sheets"

# Run the refresh script

python course/dashboard/app/scripts/refresh-from-motherduck.py

# run the dashboard

npm run dev --prefix course/dashboard/app

# When done, deactivate

#deactivate

Now run the following command to actually run the dashboard, npm run dev.

It may take around 20 seconds or so to launch the first time.

Behold your dashboard running on your MotherDuck data warehouse.

Be sure to visit DLH.io to learn more about data integration and find more information about training on data warehousing in the future of AI + Data.