DEV Community: Propel

How to stream PostgreSQL CDC to Kafka and use Propel to get an instant API

Tyler Wells — Mon, 08 Apr 2024 13:00:00 +0000

Learn how to stream PostgreSQL Change Data Capture (CDC) to a Kafka topic and serve the data via an API with Propel. This guide provides step-by-step instructions, from setting up your PostgreSQL database and Kafka broker to deploying the Debezium PostgreSQL connector and creating a Propel Kafka Datapool.

Change Data Capture (CDC) is the process of tracking and capturing changes to your data. In PostgreSQL, we can implement CDC efficiently using the transaction log so that, rather than running batch jobs to gather data, we can capture data changes as they occur continuously. This has numerous benefits, including allowing your applications to see and react to changes in near real-time with lower impact on your source systems. In this article, we'll walk you step-by-step through how to stream CDC from PostgreSQL to a Propel Kafka Data Pool to get an instant data-serving API.

What are we trying to do?

We want to power large-scale analytics applications with data coming from PostgreSQL. As we all know, PostgreSQL, as an OLTP database, is notoriously slow in handling analytical queries that process and aggregate large amounts of data.

First, we want to capture all changes to our PostgreSQL database table(s) and send them as JSON messages (see below) to a Kafka topic.
Second, we’ll need to configure Kafka Connect and the Debezium PostgreSQL connector to stream the CDC to a Kafka topic.
Lastly, create a Propel Kafka Data Pool that ingests data from the Kafka topic and exposes it via a low latency API.

We will cover how to deploy how to create and deploy all services in a Kubernetes cluster.

How PostgreSQL CDC with Debezium works

In this section, we will provide a quick overview of how PostgreSQL change data capture works with Debezium.

Imagine you have a simple table:

CREATE TABLE "simple" (
    id uuid NOT NULL PRIMARY KEY,
    foo character varying,
);

You update a row with the following SQL statement:

UPDATE public.simple SET foo = 'baz' WHERE ID = 'b18df779-0cae-43bc-9f4b-8efe571abd8a';

The change to the column foo will result in an “update” to the transaction log, which Debezium then transforms into the JSON message below and drops into a Kafka topic.

CDC JSON with before and after:

{
  "before": {
        "id": "b18df779-0cae-43bc-9f4b-8efe571abd8a",
    "foo": "bar",
  },
  "after": {
    "id": "b18df779-0cae-43bc-9f4b-8efe571abd8a",
        "foo": "baz"
  },
  "source": {
    "version": "2.5.0.Final",
    "connector": "postgresql",
    "name": "pg",
    "ts_ms": 1707250399964,
    "snapshot": "false",
    "db": "production",
    "sequence": "[\"573123170952\",\"573123068760\"]",
    "schema": "public",
    "table": "simple",
    "txId": 55986537,
    "lsn": 573123068760,
    "xmin": null
  },
  "op": "u",
  "ts_ms": 1707250400533,
  "transaction": null
}

The Debezium PostgreSQL connector generates a data change event for each row-level INSERT, UPDATE, and DELETE operation. The op key describes the operation that caused the connector to generate the event. In this example, u indicates that the operation updated a row. Valid values are:

c = create
u = update
d = delete
r = read (applies to only snapshots)
t = truncate
m = message

What is the Propel Kafka Datapool?

The Kafka Data Pool lets you ingest real-time streaming data into Propel. It provides an instant low-latency API on top of Kafka to power real-time dashboards, streaming analytics, and workflows.

Consider using Propel on top of Kafka when:

You need an API on top of a Kafka topic.
You need to power real-time analytics applications with streaming data from Kafka.
You need to ingest Kafka messages into ClickHouse.
You need to ingest from self-hosted Kafka, Confluent Cloud, AWS MSK, or Redpanda into ClickHouse.
You need to transform or enrich your streaming data.
You need to power real-time personalization and recommendations for use cases.

Once we have the PostgreSQL, Debezium, and Kafka set up, we’ll ingest the data into a Propel Kafka Data Pool to expose it via an API.

Setting up Minikube, PostgreSQL, Kafka, Kafka Connect, and Debezium

We will walk through setting this up in a Kubernetes cluster on a Mac. This can be done in other environments, but Kubernetes is a common platform for hosting and running data streaming services in the cloud. For this example, we will use Minikube to deploy our Kubernetes cluster, Redpanda to deploy our Kafka cluster, and Helm to manage Kubernetes applications like PostgreSQL. I’ll provide the Mac-specific commands but links to the installed services if you are running a different OS.

Setting up Minikube

Step 1: Install Docker Desktop

brew install --cask docker

Step 2: Install Kubectl

brew install kubectl

Step 3: Install minikube

curl -LO https://storage.googleapis.com/minikube/releases/latest/minikube-darwin-amd64
sudo install minikube-darwin-amd64 /usr/local/bin/minikube

Step 4: Start minikube

minikube start --nodes 8 #docker-desktop will need to be running

Step 5: Interact with your cluster

minikube kubectl -- get pods -A

Step 6: Install helm

brew install helm

Setting up PostgreSQL

Step 1: Install the PostgreSQL Helm Chart with extended configuration

Extend the configuration to set the wal_level

cat > pg-values.yaml <<'EOF'
primary:
  extendedConfiguration: |-
    wal_level = logical
    shared_buffers = 256MB
EOF

helm install blog oci://registry-1.docker.io/bitnamicharts/postgresql -f pg-values.yaml

Step 2: Connect to PostgreSQL using the PostgreSQL CLI Create the database

export POSTGRES_PASSWORD=$(kubectl get secret --namespace default blog-postgresql -o jsonpath="{.data.postgres-password}" | base64 -d)

kubectl run blog-postgresql-client --rm --tty -i --restart='Never' --namespace default --image docker.io/bitnami/postgresql:16.2.0-debian-12-r6 --env="PGPASSWORD=$POSTGRES_PASSWORD" \
      --command -- psql --host blog-postgresql -U postgres -d postgres -p 5432

Step 3: Create the table

# after connecting to the PostgreSQL instance, create the table      
CREATE TABLE simple (
id uuid NOT NULL PRIMARY KEY,
foo character varying);

ALTER TABLE public.simple REPLICA IDENTITY FULL;

Step 4: Create a PostgreSQL user

To create a PostgreSQL user with the necessary privileges for Debezium to stream changes from the PostgreSQL source tables, run this statement with superuser privileges:

CREATE USER blog WITH REPLICATION PASSWORD 'your_password' LOGIN;

Setting up Kafka (Redpanda)

Step 1: Add the helm repo and install dependencies

helm repo add jetstack https://charts.jetstack.io
helm repo add redpanda https://charts.redpanda.com
helm repo update
helm install cert-manager jetstack/cert-manager  --set installCRDs=true --namespace cert-manager  --create-namespace

Step 2: Configure and install Redpanda

  helm install redpanda redpanda/redpanda \
  --namespace redpanda \
  --create-namespace \
  --set statefulset.initContainers.setDataDirOwnership.enabled=true \
  --set image.tag=v23.3.4-arm64 \
  --set tls.enabled=false \
  --set statefulset.replicas=1 \
  --set auth.sasl.enabled=true \
  --set "auth.sasl.users[0].name=superuser" \
  --set "auth.sasl.users[0].password=changethispassword" \
  --set external.domain=customredpandadomain.local

