“Every solution to every problem is simple. It’s the distance between the two where the mystery lies.”
– Derek Landy

I’m back and hacking away at data once again!!

*horrified shrieks, sound of explosions, that one fish from SpongeBob shouts “my legs!”*

But seriously. I’m really excited to be back wrangling data, getting my head around a new yet familiar domain and trying to understand some of the problems that businesses all over the world are facing right now. I was drawn to Keboola before I even got to use it. Its ability to democratize and federate analytics is really clever. Whether it’s about feeding the right data to AI models or improving how PowerBI and Tableau reports are created, it offers a lot.

I joined as a Senior Solution Engineer just a little over 2 weeks ago and have been figuring out how to use the platform since – did you know you can even just create your own free account and try it yourself!? but I also found the academy to be super helpful. It needs a little updating but trust me it’s well in hand. So, what have I actually done?

Keboola

Keboola is a “self-serve data operations and analytics platform”, which is a very grand statement and I see why now – because it does a lot. You can absolutely use it for ETL, you can use it for data science and model building/training/usage, heck you can even build data apps using StreamLit within the platform and publish dashboards out. It’s very cool, and I have no doubt you’ll see a lot more posts from me over the coming months with some of the cool projects we can put together and challenges we can solve.

If you’re wondering who uses this beautiful, powerful platform; I can’t really give you a solid answer because I believe that a lot of people can get value out of it in so many ways – but something that has been relevant to my time at Collibra has been 1) The availability of Data Products and 2) The ability to automate even the most onerous of Business Processes.

But you’re going to have to wait for some of these walkthroughs, I wouldn’t do them justice writing about them here so let’s focus on one very specific task I thought I’d set myself…

“There’s too many Martins”

When I joined Keboola I was told that a lot of people have the same name – I won’t give too many away for privacy reasons but one of the big ones was “Martin”. Everyone kept saying “you’ll need one of the Martins” or “Oh! Another Martin!” I think in jest but still, I couldn’t be sure. But how was I supposed to find out, and more importantly how was I supposed to find out as the number changes? I wasn’t going to go through Slack and count everyone that would takes ages – so instead, I used Keboola.

Step 1 – Getting employee data

This was the step that took the longest – the basis for this count is being able to pull a list of names and my first thought was to use Slack. This actually wasn’t viable because Slack can have all sorts of people (including contractors or external parties) so I didn’t pursue it.

Next was our internal Employee Management System – it has a full REST API specification and as an employee I’m able to request an Access Token that I can use for authentication. BINGO. So how do you pull data from an API in Keboola? There’s a couple ways but I went down the route of a Generic Extractor (docs here for us nerds: https://help.keboola.com/components/extractors/other/generic/#main-header) and actually once I got my head around the OAuth2.0 requirements the JSON specification pretty much wrote itself (and I mean that, I wrote a lot of it but forgot to map it into Keboola so one of the co-founders had to fix it for me – oopsie!)

{
 "parameters": {
  "api": {
   "baseUrl": "https://OURHRSYSTEM.com/api/",
   "authentication": {
    "type": "oauth20",
    "headers": {
     "Authorization": {
      "function": "concat",
      "args": [
       "Bearer ",
       {
        "authorization": "data"
       }
      ]
     }
    }
   }
  },
  "config": {
   "debug": "true",
   "incrementalOutput": false,
   "outputBucket": "BucketName",
   "jobs": [
    {
     "endpoint": "people",
     "dataType": "data",
     "dataField": "data"
    }
   ]
  }
 },
 "authorization": {
  "oauth_api": {
   "credentials": {
    "#data": "MY BEARER TOKEN",
    "appKey": "clientId",
    "#appSecret": "MY APP SECRET"
   }
  }
 }
}

This resulted in the JSON response being pulled into storage in Keboola hosted storage (Snowflake back-ended for me):

Step 2 – Transform the Data

As it was in a form I could now use I needed to transform the data and do the counting and sorting I wanted to do to show the highest frequency name within the company. To do this I used a Workspace – effectively a place to build your transformations and rapidly prototype to your ultimate product with the correct set of data already mapped in for you. This is what I see as being a major MAJOR win –

Your data for this Data Product comes from S3, MySQL, Snowflake and Oracle Database? No problems – they’re all mapped into Keboola and simply made available to the right person (when they’re given access) for them to build on. No waiting for IT to provision access to different systems, no manual wrangling of spreadsheets or CSVs – just a packaged product ready to go.

A simple transformation takes only the data I need from the mapped in “data” table and creates a new table called “KeboolaPopularNames” with the counts:

Then for the transformation you need to make sure you map these tables back out into storage:

Note: You’re seeing a second output table there too because in the original code I’ve also done this for employee Birthdays – but one step at a time! 🙂

Step 3 – Represent the data in a StreamLit Web App

StreamLit is the best way in Keboola to create a straight forward dashboard of insights without writing and refreshing a Tableau or PowerBI Data Set somewhere (and THAT was too far for week 2!). It actually worked out remarkably simple to get my Data App up because this was all the syntax I needed:

import streamlit as st
import pandas as pd

# Load the data
df_popularity = pd.read_csv("/data/in/tables/KeboolaPopularNames.csv")

# Title and header
st.header("Current Popular Names")

# Display the popular names in a grid
st.dataframe(df_popularity)

Easy peasy? No? Well once I built it all into a flow to run every Monday morning:

This was the result:

Anyone within Keboola is now able to see this dashboard and know at a glance exactly what the most frequent/popular names are within the company!

Conclusion

This mini-project started out as a small but exciting test to see if I’d understood how to use the Keboola platform, and surprisingly I came through unscathed! Not only am I pleasantly surprised with how easy and intuitive it was to get to this point, but I’m really pleased that doing this has given me a much better understanding for how everything is working and already the sorts of problems I might be able to help fix!

The real conclusion: I don’t think there’s TOO many Martins…