DEV Community: Nico Acosta

How to reduce Snowflake costs: A five-point checklist

Nico Acosta — Tue, 23 May 2023 02:58:11 +0000

Facing high Snowflake costs?

Check out our practitioner's five-point checklist to reduce Snowflake costs and optimize your data warehouse usage.

Snowflake is an incredibly powerful and scalable data warehouse, but without proper management, your costs can skyrocket and get out of control. In this checklist, we will explore five key strategies for optimizing Snowflake costs, ensuring that your data warehouse remains efficient and cost-effective.

1. Set the correct warehouse auto-suspend

What is the Auto-Suspend feature?

Auto-Suspend is a Snowflake feature that allows your warehouse to automatically suspend itself after a specified period of inactivity. This helps to reduce costs by preventing unnecessary credit usage when the warehouse is not in use.

The default value and why it's inefficient

The default auto-suspend value is 10 minutes, but this is often too long for many workloads. For example, if a query lasts 20 seconds, the warehouse will still be charged for 10 minutes of usage, leading to unnecessary costs.

Example query for querying and modifying auto-suspend

To query the auto-suspend settings for all warehouses, use the following query:

SHOW WAREHOUSES;

The auto_suspend column specifies how long a running warehouse can remain inactive, in seconds, before automatically suspending and stopping credit usage. A null value means that the warehouse will never automatically suspend. You’ll want to avoid having any warehouse with a null auto-suspend. Typically, 60 , meaning an auto-suspend of a minute is a good place to start.

To modify the auto-suspend setting for a specific warehouse to 1 minute (60 seconds), you can run the following:

ALTER WAREHOUSE "<warehouse_name>"
  SET AUTO_SUSPEND = 60;

2. Right-size your warehouses: monitor remote disk spillage

What is remote disk spillage, and why it causes costs to increase

Remote disk spillage occurs when a virtual warehouse runs out of memory and begins spilling intermediate results to remote storage. This can lead to increased query times and higher Snowflake costs due to increased I/O operations and additional credit usage.

Identify the warehouses that have remote disk spillable

To monitor remote disk spillage, you can use Snowflake's QUERY_HISTORY function. Here's an example query to identify the warehouses with remote disk spillage over the last 30 days:

SELECT  
    WAREHOUSE_NAME,
    SUM(BYTES_SPILLED_TO_REMOTE_STORAGE) as TOTAL_BYTES_SPILLED_TO_REMOTE_STORAGE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY WAREHOUSE_NAME;

Fixing remote disk spillage by increasing warehouse size

To fix remote disk spillage, you can increase the size of your warehouse. By doing so, you will allocate more memory to the warehouse and reduce the need for remote storage, ultimately lowering costs. With a larger warehouse, your queries will complete more than twice as fast. Make sure to monitor and adjust warehouse sizes as needed to prevent spillage.

3. Use a serving layer for high-concurrency sub-second queries

Sometimes, you require high-concurrency sub-second queries. These are critical for customer-facing dashboards, data APIs, and usage metering, where fast response times are essential.

How a serving layer on top of Snowflake works

A serving layer on top of Snowflake syncs data to high-speed storage, optimizes it, and serves it via an API without consuming Snowflake credits. This reduces Snowflake costs while providing high-performance analytics to your end-user applications.

It is a better alternative to traditional embedded analytics because it reduces the load on Snowflake. Furthermore, by providing an API, it offers greater flexibility and control.

Propel's data API platform provides engineering teams with a unified platform for delivering high-performance customer-facing analytics. Its serving layer solution offers an analytics backend with a GraphQL API and React UI component library, requiring no infrastructure scaling or management. To learn more about Propel, read the docs.

4. Identify and fix inefficient queries

Inefficient queries are the source of a lot of waste. In this section, we show you how to identify them and share some tips to optimize them.

How to identify inefficient queries

Inefficient queries can significantly contribute to higher Snowflake costs. To identify them, you can monitor the QUERY_HISTORY view and look for queries with long execution times or high resource consumption.

You can use the following query to identify your slowest queries in the last 30 days in Snowflake:

SELECT  
    QUERY_TEXT,       
    SUM(TOTAL_ELAPSED_TIME) AS TOTAL_ELAPSED_TIME,
    SUM(BYTES_SCANNED) AS BYTES_SCANNED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY QUERY_TEXT
ORDER BY TOTAL_ELAPSED_TIME DESC

Tips to improve query efficiency

To make queries more efficient, consider the following tips:

Use selective filtering and avoid SELECT *.
Optimize JOIN operations.
Make sure tables have an ORDER BY.
Limit the number of rows returned using LIMIT.
Use materialized views for pre-computed results.
Leverage clustering keys to improve query performance.

5. Set up Resource Monitors

What are Resource Monitors?

Resource Monitors in Snowflake allow you to track and manage your credit usage, helping to prevent unexpected costs. You can set thresholds that, when exceeded, trigger actions such as suspending a warehouse or sending notifications.

Example: Creating a Resource Monitor

To create a Resource Monitor that suspends a warehouse when credit usage exceeds a specified amount, you can use the following SQL command:

CREATE RESOURCE MONITOR "monitor_name"
  WITH CREDIT_QUOTA = <quota>
  TRIGGERS
    ON 100 PERCENT DO SUSPEND;

Conclusion

Optimizing Snowflake costs is crucial for maintaining an efficient and cost-effective data warehouse. By following this five-point checklist, you can optimize warehouse usage by preventing remote disk spillage, adjusting auto-suspend settings, leveraging a serving layer for high-concurrency queries, and monitoring resource consumption. So, start implementing these strategies today and make the most of your Snowflake investment.

Introducing Usage Metering for ChatGPT-Powered LLM Apps

Nico Acosta — Mon, 22 May 2023 07:00:00 +0000

Add usage metering to your ChatGPT-powered products with five lines of code. Quickly implement functionality to enforce limits, detect abuse, act on user feedback, and visualize usage insights and get to market faster.

Today, we are excited to announce the preview of Propel's usage metering solution for ChatGPT-powered applications. It enables developers to streamline building and operating usage metering functionality, such as token usage tracking, latency monitoring, and user feedback collection, through a highly scalable analytics backend and a blazing-fast data API.

With Propel, developers can quickly implement functionality to enforce limits, detect abuse, act on user feedback, and visualize usage insights in their user-facing product or internal tools. The preview supports OpenAI's ChatGPT API (GPT-3.5 and GPT-4). We plan to add support for more APIs and custom-built LLMs based on customer demand in the future. To get access, join our waitlist.

Large Language Models (LLMs), such as ChatGPT, have been the biggest advance in computing since the graphical user interface. They fundamentally change how we interact with computers. LLM apps are being built into every consumer or B2B product experience. However, taking ChatGPT-powered products to market is not an easy task.

The Problem: Generative AI Apps Need to Track Usage

Each ChatGPT API call consumes tokens that cost money. Therefore, usage needs to be tracked and communicated to end users. It must be designed and built into the product experience. With all the excitement around LLMs, prompt design and tuning, preventing prompt injections, and model benchmarking, tracking usage is often an afterthought. However, it's a critical aspect that can cause delays in bringing generative AI products to market.

There are different ways to productize GPT-powered features. Some products will include GPT-powered features as part of their current offerings. In such cases, they need to keep track of usage and enforce limits to avoid runaway costs. Other products will pass the cost onto the end user. In such cases, they need to keep track of usage to be able to charge at the end of the month. In either case, usage metrics must be available via APIs for developers to surface to end-users, and to integrate into the rest of their application logic that enforces limits, bills, and sends user notifications.

