Blog > Data-engineering & analytics

Powering SQL Draw with Rockset, Retool and dbt

Posted by James Weakley | December 16, 2021

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:

  1. 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:



  2. 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.


Omnata blog

Opinions, technical guides and product news from Omnata - modern data integration.

Blogs from James Weakley, Snowflake Data Super Hero.

Insights about Reverse ETL and operational data integration.

Sign up for genuine updates from time to time - no spam 🙂

Thanks for signing up!

Error sending please try again

Popular Posts

You might also enjoy

Card image cap
Technical guides

The best external database for Salesforce Connect

As data grows many companies will reach the point where Salesforce CRM reaches its data limits. Salesforce Connect with an external database is the best way to offload non-core or historical data to external objects. Connecting directly to your ERP database is a bad idea! So, what database should I choose and why?

Posted by Chris Chandler | December 1, 2021
Card image cap
Product

The need for real-time operational data in CX & support workspaces

Most data that support agents need originates from outside of the CX platform itself. A crucial driver of efficiency is bringing that data to an agent, not sending them on a hunt for information. There are two categories of data that need to be integrated differently to complete the picture. Reverse ETL or data Push serves one category, but the other requires a real-time connection to your data warehouse.

Posted by Chris Chandler | September 1, 2021