Snowflake, BigQuery, Postgres, MySQL and S3

Snowflake, BigQuery, Postgres, MySQL and S3 each work in both directions:

• As an output. Alert rules send rows to them. Each rule writes to its own table, or its own object in S3, using the rule’s write mode. See Sheets, warehouses and webhooks for what each kind of rule sends.
• As an input. They fill a dataset. The warehouses run one read-only query; S3 reads one object. Each sync replaces the dataset with the result.

Outputs

Replace and append everywhere; upsert on Snowflake, BigQuery, Postgres and MySQL

Inputs

A SELECT query, or an S3 object

Credentials

Typed by you on the integration page, stored encrypted

Set up by

Jason, from Connect Input or Connect Output

Setting one up

1. Connect it. Open Integrations → Outputs → Connect Output (or Inputs → Connect Input), choose the service and say what it is for, for example “Keep a Snowflake table in sync with a pipeline’s results” or “Load the URLs to track from a Postgres query”. Jason creates the integration and links to its page.
2. Fill in the connection. On the Settings tab, type the fields below. Passwords, keys and service account keys are secret fields: “Only you enter this — Jason never sees it.”
3. Save. Jsonify connects with your settings and runs the check listed for each service. A failure shows “Couldn’t turn this on:” with the database’s answer, and nothing changes.
4. Test. Test in the header runs the same check again at any time and records it on the Activity tab.
5. Add a rule (outputs). On the output’s Rules tab press Add alert rule, or let Jason add it. Choose Rows and the Table or Object name.

A secret field that already holds a value reads “Set — enter a new value to replace it.” Leave it empty to keep what is stored. Jsonify never sends a stored credential back to the browser.

How outputs write

Every warehouse output follows the same rules:

• One table per rule. The table is named after the rule’s Table field, or the rule’s name when that is blank, made into a valid name: spaces and punctuation become underscores. Snowflake names are upper case; Postgres and MySQL names are lower case.
• Jsonify creates the table. On the first delivery, with a column for every field, typed from the values. Later deliveries add any new columns; existing columns are never dropped.
• A synced-at column. Every row carries _jsonify_synced_at (_JSONIFY_SYNCED_AT in Snowflake), the time it was written.
• Replace never shows a half-written table. Each service loads the new rows first and swaps them in, or does the whole write in one transaction.
• Upsert merges on the dataset’s key columns. Set them first; without a key the delivery fails with “Upsert needs a key: set the dataset’s key columns, or use replace or append”.

Rows	Snowflake	BigQuery	Postgres	MySQL	S3
Keep it matching (replace)	Loads a staging table and swaps it in	One load that replaces the contents when it commits	Empties the table and inserts, in one transaction	Fills a staging table and swaps it in	Overwrites one object per rule
Add new rows to the bottom (append)	Inserts below	Appends	Inserts below	Inserts below	Writes a new timestamped object each time
Update rows by key, add the rest (upsert)	Merges on the key	Merges on the key	Replaces rows with arriving keys	Replaces rows with arriving keys	Not offered

How inputs read

A warehouse input is “Fill a dataset from a query”. It “runs one SELECT and replaces the dataset with its result, when you sync and before each run that uses it.”

• One read-only statement. It must start with SELECT or WITH, with no semicolon in the middle.
• Up to one million rows. A query that returns more fails with “The query returned more than 1,000,000 rows. Add a WHERE or LIMIT so Jsonify doesn’t store a partial result.”
• Replace, never accumulate. Each sync writes a new dataset version holding exactly the query result.
• When it runs. Before every run of a pipeline that uses the dataset as an input, and when you press Sync on the dataset. See Sync an input by hand.

Save proves the query works by running it once with LIMIT 1, without storing anything.

Snowflake

Jsonify connects to Snowflake as a user you create for it. It never asks for a password: it either signs in with a key pair it generates, or with a programmatic access token you paste.

The Snowflake integration’s Settings tab starts with the Connect Snowflake panel, “Jsonify connects as a Snowflake user you grant access to. It never sees a password.”

Option	Values	Default	What it does
Account identifier	Text, for example myorg-myaccount	Required	Your Snowflake account
Snowflake user	Text, for example JSONIFY_SVC	Required	The user Jsonify signs in as. The key-pair script creates it
Role	Text, for example JSONIFY_ROLE	Blank, which the script treats as JSONIFY_ROLE	The role Jsonify uses
Warehouse	Text, for example ANALYTICS_WH	Required	The warehouse that runs Jsonify’s statements
Database	Text, for example PROD	Required	Where tables are written, or queried from
Schema	Text, for example JSONIFY	Required	The schema inside that database
How should Jsonify sign in?	Run a script (recommended) / Paste a token	Run a script	How Jsonify proves who it is

• Run a script (recommended). “Jsonify generates a key pair and shows the SQL to run. Nothing expires.”
• Paste a token. “A Snowflake programmatic access token. It expires, and Jsonify will show when.” A token box appears; on a connection that already has one it reads “Leave blank to keep the stored token”.

Save connection stays disabled until every required field, and a token if you chose one, has a value. It confirms with “Snowflake connection saved. Run the script, then press Test.”

Once saved, the panel becomes Snowflake connection: “{user} on {account}, writing to {database}.{schema} with {warehouse}.” It shows:

