Snowflake connector

Extracts a Snowflake table into canonical events by unloading, never row-fetching: each sync creates a per-sync temporary internal stage, runs COPY INTO @stage … FILE_FORMAT=(TYPE=PARQUET) selecting only the columns your mapping references (incremental syncs push the cursor predicate down), downloads the staged files, and drops the stage — even when the sync fails. The connector never writes to your tables: the only server-side object it creates is that temporary stage, and it is always removed.

Authentication is key-pair only — no passwords. Tessera generates the RSA-2048 key pair for you and holds the private key encrypted; you register the public key on a dedicated Snowflake user.

Prerequisites

• An API key with the admin scope.
• A Snowflake table holding event-shaped rows: an entity id column, a timestamp column, attribute columns.
• Permission to create a user/role and grant warehouse + table access.
• The account identifier, not a URL. Use orgname-accountname, or a legacy locator such as xy12345.us-east-2.aws. Tessera connects to <account>.snowflakecomputing.com, which must resolve to public addresses, so PrivateLink-only accounts are not reachable. A refused account fails authenticate with failure: configuration. PUT /mapping returns 400 invalid_argument with a field_violation on config/account, and a sync fails with extraction_failed.

Create the credential (least privilege)

Create a dedicated user and role that can use one warehouse, read the one table, and create the per-sync temporary stage it unloads through — nothing else:

CREATE ROLE TESSERA_READER;
GRANT USAGE ON WAREHOUSE ANALYTICS_WH TO ROLE TESSERA_READER;
GRANT USAGE ON DATABASE PLAYBACK TO ROLE TESSERA_READER;
GRANT USAGE ON SCHEMA PLAYBACK.PUBLIC TO ROLE TESSERA_READER;
GRANT SELECT ON TABLE PLAYBACK.PUBLIC.EVENTS TO ROLE TESSERA_READER;
GRANT CREATE STAGE ON SCHEMA PLAYBACK.PUBLIC TO ROLE TESSERA_READER;
CREATE USER TESSERA_SVC DEFAULT_ROLE = TESSERA_READER;
GRANT ROLE TESSERA_READER TO USER TESSERA_SVC;

CREATE STAGE is required because every sync runs CREATE TEMPORARY STAGE in the configured database and schema (temporary stages need the privilege too); the role owns that stage, so COPY INTO, LIST, GET and DROP STAGE need nothing more. DESCRIBE TABLE and the unload's SELECT are covered by SELECT on the table. No other object is created and the table is never written.

Create the source

Supply only the username — Tessera mints the key pair (WP-C4: key-pair auth is the only mode; passwords are never accepted):

{
  "name": "playback-events",
  "kind": "snowflake",
  "config": {
    "account": "myorg-account1",
    "warehouse": "ANALYTICS_WH",
    "database": "PLAYBACK",
    "schema": "PUBLIC",
    "table": "EVENTS",
    "role": "TESSERA_READER"
  },
  "credentials": {
    "username": "TESSERA_SVC"
  }
}

The create response carries a reveal-once setup object — the RSA public key and the exact statement to run:

ALTER USER TESSERA_SVC SET RSA_PUBLIC_KEY='MIIBIjANBgkq…';

Run it in Snowflake, then POST /sources/{id}/test-connection. Neither key half is ever readable again: subsequent GET /sources/{id} responses expose only credentials_set: true. Key rotation: replace the source credentials (PATCH /sources/{id} with {"credentials": {"username": "TESSERA_SVC"}}) — Tessera mints a fresh key pair and returns the new ALTER USER statement once. The new private key replaces the stored one immediately, but Snowflake keeps the previous public key until you run that statement. Connection tests and syncs fail with authentication in between, so run it straight away and then check POST /sources/{id}/connection-diagnostics. Tessera cannot use a password, a session token or an externally issued key for this connector.

The config object validates against the connector's registered schema:

{
  "account": "myorg-account1",
  "warehouse": "ANALYTICS_WH",
  "database": "PLAYBACK",
  "schema": "PUBLIC",
  "table": "EVENTS"
}

