Article

Create a Data App with the new MotherDuck Destination Connector

•

November 12, 2024

•

Create a Data App with the new MotherDuck Destination Connector

We just launched a new destination connector for MotherDuck to the Airbyte Marketplace. This connector is available for Cloud, OSS, and via PyAirbyte. MotherDuck is frequently used to replicate data from transactional databases such as Postgres, MongoDB, and other business systems like HubSpot, via data apps.

Data app architecture via https://motherduck.com/docs/key-tasks/data-apps/

‍

Let’s say we wanted to build an app in Streamlit to build a dashboard atop MotherDuck. To make things easy, we will use the Faker data source in Airbyte. Faker provides sample e-commerce data for a vehicle dealer. The data includes product, purchases, and users. MotherDuck is often used for for enabling OLAP use cases from OLTP data sources, so Faker is well suited for our data app example.

‍

Step 1: Create data pipeline

Start in the Airbyte UX by adding a new source to Faker. Once completed, tap create a connection to add the new MotherDuck connector. Search for “motherduck”, select the connector, and add your Access token. If you don't already have an Airbyte account, you can install on your local machine, or sign up for a free Cloud trial.

If you are unsure where to obtain your access token in MotherDuck, you can grab this via Settings > General, in the MotherDuck UI, then tap Create token if you don't already have one.

Test and save your connector, leaving the default values checked for which data to sync.

Tap next, and change the schedule type to manual. There is no need to use additional system resources for our sample app. We can save that for production. When ready, tap Set up connection, then Sync now to see data. In a moment, your data will be loaded into MotherDuck.

‍

Step 2: Verify data

At this stage your data should be synced with MotherDuck. Log in quickly to check it out. You should see the three streams: products, purchases, and users synced correctly. Great! The new MotherDuck connector makes it incredibly easy to unlock data from sources and load into a modern data warehouse for any use case.

Step : Build the data app

Now your data is in MotherDuck, it’s time to build a quick data app for accessing the data as a dashboard. I’m going to use Streamlit but you could just have easily used Evidence, React, Hex, or whatever frontend you choose.

Start by creating a .env file to store your MotherDuck access token:

MD_ACCESS_TOKEN=[YOUR TOKEN HERE]

Then, add the following code into a file called main.py:

import streamlit as st
import duckdb
import os
import pandas as pd
from dotenv import load_dotenv


# Load the .env file
load_dotenv()

# Connect to MotherDuck
@st.cache_resource
def get_connection():
    token = os.environ.get("MD_ACCESS_TOKEN")
    if token in os.environ:
        print("Missing MotherDuck access token")
    else:
        return duckdb.connect(f"md:?motherduck_token={token}")
   

conn = get_connection()

# Create a simple UI
st.title("MotherDuck Data Explorer")

# Display table names
st.subheader("Available Tables")
tables = conn.execute("SHOW TABLES").fetchdf()
st.dataframe(tables)

# Query input
default_query="select products.make, COUNT(*) from products, purchases where products.id = purchases.product_id GROUP BY products.make ORDER BY products.make"
query = st.text_area("Enter your SQL query:", height=200, value=default_query)


if st.button("Run Query"):
    if query:
        try:
            # Execute query
            df = conn.execute(query).fetchdf()
            
            # Display results
            st.subheader("Results") 
            st.dataframe(df)
            
        except Exception as e:
            st.error(f"Error executing query: {e}")
    else:
        st.warning("Please enter a query")

‍

Looking at the code, we start by creating connection to MotherDuck. You use the duckdb library and simply pass in an md prefix along with your access token for authentication. Then, all we need is a query to access the data we want. I've prefilled a query to get a count of all product sales grouped by vehicle manufacturers, which I'll display in a data table. Of course, you can create dozens of charts using Streamlit's inbuilt options, or other integrated libraries like Plotly.

‍

All that is left is to start your app with:

streamlit run main.py

Everything going to plan, in less than 50 lines of code you now have a data app connected to MotherDuck, and a fully functional data pipeline powered by Airbyte to keep data in sync. Let us know what you think of the new MotherDuck Destination Connector by sharing your experience in our Slack Community. If you don't already have an Airbyte account, you can install on your local machine, or sign up for a free Cloud trial now.

‍

Limitless data movement with free Alpha and Beta connectors

Introducing: our Free Connector Program ->

The data movement infrastructure for the modern data teams.

Try a 14-day free trial