Step 3: Install the Redpanda client rpk

brew install redpanda-data/tap/redpanda

Step 4: Create an alias to simplify the rpk commands

alias local-rpk="kubectl --namespace redpanda exec -i -t redpanda-0 -c redpanda -- rpk -X user=superuser -X pass=changethispassword -X sasl.mechanism=SCRAM-SHA-512"

Step 5: Describe the Redpanda Cluster

local-rpk cluster info

CLUSTER
=======
redpanda.69087c28-e59b-4b25-bee2-bd199e6a9ab2

BROKERS
=======
ID    HOST                                             PORT
0*    redpanda-0.redpanda.redpanda.svc.cluster.local.  9093

TOPICS
======
NAME      PARTITIONS  REPLICAS
_schemas  1           1

Step 6: Create a user

local-rpk acl user create blog -p changethispassword --mechanism scram-sha-256

Step 7: Create an ACL for the user blog

local-rpk acl create --allow-principal User:blog \
  --operation all  --topic '*' --group '*'

Setting up Kafka Connect

Step 1: Create a namespace

kubectl create namespace kafka-connect

Step 2: Create the Service

Below is the manifest you’ll need to apply to set up the Kafka Connect service with your Redpanda broker (see above). Copy the manifest below and provide the needed values to configure the service for your environment. In the example below, we are connecting with SASL_PLAINTEXT and SCRAM-SHA-256, ensure that CONNECT_SASL_JAAS_CONFIG is using org.apache.kafka.common.security.scram.ScramLoginModule and that you have set the username and password for both the CONNECT_ and PRODUCER_ keys.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: kafka-connect
  labels:
    app: kafka-connect
spec:
  replicas: 1
  selector:
    matchLabels:
      app: kafka-connect
  template:
    metadata:
      labels:
        app: kafka-connect
    spec:
      containers:
      - name: kafka-connect
        image: debezium/connect:2.5.2.Final # Use the appropriate Debezium version
        env:
        - name: LOG_LEVEL
          value: INFO
        - name: BOOTSTRAP_SERVERS
          value: "redpanda-0.redpanda.redpanda.svc.cluster.local.:9093" # Replace with your Kafka broker address
        - name: GROUP_ID
          value: "blog-connect-cluster" # Replace with your group ID
        - name: CONFIG_STORAGE_TOPIC
          value: "connect-configs"
        - name: OFFSET_STORAGE_TOPIC
          value: "connect-offsets"
        - name: STATUS_STORAGE_TOPIC
          value: "connect-status"
        - name: CONNECT_SECURITY_PROTOCOL # additional values https://kafka.apache.org/24/javadoc/index.html?org/apache/kafka/common/security/auth/SecurityProtocol.html
          value: "SASL_PLAINTEXT"
        - name: CONNECT_SASL_MECHANISM # additional values https://docs.confluent.io/platform/current/kafka/authentication_sasl/auth-sasl-overview.html
          value: "SCRAM-SHA-256"
        - name: CONNECT_SASL_JAAS_CONFIG # additional values https://docs.confluent.io/platform/current/kafka/authentication_sasl/index.html
          value: "org.apache.kafka.common.security.scram.ScramLoginModule required username='blog' password='changethispassword';"
        - name: CONNECT_SSL_ENDPOINT_IDENTIFICATION_ALGORITHM
          value: "" # Set to an empty string to disable hostname verification
        - name: PRODUCER_SECURITY_PROTOCOL
          value: "SASL_PLAINTEXT"
        - name: PRODUCER_SASL_MECHANISM
          value: "SCRAM-SHA-256"
        - name: PRODUCER_SASL_JAAS_CONFIG
          value: "org.apache.kafka.common.security.scram.ScramLoginModule required username='blog' password='changethispassword';"
        - name: PRODUCER_SSL_ENDPOINT_IDENTIFICATION_ALGORITHM
          value: "" # Set to an empty string to disable hostname verification
        ports:
        - containerPort: 8083

Use kubectl to install the manifest and start the Kafka Connect pod in your cluster.

kubectl apply -n kafka-connect -f kafka-connect-pod.yaml

After you apply the manifest, you should get the following output.

deployment.apps/kafka-connect configured

Check the pod log files to ensure the service can log in to the broker. You should see the following in the logs if everything has been configured correctly.

2024-03-03 23:40:40,484 INFO   ||  Successfully logged in.   [org.apache.kafka.common.security.authenticator.AbstractLogin]
2024-03-03 23:40:40,504 INFO   ||  Kafka version: 3.6.1   [org.apache.kafka.common.utils.AppInfoParser]
2024-03-03 23:40:40,504 INFO   ||  Kafka commitId: 5e3c2b738d253ff5   [org.apache.kafka.common.utils.AppInfoParser]
2024-03-03 23:40:40,504 INFO   ||  Kafka startTimeMs: 1709509240504   [org.apache.kafka.common.utils.AppInfoParser]
2024-03-03 23:40:40,508 INFO   ||  Kafka Connect worker initialization took 5851ms   [org.apache.kafka.connect.cli.AbstractConnectCli]
2024-03-03 23:40:40,508 INFO   ||  Kafka Connect starting   [org.apache.kafka.connect.runtime.Connect]
2024-03-03 23:40:40,510 INFO   ||  Initializing REST resources   [org.apache.kafka.connect.runtime.rest.RestServer]
2024-03-03 23:40:40,510 INFO   ||  [Worker clientId=connect-1, groupId=propel-connect-cluster] Herder starting   [org.apache.kafka.connect.runtime.distributed.DistributedHerder]
2024-03-03 23:40:40,510 INFO   ||  Worker starting   [org.apache.kafka.connect.runtime.Worker]

Check that the connect-offsets topic has been created on the Redpanda broker.

local-rpk topic list

NAME             PARTITIONS  REPLICAS
_schemas         1           1
connect-configs  1           1
connect-offsets  25          1
connect-status   5           1

Setting up the Debezium PostgreSQL Connector

We must create a topic to store the CDC messages before deploying the Debezium PostgreSQL connector to the Kafka Connect service. If you take a look at the config below we’ve set the property topic.prefix to “blog” andpublication.autocreate.mode to “filtered” and set the table.include.list to “public.simple”. This configures the connector to write the CDC messages to a topic named blog.public.simple, use the command below to create the topic, then deploy the connector using the cURL command. You’ll need to provide your environment-specific details for connecting to your PostgreSQL server and the username/password for the Redpanda service you used above.

Step 1: Create a topic in Redpanda

local-rpk topic create blog.public.simple

Install the Debezium PostgreSQL connector using cURL. You’ll need to fetch the password for the PostgreSQL user and fill it in below. We will also set up port forwarding from the Kafka Connect service to our local host.

Step 2: Get the PostgreSQL password

export POSTGRES_PASSWORD=$(kubectl get secret --namespace default blog-postgresql -o jsonpath="{.data.postgres-password}" | base64 -d)
echo $POSTGRES_PASSWORD

Step 3: Forward the Kafka-Connect service port (8083)