• The setup script. Headed “Run this in Snowflake, then press Test” for a key pair, “Grants to run in Snowflake” for a token, and “Setup script” once Jsonify has signed in successfully. It is a collapsible SQL card with Copy. Run it once in Snowsight as ACCOUNTADMIN, or a role that can create users.
• Token expiry. For a pasted token, a pill such as “Token expires in 20 days”. It turns amber at 14 days or fewer, then reads “Token expires today” and, in red, “Token expired”. The same text is on the integration’s card.
• Edit. Reopens the settings. Cancel closes them without saving.
• New key. Key pairs only. Asks “Generate a new key? Snowflake keeps refusing the old one until you run the new script.” and confirms with “New key generated. Run the script again in Snowflake.” The script then holds the ALTER USER line to run.

What the script does:

• Key pair. Creates the role, creates the user as a service user with Jsonify’s public key, and grants the role to it.
• Token. Grants the role to your existing user and adds a programmatic access token restricted to that role. Paste the token into Jsonify.
• Both. Grants usage on the warehouse and database, usage and CREATE TABLE on the schema, and SELECT on its tables, current and future. Where your account uses network policies, it also lists the addresses Jsonify connects from.

Test signs in and checks it can reach the account. As an input, Snowflake also has these fields below the panel, and a Save button:

Option	Values	Default	What it does
Query	One SELECT	Required	Placeholder SELECT sku, url FROM prod.catalog.products WHERE active. “One read-only SELECT. Each sync replaces the dataset with its result.”
Target dataset	A dataset	Required	The dataset the result replaces

A pasted token stops working on its expiry date and every delivery after that fails. Switch the connection to Run a script with Edit when you can; key pairs do not expire.

BigQuery

BigQuery signs in with a service account key.

Option	Values	Default	What it does
Service account key	The JSON key file’s contents	Required, secret	“The JSON key for a service account with BigQuery Data Editor on the dataset and BigQuery Job User on the project.”
Project	A project ID	Blank, the project named in the key	Which project to work in
Dataset	A BigQuery dataset, for example jsonify	Required	Where tables are written, or queried from
Location	For example EU	Blank	“Needed when the dataset isn’t in the US multi-region.”

As an input, two more fields follow:

Option	Values	Default	What it does
Query	One SELECT	Required	Placeholder SELECT sku, url FROM `project.dataset.products` WHERE active
Target dataset	A Jsonify dataset	Required	The dataset the result replaces

Save and Test check the key can reach the dataset. For an input they also run the query once.

Postgres

Option	Values	Default	What it does
Host	A host name, for example db.example.com	Required	Your database server
Port	A number	5432	The port it listens on
Database	Text	Required	The database to use
User	Text	Required	The user Jsonify signs in as
Password	Text	Required, secret	That user’s password
Schema	Text	public	Where tables are written
SSL	Require (default) / Verify the certificate / Prefer / Disable (local databases only)	Require	How the connection is encrypted

As an input, Query (placeholder SELECT sku, url FROM products WHERE active) and Target dataset follow, both required.

Save and Test sign in and, for an output, check the user can create tables in the schema. If not, the message says so: “{user} can’t create tables in {schema}; grant CREATE on the schema.” For an input they run the query once, read-only.

MySQL

MySQL has the same fields as Postgres, without Schema: tables are written to the Database itself.

Option	Values	Default	What it does
Host	A host name	Required	Your database server
Port	A number	3306	The port it listens on
Database	Text	Required	Where tables are written, or queried from
User	Text	Required	The user Jsonify signs in as
Password	Text	Required, secret	That user’s password
SSL	Require (default) / Verify the certificate / Disable (local databases only)	Require	How the connection is encrypted

As an input, Query (placeholder SELECT sku, url FROM products WHERE active = 1) and Target dataset follow, both required.

Save and Test sign in and, for an output, create and drop a small check table to prove the user can write.

S3

S3 works with AWS and any S3-compatible store, such as Hetzner, Cloudflare R2 or MinIO.

Option	Values	Default	What it does
Bucket	Text, for example my-bucket	Required	The bucket to write to or read from
Endpoint URL	A URL, for example https://fsn1.your-objectstorage.com	Blank, AWS	“Leave blank for AWS. Set a public endpoint for Hetzner, Cloudflare R2, MinIO and the like.”
Region	For example eu-central-1	Blank	The bucket’s region
Access key ID	Text	Required, secret	The key Jsonify signs in with
Secret access key	Text	Required, secret	Its secret
Prefix	For example jsonify/	Blank	“Folder to keep objects under.”
Format	JSON Lines (.jsonl) / CSV (.csv)	JSON Lines	The file format written, or read

As an input (“Read objects into a dataset”), two more fields follow:

Option	Values	Default	What it does
Object key	A key in the bucket	Blank, the newest object under the prefix	“One object to read. Leave blank to read the newest object under the prefix.”
Target dataset	A dataset	Required	The dataset the object’s rows replace

As an output (“Write objects”), objects are named after the rule’s Object name, or its name, with spaces and punctuation turned into hyphens. Keep it matching overwrites one object per rule, for example jsonify/Retail-prices.jsonl. Add new rows to the bottom writes a new timestamped object each time, under jsonify/Retail-prices/. An input reads objects up to 50 MB.

Save and Test prove the key can write, by writing and deleting a small check object under the prefix, for an output; and that the object or prefix can be read, for an input.

What’s next