Yanni Kouloumbis
Yanni Kouloumbis's Blog


Yanni Kouloumbis's Blog

Firebase Firestore + Metabase + Replit(The Ultimate KPI Tracker)

Firebase Firestore + Metabase + Replit(The Ultimate KPI Tracker)

How to connect Firebase Firestore to Metabase for data tracking & visualization, all seamlessly hosted in a Repl

Yanni Kouloumbis's photo
Yanni Kouloumbis
·Jul 28, 2022·

5 min read

Table of contents

  • Funneling Data from Firebase to Google BigQuery
  • Structuring BigQuery
  • Import Existing Documents into BigQuery
  • Setting up Metabase
  • Configuring Metabase
  • Bonus: Deploy your Dashboard to a Custom Domain

Beautiful dashboards, clean data visualization, and live data streams. All features of the open source data dashboard platform Metabase.

However, Firebase Firestore, a popular database choice, isn't exactly Metabase friendly out of the box. In this article, I'll be explaining how to funnel Firebase Firestore data into Metabase, all while running in the cloud on a Repl.

I'm going to assume you already have Firebase Firestore set up, but if you don't, start here. Make sure to be on the Blaze plan.

Funneling Data from Firebase to Google BigQuery

Because Metabase utilizes SQL queries, we've got to convert our document based database(Firestore) to a SQL-table based one(Google BigQuery). Thankfully, the team @ Firebase has created an extension to automatically track documents within a collection and stream them to a Google BigQuery database. We'll start by setting up this extension.

Navigate to the Google BigQuery Firebase Extension and then click on "Install in Console."

Google BigQuery Firebase Extension

Select the project you'd like to add the extension to. For me, I'll be selecting polyglotprod.

Firebase Projects Console

In the configuration settings of the extension, change the "collection path" & "table ID" to the Firestore collection that you would like to listen to. Be aware that only the documents in the specified collection will be monitored. If you'd like to listen to sub-collections, you'll need to configure this extension again to listen to sub-collections as well. For me, I'll be monitoring the users collection, so that I can see my user metrics in Metabase. Click "Install Extension" once you are done.

Google BigQuery Firebase Extension Configuration

Once the extension is done installing, add a test document in Firebase Firestore so we can check that the extension worked.

Adding a Document to Firebase Firestore

Now let's check our Google BigQuery table to make sure everything worked. In the Firebase Extensions dashboard, click on the "Manage" Button.

Manage Firebase Extension Button

Navigate to the "How this extension works" page and follow steps 4-6, which should confirm that you can see your test document in the Google BigQuery Table.

Screen Shot 2022-07-28 at 1.17.43 AM.png

Success! Successfully seeing Data in Google BigQuery Table Viewer

Our Firebase data is now in Google BigQuery, but unfortunately, all of the data is squished into a single column labeled "Data," which Metabase can't parse.

Structuring BigQuery

With our data now funneled to BigQuery, there are two options to making the data Metabase friendly.

  1. A man-in-the-middle HTTP function that receives the Firestore data and transforms it before it is sent off to BigQuery. However, this approach does not work because BigQuery expects the form of the HTTP request & response to be identical, so changing the SQL column labels is impossible(it took 3 hours of Deno deployments to get that through my head).

  2. Creating a BigQuery Database schema that maps the values of our current database to a new Metabase-friendly database view. That's what we want.

Thankfully, the process isn't too complicated. You'll need to create a json map(or "schema") of your Firestore document properties to SQL columns. Fork the Repl below and follow the instructions in the Readme!

Import Existing Documents into BigQuery

Now that our database schemas are set up correctly, all new & updated documents will be correctly added to the BigQuery database.

However, what if we want to import all the documents that already exist in Firestore? Time for another Repl & handy script. Once again, the instructions are in the Readme!

Setting up Metabase

To host your own Metabase instance, fork the below Repl, and hit "run." It may take up to 3 minutes for Metabase to initialize.
(Pro-tip: If you're on the Replit Hacker/Teams Pro plan, you can increase the ram limits for a smoother experience - checkout the readme!)

The Power of the Cloud ☁️

Metabase Running In the Cloud

Configuring Metabase

Once you've reached step 3 of the Metabase Setup, you'll need to connect your Google BigQuery Database to Metabase. Click on the Google BigQuery Option.

Metabase Setup with Google BigQuery Option

Choose any display name you like, and for the service account JSON file, we'll need to go fishing 🐟 in the Google IAM panel.

Service Account and Display Name Selection Panel

Head back the Google Cloud BigQuery console, and in the search bar, type in Service Accounts and select "Service Accounts." Google Cloud Console Search Bar with Service Accounts typed in

Click the "+Create Service Account" button, and replicate the below details. We'll be giving Metabase "BigQuery Admin" access.

+Create Service Account Page Details +Create Service Account Page Details

Click "Done." You should be deposited back to the Service Accounts Dashboard, where you'll now need to click on the three dots on the very right of the Metabase Service Account.

Three Dots on The Right of the Metabase Service Account

Select "Manage keys" Showing the "Mange keys" button Click "ADD KEY" and "Create new key." Screenshot showing where to click Make sure the JSON option is selected, and click "CREATE." Save the JSON file somewhere safe. Create JSON Service Account Key Now let's head back to Metabase. Click "Select a file" under the "Service account JSON file" section and upload your json file.

Metabase Select A File

Click "Next," and start exploring your data to your heart's desires!

Metabase Dashboard

Bonus: Deploy your Dashboard to a Custom Domain

Click on the custom domain icon(a pencil) to the right of your domain name. Image of Replit Custom Domain

Click on create a domain link, and type in your domain name. Image of Replit Custom Domain Text Input Navigate to the domain management settings of your domain registrar of choice & add the appropriate records. Screen Shot 2022-07-27 at 10.34.04 PM.png

And viola! Your Metabase dashboard is now accessible at a custom domain!

Share this