Why Is It Hard to Track Usage?

Tracking usage is harder than it seems. Why not just store it in the database and read it from there? There are three main challenges:

Aggregates and query latency: Usage queries are analytical in nature, meaning they do not retrieve individual records from a database. Instead, they retrieve a set of records for a given time range based on a given aggregation. For example, your customers may want to know their token consumption by day, broken down by user, for the last 30 days. This query requires a SUM aggregation over the last 30 days, grouped by user, and filtered by customer ID. As usage data grows, your transactional database will become increasingly slower at serving these queries, which can slow down your entire application.
Ever-evolving metadata: Your product is constantly evolving, with new GPT-powered features being shipped to different parts of the product. This means that the metadata contextualizing usage data is constantly changing. The nature of this data is unstructured, so you will need the ability quickly add new metadata and query your usage data with the ability to slice and dice by the metadata you provided.
Custom metrics: Every product is unique, and how you account for usage and productize your GPT features will depend on your product, customer base, and the experience you want to provide. Therefore, you need the ability to define custom metrics to track your primary usage unit. Some products track usage based on requests, while others track usage on input words or input tokens. Whatever method you choose, you need a centralized metric definition that you define once and use everywhere.

Propel’s Usage Metering for LLM Apps

Propel's LLM usage metering solution equips generative AI developers with a powerful analytics backend, low-latency data API, and a React component library to easily capture usage data, model it into metrics, and expose usage data via a fast and responsive GraphQL API. This API can be used to drive product behaviors, such as enforcing limits or usage accounting or building in-product usage visualizations like reports and dashboards.

Key Features

The solution consists of the following features:

Data collection endpoint

The data collection endpoint enables developers to send the response of the OpenAI API directly to Propel with a simple HTTP POST.

const openAICompletion = await openai.createChatCompletion({
  model: "gpt-4",
  messages: [{role: "user", content: "Hello world"}],
});

// Log response with Propel
const logResponse = await fetch("PROPEL_URL", {
    method: "POST", 
    headers: { "Authorization": "Bearer YOUR_TOKEN"},
    body: JSON.stringify(openAICompletion),
});

Define custom metrics

Once the data is in Propel, developers can define custom metrics that give meaning to the data. The example below illustrates how to configure a TokenUsage metric.

Query data via a GraphQL API

Once the Metric is defined, developers can query the data with a simple GraphQL API request. The example below queries the token usage metric when the gpt-4 was used over the last 30 days.

query TokenUsage {
  metricByName (uniqueName: "TokenUsage") {
    counter ({
      timeRange: { relative: "LAST_N_DAYS"  n:"30"}
      filters: [{
        column: "model"
        operator: EQUALS
        value: "gpt-4"      
      }]
    }) {
      values
      labels
    }
  }
}

Slice and dice on your own metadata

If you added custom metadata to the payload, you can slice and dice the metric values by your own metadata. The example below adds a filter based on a customerId value provided.

query TokenUsage {
  sales: metricByName (uniqueName: "tokenUsage") {
    timeSeries ({
      timeRange: { relative: "LAST_N_DAYS"  n:"30"}
      granularity: "DAY"
      filters: [{
        column: "metadata.customerId"
        operator: EQUALS
        value: "ac5bc654-6449-4003-ac72-6f07e761b152"      
      }]
    }) {
      values
    }
  }
}

Build user-facing usage dashboards

By querying the API and using our UI components (optional), you can quickly build dashboards, log interfaces, and in-product metrics.

Integrate’s with the observability tools you already use

Lastly, you can easily integrate with your existing observability and internal tools stack, such as Grafana and Retool to manage and monitor your ChatGPT-powered apps. No need to reinvent the wheel.

Final thoughts

With Propel's usage metering solution, developers can focus on what they do best - building great ChatGPT-powered products - without having to worry about the complexities of usage tracking. By streamlining the development and operation of usage metering functionality, Propel is helping to bring ChatGPT-powered products to market faster and more efficiently than ever before.

Join our waitlist now and start getting the benefits of Propel's usage metering solution for ChatGPT-powered applications.

How to build a Snowflake API?

Nico Acosta — Tue, 17 Jan 2023 16:42:34 +0000

In this post, you’ll see exactly what it takes to create and query an API on top of your Snowflake data warehouse using Propel’s blazing-fast Serverless Analytics API Platform. In five easy steps, you’ll connect to Snowflake, define a Metric, and query it with our GraphQL API.

Are you looking for a way to build a fast, secure API on top of your Snowflake data warehouse? Propel's Serverless Analytics API Platform offers a fast and cost-effective way to create an API on top of your Snowflake data that is secure, performant, and low latency. In this post, we will walk you through the five easy steps to connecting your Snowflake Data Source, syncing your tables, defining Metrics, and querying your API with JavaScript, Python, Java, Go, Ruby, and C#/.NET. Let's get started!

Why do you need an API on top of Snowflake?

Snowflake typically has the most complete data organizations have. It centralizes data from all the different data silos, whether internal databases or SaaS applications. It is also the right platform to clean and transform your data, making it optimal for visualization and consumption.

When using the data internally for employees, you can either query the data directly with SQL or through a BI tool like Looker or Tableau; therefore, you don’t typically need an API.

You need an API on top of Snowflake when you are building customer-facing web or mobile applications that include:

User-facing dashboards
Product usage and metering
Analytics APIs
Email Reporting
Customer data sharing

These use cases have unique requirements and require a secure and performant data serving layer with low latency and high concurrency to provide a seamless user experience.

What are the requirements for an API on top of Snowflake?

When looking to build an API on top of Snowflake to power data applications and features powered by large-scale analytical data, you should consider the following requirements:

⚡️ Low latency → Customers expect fast, snappy product experiences. While internal employees may be okay waiting 45 seconds or even minutes for a query to run, this latency is a no-go for customer-facing products where the data analysis is part of the core product experience
🌊 High concurrency → Products need to support thousands or millions of users. Because data products ultimately serve customers rather than employees, they must support a dramatically higher number of concurrent requests than what internal data tools are designed to handle. And they must do so seamlessly and cost-effectively.
💰 Cost-effectiveness → Data has a fundamental tradeoff between flexibility, cost, and performance. The API should make it easy for users to understand and then make this tradeoff for different use cases.
🔐 Multi-tenant security layer → Each end-user can see only their own data. In SaaS and consumer products, end-users access their own data from web or mobile applications. This requires a multi-tenant security layer for business-to-business or consumer environments, not just employee-centric role-based access control.

Querying the warehouse directly from customer-facing applications can be slow, costs can get out of control, and it still requires substantial backend service logic to serve this data securely to the frontend.

Does Snowflake have an API?

Yes, Snowflake offers a pass-through SQL API. It is a REST API that takes SQL statements and executes them against the data warehouse. It is useful if you want to query SQL over REST, but it does not provide any additional abstraction, optimization, or security features to power secure, performant, low-latency customer-facing applications.

How do I build an API on top of Snowflake?

To build a performant Snowflake API to power your customer-facing web and mobile apps, you can use Propel, a serverless analytics API platform built on top of Snowflake.

Propel provides the cloud infrastructure, scalability, and security required to power data applications at a massive scale while giving you the flexibility to define the metrics you want to query. You can build your first Snowflake API in minutes by following these five easy steps:

1. Connect your Snowflake Data Source

To set up a Snowflake Data Source for Propel, follow our How to set up a Snowflake Data Source guide. If you need a Propel account, book a demo, and we'll onboard you as soon as possible. Then, enter your Snowflake connection details in Propel's Console to create the Data Source.