Test the connection

POST /sources/{id}/connection-diagnostics runs four checks in order, each tagged with the diagnostic stage it verifies:

Check	Stage	Verifies
authenticate	authentication	key-pair (JWT) authentication — a failure detail includes the exact ALTER USER statement to re-run
warehouse_usable	authorization	the configured warehouse accepts a probe statement
table_readable	schema	the table exists and the role can describe it
stage_writable	authorization	the role can create a temporary stage in the schema (a probe stage is created and dropped immediately)

A failed check carries a typed failure cause and a detail naming the exact grant that fixes it; later checks report not_attempted. Snowflake reports a missing table and a table the role may not see the same way, so that case names both fixes. Details never repeat Snowflake's message or any key material. The response's checked_at is the server time of the test, which is also recorded in the audit log as source.test_connection. POST /sources/{id}/test-connection runs the same checks and returns only their names, statuses and details.

How extraction works

1. CREATE TEMPORARY STAGE tessera_sync_{sync_id} — a per-sync internal stage.
2. COPY INTO @tessera_sync_{sync_id} FROM (SELECT <mapped columns> FROM … [WHERE cursor > watermark]) FILE_FORMAT=(TYPE=PARQUET) HEADER=TRUE MAX_FILE_SIZE=268435456 — only mapped columns leave Snowflake.
3. Staged files download one at a time (bounded local buffering) and stream into the landed snapshot.
4. DROP STAGE — always, in a finally path, even when the sync fails.

Incremental syncs require a mapping cursor on a TIMESTAMP_TZ/TIMESTAMP_LTZ/TIMESTAMP_NTZ/DATE or integer-safe NUMBER(p,0) column; the watermark advances only when a sync commits.

Type map

Column types map onto canonical attribute types exactly as follows; mapping a column whose type is unsupported fails PUT /sources/{id}/mapping naming the column and its type.

Snowflake type	Canonical type	Notes
number(p<=18,0)	integer	NUMBER(p,0) with precision ≤ 18: exact 64-bit integers.
number	number	Any other NUMBER(p,s) (scale > 0 or precision > 18): converted to float64 (values beyond float64 precision convert lossily). An entity or event id column keeps its exact decimal value.
float	number	IEEE-754 float64, as-is.
boolean	boolean	Booleans, as-is.
varchar	string	UTF-8 strings, as-is (≤ 4096 chars per 002 §2). STRING/TEXT are synonyms.
char	string	Fixed-length strings, as returned by Snowflake.
timestamp_tz	timestamp	Converted to RFC 3339 UTC.
timestamp_ltz	timestamp	Converted to RFC 3339 UTC (session-local instants are absolute).
timestamp_ntz	timestamp	Naive wall-clock time: the mapping's IANA timezone is required — without it, rows carrying a value are rejected.
date	timestamp	Midnight UTC of the date (YYYY-MM-DDT00:00:00Z).
array	array	Arrays of strings or numbers only (≤ 64 items per 002 §2); a row whose array mixes element types (or nests) is rejected.
variant	unsupported	Cannot be mapped (flatten upstream); PUT /mapping fails naming the column.
object	unsupported	Cannot be mapped (flatten upstream); PUT /mapping fails naming the column.
geography	unsupported	Cannot be mapped; PUT /mapping fails naming the column.
binary	unsupported	Cannot be mapped; PUT /mapping fails naming the column.

DATE maps to midnight UTC by convention; if your dates carry local-day semantics, expose a naive TIMESTAMP_NTZ column instead and set the mapping timezone.

Worked example

A media-streaming table PLAYBACK.EVENTS with VIEWER_ID VARCHAR, KIND VARCHAR, TS TIMESTAMP_TZ, BITRATE NUMBER(9,0), TAGS ARRAY maps as: entity {constant: "viewer", id_column: "VIEWER_ID"}, event type from KIND (play.start, play.stall, play.finish), timestamp TS, attributes BITRATE, TAGS, cursor TS.