Skip to main content

Copy a Snowflake table or view once a day

This page is for an administrator who wants Prism to copy a table or view from Snowflake on a schedule, rather than read it live. Prism runs one statement a day against your warehouse, keeps the rows as a dated copy in its own memory, and answers every question from that copy. Your warehouse sees one query per copy and nothing while people are asking questions.

Choose this over the live Snowflake recipes (a table, a directory) when:

  • your warehouse team does not want query load while the product is in use; or
  • the table is a directory of your people that you want to be the organizational context stream. Prism resolves GitHub logins, Jira accounts and employee ids to one person from the organizational stream's rows. A live Snowflake directory never puts rows in Prism, so it cannot do that (issue 938). A copy can.

You author a source manifest from the snowflake_snapshot recipe. Its worked example is a directory of people, because that is the case that needs a copy. For any other table, replace the columns with your own the same way.

Read Take a source live first if you have not connected any source yet.

What this has been tested against, and what it has not​

Prism's test suite has no Snowflake account. Every behaviour on this page was tested against a model of Snowflake's SQL API v2, built from Snowflake's own documentation and checked against the real driver code. The following were not observed against a real Snowflake. Confirm each one on your account before relying on it:

  • Authentication. Only key-pair sign-in is implemented. Password, OAuth and workload identity are not supported at all. Against the model, the signed token is verified the way Snowflake documents; a real account's handling of it was not observed.
  • Warehouse suspend and resume. A suspended warehouse resumes when the copy runs, and the resume counts against statement_timeout. How long your warehouse takes to resume was not measured. Set the timeout with it in mind.
  • The statement timeout firing. Prism sends statement_timeout to Snowflake with every statement, so Snowflake cancels it at that point. Prism also holds its own limit and cancels a statement it stops waiting for. Snowflake's side was not observed firing.
  • Result chunking. Snowflake returns a large result in partitions whose size it chooses. Prism reads every partition, and refuses a copy whose rows do not add up to what Snowflake said, both per partition and in total. Real partition sizes were not observed.
  • Identifier case. Snowflake treats the warehouse, database, schema and role names as case-sensitive: enter them as SHOW lists them (usually upper case). A mismatch was not tested against a real account.
  • Date and time formats. Window bounds are sent as ISO text and rely on the account's input formats being AUTO (the default). An account that sets DATE_INPUT_FORMAT or TIMESTAMP_INPUT_FORMAT otherwise was not tested.
  • Clock skew. Prism signs a five-minute token per request with the pod's clock. How much skew the account tolerates was not observed.
  • Resubmitting a statement. When a submission gets no answer, Prism sends it again under the same request id with retry=true. Snowflake documents that this does not re-run a statement that already completed. What it does with one that is still running is not documented and was not observed.

The first copy against your account is the real test. Check it with the verification questions in step 6 before you designate anything.

1. What to ask your account administrator for​

The same things the directory page lists:

  • the table or view (a view is your access control: leave out the columns you would rather not hand over);
  • a TYPE = SERVICE login with a key pair registered against it;
  • a read-only role with USAGE on the warehouse, the database and the schema, and SELECT on the one object;
  • the warehouse the daily copy should run on. The spend lands there.

2. The five connection values​

You enter these on the source in Admin → Context streams, not in the chart:

ValueWhat it is
accountYour account identifier, orgname-account_name
warehouseThe virtual warehouse the copy runs on
databaseThe database holding the table or view
schemaThe schema holding it. Optional: leave it empty when the service login's default schema is the right one
userThe service login Prism signs in as

The credential is the private key, as PEM (a line starting -----BEGIN). A .p8 file is often DER-encoded; convert it with openssl pkcs8 -topk8 -nocrypt -inform DER -in key.p8 -out key.pem.

Egress: the ingest pod calls <account>.snowflakecomputing.com on 443 (egress.md).

