Snowflake
Read Snowflake tables and views into a Strata flow, and write results to permanent or transient tables.
Before you start
- Your account identifier, in the form
org-account. - A programmatic access token. There's no role field, so make sure the token's default role has the grants below.
- A warehouse the role can use. If you leave Warehouse (optional; uses your default) blank, your default warehouse is used.
- Connections come from Oplema's servers, not your browser. Snowflake's documentation describes a network policy requirement for programmatic access tokens. Oplema has no fixed IP address, so a network policy on your user can block Oplema's connections and stop this connector working; contact us before you set one up.
-- Read
GRANT USAGE ON WAREHOUSE compute_wh TO ROLE analyst;
GRANT USAGE ON DATABASE sales TO ROLE analyst;
GRANT USAGE ON SCHEMA sales.public TO ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA sales.public TO ROLE analyst;
-- Write: Append rows, and Replace, which uses INSERT OVERWRITE
GRANT INSERT, DELETE ON ALL TABLES IN SCHEMA sales.public TO ROLE analyst;
-- Write: Create new table
GRANT CREATE TABLE ON SCHEMA sales.public TO ROLE analyst;See Snowflake's GRANT to confirm which privileges your setup needs.
Connect
- In Strata, select an Input block to read, or an Output block to write.
- Set Source to Connectors. On an Output block the same control is Destination.
- In the connector dropdown below it, choose Snowflake.
- Open the Connection dropdown and choose Create new connection.
- Fill in Account identifier (org-account), Programmatic access token and Warehouse (optional; uses your default). The token is shown as you type.
- Set Name (optional). Without a name, the connection is labeled with the account identifier.
- Select Save connection.
The new connection is selected. Saving only checks the format of what you typed. It doesn't sign in, so wrong details first show up as an error when the pickers below load.
A saved connection can't be edited. To change a password, token or any other field, open the Connection dropdown, choose Delete (the ×) on its row, and create the connection again. Blocks that used the deleted connection must then select the new one.
Read
| Setting | What it does |
|---|---|
| Database, Schema, then Table or view | Pick these in order to choose what to read. |
| Columns | All columns or Selected columns. |
| Date column, From and To | Optional. These are inclusive whole days. Zoned timestamps use UTC. |
Select Refresh data to read again. Snowflake bills the warehouse time separately.
Write
Pick Database, Schema, then Table.
- Create new table: enter a Table name, default
OUTPUT. Names keep their exact spelling. - An existing table: set Write to Replace or Append rows. Replace uses
INSERT OVERWRITEand keeps the table.
Output writes when you run the flow. Choosing tables in the panel never writes. If you pick Create new table and a table with that name already exists, the run fails. Up to 1,000 columns. Tagged tables and tables with access or masking policies are refused.
Limits and billing
- Reads: the first 50 MB of each download is free. After that, each download costs $0.01 per GB from your usage. Failed reads aren't charged, and a read stops when your usage runs out.
- Writes: the first 50 MB of each write is free. After that, each write costs $0.01 per GB from your usage, and a write stops when your usage runs out.
- Snowflake bills warehouse time separately.
See Billing for who pays and how usage works.
Troubleshooting
| Message | What to do |
|---|---|
Snowflake: enter an account identifier such as org-account, not a Snowsight URL | Use only the identifier. |
Snowflake: replace the expired or invalid programmatic access token in your connection | Create a new token, delete the connection and create it again. |
Snowflake: unsupported column <name> (<type>); select supported columns | Use Selected columns and leave that column out. |
Snowflake: writing tagged destinations is unsupported because tags can apply inherited access or masking policies | Write to an untagged table. |
Snowflake: destination must be an ordinary permanent or transient table | Choose another table. |
Snowflake: writes require a visible table definition without access or masking policies | Grant access to the table's definition, or choose another table. |
Snowflake: loading failed; the new empty table remains. Check it before running again. | Drop the empty table, or select it and run with Append rows. |
Snowflake: column metadata is unavailable or exceeds the discovery limit | Check the role can see the table. Very wide schemas can't be read. |
Snowflake: empty replacement is blocked; existing data was preserved | The incoming table has no rows. Check the flow upstream. |
Snowflake: write completion could not be confirmed. Check the destination before running again. | The connection dropped during the write. Look at the table first, because a retry could write the rows twice. |
This transfer exceeds the free 50 MB and the available usage credits | The read or write passed 50 MB and your usage ran out. Add usage, or move less data. |
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.