Propel will connect to Snowflake and run a series of checks to ensure the connection has the correct permissions.

An animated screen capture of the Propel Console, showing how to create a Snowflake Data Source and the numerous checks that confirm the connection is working.

2. Sync a table from Snowflake

Once connected, you can see your Snowflake tables in Propel. You can create a Data Pool for the table you want to serve data from. The Data Pool is a synced cache of your table that Propel optimizes for serving data. Propel’s built-in syncing eliminates the need for data pipelines, ETLs, or data engineering.

When creating the Data Pool, you must specify the table, its primary timestamp column (typically when the record was created), and a tenant ID if tenant-level access control is needed for your application.

An animated screen capture of the Propel Console, showing how to create a Data Pool from a table within a Data Source.

3. Define Metrics

With your data in a Propel Data Pool, you can define the Metrics you want to use in your web or mobile application. Metrics are the analytics measures you want to track, and they can be of different types: SUM, COUNT, COUNT_DISTINCT, MIN, MAX, and AVERAGE.

Once you configure your Metric settings in Propel, you can select the columns you want to expose as dimensions. Dimensions are different ways to filter Metric data to uncover insights. When defining a customer-facing Metric, you can control which columns are exposed to your customer via Dimensions.

4. Query your Snowflake API

Propel gives you a GraphQL API on top of Snowflake that is always highly available and performant in just a few easy steps. Once you have your Metric defined, you can try Propel’s GraphQL Explorer to query the Metric data in the format you are going to use: a time series, a counter, or a leaderboard.

At the top-right of the GraphQL Explorer, you'll see the quick response times Propel's high-performance GraphQL API provides. In this example, querying hundreds of thousands of records takes about 500 milliseconds (end to end).

An animated screen capture of Propel’s GraphQL explorer, showing how to query a Metric using the GraphQL API with various time granularities and filters.

5. Build your analytics product using Propel’s GraphQL API for Snowflake

The Metric you defined previously is now available via the Snowflake GraphQL API. Using the technology stack of your choice, you can build in-product analytics into your app. You didn’t have to do any data engineering beyond loading your data to Snowflake.

API Authentication

Once you have your Snowflake API up and running with Propel, you’ll need to authenticate your requests. Propel uses OAuth 2.0 client credentials flow to authenticate your requests to the API securely. This flow requires you to provide a client ID and a client secret to authenticate your requests. These credentials are generated in the Propel Console when you create an Application. To learn more, read our GraphQL Authentication guide.

The examples below show how to query your new Snowflake API using different programming languages, frameworks, and tools.

Example 1: The GraphQL query to your Snowflake API

An example GraphQL query on top of Snowflake. The first code block contains the GraphQL query and the second code block contains the JSON variables to include. When issuing the GraphQL request, ensure you replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID and include your access token in the “Authorization” header as a bearer token.

query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
  metric(id: $metricId) {
    timeSeries(input: $input) {
      labels
      values
    }
  }
}

{
  "metricId": "METXXXXXXXXXXXXXXXXXXXXXXXXXX",
  "input": {
    "granularity": "DAY",
    "timeRange": {
      "relative": "LAST_N_DAYS",
      "n": 30
    },
    "filters": [{
      "column": "PROMO",
      "operator": "EQUALS"
      "value": "FALSE"
    }]
  }
}

Example 2: Query your Snowflake API with curl

An example of a Snowflake API request using the command-line tool curl. Ensure you replace <ACCESS TOKEN> with your access token and METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

curl https://api.us-east-2.propeldata.com/graphql \
  -H "Authorization: Bearer <ACCESS TOKEN>" \
  -H "Content-Type: application/json" \
  -d '{
  "query": "query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) { metric(id: $metricId) { timeSeries(input: $input) { labels values } } }",
  "variables": {
    "metricId": "METXXXXXXXXXXXXXXXXXXXXXXXXXX",
    "input": {
      "granularity": "DAY",
      "timeRange": {
        "relative": "LAST_N_DAYS",
        "n": 30
      },
      "filters": [{
        "column": "PROMO",
        "operator": "EQUALS",
        "value": "FALSE"
      }]
    }
  }
}'

Example 3: Query your Snowflake API with JavaScript (Node.js)

An example of a Snowflake API request using JavaScript. This example uses the graphql-request library to issue the actual GraphQL request. Install the dependency:

npm i graphql-request

Then, copy the following sample to a file, index.js, replace <ACCESS TOKEN> with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

const { request, gql } = require('graphql-request')

const endpoint = 'https://api.us-east-2.propeldata.com/graphql'
const accessToken = '<ACCESS TOKEN>'

const query = gql`
  query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
    metric(id: $metricId) {
      timeSeries(input: $input) {
        labels
        values
      }
    }
  }
`

const variables = {
  metricId: 'METXXXXXXXXXXXXXXXXXXXXXXXXXX',
  input: {
    granularity: 'DAY',
    timeRange: {
      relative: 'LAST_N_DAYS',
      n: 30
    },
    filters: [{
      column: 'PROMO',
      operator: 'EQUALS',
      value: 'FALSE'
    }],
  }
}

request({
  url: endpoint,
  document: query,
  variables,
  headers: {
    Authorization: `Bearer ${accessToken}`
  }
}).then(data => {
  console.log(JSON.stringify(data))
}, error => {
  console.error(error)
})

Finally, run the example:

node index.js

Example 4: Query your Snowflake API with React

An example of a Snowflake API request using JavaScript, React, and Chart.js. This example uses react-chartjs-2, a wrapper for Chart.js. Install the dependencies:

npm i react-chartjs-2 chart.js

Then, copy the following sample to a file, TimeSeries.jsx, replace <ACCESS TOKEN> with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

import React, { useEffect, useState } from 'react'
import {
  Chart as ChartJS,
  CategoryScale,
  LinearScale,
  PointElement,
  LineElement,
  Title,
  Tooltip,
  Legend
} from 'chart.js'
import { Line } from 'react-chartjs-2'

ChartJS.register(
  CategoryScale,
  LinearScale,
  PointElement,
  LineElement,
  Title,
  Tooltip,
  Legend
)

const endpoint = 'https://api.us-east-2.propeldata.com/graphql'
const accessToken = '<ACCESS TOKEN>'

const query = `
  query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
    metric(id: $metricId) {
      timeSeries(input: $input) {
        labels
        values
      }
    }
  }
`

const variables = {
  metricId: 'METXXXXXXXXXXXXXXXXXXXXXXXXXX',
  input: {
    granularity: 'DAY',
    timeRange: {
    relative: 'LAST_N_DAYS',
      n: 30
    },
    filters: [{
      column: 'PROMO',
      operator: 'EQUALS',
      value: 'FALSE'
    }]
  }
}

const options = {
  method: 'POST',
  headers: {
    Authorization: `Bearer ${accessToken}`,
    'Content-Type': 'application/json'
  },
  body: JSON.stringify({
    query,
    variables
  })
}

export function TimeSeries () {
  const [error, setError] = useState(undefined)
  const [timeSeries, setTimeSeries] = useState(undefined)

  useEffect(() => {
    async function query () {
      const response = await fetch(endpoint, options)
      const { data } = await response.json()
      setTimeSeries(data?.metric?.timeSeries ?? undefined)
    }

    setError(undefined)
    query().catch(setError)
  }, [])

  if (error) return 'An error has occurred: ' + error.message
  if (!timeSeries) return 'Loading...'

  return (
    <Line
      data={{
        labels: timeSeries.labels,
        datasets: [
          {
            label: 'Metric Values',
            data: timeSeries.values
          }
        ]
      }}
    />
  )
}