# Run this in a separate terminal to get the Pod name
kubectl get pod -n kafka-connect -o wide
NAME                             READY   STATUS    RESTARTS      AGE   IP             NODE       NOMINATED NODE   READINESS GATES
kafka-connect-5498764f4f-qw5qg   1/1     Running   1 (34m ago)   35m   10.244.0.253   minikube   <none>           <none>

# Run this command to forward the traffic to local host
kubectl port-forward -n kafka-connect pod/kafka-connect-5498764f4f-qw5qg 8083:8083

Forwarding from 127.0.0.1:8083 -> 8083

Step 4: Deploy the Debezium Postgres Connector with cURL

curl -X POST http://127.0.0.1:8083/connectors -H "Content-Type: application/json" --data '{
                "name": "debezium-postgres-connector",
                "config": {
                  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
                  "after.state.only": "false",
                  "tasks.max": "1",
                  "plugin.name": "pgoutput",
                  "database.hostname": "blog-postgresql.default.svc.cluster.local",
                  "database.port": "5432",
                  "database.user": "postgres",
                  "database.password": "<replace>",
                  "database.dbname": "postgres",
                  "topic.prefix": "blog",
                  "publication.autocreate.mode": "filtered",
                  "table.include.list": "public.simple",
                  "key.converter": "org.apache.kafka.connect.json.JsonConverter",
                  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
                  "key.converter.schemas.enable": "false",
                  "value.converter.schemas.enable": "false",
                  "producer.override.sasl.mechanism": "SCRAM-SHA-256",
                  "producer.override.security.protocol": "SASL_PLAINTEXT",
                  "producer.override.sasl.jaas.config": "org.apache.kafka.common.security.scram.ScramLoginModule required username='blog' password='changethispassword';",
                  "producer.override.ssl.endpoint.identification.algorithm": "",
                  "producer.override.tls.insecure.skip.verify": "true"
                }
}'

Insert data and validate the setup

Let’s validate the CDC messages are landing in the topic we created; we’ll insert some data into our PostgreSQL table and then consume a message from the topic.

Step 1: Connect to the database

kubectl run blog-postgresql-client --rm --tty -i --restart='Never' --namespace default --image docker.io/bitnami/postgresql:16.2.0-debian-12-r6 --env="PGPASSWORD=$POSTGRES_PASSWORD" \
      --command -- psql --host blog-postgresql -U postgres -d postgres -p 5432

Step 2: Insert data into the table

INSERT INTO public.simple VALUES (
'9cb52b2a-8ef2-4987-8856-c79a1b2c2f71',
'foo'
);

INSERT INTO public.simple values(
'9cb52b2a-8ef2-4987-8856-c79a1b2c2f73',
'bar'
);

Step 3: Consume the CDC message from the topic

local-rpk topic consume blog.public.simple --num 1

{
  "topic": "blog.public.simple",
  "key": "{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f71\"}",
  "value": "{\"before\":null,\"after\":{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f71\",\"foo\":\"foo\"},\"source\":{\"version\":\"2.5.2.Final\",\"connector\":\"postgresql\",\"name\":\"blog\",\"ts_ms\":1710755794795,\"snapshot\":\"false\",\"db\":\"postgres\",\"sequence\":\"[null,\\\"22407328\\\"]\",\"schema\":\"public\",\"table\":\"simple\",\"txId\":747,\"lsn\":22407328,\"xmin\":null},\"op\":\"c\",\"ts_ms\":1710755795117,\"transaction\":null}",
  "timestamp": 1710755795494,
  "partition": 0,
  "offset": 0
}

We’ve validated that the Debezium CDC messages with before-and-after values are landing in the topic. Don’t be concerned if before is not filled out in this example; if you take a look, the “op” is c, which means a new row was created in the table.

Expose Redpanda to the internet

Deploying all of the services locally using minikube is a great way to do local development and get hands-on experience without paying a cloud provider. However, for us to use Propel and create a Kafka Data Pool, Propel has to connect to our Redpanda topic. There are a number of ways that this can be accomplished. For the sake of simplicity, we are going to install a reverse proxy and create a TCP Tunnel to expose the Redpanda port to the internet. The reverse proxy we are going to use is called LocalXpose.

💡 To use the LocalXpose TCP Tunnel with a dedicated port, I had to pay $6. Other free services also do this, but you can’t control which port the tunnel is on. Redpanda has a limitation when creating the external NodePort that restricts it to ports 30000-32767.

Step 1: Install LocalXpose and kcat

https://localxpose.io/docs

brew install --cask localxpose
brew install kcat

Step 2: Start the TCP Tunnel

loclx tunnel tcp --port 31092 -t 127.0.0.1:9093

You can now see the running tunnels. Copy the domain name in From, e.g., us.localx.io. This will be used in step 3 and later when creating the Kafka Data Pool.

Step 3: Upgrade the Redpanda install and expose an external NodePort

Save the yaml below to external-dns.yaml, and replace the <domain> with the value from step 2.

external:
  enabled: true
  type: NodePort
  addresses:
    - <domain>

Upgrade Redpanda.

helm upgrade --install redpanda redpanda/redpanda --namespace redpanda --create-namespace --values external-dns.yaml --reuse-values --wait

Step 4: Forward the traffic to minikube

kubectl port-forward -n redpanda svc/redpanda-external 9093:9094

Step 5: Validate you can connect externally using kcat

kcat -L -b us.loclx.io:31092 -X sasl.username=blog -X sasl.password=changethispassword -X sasl.mechanism=SCRAM-SHA-256 -X security.protocol=SASL_PLAINTEXT

You can now tunnel traffic to Redpanda running in minikube, allowing Propel’s backend to connect and consume the CDC messages from the topic.

Create a Propel Kafka Data Pool

Login to the Propel console or create a new account. Then choose your environment and create a new Kafka Datapool, for this example I’m using the development environment.

Next, choose Add new credentials to configure the connection to our Redpanda cluster. (use the domain and port from the TCP Tunnel that you created above for the Bootstrap Servers)

Click Create and test Credentials

Ensure the topics are visible

Next, choose the topic blog.public.simple to start pulling in the CDC data.

Click Next to choose the primary timestamp.

Give the Data Pool a unique name.

Click Next to start syncing the data from the topic into Propel.

Once you see two records in the Datapool, you can click Preview Data.

Query the data

To query the data we have in Propel, you’ll need to create an Application. Follow the guide in the docs to create an application. Once you’ve created an Application, you can get a token, as seen in the image below. We’ll use that token in the subsequent API calls below.

Use the Data Grid API to query the data in the newly created Kafka Data Pool. Make sure to copy the token you created above and replace <TOKEN> with the value. You will also need to replace <DATAPOOL_ID> with the ID of the newly created Kafka Data Pool. Copy the curl command below and execute it from the command line.

curl 'https://api.us-east-2.propeldata.com/graphql' \
-H 'Authorization: Bearer <TOKEN>' \
-H 'Content-Type: application/json; charset=utf-8' \
-d '{
  "query": "query DataGridQuery($input: DataGridInput!) {
    dataGrid(input: $input) {
      headers
      rows
      pageInfo {
        hasNextPage
        hasPreviousPage
        endCursor
        startCursor
      }
    }
  }",
  "variables": {
    "input": {
      "dataPool": {
        "id": "<DATAPOOL_ID>"
      },
      "columns": [
        "_key",
        "_timestamp",
        "_propel_payload"
      ],
      "orderByColumn": 1,
      "sort": "DESC",
      "filters": [],
      "first": 5
    }
  }
}'

