--- title: "Load SaaS Data to Snowflake: Key-Pair Auth, SQL API & VARIANT Columns" slug: load-saas-data-to-snowflake-key-pair-auth-sql-api-variant-columns date: 2026-11-01 author: Sidharth Verma categories: [Product Updates, Guides] excerpt: "Load SaaS data into Snowflake with Truto sync jobs, using key-pair JWT auth, the SQL API, insertRows, and PARSE_JSON for VARIANT columns." tldr: "Truto's Snowflake datastore writes SaaS records over the SQL API with key-pair JWT auth. Use insertRows for flat columns, and executeStatement with PARSE_JSON (or a MERGE) for VARIANT columns and re-run-safe writes." canonical: https://truto.one/blog/load-saas-data-to-snowflake-key-pair-auth-sql-api-variant-columns/ --- # Load SaaS Data to Snowflake: Key-Pair Auth, SQL API & VARIANT Columns To load SaaS integration data into Snowflake programmatically, use the Snowflake SQL API with key-pair authentication, and write JSON payloads into `VARIANT` columns with `PARSE_JSON`. Truto's Snowflake datastore does the SQL API and JWT work for you, so a sync job can write records with `insertRows` or `executeStatement`. If you are building B2B SaaS, your product's value is directly tied to the data it can ingest. Your customers expect you to pull records from their existing CRMs, HRIS platforms, applicant tracking systems, and accounting tools into your application. You need a reliable way to sync this SaaS data to Snowflake. You do not want to build and maintain 50 custom data pipelines, nor do you want to force customer-facing integrations through an internal ELT tool designed for offline analytics. Snowflake is one of the most common cloud data warehouses for this kind of workload, so the requirement is simple to state: build the integrations, and get the data into Snowflake safely. This guide shows how to architect a multi-tenant data sync with Truto's sync job runtime. We cover how to configure Snowflake as a datastore destination, how key-pair authentication works, what the client does on retries and failures, and how to handle schema drift with `VARIANT` columns. ## The Challenge of Syncing SaaS Data to Snowflake at Scale Bulk data extraction in a B2B SaaS context is fundamentally different from internal data warehousing. When you pull data for internal business intelligence, you control the source, the credentials, and the destination. When you extract data on behalf of your customers, you control none of those things. **The Multi-Tenant Multiplier Effect** If you have 1,000 customers and offer 50 integrations, you do not have 50 data pipelines. You have 50,000 unique integration states. Each of those connections has its own OAuth token lifecycle, its own API rate limits, and its own schema customizations. Many engineering teams attempt to solve this by deploying traditional ETL tools. However, these platforms are built for single-tenant internal data movement. Fivetran positions itself as a fully managed data movement platform for Snowflake, and it charges based on Monthly Active Rows (MAR). Consumption-based pricing grows with data volume, which gets expensive for B2B SaaS companies that sync data for every customer. Alternatively, teams build bespoke pipelines using the Snowflake Connector for Python. This leads to large maintenance overhead. You end up writing custom code to handle schema drift, manage OAuth token refreshes, and track incremental sync watermarks across thousands of tenants. This is a challenge we explore deeply in our guide to [handling bulk data extraction and ETL workflows through unified APIs](https://truto.one/handling-bulk-data-extraction-and-etl-workflows-through-unified-apis/). To build a scalable, multi-tenant sync to Snowflake, you need a declarative pipeline that understands third-party API pagination, manages state, and writes directly to Snowflake's APIs. ## Securing Programmatic Access: Snowflake Key-Pair Authentication Password authentication is a poor fit for API-driven workflows. Passwords in environment variables or connection strings are easy to leak, and the Snowflake SQL API does not accept a plain username and password. Key-pair authentication is the standard choice for a service that writes to Snowflake. Key-pair authentication uses an RSA public/private key pair to verify identity. You assign the public key to the Snowflake user. The client uses the private key to sign a JSON Web Token (JWT) and sends it in the `Authorization` header with `X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT`. A Snowflake datastore in Truto holds the connection details. You create it with `POST /datastore`: ```json { "type": "snowflake", "label": "warehouse-prod", "config": { "account": "myorg-myaccount", "user": "TRUTO_LOADER", "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----", "warehouse": "LOAD_WH", "database": "PROD_DB", "schema": "INTEGRATIONS", "role": "TRUTO_LOADER_ROLE" } } ``` - `account` is `orgname-account_name` or a legacy account locator. - `private_key` is an unencrypted RSA PEM key, PKCS#8 or PKCS#1. Truto encrypts it at rest and never returns it in API responses. - `warehouse`, `database`, `schema` and `role` are case-sensitive. - Add `"validation_methods": ["testConnection"]` to the request to run `SELECT 1` before the datastore is saved. Validation never writes to a table. ```mermaid sequenceDiagram participant SyncJob as Sync Job Run (V4) participant Datastore as Datastore Service participant Client as Snowflake Client participant Snowflake as Snowflake SQL API SyncJob->>Datastore: Get datastore (cached per run) Datastore-->>SyncJob: Config with decrypted private key SyncJob->>Client: createDatastoreClient() Client->>Client: Sign key-pair JWT (valid 1 hour) Client->>Snowflake: POST /api/v2/statements?requestId=...&retry=true Snowflake-->>Client: 200 result, or 202 + statementHandle Client->>Snowflake: GET /api/v2/statements/{handle} (poll while 202) Client-->>SyncJob: Rows, stats, or a failure ``` When a sync job runs, the runtime fetches the datastore once per run and builds a Snowflake client from it. The client signs a JWT with `iss = ACCOUNT.USER.SHA256:`. The token is valid for one hour, and the client signs a new one after 50 minutes. Your application code never handles the private key or the token. **Key takeaway:** Do not use static passwords for programmatic warehouse access. Use key-pair authentication, and keep the private key in one encrypted place. ## How Truto Sends Statements to the Snowflake SQL API Modern serverless and API-driven architectures use the Snowflake SQL API instead of a driver with a persistent TCP connection. The core endpoint is `POST /api/v2/statements`, which takes SQL text and bind variables over HTTP. Writing over HTTP has a distributed systems problem: network failures. Snowflake can run an `INSERT`, and the HTTP response can drop before it reaches the caller. A naive retry then runs the statement again and creates duplicate rows. Snowflake solves this at the request level with the `requestId` and `retry` query parameters. If a request is resent with the same `requestId` and `retry=true`, Snowflake does not run the statement a second time. Truto's Snowflake client sends this request body for each statement: ```json { "statement": "INSERT INTO \"RAW_CONTACTS\" (\"REMOTE_ID\", \"NAME\") VALUES (?, ?)", "timeout": 60, "warehouse": "LOAD_WH", "database": "PROD_DB", "schema": "INTEGRATIONS", "role": "TRUTO_LOADER_ROLE", "bindings": { "1": { "type": "TEXT", "value": "003xx000004TmiQ" }, "2": { "type": "TEXT", "value": "Acme Corp" } } } ``` The client behaves as follows: - **Request-level retries.** Each statement gets a new random `requestId`. The client retries a network error, a 429 or a 5xx with the same `requestId` and `retry=true`, so a retry does not run the statement twice. - **Polling.** When Snowflake answers 202, the client polls `GET /api/v2/statements/{handle}`. - **Timeout and cancel.** A statement runs with a 60-second `timeout`, the same as the poll budget. When the polls give up, the client cancels the statement, so the statement cannot commit after the sync job counted it as failed. - **Bindings.** Every value is sent as a `TEXT` binding, and Snowflake casts it to the column type. Objects are sent as JSON text. > [!WARNING] > The `requestId` protects against duplicates only inside one call. If a sync job run fails and you run the sync job again, the new run sends new statements with new request IDs. Plain `INSERT` statements then add the rows again. Use a `MERGE` keyed on the record ID (see below) when duplicates matter. This approach is conceptually similar to the challenges faced when streaming data to other warehouses, a topic we cover in our guide on [syncing SaaS data to BigQuery streaming inserts and deduplication](https://truto.one/sync-saas-data-to-bigquery-streaming-inserts-deduplication/). ## Handling Schema Drift with VARIANT Columns and PARSE_JSON The most brittle part of any SaaS integration pipeline is the schema mapping layer. Third-party APIs change. Vendors add new fields, deprecate old ones, and allow their users to create unlimited custom fields. If you map a Salesforce Contact or a Workday Employee only to strict relational columns in Snowflake (`first_name VARCHAR`, `last_name VARCHAR`, `title VARCHAR`), every new field needs a schema change. When a customer adds a custom `industry_vertical` field in their CRM, a write that includes that field fails because the destination column does not exist. Snowflake's `VARIANT` data type addresses this. A `VARIANT` column stores semi-structured data such as JSON. A single `VARIANT` value can hold up to 128 MB. This enables a "schema-on-read" design: you load the raw or lightly mapped JSON record into Snowflake, and parse it at query time. To insert a JSON string into a `VARIANT` column, wrap the binding in `PARSE_JSON()`. If you bind a JSON string straight to a `VARIANT` column, Snowflake stores a string value, not a traversable JSON object. Snowflake does not accept `PARSE_JSON` inside a `VALUES` clause, so use `INSERT ... SELECT`: ```sql -- Insert an API record into a VARIANT column INSERT INTO RAW_CONTACTS (INTEGRATED_ACCOUNT_ID, REMOTE_ID, RAW_DATA, UPDATED_AT) SELECT ?, ?, PARSE_JSON(?), ?; ``` Once the data is stored as a `VARIANT`, your analytics engineers can query it with path notation: ```sql SELECT remote_id, raw_data:name::string AS company_name, raw_data:custom_fields.industry_vertical::string AS industry, updated_at FROM raw_contacts WHERE integrated_account_id = 'acc_12345'; ``` With a `VARIANT` column for the raw record, a new upstream field no longer breaks the write. The sync job fetches the record from the unified API, maps it with JSONata, and writes it to Snowflake. ### insertRows vs. executeStatement Truto's Snowflake datastore has three methods: `testConnection`, `insertRows` and `executeStatement`. | Method | Parameters | Use it for | |---|---|---| | `testConnection` | none | `SELECT 1`, to check credentials | | `insertRows` | `table`, `rows`, `batch_size` | Flat columns. Rows go in as `INSERT … VALUES` with `TEXT` bindings. | | `executeStatement` | `statement`, `bindings` | `PARSE_JSON` into `VARIANT`, `MERGE`, or any other SQL. Returns rows keyed by column name. | `insertRows` details that matter in production: - **Identifiers are upper-cased and quoted.** A column `remote_id` becomes `"REMOTE_ID"`, which resolves the same way as an unquoted name. `table` can be `TABLE`, `SCHEMA.TABLE` or `DATABASE.SCHEMA.TABLE`. - **Objects become JSON text.** A `VARIANT` column then stores a string. For `VARIANT`, use `executeStatement` with `PARSE_JSON`. - **At most 10000 bindings per statement.** The client lowers the rows per statement to fit this limit. The default `batch_size` is 1000 rows. - **Each statement commits on its own.** If a later statement fails, the rows from earlier statements stay inserted, and the error says how many rows went in before the failure. ## Architecting the Pipeline with Truto Sync Jobs Building this architecture from scratch means you must paginate through third-party APIs, manage watermarks for incremental syncs, and run batch writes to the Snowflake SQL API. Truto's sync job runtime (V4) handles this through a resource graph. Each node has a `type`, and `depends_on` names its parent. Instead of a bespoke Python script for every integration, you define the sync logic in JSON. ### 1. State Management and Incremental Pulls A `get_state` node reads the last watermark (for example `last_synced_at`) from the sync job run's state, and an `add_context` node exposes it to the request nodes. The request then asks the unified API only for records that changed since that time. An `update_state` node writes the new watermark when the run completes, gated on `sync_job_run.status = 'completed'`. A failed run does not advance the watermark, so the next run replays from the last successful sync. ### 2. Rate Limits for Direct Unified API Callers If your own code calls Truto's Unified API directly, Truto passes an upstream HTTP 429 through to you with IETF `ratelimit-limit`, `ratelimit-remaining` and `ratelimit-reset` headers, and your code decides when to retry. ### 3. OAuth Token Refreshes Syncing large datasets can take a long time, and source OAuth access tokens expire. Truto refreshes the integrated account's token before it expires, and a mutex makes sure that only one refresh runs at a time for the same account. We describe this pattern in our guide to [the architecture of reliable token refreshes](https://truto.one/oauth-at-scale-the-architecture-of-reliable-token-refreshes/). ### 4. Declarative Snowflake Destinations A JSONata `transform` node maps the unified API response into the row shape the table expects. Truto does not change the data beyond that JSONata mapping. Here the transform keeps two flat fields and serializes the whole record for the `VARIANT` column: ```json { "type": "transform", "name": "contacts-to-rows", "depends_on": "list-crm-contacts", "config": { "expression": "resources.crm.contacts.{ \"remote_id\": id, \"updated_at\": updated_at, \"raw_json\": $string($) }" } } ``` The datastore destination iterates the rows with `loop_on: "payload.records"`. Inside each iteration, `payload.records` is the current row. The `MERGE` updates a row that already exists and inserts a new one, so a re-run of the sync job does not create duplicates: ```json { "type": "destination", "name": "write-contacts-to-snowflake", "destination_type": "datastore", "method": "executeStatement", "loop_on": "payload.records", "run_if": "$exists(payload.records) and $type(payload.records) = \"array\" and $count(payload.records) > 0", "config": { "id": "{{args.snowflake_datastore_id}}", "config": { "statement": "MERGE INTO RAW_CONTACTS t USING (SELECT ? AS INTEGRATED_ACCOUNT_ID, ? AS REMOTE_ID, PARSE_JSON(?) AS RAW_DATA, ? AS UPDATED_AT) s ON t.INTEGRATED_ACCOUNT_ID = s.INTEGRATED_ACCOUNT_ID AND t.REMOTE_ID = s.REMOTE_ID WHEN MATCHED THEN UPDATE SET t.RAW_DATA = s.RAW_DATA, t.UPDATED_AT = s.UPDATED_AT WHEN NOT MATCHED THEN INSERT (INTEGRATED_ACCOUNT_ID, REMOTE_ID, RAW_DATA, UPDATED_AT) VALUES (s.INTEGRATED_ACCOUNT_ID, s.REMOTE_ID, s.RAW_DATA, s.UPDATED_AT)", "bindings": [ "{{args.integrated_account_id}}", "{{payload.records.remote_id}}", "{{payload.records.raw_json}}", "{{payload.records.updated_at}}" ] } }, "resources_to_persist": ["contacts-to-rows"] } ``` `bindings` is a plain array of values. The client turns each value into a `TEXT` binding. **Always pass record values through `bindings`, never template them into `statement`.** The runtime replaces placeholders in the whole method config, so a record field templated into `statement` runs as SQL. The `MERGE` runs one statement per record. For high volume with flat columns, `insertRows` writes up to 1000 rows per statement instead: ```json { "type": "destination", "name": "append-contacts-to-snowflake", "destination_type": "datastore", "method": "insertRows", "config": { "id": "{{args.snowflake_datastore_id}}", "config": { "table": "INTEGRATIONS.RAW_CONTACTS_STAGE", "rows": "{{payload.records:json}}", "batch_size": 1000 } }, "resources_to_persist": ["contacts-to-rows"] } ``` `insertRows` stores `raw_json` as text. A Snowflake task can then run `PARSE_JSON` and a `MERGE` from the staging table into the final `VARIANT` table. When a Snowflake write fails, the sync job sends a `sync_job_run:record_error` webhook event with the error, including how many rows earlier statements inserted. > [!TIP] > Give the datastore a dedicated `role` with only the privileges the sync job needs: `USAGE` on the warehouse, database and schema, and `INSERT` (plus `SELECT` and `UPDATE` for a `MERGE`) on the target tables. Size the warehouse for the batch load. Principle of least privilege applies to API integrations. ## Strategic Wrap-Up Syncing multi-tenant SaaS data to Snowflake does not require a consumption-priced ETL vendor or thousands of lines of brittle Python code. The Snowflake SQL API, key-pair authentication and `VARIANT` columns give you a scalable data pipeline that tolerates schema drift. Truto's sync job runtime handles OAuth refreshes, incremental watermarks, and the Snowflake client's JWT signing, request-level retries and statement timeouts. You choose `insertRows` or a `MERGE` through `executeStatement`, depending on whether a re-run must be duplicate-free. The result is an integration layer that delivers customer data into Snowflake, so your engineering team can focus on core product features instead of babysitting data pipelines. > Stop wasting engineering cycles on brittle ETL pipelines. Partner with Truto to build declarative, multi-tenant syncs directly into Snowflake. > > [Talk to us](https://cal.com/truto/partner-with-truto)