Finally, import your component and mount it in your React app:

import React from 'react'
import { createRoot } from 'react-dom/client'

import { TimeSeries } from './TimeSeries.jsx'

const rootElement = document.getElementById('root')
createRoot(rootElement).render(<App />)

Example 5: Query your Snowflake API with Python

An example of a Snowflake API request using Python. This example uses Python’s built-in urllib.request library for issuing the GraphQL request, so it doesn’t require additional dependencies; however, in production, you could use a library like gql for stronger typing.

Copy the following sample to a file, main.py, replace <ACCESS TOKEN> with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

import json
import urllib.request

endpoint = 'https://api.us-east-2.propeldata.com/graphql'
access_token = '<ACCESS TOKEN>'

query = """
  query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
    metric(id: $metricId) {
      timeSeries(input: $input) {
        labels
        values
      }
    }
  }
"""

variables = {
  'metricId': 'METXXXXXXXXXXXXXXXXXXXXXXXXXX',
  'input': {
    'granularity': 'DAY',
    'timeRange': {
      'relative': 'LAST_N_DAYS',
      'n': 30
    },
    'filters': [{
      'column': 'PROMO',
      'operator': 'EQUALS',
      'value': 'FALSE'
    }]
  }
}

headers = {
  'Content-Type': 'application/json',
  'Authorization': 'Bearer {}'.format(access_token)
}

body = json.dumps({
  'query': query,
  'variables': variables
}).encode('utf-8')

request = urllib.request.Request(endpoint, headers=headers, data=body)
response = urllib.request.urlopen(request)
result = response.read().decode('utf-8')
print(result)

Finally, run the example:

python main.py

Example 6: Query your Snowflake API with Java

An example of a Snowflake API request using Java. This example uses Java’s built-in HttpClient and constructs JSON manually, so it doesn’t require additional dependencies; however, in production, you should use a library like Jackson for constructing JSON. Additionally, for stronger typing, you could use Apollo’s Kotlin-based GraphQL client.

Copy the following sample to a file, Main.java, replace <ACCESS TOKEN> with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

import java.io.IOException;
import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;

public class Main {
    public static void main(String[] args) throws IOException, InterruptedException {
        final var endpoint = "https://api.us-east-2.propeldata.com/graphql";
        final var accessToken = "<ACCESS TOKEN>";

        final var query = """
                query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
                  metric(id: $metricId) {
                    timeSeries(input: $input) {
                      labels
                      values
                    }
                  }
                }
                """;

        final var variables = """
                {
                  "metricId": "METXXXXXXXXXXXXXXXXXXXXXXXXXX",
                  "input": {
                    "granularity": "DAY",
                    "timeRange": {
                      "relative": "LAST_N_DAYS",
                      "n": 30
                    },
                    "filters": [{
                      "column": "PROMO",
                      "operator": "EQUALS",
                      "value": "FALSE"
                    }]
                  }
                }
                """;

        final var body = "{\"query\":\"" + query.replaceAll("\n", "\\\\n") + "\",\"variables\":" + variables + "}";

        final var client = HttpClient.newHttpClient();
        final var request = HttpRequest.newBuilder(URI.create(endpoint))
                .header("Content-Type", "application/json")
                .header("Authorization", "Bearer " + accessToken)
                .POST(HttpRequest.BodyPublishers.ofString(body))
                .build();

        final var response = client.send(request, HttpResponse.BodyHandlers.ofString());

        System.out.println(response.body());
    }
}

Finally, run the example, either from your IDE or from the command-line with java.

Example 7: Query your Snowflake API with Go

An example of a Snowflake API request using Go. This example uses the Go standard library, so it doesn’t require additional dependencies; however, in production, you could use a library like shurcooL/graphql or Khan/genqlient for stronger typing.

Copy the following sample to a file, main.go, replace <ACCESS TOKEN> with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

package main

import (
    "bytes"
    "encoding/json"
    "fmt"
    "io/ioutil"
    "net/http"
)

type timeRangeInput struct {
    Relative string `json:"relative"`
    N        int    `json:"n"`
}

type filterInput struct {
    Column   string `json:"column"`
    Operator string `json:"operator"`
    Value    string `json:"value"`
}

type timeSeriesInput struct {
    Granularity string         `json:"granularity"`
    TimeRange   timeRangeInput `json:"timeRange"`
    Filters     []filterInput  `json:"filters"`
}

type timeSeriesResponse struct {
    Data struct {
        Metric struct {
            TimeSeries struct {
                Labels []string `json:"labels"`
                Values []string `json:"values"`
            } `json:"timeSeries"`
        } `json:"metric"`
    } `json:"data"`
}

type request struct {
    Query     string         `json:"query"`
    Variables map[string]any `json:"variables"`
}

func main() {
    endpoint := "https://api.us-east-2.propeldata.com/graphql"
    accessToken := "<ACCESS TOKEN>"

    query := `
      query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
        metric(id: $metricId) {
          timeSeries(input: $input) {
            labels
            values
          }
        }
      }
    `

    variables := map[string]any{
        "metricId": "METXXXXXXXXXXXXXXXXXXXXXXXXXX",
        "input": timeSeriesInput{
            Granularity: "DAY",
            TimeRange: timeRangeInput{
                Relative: "LAST_N_DAYS",
                N:        30,
            },
            Filters: []filterInput{
                {
                    Column:   "PROMO",
                    Operator: "EQUALS",
                    Value:    "FALSE",
                },
            },
        },
    }

    requestBody, err := json.Marshal(request{
        Query:     query,
        Variables: variables,
    })
    if err != nil {
        panic(err)
    }

    req, err := http.NewRequest("POST", endpoint, bytes.NewBuffer(requestBody))
    if err != nil {
        panic(err)
    }

    req.Header.Add("Content-Type", "application/json")
    req.Header.Add("Authorization", "Bearer "+accessToken)

    res, err := http.DefaultClient.Do(req)
    if err != nil {
        panic(err)
    }
    defer res.Body.Close()

    responseBody, err := ioutil.ReadAll(res.Body)
    if err != nil {
        panic(err)
    }

    var response timeSeriesResponse
    err = json.Unmarshal(responseBody, &response)
    if err != nil {
        panic(err)
    }

    fmt.Printf("%+v\n", response)
}

Finally, run the example:

go run main.go

Example 8: Query your Snowflake API with Ruby

An example of a Snowflake API request using Ruby. This example uses the Ruby standard library, so it doesn’t require additional dependencies; however, in production, you could use a library like GitHub’s own graphql-client for stronger typing.

Copy the following sample to a file, main.rb, replace <ACCESS TOKEN> with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

require 'json'
require 'net/http'

endpoint = "https://api.us-east-2.propeldata.com/graphql"
accessToken = "<ACCESS TOKEN>"

query = """
  query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
    metric(id: $metricId) {
      timeSeries(input: $input) {
        labels
        values
      }
    }
  }
"""

variables = {
  metricId: 'METXXXXXXXXXXXXXXXXXXXXXXXXXX',
  input: {
    granularity: 'DAY',
    timeRange: {
      relative: 'LAST_N_DAYS',
      n: 30
    },
    filters: [{
      column: 'PROMO',
      operator: 'EQUALS',
      value: 'FALSE'
    }]
  }
}

body = {
  'query': query,
  'variables': variables
}

res = Net::HTTP.post(URI(endpoint), body.to_json, {
  'Content-Type': 'application/json',
  'Authorization': 'Bearer ' + accessToken
})

puts res.body

Finally, run the example:

ruby main.rb

Example 9: Query your Snowflake API with C#/.NET