This will return a Data Grid with headers and rows.

{
  "dataGrid": {
    "headers": [
      "_key",
      "_timestamp",
      "_propel_payload"
    ],
    "rows": [
      [
        "{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f73\"}",
        "2024-03-18T09:56:37Z",
        "{\"before\":null,\"after\":{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f73\",\"foo\":\"bar\"},\"source\":{\"version\":\"2.5.2.Final\",\"connector\":\"postgresql\",\"name\":\"blog\",\"ts_ms\":1710755796308,\"snapshot\":\"false\",\"db\":\"postgres\",\"sequence\":\"[\\\"22407624\\\",\\\"22407680\\\"]\",\"schema\":\"public\",\"table\":\"simple\",\"txId\":748,\"lsn\":22407680,\"xmin\":null},\"op\":\"c\",\"ts_ms\":1710755796782,\"transaction\":null}"
      ],
      [
        "{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f71\"}",
        "2024-03-18T09:56:35Z",
        "{\"before\":null,\"after\":{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f71\",\"foo\":\"foo\"},\"source\":{\"version\":\"2.5.2.Final\",\"connector\":\"postgresql\",\"name\":\"blog\",\"ts_ms\":1710755794795,\"snapshot\":\"false\",\"db\":\"postgres\",\"sequence\":\"[null,\\\"22407328\\\"]\",\"schema\":\"public\",\"table\":\"simple\",\"txId\":747,\"lsn\":22407328,\"xmin\":null},\"op\":\"c\",\"ts_ms\":1710755795117,\"transaction\":null}"
      ]
    ],
    "pageInfo": {
      "hasNextPage": false,
      "hasPreviousPage": false,
      "endCursor": "eyJvZmZzZXQiOjJ9",
      "startCursor": "eyJvZmZzZXQiOjB9"
    }
  }
}

Update the source PostgreSQL database

If we update the PostgreSQL database with a new value for one of the rows, we’ll see that change reflected in the Propel Data Pool. Let’s execute the SQL statement below and re-run the GraphQL query we ran above.

# Connec to the database
kubectl run blog-postgresql-client --rm --tty -i --restart='Never' --namespace default --image docker.io/bitnami/postgresql:16.2.0-debian-12-r6 --env="PGPASSWORD=$POSTGRES_PASSWORD" \
      --command -- psql --host blog-postgresql -U postgres -d postgres -p 5432

UPDATE public.simple SET foo = 'baz' WHERE id = '9cb52b2a-8ef2-4987-8856-c79a1b2c2f73';

When we re-run the GraphQL query, we get the following response.

{
  "dataGrid": {
    "headers": [
      "_key",
      "_timestamp",
      "_propel_payload"
    ],
    "rows": [
      [
        "{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f73\"}",
        "2024-03-18T09:56:37Z",
        "{\"before\":null,\"after\":{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f73\",\"foo\":\"bar\"},\"source\":{\"version\":\"2.5.2.Final\",\"connector\":\"postgresql\",\"name\":\"blog\",\"ts_ms\":1710755796308,\"snapshot\":\"false\",\"db\":\"postgres\",\"sequence\":\"[\\\"22407624\\\",\\\"22407680\\\"]\",\"schema\":\"public\",\"table\":\"simple\",\"txId\":748,\"lsn\":22407680,\"xmin\":null},\"op\":\"c\",\"ts_ms\":1710755796782,\"transaction\":null}"
      ],
      [
        "{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f73\"}",
        "2024-03-18T17:38:53Z",
        "{\"before\":{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f73\",\"foo\":\"bar\"},\"after\":{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f73\",\"foo\":\"baz\"},\"source\":{\"version\":\"2.5.2.Final\",\"connector\":\"postgresql\",\"name\":\"blog\",\"ts_ms\":1710783532893,\"snapshot\":\"false\",\"db\":\"postgres\",\"sequence\":\"[\\\"22407880\\\",\\\"22408168\\\"]\",\"schema\":\"public\",\"table\":\"simple\",\"txId\":749,\"lsn\":22408168,\"xmin\":null},\"op\":\"u\",\"ts_ms\":1710783533138,\"transaction\":null}"
      ],
      [
        "{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f71\"}",
        "2024-03-18T09:56:35Z",
        "{\"before\":null,\"after\":{\"id\":\"9cb52b2a-8ef2-4987-8856-c79a1b2c2f71\",\"foo\":\"foo\"},\"source\":{\"version\":\"2.5.2.Final\",\"connector\":\"postgresql\",\"name\":\"blog\",\"ts_ms\":1710755794795,\"snapshot\":\"false\",\"db\":\"postgres\",\"sequence\":\"[null,\\\"22407328\\\"]\",\"schema\":\"public\",\"table\":\"simple\",\"txId\":747,\"lsn\":22407328,\"xmin\":null},\"op\":\"c\",\"ts_ms\":1710755795117,\"transaction\":null}"
      ]
    ],
    "pageInfo": {
      "hasNextPage": false,
      "hasPreviousPage": false,
      "endCursor": "eyJvZmZzZXQiOjJ9",
      "startCursor": "eyJvZmZzZXQiOjB9"
    }
  }
}

This shows that we have three rows in our Data Pool; it reflects the raw CDC messages that have been sent to the Kafka Data Pool. In order to use the data for analytics, we need to transform it to correctly represent the current state in the source database.

Create a real-time transformation to unpack the CDC JSON message

Now that we’re getting the stream of CDC messages to Propel and landing them in a Kafka Data Pool, we need to write some SQL to transform the messages based on the “op” type. The columns we are interested in using are id, _timestamp, and foo. Our SELECT statement below will query the source table blog-post and extract id and foo from _propel_payload.after. This will handle both the create and update operations. In the case of an update operation a duplicate row will be inserted and then de-duped by Propel. This will ensure that when we set id as the uniqueId column in the MaterializedView only one version of the id exists with the latest value from the source table.

SELECT 
    "blog-post"."_propel_payload.after.id" AS id,
    "_timestamp" AS timestamp,
    "blog-post"."_propel_payload.after.foo" AS foo
FROM 
    "blog-post" # the name of the Kafka Data Pool from above
WHERE 
    "blog-post"."_propel_payload.op" = 'c' OR 
    "blog-post"."_propel_payload.op" = 'u';

We will use the Propel API to create a Materialized View with the SQL above and set the uniqueId to the column id in the resulting destination table. Be sure to replace the <TOKEN> with the Application token we created earlier before running the curl command. Be sure to save the response from the curl command, as we’ll use some of the returned ids in later API calls.

