Accessing Snowflake using GraphQL

Snowflake is a fully managed service to load, integrate, analyze, and share your data - securely. As a fully managed service, Snowflake is easy to use, yet powerful enough to run your essential workloads with near-unlimited concurrency.

Millions of developers also recognize that GraphQL is a fantastic way to access data, because it provides a single, flexible API from which they can consume data from all their databases and data APIs. So a natural next question is: can we use GraphQL to access data stored in Snowflake?

Yes, with StepZen, you can easily include data from a Snowflake warehouse in your GraphQL API.

Let's get started with an example based on Snowflake's getting started tutorial - Snowflake in 20 minutes that results in a simple employee table created by this DDL:

create or replace table emp_basic (
  first_name string ,
  last_name string ,
  email string ,
  streetaddress string ,
  city string ,
  start_date date
  );

While the sample table contains a limited number of rows, it can be used to demonstrate access from StepZen.

Mapping SQL tables to GraphQL Query fields

StepZen uses a simple declarative approach to define how GraphQL fields are resolved from backend data sources, including databases, REST APIs and GraphQL endpoints.

We want to create two GraphQL fields that return GraphQL Employee objects, accessing data from EMP_BASIC:

  • employee - accesses a single employee given an email address
  • employees - pages through employees with optional filtering

Note If you want to jump right into the example, find it in our GitHub Examples repo.

We use the StepZen custom directive @dbquery to indicate that selection of the field will resolve its data from a database.

Query.employee field

Query.employee is a simple lookup for a single employee from an email address.

extend type Query {
  """
  Looks up employee records by `EMAIL` in the Snowflake table `EMP_BASIC`.

  The specific warehouse, database and schema for `EMP_BASIC` is defined
  by a DSN in `config.yaml` in the `snowy` configuration.
  """
  employee(EMAIL: String!): Employee
    @dbquery(type: "snowflake", table: "EMP_BASIC", configuration: "snowy")
}

That's it! Under the covers, StepZen generates the correct SQL SELECT accessing columns that map to fields in the GraphQL type Employee from the table EMP_BASIC with a WHERE clause of "EMAIL"= ?. The parameter marker represents the value of the EMAIL field argument at runtime.

Query.employees field

Query.employees uses an identical @dbquery but because it follows standard GraphQL pagination and has a filter argument, StepZen automatically provides the rich functionality for pagination and filtering.

extend type Query {
  """
  pages through employee records from `EMP_BASIC` with optional filtering.
  Standard GraphQL pagination is used, thus executing `Query.employees`
  returns a `EmployeeConnection` that contains standard paging information
  and the edges that match the filter with their node values and cursor information.

  The specific warehouse, database and schema for `EMP_BASIC` is defined
  by a DSN in `config.yaml` in the `snowy` configuration.
  """
  employees(
    first: Int! = 5
    after: String = ""
    filter: EmployeeFilter
  ): EmployeeConnection
    @dbquery(type: "snowflake", table: "EMP_BASIC", configuration: "snowy")
}

Now when the employees field is selected in a GraphQL operation, StepZen automatically generates and executes a SQL SELECT statement matching the behavior requested by the arguments, including:

  • predicates based upon the filter, for example

    • field argument: filter: {CITY:{lt:"Calatrava"}} - SQL predicate: CITY < ?
    • field argument: filter: {CITY:{lt:"Calatrava"} LAST_NAME:{lt:"Smith" gt:"Jones"}} - SQL predicate: CITY < ? AND LAST_NAME < ? and LAST_NAME > ?

  • pagination FETCH clause

Example Code: StepZen with Snowflake

A complete example for accessing Snowflake's EMP_BASIC with StepZen with instructions, full GraphQL schema (SDL) and sample queries is available at:

https://github.com/stepzen-dev/examples/tree/main/with-snowflake#readme

Give it a try and use your StepZen GraphQL API to run queries against Snowflake.

Next steps

We hope this tutorial and example are helpful. You can also:

  • Create GraphQL types and Query fields that match existing data stored in your Snowflake warehouses and deploy them to StepZen
  • Link in additional data sources into your StepZen endpoint that interact with your warehouse data such as connecting a REST API that provides value to customer objects.

There's much more to learn about what StepZen can do. We'd love to hear about what you're doing with StepZen - and Snowflake or any datasource! Visit our Discord Community and let us know.