An example of a Snowflake API request using C#/.NET. This example uses the C#/.NET standard library, so it doesn’t require additional dependencies; however, in production, you could use a library like graphql-client for stronger typing.

Copy the following sample to a file, Program.cs, replace <ACCESS TOKEN> with your access token, and replace METXXXXXXXXXXXXXXXXXXXXXXXXXX with your Metric ID.

using System.Text.Json;

namespace Example
{
    class TimeSeriesResponse
    {
        public DataField data { get; set; }
    }

    class DataField
    {
        public MetricField metric { get; set; }
    }

    class MetricField
    {
        public TimeSeriesField timeSeries { get; set; }
    }

    class TimeSeriesField
    {
        public string[] labels { get; set; }
        public string[] values { get; set; }
    }

    public class Program
    {
        public static void Main()
        {
            var endpoint = "https://api.us-east-2.propeldata.com/graphql";
            var accessToken = "<ACCESS TOKEN>";

            var query = @"
query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
  metric(id: $metricId) {
    timeSeries(input: $input) {
      labels
      values
    }
  }
}
";

            var variables = new
            {
                metricId = "METXXXXXXXXXXXXXXXXXXXXXXXXXX",
                input = new
                {
                    granularity = "DAY",
                    timeRange = new
                    {
                        relative = "LAST_N_DAYS",
                        n = 30
                    },
                    filters = new[]
                    {
                        new
                        {
                            column = "PROMO",
                            @operator = "EQUALS",
                            value = "FALSE"
                        }
                    }
                }
            };

            var body = JsonSerializer.Serialize(new
            {
                query = query,
                variables = variables
            });

            var client = new HttpClient()
            {
                BaseAddress = new Uri(endpoint)
            };

            client.DefaultRequestHeaders.Add("Authorization", "Bearer " + accessToken);

            var response = client.PostAsync(endpoint, new StringContent(body, System.Text.Encoding.UTF8, "application/json")).Result;
            var responseJson = response.Content.ReadAsStringAsync().Result;

            var timeSeriesResponse = JsonSerializer.Deserialize<TimeSeriesResponse>(responseJson);

            Console.WriteLine(JsonSerializer.Serialize(timeSeriesResponse));
        }
    }
}

Finally, run the example either from your IDE or from the command line.

How to create a Postman collection for your Snowflake API

Below is a step-by-step guide on how to create a Postman collection for your new Snowflake API:

Open Postman and create a new collection.
Create a new request in the collection and name it "Snowflake API".
In the “Authorization” section of your collection, go to “Configure new Token” and follow these steps:
1. Select Type as “OAuth 2.0”.
2. Select Add auth data to “Request headers”.
3. Header prefix: “Bearer”
4. On “Configure New Token” add the following values:
  - Token name: {{ $isoTimestamp }}
  - Grant Type: “Client Credentials”
  - Access Token Token URL: https://auth.us-east-2.propeldata.com/oauth2/token
  - Client ID: The Client ID from your Propel Application created in the Propel Console.
  - Client Secret: The Client secret from your Propel Application created in the Propel Console.
  - Scope: “metric:query”
  - Client Authentication: “Send as Basic Auth header”
5. Make sure you have “Auto-refresh access token” enabled.
In the request URL field, enter the endpoint https://api.us-east-2.propeldata.com/graphql.
Select the "POST" method in the dropdown menu.
In the body of the request, select the "GraphQL" option from the dropdown menu.
In the query field, enter the GraphQL query:

query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {
  metric(id: $metricId) {
    timeSeries(input: $input) {
      labels
      values
    }
  }
}

In the variables field, enter the variables:

{
  "metricId": "METXXXXXXXXXXXXXXXXXXXXXXXXXX",
  "input": {
    "granularity": "DAY",
    "timeRange": {
      "relative": "LAST_N_DAYS",
      "n": 30
    },
    "filters": [{
        "column": "PROMO",
        "operator": "EQUALS",
        "value": "FALSE"
      }]
  }
}

Save the request and run it to make the GraphQL request.

Here is the resulting Postman collection in JSON:

{
    "info": 
        "_postman_id": "c316d428-0d89-49e5-bb21-990fae5e252a",
        "name": "Propel API",
        "schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json",
        "_exporter_id": "15021803"
    },
    "item": [
        {
            "name": "Query Metric",
            "event": [
                {
                    "listen": "test",
                    "script": {
                        "exec": [
                            "",
                            ""
                        ],
                        "type": "text/javascript"
                    }
                }
            ],
            "protocolProfileBehavior": {
                "strictSSL": true
            },
            "request": {
                "method": "POST",
                "header": [],
                "body": {
                    "mode": "graphql",
                    "graphql": {
                        "query": "query TimeSeriesQuery($metricId: ID!, $input: TimeSeriesInput!) {\n  metric(id: $metricId) {\n    timeSeries(input: $input) {\n      labels\n      values\n    }\n  }\n}",
                        "variables": "{\n  \"metricId\": \"METXXXXXXXXXXXXXXXXXXXXXXXXXX\",\n  \"input\": {\n    \"granularity\": \"DAY\",\n    \"timeRange\": {\n      \"relative\": \"LAST_N_DAYS\",\n      \"n\": 30\n    },\n    \"filters\": [{\n        \"column\": \"PROMO\",\n        \"operator\": \"EQUALS\",\n        \"value\": \"FALSE\"\n    }]\n  }\n}\n"
                    }
                },
                "url": {
                    "raw": "https://api.us-east-2.propeldata.com/graphql",
                    "protocol": "https",
                    "host": [
                        "api",
                        "us-east-2",
                        "propeldata",
                        "com"
                    ],
                    "path": [
                        "graphql"
                    ]
                }
            },
            "response": []
        }
    ],
    "auth": {
        "type": "oauth2",
        "oauth2": [
            {
                "key": "clientSecret",
                "value": "{{clientSecret}}",
                "type": "string"
            },
            {
                "key": "clientId",
                "value": "{{clientID}}",
                "type": "string"
            },
            {
                "key": "tokenType",
                "value": "",
                "type": "string"
            },
            {
                "key": "accessToken",
                "value": "",
                "type": "string"
            },
            {
                "key": "scope",
                "value": "metric:query",
                "type": "string"
            },
            {
                "key": "accessTokenUrl",
                "value": "{{tokenURL}}",
                "type": "string"
            },
            {
                "key": "tokenName",
                "value": "{{$isoTimestamp}}",
                "type": "string"
            },
            {
                "key": "grant_type",
                "value": "client_credentials",
                "type": "string"
            },
            {
                "key": "addTokenTo",
                "value": "header",
                "type": "string"
            }
        ]
    },
    "event": [
        {
            "listen": "prerequest",
            "script": {
                "type": "text/javascript",
                "exec": [
                    ""
                ]
            }
        },
        {
            "listen": "test",
            "script": {
                "type": "text/javascript",
                "exec": [
                    ""
                ]
            }
        }
    ]
}

Conclusion

In this post, we have explored the steps to build an API on top of your Snowflake data warehouse using Propel's Serverless Analytics API Platform. We have discussed why an API is necessary for customer-facing applications, the requirements for an API on top of Snowflake, and the five steps to build a Snowflake API: connect Snowflake, sync your tables, define Metrics, query the API, and build your data app.

Propel provides the infrastructure, scalability, and security required to power data applications while giving you the flexibility to define the metrics you want to query. Building an API on top of Snowflake with Propel is a fast, secure, and cost-effective way to power customer-facing web and mobile applications.

How can I get started building data APIs for Snowflake with Propel?

You can get started with Propel by reading the documentation. If you don't have your Propel Account yet, book a demo, and we can get you going!