curl --location "https://api.us-east-2.propeldata.com/graphql" \
-H "Content-Type: application/json" \
-H "Authorization: Bearer <TOKEN>" \
-d '{
  "query": "mutation MaterializedView($input: CreateMaterializedViewInput!) { createMaterializedView(input: $input) { materializedView { id sql destination { id uniqueName tableSettings { partitionBy primaryKey orderBy } } source { id uniqueName tableSettings { partitionBy primaryKey orderBy } } } } }",
  "variables": {
    "input": {
      "uniqueName": "Blog Post MV",
      "description": "Blog Post unpacked",
      "sql": "SELECT \"blog-post\".\"_propel_payload.after.id\" as id, \"_timestamp\" as timestamp, \"blog-post\".\"_propel_payload.after.foo\" as foo FROM \"blog-post\" WHERE \"blog-post\".\"_propel_payload.op\"='\''c'\'' OR \"blog-post\".\"_propel_payload.op\" ='\''u'\''",
      "destination": {
        "newDataPool": {
          "uniqueId": {
            "columnName": "id"
          }
        }
      },
      "backfillOptions": {
        "backfill": true
      }
    }
  }
}'

You should get a response like the one below.

{
  "data": {
    "createMaterializedView": {
      "materializedView": {
        "id": "MAT01HSY2DHN9HWKD1QAG0B9C115J",
        "sql": "SELECT \"blog-post\".\"_propel_payload.after.id\" as id, \"_timestamp\" as timestamp, \"blog-post\".\"_propel_payload.after.foo\" as foo FROM \"blog-post\" WHERE \"blog-post\".\"_propel_payload.op\"='c' OR \"blog-post\".\"_propel_payload.op\" ='u'",
        "destination": {
          "id": "DPO01HSY2DHTZZDRZP2BDZAMX6PKP",
          "uniqueName": "Data Pool for Materialized View MAT01HSY2DHN9HWKD1QAG0B9C115J",
          "tableSettings": {
            "partitionBy": null,
            "primaryKey": null,
            "orderBy": [
              "\"id\""
            ]
          }
        },
        "source": {
          "id": "DPO01HS98EGS0W40ARYHBR2SDVR5X",
          "uniqueName": "blog-post",
          "tableSettings": {
            "partitionBy": null,
            "primaryKey": null,
            "orderBy": null
          }
        }
      }
    }
  }
}

Query the Materialized View Data Pool

Now that we’ve created the MaterialzedView let’s query the Data Pool that was created along with it. Replace <token> with the token you created earlier, and use the response from the above curl command to get the destination data pool id. This is available at data.createMaterializedView.materializedView.destination.id in the JSON. Replace <datapool_id> with that value and run the curl command from the command line.


curl 'https://api.us-east-2.propeldata.com/graphql' \
-H 'Authorization: Bearer <TOKEN>' \
-H 'Content-Type: application/json; charset=utf-8' \
-d '{
  "query": "query DataGridQuery($input: DataGridInput!) { dataGrid(input: $input) { headers rows pageInfo { hasNextPage hasPreviousPage endCursor startCursor } } }",
  "variables": {
    "input": {
      "dataPool": {
        "id": "<DATAPOOL_ID>"
      },
      "columns": [
        "id",
        "foo"
      ],
      "orderByColumn": 1,
      "sort": "DESC",
      "filters": [],
      "first": 5
    }
  }
}'

The response you get should only have two rows and the updated value of baz for 9cb52b2a-8ef2-4987-8856-c79a1b2c2f73.

{
  "dataGrid": {
    "headers": [
      "id",
      "foo"
    ],
    "rows": [
      [
        "9cb52b2a-8ef2-4987-8856-c79a1b2c2f73",
        "baz"
      ],
      [
        "9cb52b2a-8ef2-4987-8856-c79a1b2c2f71",
        "foo"
      ]
    ],
    "pageInfo": {
      "hasNextPage": false,
      "hasPreviousPage": false,
      "endCursor": "eyJvZmZzZXQiOjF9",
      "startCursor": "eyJvZmZzZXQiOjB9"
    }
  }
}

Visualize the results

Let’s write a small node.js application that combines all of the Propel concepts and renders a table with the results from the MaterializedView. Instead of providing a token, we’ll use the Propel API to mint a token using the OAuth2 client credentials from the Propel Application we created. These are found in the Propel console within the Applications section, as seen below.

Step 1: OAuth2 client credentials

Step 2: Set environment variables

Replace the tokens with the values from the console, then paste and execute the commands.

export CLIENT_ID=<CLIENT_ID> \
export CLIENT_SECRET=<CLIENT_SECRET> \
export TOKEN_HOST=https://auth.us-east-2.propeldata.com \
export TOKEN_PATH=/oauth2/token

Step 3: Install the dependencies

npm install simple-oauth2 cli-table

Step 4: Copy the code
Copy the code below and replace <DATAPOOL_ID> with the data pool ID for the MaterializedView from above.

const { ClientCredentials } = require("simple-oauth2");
const Table = require("cli-table");

async function getToken() {
  const config = {
    client: {
      id: process.env.CLIENT_ID,
      secret: process.env.CLIENT_SECRET,
    },
    auth: {
      tokenHost: process.env.TOKEN_HOST,
      tokenPath: process.env.TOKEN_PATH,
    },
  };

  const client = new ClientCredentials(config);

  try {
    const accessToken = await client.getToken();
    return accessToken.token.access_token;
  } catch (error) {
    console.error("Access Token Error", error.message);
    return null;
  }
}

