Snowflake.Client

Unofficial .NET client for Snowflake REST API.
Execute SQL queries in Snowflake and get mapped response back.
Read my blog post about it.

Main Features

• User/Password authentication
• Execute SQL queries with parameters
• Map response data to your models

Basic Usage

// Creates new client
var snowflakeClient = new SnowflakeClient("user", "password", "account", "region");

// Executes query and maps response data to "Employee" class
var employees = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

// Executes query and returns raw response from Snowflake (rows, columns and query information)
var queryRawResponse = await snowflakeClient.QueryRawResponseAsync("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

// Executes query and returns value of first cell as string result
string useRoleResult = await snowflakeClient.ExecuteScalarAsync("USE ROLE ACCOUNTADMIN;");

// Executes query and returns affected rows count
int affectedRows = await snowflakeClient.ExecuteAsync("INSERT INTO EMPLOYEES Title VALUES (?);", "Dev");

// Executes query with parameters (several syntax options):
var employees1 = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM EMPLOYEES WHERE TITLE = ?", "Programmer");
var employees2 = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM EMPLOYEES WHERE ID IN (?,?)", new int[] { 1, 2 });
var employees3 = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM EMPLOYEES WHERE TITLE = :Title", new Employee() { Title = "Programmer" });
var employees4 = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM EMPLOYEES WHERE TITLE = :Title", new { Title = "Programmer" });

Comparison with Snowflake.Data

Official Snowflake.Data connector implements ADO.NET interfaces (IDbConnection, IDataReader etc), so you have to work with it as with usual database on a low level (however under the hood it actually uses Snowflake REST API). In contrast this library is designed as REST API client (or wrapper) with straightforward and clean API. Read more about it.

Improvements in Snowflake.Client vs Snowflake.Data:

• Performance: Re-uses Snowflake session, i.e. ~3x less roundtrips to SF
• Performance: Doesn't have additional intermediate mapping from SF to DB types
• Better API: Clean and simple API vs verbose ADO.NET
• Less third party dependencies: 0 vs 4

Added features in Snowflake.Client vs Snowflake.Data:

• Map response data to entities
• Supports describeOnly flag
• Has option to return raw data from Snowflake response (including QueryID and more)
• Exposes Snowflake session info
• New SQL parameter binding API with a few options (inspired by Dapper)

Missing features in Snowflake.Client vs Snowflake.Data:

• Chunks downloader (to download massive response data)
• OKTA Authentication

Mapping basics

Use QueryAsync<T> method to get response data automatically mapped to your model (T):

// Executes query and maps response data to "Employee" class
IEnumerable<Empolyee> employees = await snowflakeClient.QueryAsync<Employee>("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

// Your model
public class Employee
{ 
    public int Id { get; set; }
    public float? Rating { get; set; }
    public bool? IsFired { get; set; }
    public string FirstName { get; set; }
    public string[] ContactLinks { get; set; } // supports arrays and lists
    public EmplyeeInfo Info { get; set; } // supports custom json ojects ("object" and "variant")
    public DateTimeOffset HiredAt { get; set; } // DateTimeOffset for "timestamp_ltz" and "timestamp_tz"
    public DateTime FiredAt { get; set; } // DateTime for "date", "time" and "timestamp_ntz"
    public byte[] Image { get; set; } // bytes array/list for "binary"
}

Internally it uses System.Text.Json to deserialize Snowflake data to your model. It uses default deserialize behavior, except PropertyNameCaseInsensitive is set to true.
You can override this behavior by providing custom JsonSerializerOptions. You can pass it in SnowflakeClient constructor or you can set it directly via SnowflakeDataMapper.SetJsonMapperOptions(jsonSerializerOptions).

If you want you can use SnowflakeDataMapper.MapTo<T> to map Snowflake data response manually:

// Executes query and returns raw response from Snowflake (rows, columns and query information)
var queryDataResponse = await snowflakeClient.QueryRawResponseAsync("SELECT * FROM MASTER.PUBLIC.EMPLOYEES;");

// Maps Snowflake rows and columns to your model (via System.Text.Json)
var employees = SnowflakeDataMapper.MapTo<Employee>(queryDataResponse.Columns, queryDataResponse.Rows);

Installation

Add nuget package Snowflake.Client to your project:

PM> Install-Package Snowflake.Client

Road Map

• [Done] Async API
• [Done] Auto-renew session
• [Done] Query cancellation
• [In Progress] Unit tests
• [In Progress] Integration tests
• Better mapper documentation
• Chunks downloader (for big amount of data)
• ? Get/Put files to Stage