3. The one statement, and its ceiling​

  • One statement per copy, read whole. paginate is {type: none} and nothing else. Snowflake splits the result into partitions itself, and a paged read would be several statements, each seeing the table at a different moment. fetch_size is refused for the same reason: Snowflake decides how much each partition carries.
  • Each stream is its own moment. A source with two streams copies each as of its own statement. Snowflake's API holds no transaction across statements, so unlike a Postgres or Oracle source, two streams of one source are not one instant.
  • statement_timeout (PT10M in the recipe) is the ceiling on the whole copy. Snowflake receives it with the statement and cancels there, and Prism holds it too. Without one, Prism's own bound (ingest.engine.databaseFetchTimeoutSeconds, an hour by default) is sent in its place. A statement that runs out of time is reported as the ceiling and never retried.
  • Every other round trip is bounded at 60 seconds: sending the statement, asking whether it has finished, and cancelling. Snowflake's API answers within about 45 seconds whatever the statement costs, so a minute with no answer means the network went quiet, not that the view is slow. A submission with no answer is sent again, and the log names what stalled.
  • While a statement runs, Prism keeps asking. Snowflake answers "still running" with 202 or 429; neither costs an attempt. A status request that gets no answer, or a gateway error, says nothing about the statement, so Prism keeps asking about the same one. After six of those in a row it gives the statement up, cancels it, and tries again.
  • A statement Prism stops waiting for is cancelled, so it does not run on and bill. That needs the handle Snowflake returns. If the submission itself got no answer there is no handle to cancel with, and the timeout sent with the statement is what ends it.
  • A copy is never landed short. If anything fails after the first rows have been read, the copy is abandoned and the next scheduled run takes it whole. The previous copy stands.

4. The worked example, and which parts are yours​

The recipe's markers say which lines are which. STRUCTURAL lines are ones the engine needs; YOURS lines are your view, your columns and your choices.

  • sql: names your view and its columns as your view spells them. Snowflake folds an unquoted name UP and Prism folds the result back DOWN, so SELECT EMPLOYEE_ID arrives as employee_id. Every from: must be a column of that result. Prism checks before landing a row and refuses a name that is not there, rather than writing a column of NULLs.
  • employee_id is the key and the anchor, the identifier a person is. email is how a Jira account resolves. github_login is optional: delete it and its identity link if your directory has no forge usernames.
  • manager_employee_id and its reports_to link are what "everyone under X" walks.
  • Only DATE, NUMBER, FLOAT, BOOLEAN and the three TIMESTAMP types arrive typed; every other type arrives as text. Cast in the SELECT if a column needs a type.

If this copy will replace a directory you already have, read Replacing an uploaded directory before you write your columns. The field names matter there.

5. Register, probe, approve, enable​

On Admin → Context streams, pick the recipe, open Review the manifest, replace the document with yours, and enter the connection values and the key. Then probe, approve, and enable.

A passing probe means: the account answered, the key was accepted, and the session was the one you asked for (the role, warehouse, database and schema are read back). Snowflake accepts a warehouse name that does not exist and runs without one, so the probe reads the warehouse back and fails on a mismatch.

It does not mean the view exists or that the role can read it. The first copy finds that out and refuses by name.

The first copy runs at the next ingest tick after the source is enabled. After that it follows schedule (03:00 UTC in the recipe).

6. Verify it​

The recipe ships two questions. The first, how many distinct people does the latest copy list, is also how you confirm a copy has landed. Compare it with SELECT COUNT(DISTINCT employee_id) over the view in your own worksheet. Mark each question correct, wrong or skipped on the verification screen.

7. Make it the organizational context stream​

If nothing holds the organizational stream yet: on the source's page, press Designate as the organizational context stream. The next ingest run compiles the crosswalk from its rows. Check the identity crosswalk line in the self test, which counts what it linked, by kind.

Replacing an uploaded directory​