Introducing Propellers: Easily select the optimal cost and query speed for each use case

Nico Acosta — Thu, 01 Dec 2022 22:41:00 +0000

Propellers equip developers with an easy way to select the optimal cost and query speed for each customer-facing analytics use case.

Today, we are thrilled to introduce Propellers, an easy way for product development teams to select the optimal cost and query speed for their customer-facing analytics use cases.

In some use cases, such as analytics on a core product workflow, you might want to prioritize speed over cost. In others, like an internal app, you might want to prioritize cost over speed.

Propel’s architecture separates storage and compute, allowing each to be consumed, scaled, and priced interdependently. This enables you to select the optimal levels of query speed and cost for your use case.

What are Propellers?

Propellers are the unit of compute in Propel. The larger the Propeller, the faster the queries and the higher the cost. Every Propel Application (and therefore every set of API credentials) has a Propeller that determines the speed and cost of queries.

You can think of a Propeller like a car engine. The size of the engine (Propeller) determines the speed at which the car can go. But just as importantly, it also affects how much fuel (costs) the car uses.

A small Propeller can be thought of as a "compact" car engine. It's great for getting around town, and it's very fuel-efficient. But if you need to go on the highway, or if you have a lot of people or cargo, you're going to want a bigger engine.

Propellers determine the maximum number of records a query can process per second. Queries that are very complex or that scan a larger time range will require the processing of more records.

The table below describes the different Propellers, their capacity, and their price. The Propeller’s price is determined by the maximum number of records it can process per second.

Propeller	Max records processed per second	Price per processed GB
P1_X_SMALL	1,000,000	$0.01 / GB
P1_SMALL	10,000,000	$0.05 / GB
P1_MEDIUM	100,000,000	$0.10 / GB
P1_LARGE	250,000,000	$0.15 / GB
P1_X_LARGE	500,000,000	$0.25 / GB

How do Propellers work?

In order to query data from Propel, you first create a Propel Application. Your Propel Application includes settings, like OAuth 2.0 credentials, as well as the Propeller to use when executing queries.

You can choose a Propeller ranging from P1_X_SMALL through P1_X_LARGE when creating your Application, and you can resize it at any time.

Next, when you query data from Propel, you include your Propel Application’s access token (see authentication). Propel checks your access token, identifies your Application, and executes your query with your Application’s configured Propeller.

How do I choose a Propeller size?

If you're not sure what size Propeller you need, don't worry! The Propel Console can help you select the optimal Propeller size for your use case. Here’s how:

Go to the Metric Playground in Console.
Create a representative query for your Metric.
1. This should be a query your Application will actually execute.
Starting with the smallest Propeller, run the query and check the query statistics.
If the query was too slow, choose the next highest Propeller from the drop-down.
Repeat until you get the desired cost-to-performance.

Here are a few rules of thumb to follow when choosing Propellers:

Start small and move to bigger Propellers as your Data Pool grows.
Choose bigger Propellers when speed is important or your Data Pool is large.
Don’t automatically choose the biggest Propeller just because you have billions of records. Filtering by time range and tenant ID significantly cuts down on the number of records, meaning even medium-sized Propellers can perform well on larger Data Pools.

What are the use cases for Propellers?

Propel's customers use Propellers for many different things, including the following:

Optimizing for fast response times on large datasets

Imagine the main dashboard of a SaaS product that has to query a ton of data and is core to every workflow in the product. Customers have no tolerance for slow products, so you'll want that dashboard to load blazingly fast. A large Propeller lets you optimize for speed here in the customer-facing product.

Optimizing for cost on small datasets and non-core workflows

Not all customer-facing workflows handle massive amounts of data or need sub-second response times. For these non-core workflows that query small datasets, you could use a smaller Propeller and avoid paying for performance that isn’t needed.

Similarly, employees might be OK waiting 5‒10 seconds for a query to load in an internal tool. Here, you might want to keep costs lower and go with a smaller Propeller.

Optimizing for development and production environments

Production environments that serve customers might want to use large Propellers, while development and staging environments can get away with smaller Propellers. Again, Propellers enable you to pay for performance where you need it.

💡 Not all use cases are created equal, and that's where Propellers come in. With Propellers, you can easily select the optimal cost and query speed for each customer-facing analytics use case.

How do I get started with Propellers?

You can use the Propel Console to create an Application and select the size of Propeller. You can learn more about Propellers by reading the documentation.

If you don't have your Propel account yet Join our waitlist!

We are onboarding users first as fast as we can. We can’t wait to see what you can build with Propel!

Why do product and data teams struggle to work together?

Nico Acosta — Wed, 30 Nov 2022 13:20:00 +0000

Product and data teams struggle to work together because there's a tradeoff in data between flexibility, performance and cost-effectiveness.

Data and product teams always work smoothly; yeah, sure.

Why is it so hard for data and product teams to work together?

Their conversations go something like this:

Product team: We need to build this insights dashboard in our console and mobile app for customers to see the outcome of XYZ by the hour and be able to slice and dice by A, B, C, D, and E.

Data team: We only have that data monthly, and you can only slice and dice by C, D, or E. How important is this? How fast do you need to query it?

Product team: We need to be able to query super-fast (sub-second), and it is crucial to meeting our goal, so it is super important.

Data team: We can't do it. File a ticket with the requirements, and we can see where it falls in the priorities.

The product team is frustrated because they can't build the product they envisioned.

The data team is frustrated because they are constantly bombarded with high-priority tasks that are impossible to prioritize against each other.

What's the problem?

It is not that the product team is unreasonable or the data team is incapable. The product team is looking to build what's best for their customer. The data team seeks to inform decisions as effectively as possible across the organization.

To understand the root of the problem, we must dig deeper. There is a fundamental tradeoff in data between flexibility, performance, and cost-effectiveness.

Flexibility: refers to the different ways to slice and dice the data. What filters do you have? Can you see it by minute, hour, day, or month (its granularity), and how far back in time does it go?
Performance: refers primarily to the query speed. How fast is a given query?
Cost-effectiveness: refers to the total cost of implementing the desired outcome, with compute and storage being the primary factors driving the cost.

You can have two data characteristics above at the same time, but not all three.

You can pre-aggregate data which will give you performance and cost-effectiveness, but you will have tragic flexibility limitations.

You can beef up compute to query as fast as possible in order to keep the performance and flexibility, but it will come at a steep cost.

You can have all the flexibility in the world and be cost-effective, but it will likely get very slow queries, even on a small scale.

So what's the problem?

The root of the problem is not the tradeoff itself but who makes the tradeoff.

In the previous conversation, the data team made a tradeoff that sacrificed flexibility. At the time, it was the right tradeoff. They are also the team empowered and equipped to make such a tradeoff.

Unfortunately, that tradeoff did not work for what the product team needed. It would be surprising if it did, as it was made a long time ago with other requirements in mind.

Now imagine the product team has a huge budget. Their big budget can’t get them the performant and flexible product they want because it is blocked by the data team's ability to prioritize it. The data team's resources are very constrained, and the organization has many other priorities which make the product’s team ask fall below the line every quarter and therefore it never gets done.

Unlike the data team, the product team is not empowered or equipped to make this tradeoff.

The root of the problem is that the team that is closest to the customer is not empowered to make and execute the tradeoff.

The data team gets pulled in a million different directions and is not in a position to prioritize what's best for an individual product team.

What now?

The solution is for product teams, who are closer to the customer experience, to be empowered and equipped to make the tradeoff. How does an organization get there without each product development team having data engineering resources?

