Powering SQL Draw with Rockset, Retool and dbt

If you were one of the 15,000 people who attended Coalesce 2021, you will likely remember SQL Draw, the Slack-based game combining SQL with cartesian geometry, art, creativity and teamwork. If you missed it, you can read more about SQL Draw on the Omnata website.

Below are a few of the artworks that received the most votes:

Behind the scenes, SQL Draw is made up of two parts:

The core game is built as a Slack app with a totally serverless backend architecture. This is the part where users interact with a bot to draw on a canvas:

The leaderboard and artwork browser are built with Retool as the frontend and Rockset as the backend.

This is so that users can see a real-time, ranked list of artworks without having to scroll back through the Slack channel:

In the core game APIs, AWS Lambda handled all the Slack events with artwork metadata, query history, reactions and all other structured data being stored in DynamoDB (yes, despite the game using a Postgres engine, there wasn’t actually a central instance!)

For those unfamiliar, DynamoDB makes database scalability a breeze, but with some major caveats. As a key-value NoSQL database, storing and retrieving individual records are its bread and butter. But querying in the traditional RDBMS sense (like applying filters to different fields, sorting, grouping, joining) is not possible.

We had chosen Retool to build the UI, as we wanted something fast and simple instead of deploying our own web stack.

For the backend, we chose Rockset. Omnata are early believers in Rockset, and partners since early 2021.

Getting started with Rockset was straightforward. Rockset is a real-time analytics database designed for sub-second queries and real-time ingest. It also fits well into our architecture with built-in integrations to DynamoDB and Retool.

The Rockset deployment process was simple:

• Create a DynamoDB integration
• Create a collection (which is like a table) for each of our DynamoDB tables
• Using their dbt adapter, create views which are updated in real-time as new data arrives. For example, raw reactions in Slack needed to be converted into a votes leaderboard
• Deploy query lambdas for Retool to use. Query Lambdas make it easy to create data APIs. Query Lambdas are named parameterized SQL queries stored in Rockset that can be executed from a dedicated REST endpoint, in our case by Retool
• Add a Rockset resource in Retool to provide the connection to Rockset

The initial integration took less than 30 minutes, and then each time we needed a dbt model exposed to Retool, we simply re-ran the dbt project.

With Retool, you can build a fully functional UI with tables, filter controls, etc. within a couple of hours.

So, by leaning on Rockset and Retool, in a matter of hours we’d deployed a fully-fledged, publicly available, real-time web app which supported all sorts of search capabilities. I could type a SQL Draw query in Slack, and see the artwork change in the Retool app in less than a second.

Here’s the final architecture:

I’ve been doing some flavour of systems integration for the past 15 years, and usually I finish a project and think “it shouldn’t have taken that much effort”. So it’s truly incredible to finally see this level of simplicity emerge in the market.

Note: This post was originally posted on the Omnata blog.

James is the CEO and Founder of Omnata, a tech startup building data integration for the modern data stack.