The usual case: an uploaded roster (csv_people) already holds the stream, and answers and definitions are built on it. In order:

  1. Register and enable the Snowflake copy (steps 1–5). Leave the roster alone. Enabling the copy is never refused because the roster holds the stream.
  2. Let it land, and check that it did. Its first copy runs at the next ingest tick. The first verification question (step 6) should count the people you expect.
  3. Move the stream. On the organizational stream's pane, press Move to the Snowflake copy. One act: the roster gives up the role and the copy takes it, so the installation is never without a directory in between.
  4. Check the crosswalk after the next ingest run: the self test's identity crosswalk line should name the Snowflake source.

The crosswalk is then rebuilt from Snowflake alone. The roster's rows are not merged in. The roster keeps its uploaded file, so you can move the stream back.

What makes the switch invisible to your readers (tested): a person present in both directories resolves to the same person afterwards, and the same questions give the same answers — by leader, by a reporting-line definition, and by a classification. That holds when two things carry over from the roster to your Snowflake manifest:

  • The same anchor. If the roster keyed people by employee id, key the copy by employee id. A reporting-line definition stores the key of each person it names and what kind of key it is. Under a different anchor, a question using it is refused rather than answered wrongly:

    this definition's people were bound as employee_id and 'snowflake_snapshot' now identifies people by email, so the keys it stored cannot match anybody here.

  • The same field names for every field a definition names. A classification over job_family and job_title needs fields of those names in the copy's model, whatever your view calls the columns (from: does the mapping). A missing one is refused by name:

    'developers' (revision 1) cannot be evaluated against 'snowflake_snapshot' as it is now: …

Moving away from a directory that holds the stream before the copy has landed is refused, and nothing changes. So is a move onto a live Snowflake directory, which never lands anything. The copy is checked the way the crosswalk reads it: every table its identity links read must exist, and at least one must hold a row.

'snowflake_snapshot' has no landed copy yet (snowflake_snapshot_people does not exist), and the crosswalk is compiled from its rows: moving the organizational context stream to it now would leave nobody resolvable until its first walk completes. Land it first — its first walk runs at the next ingest tick after it is enabled — then move. Nothing was written.

The same condition is also checked when the crosswalk is compiled, in case the table disappears later. In that case the crosswalk is emptied: nobody resolves until the directory has rows again or the stream is moved back. This is deliberate. A directory Prism cannot read must not go on answering. The self test's crosswalk line FAILs and carries the reason:

… has no landed copy yet (… does not exist), so there is nobody to compile. Let its first walk complete — the source's status shows it — or move the designation back to the source that has rows

If something is wrong​

A refusal names Snowflake's code and what to do. The ones with a sentence of their own:

CodeWhat to do
390100, 390144, 390318, 394204The account did not accept the key pair. Check the user, and that its RSA_PUBLIC_KEY matches the stored private key (DESC USER shows the fingerprint). Stops the whole source, never retried.
NO-ACCOUNTThe account host answered 404. Check the account identifier. An identifier that names no host at all fails as a connection error instead; the run retries it and then gives up, naming the last error.
KEY-UNLOADABLEThe stored key is not a usable PEM. Re-enter it (see step 2).
002003The object does not exist in the statement's database and schema, or the role cannot see it. Snowflake reports both the same way.
000904A column the statement names is not one the object has. Snowflake folds unquoted names up: alias the column in the SELECT.
003001The role can see the object and may not read it: grant SELECT (and USAGE on the database and schema).
000606No usable warehouse: the statement had none, or the role cannot use the one named.
391920The warehouse name does not exist for this role, and the API ran the statement without one.
001003Snowflake could not parse the SELECT.
391908Snowflake answered that its response does not include the whole result set. Nothing was landed; narrow the SELECT.
PARTITION-SHORTA partition arrived with fewer rows than Snowflake said. Treated as a network fault: the copy is abandoned, never landed short.
another six-digit codeRun the stream's SELECT in a worksheet as this user, role and warehouse and read Snowflake's own message. Prism never records it, because it can quote the statement.

The ceiling (000630, or Prism's own limit) says which limit fired, the manifest's or the engine's, and is never retried.