Our goal at Propel is to empower product development teams to build in-product analytics quickly and easily. We want product teams to be able to make the tradeoff they need to build their product. Propel does this by:

Simplifying data requirements. There’s no need to pre-aggregate, roll-up data, or create complicated indexes; just point us at your data.
Easily scaling compute. Product development teams can authorize applications with different levels of computing power with different levels of computing power that enable them to prioritize flexibility and performance.
Optimizing common queries. When a team builds a dashboard, there are a common set of queries that power it. Once developers know what those common queries are, they can optimize them for blazing-fast response times with just one click.

If you are interested in what we are doing, sign up for monthly product updates, follow us on Twitter @propeldatacloud, or join our waitlist to kick the tires on the product.

What is the separation of storage and compute in data platforms and why does it matter?

Nico Acosta — Tue, 29 Nov 2022 23:13:42 +0000

As businesses strive to become data-driven, the separation of storage and compute has become a critical factor in data platforms. By separating these two components, businesses can now take advantage of application design benefits for data analytics. To learn more about this new paradigm, read the blog post above.

The separation of storage and compute is a new paradigm in data platforms. By separating storage and compute, they can be consumed, scaled, and priced independently. This allows businesses to pay for what they use and nothing more. As customers, we want to avoid waste in all forms, including unused capacity. Let's take a closer look at how this separation works and the benefits it brings.

Before this separation was possible, many companies were running systems like Hadoop or HDFS where the dominant paradigm was that compute and storage were tightly coupled 1:1. For example, if your queries got more complicated, you had to resize the HDFS cluster; if the amount of data you had increased, you had to resize the HDFS cluster. And this always had to be done for the peak queries and peak storage. It resulted in a very inelastic data architecture with a lot of waste, but this was state-of-the-art in 2006 when it was first released and is used still today.

In this post, we will cover how the separation of storage and compute works, how you can take advantage of it, what are some of the key technologies that make it possible, and some examples of data platforms that implement the separation of storage and compute.

How does the separation of storage and compute work?

When you buy a computer or rent an AWS instance, it comes with one or more CPUs and one or more hard drives, the compute and storage. Typically, more powerful CPUs are paired with larger hard drives. So if you only need the more powerful compute, the extra storage goes unused, and vice versa.

With the advances in serverless computing and cloud storage, data platforms can now combine whatever storage or compute they need on-demand vs relying on predefined instance configurations. The "infinite storage" offered by services like AWS S3 makes it so that data platforms don't even need to provision more storage as customers use more space. Similarly, elastic computing enables data platforms to offer different levels of compute capacity on demand that customers can choose to meet their query processing requirements.

If a customer on a data platform needs a lot more storage, but a small amount of compute processing power, the platform can offer this by allocating more storage to that specific customer and a smaller processor for compute because they are not coupled 1:1.

What are the benefits of separating storage and compute?

The are many benefits of separating storage and compute in data platforms. Here are some of the main ones:

You only pay for what you use: With the separation of storage and compute, you only pay for the resources you actually use. If you have a lot of data but don't need to do much processing on it, you won't be paying for expensive compute resources that you don't need.
Flexible Scaling: You can scale your storage and compute independently to match your needs, potentially unlocking new use cases. If you need to do more processing, you can simply add more compute resources.
Better Utilization of Resources: By being able to scale storage and compute independently, data platforms can make better use of their resources. For example, if you have a lot of data but don't need to do much processing on it, you can use less expensive storage options and save money.

How can you take advantage of the separation of storage and compute?

There are a few steps businesses can take advantage of the separation of storage and compute in data platforms:

Identify your workloads: Identify what are the discrete data workloads you have. For example, internal reporting could be one workload and customer-facing analytics for your product could be another.
Allocate resources to each workload: Once you have identified the workloads you need, the next step is to allocate resources. Following our example, internal employees might be OK waiting 30 seconds for reports to load, while customers have no tolerance for slow dashboards. In this case, you could allocate more resources to your customer-facing analytics workload.
Optimize your resources: Finally, make sure to optimize your resources to get the most out of them. This is going to depend on the platform, especially if these resources need to be provisioned versus offered on demand.

What are some examples of data platforms that separate storage and compute?

There are a few data platforms that have adopted this new paradigm:

Snowflake - In Snowflake you pay for storage and compute independently. Compute is provisioned with "Warehouses" that have different computing power.
Dremio - Dremio is a data lakehouse based on the open-source Apache Iceberg table format. It offers different compute instances to process data that lives in your S3 bucket. You pay for S3 storage independently.
Propel - is an Analytics API platform to build customer-facing analytics. In Propel you can customize the compute assigned to your different web or mobile apps. Storage and compute are priced independently.

What are the key technologies that make the separation of compute and storage possible?

Every data platform that takes advantage of the separation of storage and compute needs a storage layer (an object store or network attached storage), table and file formats, and a query engine. Some, like Snowflake, use proprietary technologies for each. The examples below are open source technologies for each of these categories (except the storage services).

Object storage and network attached storage: AWS S3, GCP Cloud Storage, or Azure Blob Store

Object Storage: Services like AWS S3 can be used to store files on-demand with "infinite storage". There is no need to provision or manage capacity and it is completely decoupled from the instance or serverless that is doing the processing.

Network Attached Storage (NAS): NAS is a storage architecture where data is stored on a dedicated server that is attached to a network. This allows users to access the data over the network using protocols like SMB (Server Message Block) or NFS (Network File System). NAS allows to swap or sometimes resize the storage capacity without changing the processing.

Open table and file formats: Apache Iceberge and Apache Parquet

Apache Iceberg is an open table format designed to store vast amounts of data for analytics. Iceberg tables can be used with different data processing engines such as Spark, Trino, Flink, or Hive. It works with Parquet files, giving them SQL table-like semantics through its metadata.

Apache Parquet is a file format that is used to store data in a columnar format. This allows for faster reads and reduced disk space. Not only is the Parquet format open source, but it also has an entire ecosystem of tools to help you create, read and transform data.

Query engines: DuckDB, Trino and Spark

The query engine actually executes queries against your tables, so it needs to support your underlying table format, file format, and object storage.

DuckDB is an in-process SQL OLAP database with a highly optimized query engine. It supports reading from object stores like S3, and it can perform predicate and projection pushdown when scanning Parquet files. These optimizations mean queries that might have previously required a cluster to complete can be executed on a single host, due to better utilization of compute.

However, once your data reaches a certain size or you reach the limits of vertical scaling, it may be necessary to distribute your queries across a cluster, or scale horizontally. This is where distributed query engines like Trino and Spark come in. Distributed query engines make use of a coordinator to plan the query and multiple worker nodes to execute them in parallel.

Final thoughts on the separation of storage and compute

The separation of storage and compute is a new paradigm in data platforms that offers many benefits, including flexible scaling, better utilization of resources, and only paying for what you use. Not all data platforms take advantage of this new paradigm. There are key open-source technologies that not only make it possible but also accelerate its adoption. Snowflake, Dremio, and Propel are examples of data platforms that take advantage of this new paradigm. To take advantage of the separation of storage and compute in your own application, make sure you identify your workloads and allocate resources accordingly.

Why we built Propel Data

Nico Acosta — Thu, 17 Nov 2022 08:00:00 +0000

Today, we are thrilled to announce Propel Data – an API Platform for developers to easily build in-product analytics with large-scale data. Propel Data enables developers to quickly build usage metering, insights dashboards, product metrics, and advanced reporting without the overhead of managing complex data infrastructure. We are excited to launch our API platform to the market and also to announce a $4.6M funding round led by Matrix Partners with notable angels including founders and executives from Twilio, Databricks, RapidAPI, FaunaDB, Labelbox, and Calixa.

