Creating APIs to retrieve data from a Snowflake database enhances performance and opens up new possibilities

INDUSTRY
Finance

HEADQUARTERS
Houston, TX

SERVICES
Cloud Services
Application Development
Enterprise Architecture

TECHNOLOGIES
Python | Flask
TerraForm
Bitbucket Pipeline
Docker
AWS Cloud Services
Kong API Gateway
Snowflake

 

The Story

Speed is not just about how fast your system runs—ease of use and repeatability are key components to moving faster. That’s why our client, whose business relies on fast data-based decision making, made the wise choice to invest in a cloud-based API strategy.

Our client is a commodities merchant and infrastructure asset investor with a core application that equips their traders and analysts with key insights. We’d recently helped them move this powerful data-modeling application to AWS cloud. In order to forward their cloud adoption initiative, the company wanted to move away from on-prem infrastructure and leverage online data warehouses. The goal was to move internal teams towards using APIs instead of querying databases directly, which would give users greater control and better restrictions. They were also forward-thinking: APIs could be opened up to external users, customers, and partners, creating a thriving community centered on their technology offerings.

As already-valued partners, the client tapped us for our API strategy expertise. We partnered with them to create an API gateway and API design and implementation pattern/structure. Our API design would provide a streamlined way for different users to query the database while opening up possibilities to go public-facing in the future.

Let's work together. Connect with us today.

Challenges

  • Multiple departments were connecting to and using the client’s database in different ways—they needed a way to streamline data access and processing ​
  • The ideal cloud-based API would need to perform better than the current database implementation
  • The client’s existing development team was not familiar with the new technology and had concerns about maintaining it
  • Teams would need to learn new ways of working, switching from direct database queries to API development and usage
  • The internal APIs would need to designed with the future in mind: They wanted to pave the way for exposing public APIs in the future

Solution

  • Migrated a subset of the client’s data to Snowflake and performance-tested different API implementations to evaluate against the current one
  • Created a hybrid solution to assist during the data migration, providing a POC for a cloud-hosted API that gets and persists data to the on-prem Oracle database
  • Developed, deployed, and hosted a Dockerized Python Flask application in AWS that queries and returns data from a Snowflake database; Embraced TerraForm and IaC for building/managing AWS resources
  • Used Kong as an API gateway tool to leverage Active Directory Authentication and Authorization along with rate limiting, caching, and other benefits to support their APIs
  • Brought the client’s development team up to speed on the API architecture and technology and built confidence in maintaining the new solution

Results

  • APIs retrieving data from the new Snowflake database outpaced the performance of queries made to the on-prem Oracle database, with a significantly larger result set
  • The client can confidently develop and deploy APIs in the cloud and manage how they will be accessed and used
  • The client has new tools and infrastructure for creating and managing AWS resources more efficiently
  • Teams have a base pattern that can be built upon to develop both internal and external-facing APIs and scale their solutions into the future

Let's work together. Connect with us today.