How to Build a Data Dashboard Using Airbyte and Streamlit

Hello fellow developers! Welcome to the Programming and Doodles blog, today, we’re going to build a dynamic data dashboard using Python, Airbyte, and Streamlit. That means, if you have a dataset hosted somewhere like Google Sheets, and its values frequently change, this data dashboard will not only show you the visualized data (you can customize these graphs) but also update it automatically as the changes happen on the source file (i.e., Google Sheets in this case) To build that, we’ll use Airbyte, a data integration tool that allows you to easily sync data from various sources (like Google Sheets, databases, APIs, etc.) to your desired destinations (such as data warehouses, like Pinecone in this tutorial) But that leaves us with a big question— why Airbyte? Well, I had the same question when researching its alternatives only to conclude that it’s the simplest EL tool, with great compatibility for Python and web application tools like Streamlit. Some fellow developers complain that it’s slow compared to others but in this case, it was not an issue at all, either due to the size of the data set or using Pinecone as the data destination. All in all, it’s decided that if you possess a sample dataset and you want to build a real-time data dashboard with Python, AirByte + Streamlit is the ultimate duo. You can also view the code on GitHub - Buzzpy / Python Projects / Data Dashboard. The sample data file can be found on - Google Sheets. Prerequisites Python 3.9+ is installed and ready to use. Streamlit: We'll use Streamlit to create the dashboard along with the graphs and charts, and you can install it using pip install streamlit command. Pandas for data manipulation. pip install pandas Pinecone library is used to fetch the data from Pinecone. pip install pinecone Setting up Data Integration on AirByte The purpose of using Airbyte here is to enable automatic synchronization and data integration in our source file and the destination. The purpose of setting up a destination is to fetch synchronized data for the visualization dashboard. And yes, the purpose of the visualization dashboard is to get to know about your data. Now that the process is clear, we can move on to setting up our data integration, or in proper terminology, “connections“. Go to cloud.airbyte.com/signup, and create an account. After the account is set, click on “Create your First Connection“ In the next section, select “Set up a new source," and search and select Google Sheets connector. In the next section, fill the Spreadsheet URL with this sample dataset: https://docs.google.com/spreadsheets/d/1NQtUz4cFTfJssRj87LxkkeqEebvCmXYFwbfSeKwAa3E/edit?usp=sharing. Then click the “Sign in with Google“ button, and sign in to your Google account in which this spreadsheet is held. Once you get the “Authentication succeeded“ message, click the “Set Up Source“ button. Next up, you’ll be asked to set up a destination. While AirByte provides various destination connectors, we’ll be using Pinecone due to its easy setup. Setting up Pinecone Go to https://login.pinecone.io, and create an account. After the account is set up, click on the Databases tab in your dashboard and create an index. In the next section, give your index a name and set up its configuration as in the following image. Then click on “Create Index“ at the bottom of the page and your Pinecone index is ready! Circle back to the Airbyte destination set up and fill out the entries as below: Chunks: chunks refer to groups of data entries (such as 100-500 rows) that are processed and synced together from your source (Google Sheets) to Pinecone during each sync operation. Let’s set it to 1000 for now. Embedding: Embedding refers to a vector representation of data. Keep it as “Fake“ for now. For entries in “Indexing“, use information from your Pinecone index, Once all above-required entries are filled out, click on “Set up destination“. After that, Airbyte will fetch the schema of our data source, and then you’ll have to select the synchronization model. For this application, select the “Replicate Source” option and continue. Finally, you’ll be directed to the final configuration section. Check if everything’s correct, and click on the “Finish & Sync” button at the bottom of the page to sync our dataset. And that’s it! You have officially set up your data integration with Airbyte! Writing the Code Our Google Sheets CSV file is integrated and synced with Pinecone at the moment. What we have to do now is write the script for our data dashboard using Python, Streamlit, and MatplotLib. To start with, let’s import the libraries needed and set up Pinecone authentication. Remember to have your API key ready. import streamlit as st import pandas as pd import time import numpy as np import matplotlib.pyplot as plt from pinecone import Pinecone, ServerlessSpec import os # Set your Pinecone API key in environment variables (ensure it's never hardcoded in the code) os.environ['PINECONE_API_KEY'] = 'gojofjoidgjfd-your-api-key-here' # Initialize Pinecone instance pc = Pinecone(api_key=os.environ['PINECONE_API_KEY']) # Define your index name index_name = "airbyte-int" Thereafter, we can write a function to fetch the data from Pinecone and then convert it to a dataframe using Pandas. Inside the same function, we can pre-process the dataset entries for the sake of data visualization. def fetch_all_data(top_k=1000): """ Fetches top k results from the Pinecone index. And returns a pandas DataFrame. """ try: # Access Pinecone index index = pc.Index(index_name) # Query Pinecone for `top_k` results result = index.query( vector=np.random.random(1536).tolist(), top_k=top_k, include_metadata=True ) # Extract metadata from matches and return as DataFrame data = [match['metadata'] for match in result['matches']] df = pd.DataFrame(data) # Clean and preprocess data for visualization df['SALES'] = pd.to_numeric(df['SALES'], errors='coerce') df['QUANTITYORDERED'] = pd.to_numeric(df['QUANTITYORDERED'], errors='coerce') df['ORDERDATE'] = pd.to_datetime(df['ORDERDATE'], errors='coerce') return df except Exception as e: st.error(f"Error fetching data from Pinecone: {e}") return pd.DataFrame() # Return an empty DataFrame if an error occurs After writing the above function, we can write another function to create graphs for the pre-processed meat. I mean, data. For the visualization, we’ll use Steamlit’s built-in graphing module. def display_graphs(df): # Display raw data table st.write("Fetched Data:") st.dataframe(df) # Total Sales by Product Line if 'PRODUCTLINE' in df.columns and 'SALES' in df.columns: product_sales = df.groupby('PRODUCTLINE')['SALES'].sum().sort_values(ascending=False) st.write("Total Sales by Product Line:") st.bar_chart(product_sales) # Quantity Ordered by State if 'STATE' in df.columns and 'QUANTITYORDERED' in df.columns: state_quantity = df.groupby('STATE')['QUANTITYORDERED'].sum().sort_values(ascending=False) st.write("Quantity Ordered by State:") st.bar_chart(state_quantity) # Sales over Time if 'ORDERDATE' in df.columns and 'SALES' in df.columns: sales_over_time = df.groupby(df['ORDERDATE'].dt.to_period('M'))['SALES'].sum() st.write("Sales Over Time (Monthly):") st.line_chart(sales_over_time) Finally, we can create a Steamlit app UI, using the following code. # Streamlit App UI st.title("Real-Time Sales Data Dashboard") st.write("This dashboard fetches and visualizes real-time sales data from Pinecone.") # Fetch data automatically on load with st.spinner("Fetching data from Pinecone..."): df = fetch_all_data(top_k=1000) # If data is fetched successfully, display graphs if not df.empty: st.success("Data fetched successfully!") display_graphs(df) else: st.error("No data fetched. Ple And voila! You have built your real-time data dashboard with Steamlit. To run the code, type in the following in your terminal. streamlit run sales_dashboard.py You’ll be automatically directed to http://localhost:8501 in your default browser, where you can see the dashboard we built. View the full script on GitHub. Feel free to add more graphs, change your source data, or deploy the Streamlit web app. Wrapping Up In this tutorial, we built a real-time data dashboard using Airbyte and Streamlit, in Python programming language. The tools we used include: Airbyte for data integration, Google Sheets for hosting the source data, Pinecone for destination of source data, and Streamlit for building the web application and visualization. Please note that this isn’t the only way to build a data dashboard; there are numerous tools that you can combine together and end up with something similar, better or worse than the one we built. Find out what suits your needs and your skill set before you decide on one option! The Octavia Octopus design is copyrighted by Airbyte. All rights belong to them. Thanks for reading Programming and Doodles! Subscribe for free to receive new posts and support my work. Hello fellow developers! Welcome to the Programming and Doodles blog, today, we’re going to build a dynamic data dashboard using Python, Airbyte, and Streamlit. That means, if you have a dataset hosted somewhere like Google Sheets, and its values frequently change , this data dashboard will not only show you the visualized data (you can customize these graphs) but also update it automatically as the changes happen on the source file (i.e., Google Sheets in this case) values frequently change To build that, we’ll use Airbyte , a data integration tool that allows you to easily sync data from various sources (like Google Sheets, databases, APIs, etc.) to your desired destinations (such as data warehouses, like Pinecone in this tutorial) Airbyte data integration tool But that leaves us with a big question— why Airbyte? Well, I had the same question when researching its alternatives only to conclude that it’s the simplest EL tool, with great compatibility for Python and web application tools like Streamlit. simplest Some fellow developers complain that it’s slow compared to others but in this case, it was not an issue at all, either due to the size of the data set or using Pinecone as the data destination. All in all, it’s decided that if you possess a sample dataset and you want to build a real-time data dashboard with Python, AirByte + Streamlit is the ultimate duo. You can also view the code on GitHub - Buzzpy / Python Projects / Data Dashboard . The sample data file can be found on - Google Sheets. Buzzpy / Python Projects / Data Dashboard Google Sheets. Prerequisites Python 3.9+ is installed and ready to use. Python 3.9+ is installed and ready to use. Python 3.9+ Streamlit: We'll use Streamlit to create the dashboard along with the graphs and charts, and you can install it using pip install streamlit command. Streamlit : We'll use Streamlit to create the dashboard along with the graphs and charts, and you can install it using pip install streamlit command. Streamlit pip install streamlit Pandas for data manipulation. pip install pandas Pinecone library is used to fetch the data from Pinecone. pip install pinecone Pandas for data manipulation. pip install pandas Pandas for data manipulation. Pandas pip install pandas pip install pandas Pinecone library is used to fetch the data from Pinecone. pip install pinecone Pinecone library is used to fetch the data from Pinecone. pip install pinecone pip install pinecone Setting up Data Integration on AirByte The purpose of using Airbyte here is to enable automatic synchronization and data integration in our source file and the destination . The purpose of setting up a destination is to fetch synchronized data for the visualization dashboard. And yes, the purpose of the visualization dashboard is to get to know about your data. automatic synchronization and data integration destination Now that the process is clear, we can move on to setting up our data integration, or in proper terminology, “connections“. Go to cloud.airbyte.com/signup, and create an account. Go to cloud.airbyte.com/signup , and create an account. cloud.airbyte.com/signup After the account is set, click on “Create your First Connection“ In the next section, select “Set up a new source," and search and select Google Sheets connector. In the next section, fill the Spreadsheet URL with this sample dataset: https://docs.google.com/spreadsheets/d/1NQtUz4cFTfJssRj87LxkkeqEebvCmXYFwbfSeKwAa3E/edit?usp=sharing. Then click the “Sign in with Google“ button, and sign in to your Google account in which this spreadsheet is held. Once you get the “Authentication succeeded“ message, click the “Set Up Source“ button. After the account is set, click on “Create your First Connection“ After the account is set, click on “Create your First Connection“ In the next section, select “Set up a new source," and search and select Google Sheets connector. In the next section, select “Set up a new source," and search and select Google Sheets connector. In the next section, fill the Spreadsheet URL with this sample dataset: https://docs.google.com/spreadsheets/d/1NQtUz4cFTfJssRj87LxkkeqEebvCmXYFwbfSeKwAa3E/edit?usp=sharing. Then click the “Sign in with Google“ button, and sign in to your Google account in which this spreadsheet is held. In the next section, fill the Spreadsheet URL with this sample dataset: https://docs.google.com/spreadsheets/d/1NQtUz4cFTfJssRj87LxkkeqEebvCmXYFwbfSeKwAa3E/edit?usp=sharing . Then click the “Sign in with Google“ button, and sign in to your Google account in which this spreadsheet is held. https://docs.google.com/spreadsheets/d/1NQtUz4cFTfJssRj87LxkkeqEebvCmXYFwbfSeKwAa3E/edit?usp=sharing Once you get the “Authentication succeeded“ message, click the “Set Up Source“ button. Once you get the “Authentication succeeded“ message, click the “Set Up Source“ button. Next up, you’ll be asked to set up a destination. While AirByte provides various destination connectors, we’ll be using Pinecone due to its easy setup. Next up, you’ll be asked to set up a destination. While AirByte provides various destination connectors, we’ll be using Pinecone due to its easy setup. Next up, you’ll be asked to set up a destination. While AirByte provides various destination connectors, we’ll be using Pinecone due to its easy setup. Pinecone Setting up Pinecone Setting up Pinecone Go to https://login.pinecone.io, and create an account. Go to https://login.pinecone.io , and create an account. https://login.pinecone.io After the account is set up, click on the Databases tab in your dashboard and create an index. In the next section, give your index a name and set up its configuration as in the following image. Then click on “Create Index“ at the bottom of the page and your Pinecone index is ready! After the account is set up, click on the Databases tab in your dashboard and create an index. After the account is set up, click on the Databases tab in your dashboard and create an index. In the next section, give your index a name and set up its configuration as in the following image. Then click on “Create Index“ at the bottom of the page and your Pinecone index is ready! In the next section, give your index a name and set up its configuration as in the following image. Then click on “Create Index“ at the bottom of the page and your Pinecone index is ready! Circle back to the Airbyte destination set up and fill out the entries as below: Chunks: chunks refer to groups of data entries (such as 100-500 rows) that are processed and synced together from your source (Google Sheets) to Pinecone during each sync operation. Let’s set it to 1000 for now. Embedding: Embedding refers to a vector representation of data. Keep it as “Fake“ for now. For entries in “Indexing“, use information from your Pinecone index, Once all above-required entries are filled out, click on “Set up destination“. Circle back to the Airbyte destination set up and fill out the entries as below: Chunks: chunks refer to groups of data entries (such as 100-500 rows) that are processed and synced together from your source (Google Sheets) to Pinecone during each sync operation. Let’s set it to 1000 for now. Embedding: Embedding refers to a vector representation of data. Keep it as “Fake“ for now. For entries in “Indexing“, use information from your Pinecone index, Chunks: chunks refer to groups of data entries (such as 100-500 rows) that are processed and synced together from your source (Google Sheets) to Pinecone during each sync operation. Let’s set it to 1000 for now. Embedding: Embedding refers to a vector representation of data. Keep it as “Fake“ for now. For entries in “Indexing“, use information from your Pinecone index, Chunks: chunks refer to groups of data entries (such as 100-500 rows) that are processed and synced together from your source (Google Sheets) to Pinecone during each sync operation. Let’s set it to 1000 for now. Chunks: chunks refer to groups of data entries (such as 100-500 rows) that are processed and synced together from your source (Google Sheets) to Pinecone during each sync operation. Let’s set it to 1000 for now. 1000 Embedding: Embedding refers to a vector representation of data. Keep it as “Fake“ for now. Embedding: Embedding refers to a vector representation of data. Keep it as “Fake“ for now. For entries in “Indexing“, use information from your Pinecone index, For entries in “Indexing“, use information from your Pinecone index, Once all above-required entries are filled out, click on “Set up destination“. After that, Airbyte will fetch the schema of our data source, and then you’ll have to select the synchronization model. For this application, select the “Replicate Source” option and continue. Finally, you’ll be directed to the final configuration section. Check if everything’s correct, and click on the “Finish & Sync” button at the bottom of the page to sync our dataset. After that, Airbyte will fetch the schema of our data source, and then you’ll have to select the synchronization model. For this application, select the “Replicate Source” option and continue. After that, Airbyte will fetch the schema of our data source, and then you’ll have to select the synchronization model. For this application, select the “Replicate Source” option and continue. Finally, you’ll be directed to the final configuration section. Check if everything’s correct, and click on the “Finish & Sync” button at the bottom of the page to sync our dataset. Finally, you’ll be directed to the final configuration section. Check if everything’s correct, and click on the “Finish & Sync” button at the bottom of the page to sync our dataset. And that’s it! You have officially set up your data integration with Airbyte! Writing the Code Our Google Sheets CSV file is integrated and synced with Pinecone at the moment. What we have to do now is write the script for our data dashboard using Python, Streamlit, and MatplotLib. To start with, let’s import the libraries needed and set up Pinecone authentication. Remember to have your API key ready. import streamlit as st import pandas as pd import time import numpy as np import matplotlib.pyplot as plt from pinecone import Pinecone, ServerlessSpec import os # Set your Pinecone API key in environment variables (ensure it's never hardcoded in the code) os.environ['PINECONE_API_KEY'] = 'gojofjoidgjfd-your-api-key-here' # Initialize Pinecone instance pc = Pinecone(api_key=os.environ['PINECONE_API_KEY']) # Define your index name index_name = "airbyte-int" import streamlit as st import pandas as pd import time import numpy as np import matplotlib.pyplot as plt from pinecone import Pinecone, ServerlessSpec import os # Set your Pinecone API key in environment variables (ensure it's never hardcoded in the code) os.environ['PINECONE_API_KEY'] = 'gojofjoidgjfd-your-api-key-here' # Initialize Pinecone instance pc = Pinecone(api_key=os.environ['PINECONE_API_KEY']) # Define your index name index_name = "airbyte-int" Thereafter, we can write a function to fetch the data from Pinecone and then convert it to a dataframe using Pandas. Inside the same function, we can pre-process the dataset entries for the sake of data visualization. def fetch_all_data(top_k=1000): """ Fetches top k results from the Pinecone index. And returns a pandas DataFrame. """ try: # Access Pinecone index index = pc.Index(index_name) # Query Pinecone for `top_k` results result = index.query( vector=np.random.random(1536).tolist(), top_k=top_k, include_metadata=True ) # Extract metadata from matches and return as DataFrame data = [match['metadata'] for match in result['matches']] df = pd.DataFrame(data) # Clean and preprocess data for visualization df['SALES'] = pd.to_numeric(df['SALES'], errors='coerce') df['QUANTITYORDERED'] = pd.to_numeric(df['QUANTITYORDERED'], errors='coerce') df['ORDERDATE'] = pd.to_datetime(df['ORDERDATE'], errors='coerce') return df except Exception as e: st.error(f"Error fetching data from Pinecone: {e}") return pd.DataFrame() # Return an empty DataFrame if an error occurs def fetch_all_data(top_k=1000): """ Fetches top k results from the Pinecone index. And returns a pandas DataFrame. """ try: # Access Pinecone index index = pc.Index(index_name) # Query Pinecone for `top_k` results result = index.query( vector=np.random.random(1536).tolist(), top_k=top_k, include_metadata=True ) # Extract metadata from matches and return as DataFrame data = [match['metadata'] for match in result['matches']] df = pd.DataFrame(data) # Clean and preprocess data for visualization df['SALES'] = pd.to_numeric(df['SALES'], errors='coerce') df['QUANTITYORDERED'] = pd.to_numeric(df['QUANTITYORDERED'], errors='coerce') df['ORDERDATE'] = pd.to_datetime(df['ORDERDATE'], errors='coerce') return df except Exception as e: st.error(f"Error fetching data from Pinecone: {e}") return pd.DataFrame() # Return an empty DataFrame if an error occurs After writing the above function, we can write another function to create graphs for the pre-processed meat. I mean, data. For the visualization, we’ll use Steamlit’s built-in graphing module. def display_graphs(df): # Display raw data table st.write("Fetched Data:") st.dataframe(df) # Total Sales by Product Line if 'PRODUCTLINE' in df.columns and 'SALES' in df.columns: product_sales = df.groupby('PRODUCTLINE')['SALES'].sum().sort_values(ascending=False) st.write("Total Sales by Product Line:") st.bar_chart(product_sales) # Quantity Ordered by State if 'STATE' in df.columns and 'QUANTITYORDERED' in df.columns: state_quantity = df.groupby('STATE')['QUANTITYORDERED'].sum().sort_values(ascending=False) st.write("Quantity Ordered by State:") st.bar_chart(state_quantity) # Sales over Time if 'ORDERDATE' in df.columns and 'SALES' in df.columns: sales_over_time = df.groupby(df['ORDERDATE'].dt.to_period('M'))['SALES'].sum() st.write("Sales Over Time (Monthly):") st.line_chart(sales_over_time) def display_graphs(df): # Display raw data table st.write("Fetched Data:") st.dataframe(df) # Total Sales by Product Line if 'PRODUCTLINE' in df.columns and 'SALES' in df.columns: product_sales = df.groupby('PRODUCTLINE')['SALES'].sum().sort_values(ascending=False) st.write("Total Sales by Product Line:") st.bar_chart(product_sales) # Quantity Ordered by State if 'STATE' in df.columns and 'QUANTITYORDERED' in df.columns: state_quantity = df.groupby('STATE')['QUANTITYORDERED'].sum().sort_values(ascending=False) st.write("Quantity Ordered by State:") st.bar_chart(state_quantity) # Sales over Time if 'ORDERDATE' in df.columns and 'SALES' in df.columns: sales_over_time = df.groupby(df['ORDERDATE'].dt.to_period('M'))['SALES'].sum() st.write("Sales Over Time (Monthly):") st.line_chart(sales_over_time) Finally, we can create a Steamlit app UI, using the following code. # Streamlit App UI st.title("Real-Time Sales Data Dashboard") st.write("This dashboard fetches and visualizes real-time sales data from Pinecone.") # Fetch data automatically on load with st.spinner("Fetching data from Pinecone..."): df = fetch_all_data(top_k=1000) # If data is fetched successfully, display graphs if not df.empty: st.success("Data fetched successfully!") display_graphs(df) else: st.error("No data fetched. Ple # Streamlit App UI st.title("Real-Time Sales Data Dashboard") st.write("This dashboard fetches and visualizes real-time sales data from Pinecone.") # Fetch data automatically on load with st.spinner("Fetching data from Pinecone..."): df = fetch_all_data(top_k=1000) # If data is fetched successfully, display graphs if not df.empty: st.success("Data fetched successfully!") display_graphs(df) else: st.error("No data fetched. Ple And voila! You have built your real-time data dashboard with Steamlit. To run the code, type in the following in your terminal. streamlit run sales_dashboard.py streamlit run sales_dashboard.py You’ll be automatically directed to http://localhost:8501 in your default browser, where you can see the dashboard we built. View the full script on GitHub . View the full script on GitHub Feel free to add more graphs, change your source data, or deploy the Streamlit web app. Wrapping Up In this tutorial, we built a real-time data dashboard using Airbyte and Streamlit, in Python programming language. The tools we used include: Airbyte for data integration, Google Sheets for hosting the source data, Pinecone for destination of source data, and Streamlit for building the web application and visualization. Please note that this isn’t the only way to build a data dashboard; there are numerous tools that you can combine together and end up with something similar, better or worse than the one we built. Find out what suits your needs and your skill set before you decide on one option! The Octavia Octopus design is copyrighted by Airbyte. All rights belong to them. The Octavia Octopus design is copyrighted by Airbyte. All rights belong to them. Thanks for reading Programming and Doodles! Subscribe for free to receive new posts and support my work.