The problem with pipelines
Creating reports and dashboards when your data’s all over the place is hard. It doesn’t help when any changes to your internal tools require going back and forth with your data team, leaving your business team paralyzed. This bottleneck in your data pipeline prevents insight generation and bringing the executive team up to speed. If you’re frustrated with problems like these, then Dropbase can help you.
Dropbase helps you create a no code data pipeline
With Dropbase, you bring in your data from offline files (csv, Excel), REST APIs, web sources, and other databases. You can then apply any processing steps to the data. These could be pre-made processing steps (e.g. sorting the data, see the entire list here), or custom python ones. You only have to make these steps explicitly once and they’re saved forever as one-click pipelines. The data is then exported to a Snowflake database that you’ll have complete access for — to connect with any downstream destination, like a BI tool, that can import data from a Snowflake database.
See how easy it is to go from csv to database in Dropbase here:
Our data setup:
We’ll be using a modified version of a publicly available e-commerce dataset from the UCI Machine Learning Repository to simulate the following data situation.
Imagine you have a Shopify store in addition to other online stores. You get sales data either periodically via email, pull it from an online source, or straight from a csv file on your desktop. And every week, you spend time doing things like:
- Grabbing your updated product list from your Shopify Store.
- Getting, transforming, and uploading a Excel file containing some sales records that you receive in a weekly email from one of your overseas locations.
- Wrangling with a csv file that has some more sales records from a domestic franchisee.
With the help of Dropbase features, we’ll see how to automate each part of the pipeline to create a dashboard that’ll show us key metrics like AOV, Top performing SKUs, and revenue over time.
1/3 Connecting a Shopify store to Dropbase
Shopify is one of many Data Connectors that you can import from in Dropbase. To connect a Shopify source, you should have you store credentials ready to enter. Once you have that info, we can get started on connecting it to Dropbase.
Create a new worksheet, go to Import Data, click on Data Connectors, and select Shopify as a new source. Type in the information, click Import Data, and your data is now loaded. If you only need some of the columns, click on the name of columns you want to remove and select Delete Column.
Click Load to Database. Note the database’s name, we’ll be loading our other data to the same one.
2/3 Creating a Dropzone Request to handle data from external parties
Has the following ever happened to you? An external party (e.g. supplier or one of your international stores) sends data to you over email every week. And every week, you spend time searching for the file, downloading, transforming, and then uploading the file to your database. Dropbase’s Dropzone Request replaces this flow.
A Dropzone Request is a data request to an external party that’s sent right from Dropbase. All the third party has to do is create a Dropbase account and upload the data from their end. Dropzone Request ensures that the data sender does not have to share any user credentials with you, the data receiver. Here’s how to set it up:
Create a pipeline first, and select “Send Dropzone Request” on the Pipelines tab. Enter the email address of the data sender, give the request the name, and click Send Dropzone Request. Once the data sender uploads the file, you’ll see it right in Dropbase. The best part is that you only have to apply any processing steps once, since they are saved as Pipeline steps.
3/3 Making a no code pipeline for a csv file
Working with csv’s is a common task in most e-commerce data pipelines. In Dropbase, all you have to do is drop your file and verify that all the columns are mapped correctly.
If your csv file eeds some processing, then you can use any of the pre-built processing steps on the file and then load it to the database. Say one of your franchisees sends a csv file to you with sales records that they download from their POS system. Then, the next time a file with this schema rolls around, all you have to do is drop it into the pipeline and all saved processing steps are automatically applied before loading to database.
Accessing your database
After clicking load to database, click on the workspace name in the top left and head to Workspace Settings. There, you can click on “Show credentials” and you’ll see all of the information you’d need to connect a downstream destination like Microsoft Power BI.
Dropbase is one of the few tools that gives you access to the final database like this. This gives you access, control, and choice to do whatever you want with your data.
→ Visualizing the results in Power BI
In Power BI, head to File > Get Data > Snowflake and paste in the Server and Warehouse field like this:
Click OK, and then you’re directed to paste in your Username and Password field. This is the Username and Password field from Dropbase, respectively. For security reasons, we haven’t shared that info in this article. Click OK and you’ll see this in Power BI:
As you can see, the 3 tables in Power BI are exactly the 3 tables from Dropbase. franchisee sales is the one we uploaded as a csv, international sales through the Dropzone Request, and ShopifyProductList by connecting Shopify to Dropbase.
Select the tables you want and that’s it! You’re connected. Any time your data changes, just hit Refresh in Power BI and the changes will be reflected immediately. This is an example of a dashboard in Power BI:
Want to try out these features and more in Dropbase? Sign up for a free trial here.