async function fetchDataGrid(accessToken) {
  const url = "https://api.us-east-2.propeldata.com/graphql";
  const query = `
    query DataGridQuery($input: DataGridInput!) {
      dataGrid(input: $input) {
        headers
        rows
      }
    }
  `;
  const variables = {
    input: {
      dataPool: { id: "<DATAPOOL_ID>" },
      columns: ["id", "foo"],
      sort: "ASC",
      first: 5,
      last: 5,
    },
  };

  try {
    const response = await fetch(url, {
      method: "POST",
      headers: {
        Authorization: `Bearer ${accessToken}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({
        query,
        variables,
      }),
    });

    const data = await response.json();
    return data.data.dataGrid;
  } catch (error) {
    console.error("Error fetching data grid:", error.message);
    return null;
  }
}

async function main() {
  const accessToken = await getToken();

  if (!accessToken) {
    console.error("Failed to retrieve access token");
    return;
  }

  const dataGrid = await fetchDataGrid(accessToken);

  if (!dataGrid) {
    console.error("Failed to fetch data grid");
    return;
  }

  const { headers, rows } = dataGrid;

  const table = new Table({
    head: headers,
  });

  rows.forEach((row) => {
    table.push(row);
  });

  console.log(table.toString());
}

main();

Step 5: Run the script

node script.js                                                                                                                 <aws:prod-propel>
┌──────────────────────────────────────┬─────┐
│ id                                   │ foo │
├──────────────────────────────────────┼─────┤
│ 9cb52b2a-8ef2-4987-8856-c79a1b2c2f71 │ foo │
├──────────────────────────────────────┼─────┤
│ 9cb52b2a-8ef2-4987-8856-c79a1b2c2f73 │ baz │
└──────────────────────────────────────┴─────┘

Wrap up

In wrapping up this post, it's important to recap the key skills and knowledge you've acquired. You have learned how to effectively stream PostgreSQL Change Data Capture (CDC) to a Propel Kafka DataPool, a significant step in managing real-time data.

Further, you've gained the ability to query this data, which opens up a wealth of opportunities for analysis and insights. This guide also walked you through the process of updating the source PostgreSQL database, ensuring that you can maintain the accuracy and relevance of your data.

One of the highlights of this guide was teaching you how to create a real-time transformation to unpack the CDC JSON message. This is a fundamental skill that elevates your data management capabilities, enabling you to manipulate and interpret data for various applications.

Finally, you've learned how to visualize the results. Visualization plays a crucial role in data analysis, making complex data more understandable, revealing trends and outliers, and contributing to more effective communication.

We hope you found the content both informative and practical and that you enjoyed the learning process. Propel is dedicated to empowering you with the tools and knowledge to transform your data into valuable insights. We look forward to supporting you in your future endeavors with Propel. Keep exploring and transforming your data!

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.

Deployment Notes: How we document changes to production

Mark Roberts — Wed, 17 May 2023 05:00:00 +0000

At Propel, our continuous integration (CI) and continuous delivery (CD) processes enables us to deliver features rapidly, multiple times during a single workday. With each release to production, we’ve found it useful to document

customer-facing changes contained within the release,
non-customer-facing changes within the release, and
our strategy for rolling back the release.

We call this documentation “deployment notes” or “release notes” and we publish these notes on every release to an internal Slack channel. For example, you can see a screenshot of deployment notes for one of our services below:

Why do we do this?

Customer communication

For changes that are not gated behind a feature flag, the deployment notes provide a record of when the changes were actually shipped to production, and for customers, this is what matters. Even with continuous delivery, there will be a little bit of delay between merging to main and releasing to production. So as soon as the deployment notes go out, we know we can reach out to customers and let them know that a new feature or bug fix is available.

Additionally, we often refer to deployment notes when we put together our customer-facing changelog entries for the month. The deployment notes are the record of truth, so we don’t have to ask engineers, “Did feature X ship already?” We just go to our internal Slack channel.

Internal communication

Occasionally, larger changes will require multiple deployments of one or more services. These changes may not be immediately customer-facing; however, we still want to document and communicate where we are in the process, so that there’s never any doubt about the state of production.

For example, Propel’s GraphQL API is actually a GraphQL supergraph combining multiple subgraphs using Apollo Router. In order to extend our API with a new subgraph X, we would

Deploy a new service for subgraph X.
Update and deploy Apollo Router to include subgraph X.

The deployment in step 1 contains no customer-facing changes; however, we still write the deployment notes. The deployment notes serve as a record that step 1 was completed, allowing us to continue to step 2.

Externalizing this knowledge means no single engineer has to keep the state of production in their head and everyone can see what was deployed. And that can be really freeing when engineers go on vacation or when collaborating with teammates.

Incident response

Bugs are a fact of life, and the only way to eliminate them is to stop writing software. But we’re not in the business of not writing software. We’re in the business of shipping features that delight customers! That’s why we invest so heavily in our testing and continuous integration processes, in order to mitigate the likelihood of shipping unintended behavior changes to production.

Despite this, there will be behavior changes, bugs, and even serious incidents that automated testing doesn’t catch. When this happens, it’s the responsibility of an on-call engineer to remediate the issue as quickly as possible in order to restore the customer experience.

Rollbacks

Most of the time, pagers go off and incidents are discovered shortly after a deployment, and the quickest path to remediation is a rollback. By “rollback”, we mean rolling back to the previous known working version of the software. This is such a common remediation strategy that we emphasize it in our deployment notes. The instructions we usually give are

Re-deploy the last successful release.

Of course, there are some rollbacks that could be more complicated than a re-deploy, like a schema change; but, as a rule, we try design all of our changes to be easy to roll back.

Reconstructing timelines

Sometimes a bug or behavior change is discovered which does not correspond to a recent deployment. In these cases, accurate deployment notes have been essential for tracking down the source of the change and reconstructing the incident timeline. Having this clarity allows us to fix issues and resolve incidents with confidence. This is a crucial part of our incident response process that we will describe in a future blog post.

How do we do this?

Deployment notes are a core feature of our CI/CD pipelines. Once we merge a change to main, our pipeline deploys the change to our development environment, where it runs our cluster tests. If the cluster tests succeed, it posts to Slack requesting deployment notes and approval for deploying to production.

Our pipelines are currently running as GitHub Actions and using GitHub environments, but they used to run on CircleCI, so you could build a similar solution on any provider.

Curation vs. Automation

There are many tools that can automate the creation of changelogs and release notes. For example, we follow the Conventional Commits specification, which means we prefix our commit messages with “fix” or “feat” to indicate a bug fix or new feature, and we include “BREAKING CHANGE” if we need to ship a breaking change.

To be clear, we consider Conventional Commits a best practice! We even automatically bump version numbers according to the commit history. But we’ve found the automated changelogs derived from Conventional Commits to be too low-level for use in deployment notes. And when it’s handled automatically, we’re not necessarily “thinking” about the changes going out.

That’s why, on every release, we have the engineer in charge of the release pause and write the deployment notes. This allows the engineer to communicate at a higher level the changes going out and to review once more the changes being released.

Template

We always follow the same Markdown template, which you are free to re-use for yourself. If one of the sections is not applicable for a particular deployment, we write “N/A”.

# Customer-facing changes

- Cool new feature X
- Cool new feature Y

# Non-customer-facing changes

- Refactored Z

# Rollback strategy

Re-deploy the last successful release.

Conclusion

Deployment notes are an effective tool for communicating customer-facing and non-customer-facing changes. They’re also useful for incident response, where the on-call engineer needs to know how to rollback changes. Although tools exist to automate the creation of changelogs, we’ve found it best to explicitly author our deployment notes and require human approval before releasing them to production.

How we migrated to Apollo Server 4

Mark Roberts — Wed, 22 Feb 2023 16:44:00 +0000

At Propel, it’s no secret we’re fans of GraphQL: our entire product is centered around serving in-product analytics and customer dashboards using this flexible and performant technology.

We chose GraphQL first because it empowers clients to fetch exactly the data they need, usually in a single round-trip to the server. This is great for building customer dashboards, which may include multiple time series, counters, and leaderboards in a single page. It’s part of what makes using Propel so snappy.

Second, the developer experience around GraphQL is amazing, and we’ve been fortunate to use some great tools from The Guild and Apollo in building our product. For example, we publish our GraphQL schemas to Apollo Studio, we embed the Apollo Studio Explorer in our docs, and our GraphQL API is actually built on top of Apollo Server.

I want to take a moment to share part of our journey on the server-side, since we just recently upgraded our GraphQL APIs from Apollo Server 3 to 4. Apollo did a great job documenting the expected effort in their “Migrating to Apollo Server 4” guide, but we still encountered (and solved!) some unexpected differences in Apollo Server 4.

💡 Apollo Server 3 is officially deprecated and will reach end-of-life October 22, 2023. Read along to see how we at Propel migrated to Apollo Server 4.

Basic changes

The apollo-server library is now @apollo/server

In Apollo Server 3, you depended on version 3.x of the apollo-server library and any supporting libraries, like apollo-server-errors or apollo-server-core.

In Apollo Server 4, everything is published in a single, namespaced library. You only have to depend on version 4.x of @apollo/server.

New `startStandaloneServer` method

In Apollo Server 3, the apollo-server library wrapped apollo-server-express and offered a “batteries-included” HTTP server for handling GraphQL requests.

In Apollo Server 4, the “batteries-included” approach is now startStandaloneServer. Migrating to this new method is straightforward and well-described in the migration guide. At Propel, we tried to use this new method; however, for reasons described below, we ultimately needed to use expressMiddleware.

The `gql` template literal tag has been removed

In Apollo Server 3, you could import the gql template literal tag directly from the apollo-server library. This template literal tag is provided by the graphql-tag library and allows parsing a GraphQL query string to an AST that can be used by Apollo and other GraphQL libraries.

import { gql } from 'apollo-server'

In Apollo Server 4, this template literal tag is no longer exported. In fact, it’s no longer depended on by the @apollo/server library at all. At Propel, we had a few usages of gql in our backend, so we had to add a dependency on graphql-tag and change our import in order to fix this. Easy fix. 👍

import { gql } from 'graphql-tag'

ApolloError is replaced by GraphQLError

In Apollo Server 3, the apollo-server-errors library defined an ApolloError class. At Propel, we sub-classed ApolloError in our backend to create a hierarchy of errors. Any time we needed to throw an error from a resolver and have it appear in the GraphQL response’s errors array, we would use this class. For example, one of our NotFoundErrors looked like this:

import { ApolloError } from 'apollo-server-errors'

export class NotFoundError extends ApolloError {
  readonly name = 'NotFoundError'
  readonly code = 'NOT_FOUND'

  constructor (resourceName: string) {
    super(`${resourceName} not found`, this.code)
    Object.defineProperty(this, 'name', { value: this.name })
  }

  // Members omitted…
}

In Apollo Server 4, ApolloError is removed. Instead, we should now use the GraphQLError class from the graphql library. We already had a dependency on graphql, so we just needed to update our sub-classes. After migrating, our NotFoundError looked like this:

import { GraphQLError } from 'graphql'

export class NotFoundError extends GraphQLError {
  readonly name = 'NotFoundError'
  readonly code = 'NOT_FOUND'

  constructor (resourceName: string) {
    super(`${resourceName} not found`, {
      extensions: {
        code: this.code
      }
    })
    Object.defineProperty(this, 'name', { value: this.name })
  }

  // Members omitted…
}

Additionally, Apollo Server 4 adds the ability to control HTTP response status codes by including an http extension in the GraphQLError. For example, throwing a GraphQLError like the following will cause the HTTP server to respond with 418 I’m a teapot. 🫖

throw new GraphQLError("I'm a teapot", {
  extensions: {
    code: 'TEAPOT',
    http: { status: 418 }
  }
})

Plugin changes

Plugin error-handling has changed

In Apollo Server 3, plugins could receive the incoming request object in their requestDidStart method. At Propel, we used this to build an authentication and authorization plugin (our “AuthPlugin”).

Our AuthPlugin determines if incoming requests are authentic and if they are authorized. If an incoming request is inauthentic or unauthorized, we throw an HttpQueryError with status code 403. This would cause Apollo Server to return a 403 Unauthorized HTTP response back to the user. It looked something like this:

import { ApolloServer } from 'apollo-server'
import { HttpQueryError } from 'apollo-server-core'

class AuthPlugin {
  requestDidStart ({ request, context }) {
    if (!isAuthenticated || !isAuthorized) {
      throw new HttpQueryError(403, 'Unauthorized')
    }
    context.authCtx = authCtx
  }
}

const server = new ApolloServer({
  typeDefs,
  plugins: [new AuthPlugin()],
  context: () => ({})
})

In Apollo Server 4, not only is HttpQueryError removed, but throwing an error from a plugin’s requestDidStart method results in a 500 Internal Server Error. We already knew we would need to migrate from HttpQueryError to GraphQLError, but how could we control the HTTP status code if Apollo Server was re-throwing plugin errors as 500s?

After some head-scratching, I opened an issue on Apollo Server’s GitHub repository. There, Apollo Server contributor @glasser shared a helpful suggestion: why not invoke our AuthPlugin from Apollo Server’s context function? Throwing from context would ensure we can control the HTTP status response without having to introduce more methods and error checks to our AuthPlugin (like unexpectedErrorProcessingRequest). With that suggestion in mind, we rewrote our AuthPlugin as follows:

import { ApolloServer } from '@apollo/server'
import { startStandaloneServer } from '@apollo/server/standalone'
import { GraphQLError } from 'graphql'

class AuthPlugin {
  getAuthContext (req) { // ← Method renamed (not a true plugin anymore).
    if (!isAuthenticated || !isAuthorized) {
      throw new GraphQLError('Unauthorized', {
        extensions: {
          code: 'UNAUTHORIZED',
          http: { status: 403 }
        }
      })
    }
    return authCtx
  }
}

const server = new ApolloServer({
  typeDefs,
  plugins: [] // ← AuthPlugin is omitted here.
})

const authPlugin = new AuthPlugin()

startStandaloneServer(server, {
  context: async ({ req }) => {
    // Plugin invoked inside of `context`. Read on for details.
    return { authCtx: await authPlugin.getAuthContext(req) }
  }
})

It’s a few extra lines, but it works well. 👍

Plugins no longer receive the request URL

In Apollo Server 3, the incoming request object passed to requestDidStart included a URL property. Our AuthPlugin would read the request’s URL and headers to determine if the request was authentic and authorized.

In Apollo Server 4, this URL property has been removed. I was curious about this change, so I asked about it on Apollo Server’s GitHub repository. Apollo Server contributor @glasser helpfully pointed out that the URL was still accessible in Apollo Server’s context function. Because we had already moved our AuthPlugin inside of context, we could accept the request object provided by startStandaloneServer and extract the URL from there. The fix was straightforward:

startStandaloneServer(server, {
  context: async ({ req }) => {
    // `req` here is an `http.IncomingMessage` with `url`.
    return { authCtx: await authPlugin.requestDidStart(req) }
  }
})

Server changes

HTTP-level health check endpoint removed

In Apollo Server 3, there was an HTTP-level health check endpoint included at /.well-known/apollo/server-health. This would return 200 OK for any GET request. While too simple to evaluate if the server process was truly healthy, it did indicate whether the Node.js process and HTTP server were running. At Propel, we deploy our GraphQL API servers as Fargate tasks behind Application Load Balancers (ALBs), so this was a natural choice to configure as our target groups’ health check endpoint:

https://your.server/.well-known/apollo/server-health

In Apollo Server 4, the HTTP-level health check endpoint has been removed. Apollo Server recommends issuing a simple GraphQL query instead. For example,

https://your.server/?query=%7B__typename%7D

Indeed, this is a more sophisticated health check than the HTTP-level health check in Apollo Server 3: by executing a GraphQL query, we can ensure that Apollo Server is working in addition to the Node.js process and HTTP server.

At Propel, two issues prevented us from adopting this:

Our entire GraphQL API is protected by our AuthPlugin, so by default the ALBs’ health check requests would be rejected with status code 403.
Apollo Server’s CSRF protection would require the ALB to send an “Apollo-Require-Preflight” header with each health check request, which is unsupported.

Instead, we followed Apollo Server’s “Swapping to expressMiddleware” guide. By swapping to expressMiddleware, we would get access to the underlying express HTTP server. This way, we can install a health check endpoint at a new URL, /.well-known/server-health. This health check endpoint is completely private, and so it’s safe to leave unauthenticated.

const app = express()

// Setup Apollo Server with `expressMiddleware`…

app.get('/.well-known/server-health', (_, res) => {
  // Response follows https://tools.ietf.org/html/draft-inadarei-api-health-check-01
  res.type('application/health+json')
  res.json({ status: 'pass' })
})

We followed the same response format as Apollo Server 3. Indeed, this is a very simple check; however, we plan to incorporate more checks in the future.

Conclusion

Migrating from Apollo Server 3 to Apollo Server 4 took us less than a week, and we received a lot of help from Apollo’s own migration guide as well as on Apollo Server’s GitHub issues tracker. If any of this interests you — be it GraphQL or in-product analytics — reach out, follow us on Twitter @PropelDataCloud, or subscribe to our email newsletter.

If you’d like to learn more about our GraphQL API, check out our GraphQL API documentation or request access!

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!

I know the customer-facing dashboard you want to build

Tyler Wells — Wed, 07 Dec 2022 15:00:00 +0000

I know the customer-facing dashboard you want to build. I know that your customers have asked for it. It's been on your roadmap for at least a quarter or more. The dashboard you want to build contains four KPIs in four boxes across the top of the screen. I know that below those boxes is a time-series graph and, to the right, a leaderboard. If you bring this to your engineering team, I know how they will want to build it.

Usually, this goes one of three ways.

Let's use our BI tools!

We can surely deliver in-product dashboards to our customers with our costly business intelligence software that we're using to provide internal reports. Davis Treybig, from Innovation Endeavours, wrote a great blog post on why this is a woefully inadequate solution, so I'll defer to his prose and encourage you all to head there to read more.

We can query the production database!

All that beautiful data is just sitting there in all its normalized glory. The backend team can build a new service to communicate directly with the database. The front-end team can write a few SQL statements with a smattering of well-intentioned JOINs. They'll send those to the nifty new service, and presto! You'll be rendering counters and graphs to all your heart's content.

I'm oversimplifying here because any team worth its operational mettle would ensure the service has observability, can page when things go wrong, and is well-tested and hardened for the rigors of production. They would, of course, ensure that any SQL received from the frontend in support of the dashboards wouldn't take down the production database if, by chance, that query took too long or locked a table or two. Better yet, the backend team would ensure only a small set of tested, performant queries are allowed to execute.

Introducing analytical workloads to your production databases - which were designed specifically for fast transactional writes - tends to inflict pain upon your end users, especially when those long analytical queries start hogging up database resources. If you enjoy the 2 AM cacophony of “somethings broken, somethings broken,” then, by all means, ship it.

We have a data pipeline (e.g., Kafka or Kinesis)!

Your data is already in motion, traversing the inner pathways of your cloud provider. The backend team just needs to land it in a customer-facing dashboard.

However, in all likelihood, your existing team isn’t running an analytical data store like Apache Kudu or ClickHouse (we used Elasticsearch at Twilio). If you have the headcount, you could hire backend engineers to spin up your analytics cluster; otherwise, you’ll pull from your existing humans to stand this up and carry the operational burden. Once your pipeline is set up, depending on your volume of data, you’ll most likely want to pre-aggregate the data to achieve predictable read performance.

So let’s build out another set of services to run Spark or an equivalent technology that will generate the buckets of aggregated data. Now, we’ll need an API to front the newfound insights stored in our services mentioned above. Node, Java, Go, take your pick: design your API and connect it up! Bring in the frontend folks to create those pretty analytical visuals that your UX designer vetted with the customer focus group and get those dashboards some data.

Reflect

Let's pause for a second and quickly summarize the last few months of your efforts. There is a new team in the org chart, two or three new backend services, a couple of new on-call rotations, an API, new SLOs/SLIs, QA cycles, UX/UI, and with that, you’ve shipped the analytics, and dashboards your customers have been asking for!

Is everyone excited to start building now? It’s not hard to understand why so many Product Managers we’ve talked to over the last two years have had to continually postpone building the in-product dashboards their customers so desperately want. Engineering leadership is abhorrent in taking on the additional backlog ceremonies and operational burdens. Organizationally, it’s a costly endeavor of both time and money, and when the only option is to “build”, the budget item to move forward will likely be at the bottom of the list.

Repeat

In multiple senior engineering leadership roles throughout my career, I’ve been part of building and shipping the approaches described above. I’ve written the job descriptions for hiring the headcount and slogged through the toil that came with delivering these critical roadmap items. During my tenure at Twilio, my team delivered the first “insights” data product that allowed our customers to understand how their WebRTC voice clients were performing. Internally we’d been using all of that data for network performance debugging and to provide answers to our customers when they opened tickets with our customer support team. As minutes increased and more critical use cases were built on the voice platform, our customers demanded that the same data be available through the console so they could self-diagnose call quality issues.

Shortly after launching, a number of the other product lines within Twilio, like Chat, Messaging, and Video, all wanted to ship their own version of insights. However, there wasn’t a platform they could buy or leverage internally; just like the teams in my organization, they had to build their own services similar to what was described above. The teams had to opt into the complexity and expense of building and operating the services to deliver insights to their customers. This was not the core competency for many teams, yet here they were: hiring and “drawing the owl”, as we liked to say. This “forced acceptance” never sat well with me.

Build Different

With Propel, the toil described above no longer needs to be suffered; the proverbial can no longer needs to be kicked down the road. With Propel, you can sleep well, knowing there won’t be any new services to operate in production and no expensive new employees to compete for. The data you’ve painstakingly landed in your warehouses and your existing team is all you need, freeing you up to get on with the rest of your roadmap.

Welcome to the world of serverless analytics - one that simplifies your architecture, where you can build faster, and deploy in days instead of months.

Like an extension of your team, you can do more with less, and your customers get the self-service magic they’ve been looking for. One Head of Product we spoke to said it well - “Propel is making the impossible possible.” It’s time to change how teams build and deliver customer-facing analytics. Your customers have been asking for it - it’s time to build different.

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: Propel

How to stream PostgreSQL CDC to Kafka and use Propel to get an instant API

What are we trying to do?

How PostgreSQL CDC with Debezium works

What is the Propel Kafka Datapool?

Setting up Minikube, PostgreSQL, Kafka, Kafka Connect, and Debezium

Setting up Minikube

Setting up PostgreSQL

Setting up Kafka (Redpanda)

Setting up Kafka Connect

Setting up the Debezium PostgreSQL Connector

Insert data and validate the setup

Expose Redpanda to the internet

Create a Propel Kafka Data Pool

Query the data

Update the source PostgreSQL database

Create a real-time transformation to unpack the CDC JSON message

Query the Materialized View Data Pool

Visualize the results

Wrap up

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

Deployment Notes: How we document changes to production

Why do we do this?

Customer communication

Internal communication

Incident response

Rollbacks

Reconstructing timelines

How do we do this?

Curation vs. Automation

Template

Conclusion

How we migrated to Apollo Server 4

Basic changes

The apollo-server library is now @apollo/server

New startStandaloneServer method

The gql template literal tag has been removed

ApolloError is replaced by GraphQLError

Plugin changes

Plugin error-handling has changed

Plugins no longer receive the request URL

Server changes

HTTP-level health check endpoint removed

Conclusion

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

New `startStandaloneServer` method

The `gql` template literal tag has been removed