Building in-product analytics should be fast and easy for developers and that’s why we built Propel Data. Empowering your customers with data shouldn’t require a multi-month or year-long project.

In-product analytics are a frequent feature request businesses make to their SaaS vendors. It’s easy to understand why – running a software product without analytics is like running a website without Google Analytics. You wouldn't know how many people visit your site, where the traffic is coming from, and if visitors are converting to customers – you’d be flying blind. Yet, most SaaS products provide no analytics and they are often pushed off and delayed on product roadmaps – despite being a top feature request – leaving their customers flying blind. That’s because building data products is really HARD, until Propel Data.

Why? What's so hard about it? 🤔

It’s taken leading companies like Twilio, LinkedIn, Shopify, and Uber years to build their customer-facing analytic products despite having huge teams behind them. Why is it so hard? Why is it so common for analytics to get punted in product roadmaps or take forever to build? Can’t you just query the database?

There are good reasons for this:

Data chaos - Data is often siloed in different data stores and transactional databases don’t perform well with analytical queries.
Scarce engineering resources - Data engineering is a very scarce resource and analytics infrastructure and pipelines require dedicated teams to stand up and operate.
Inadequate tooling - Business intelligence dashboards that work well for internal use cases lack the customizability required to feel fully native when applied to a customer-facing data analytics use case.

My co-founders and I faced these challenges at Twilio where we were early product and engineering leaders. Customers loved our APIs but needed insight into usage, spend, and deliverability in order to troubleshoot with autonomy and give them the confidence to accelerate adoption. In the early days, we had grand ideas to visualize ‘Google Analytics’-like features in the Twilio Console. Unfortunately, these fell flat when we realized that we needed new data infrastructure that we quickly learned was out of our reach.

It wasn’t until years later that Tyler Wells and Mark Roberts (my co-founders) built the first Voice Insights product for WebRTC at Twilio. Voice Insights is a call quality analytics product that gives customers a complete picture of call performance as calls move through the customer's networks, Twilio's infrastructure, and carrier networks. It started by collecting telemetry from the WebRTC endpoints, processed it in real-time, and gave customers metric dashboards to understand what networks or individual agents had connectivity issues. Today, you’d be irresponsible to run a sizable Twilio Voice deployment without Voice Insights. But the biggest learning - it turned customer frustration into customer empowerment.

Even for the best companies like Twilio, building data products that use large-scale analytical data requires a significant engineering investment and takes years to build. When we left Twilio at the end of 2020, hundreds of engineers were working on the data products across the company. This is not unique to Twilio.

We asked ourselves – does it have to be this hard? Could the complexity be delivered as an API so that every company can deliver in-product analytics without fleets of engineers and investment?

Introducing Propel Data! 🎉

Propel Data is an Analytics API Platform for developers to easily build data products powered by large-scale analytical data. It enables developers to quickly build usage metering, insights dashboards, product metrics, and advanced reporting without the overhead of managing additional complex data infrastructure or creating new data silos.

Propel's GraphQL API and serverless infrastructure make building and running data products seamless. It leverages the data you already have in your data warehouse, operationalizes it, and serves it via highly available low-latency APIs.

Building large-scale data analytics into the product features requires sophisticated data infrastructure because customer-facing products have unique requirements:

⚡️ Low latency → Customers expect fast, snappy product experiences. While internal employees may be okay waiting 45 seconds or even minutes for a query to run, this latency is a no-go for customer-facing products where the data analysis is part of the core workflow that the product enables.
🌊 High concurrency → Products need to support thousands or millions of users. Because data products ultimately serve customers rather than employees, they must support a dramatically higher number of concurrent requests than what internal data tools are designed to handle. And they must do so seamlessly and cost-effectively.
📜 APIs → A contract in code between product and data teams. Shipping a modern data application requires collaboration between data and the product development teams (frontend & backend). APIs codify the contract and SLAs that allow the separation of concerns that keep the product development teams focused on solving customer problems.
🔐 App-centric security layer → Each end-user can only see their own data. In SaaS and consumer products, end-users access their own data from the web and mobile applications.
This requires an app-centric security layer for multi-tenant or consumer environments, not just the employee-centric role-based access control.

With Propel you can build in days what has taken leading companies years and hundreds of engineers to build.

What are customers saying? ❤️

Over the last year, we have been working with around a dozen design partners to build Propel. We are thrilled to share that Courier, the leading developer notifications platform, will be powering their notifications insights with Propel.

“Analytics was one of the top feature requests that always got punted due to the complexity it involved. With Propel, we were able to ship the analytics we always wanted without having to staff new teams or manage additional infrastructure, all in a couple of weeks.”

Seth Carney, CTO at Courier

How do I get started? 🚀

Join our waitlist! We are onboarding customers as fast as we can. We are starting with Snowflake customers and will be adding support for AWS Redshift, GPC BigQuery, and Databricks in the coming quarters.

Stay tuned! Follow us on Twitter or subscribe to our product updates.

DEV Community: Nico Acosta

How to reduce Snowflake costs: A five-point checklist

1. Set the correct warehouse auto-suspend

What is the Auto-Suspend feature?

The default value and why it's inefficient

Example query for querying and modifying auto-suspend

2. Right-size your warehouses: monitor remote disk spillage

What is remote disk spillage, and why it causes costs to increase

Identify the warehouses that have remote disk spillable

Fixing remote disk spillage by increasing warehouse size

3. Use a serving layer for high-concurrency sub-second queries

How a serving layer on top of Snowflake works

4. Identify and fix inefficient queries

How to identify inefficient queries

Tips to improve query efficiency

5. Set up Resource Monitors

What are Resource Monitors?

Example: Creating a Resource Monitor

Conclusion

Further reading

Introducing Usage Metering for ChatGPT-Powered LLM Apps

The Problem: Generative AI Apps Need to Track Usage

Why Is It Hard to Track Usage?

Propel’s Usage Metering for LLM Apps

Key Features

Final thoughts

How to build a Snowflake API?

Why do you need an API on top of Snowflake?

What are the requirements for an API on top of Snowflake?

Does Snowflake have an API?

How do I build an API on top of Snowflake?

1. Connect your Snowflake Data Source

2. Sync a table from Snowflake

3. Define Metrics

4. Query your Snowflake API

5. Build your analytics product using Propel’s GraphQL API for Snowflake

How to create a Postman collection for your Snowflake API

Conclusion

How can I get started building data APIs for Snowflake with Propel?

Introducing Propellers: Easily select the optimal cost and query speed for each use case

What are Propellers?

How do Propellers work?

How do I choose a Propeller size?

What are the use cases for Propellers?

Optimizing for fast response times on large datasets

Optimizing for cost on small datasets and non-core workflows

Optimizing for development and production environments

How do I get started with Propellers?

Why do product and data teams struggle to work together?

What's the problem?

So what's the problem?

What now?

What is the separation of storage and compute in data platforms and why does it matter?

How does the separation of storage and compute work?

What are the benefits of separating storage and compute?

How can you take advantage of the separation of storage and compute?

What are some examples of data platforms that separate storage and compute?

What are the key technologies that make the separation of compute and storage possible?

Object storage and network attached storage: AWS S3, GCP Cloud Storage, or Azure Blob Store

Open table and file formats: Apache Iceberge and Apache Parquet

Query engines: DuckDB, Trino and Spark

Final thoughts on the separation of storage and compute

Why we built Propel Data

Why? What's so hard about it? 🤔

Introducing Propel Data! 🎉

What are customers saying? ❤️

How do I get started? 🚀