# The Snowflake kit — warehouse tables as entities, and exports back

> An optional package that makes Snowflake tables ordinary Osy# entities: your app reads them with LINQ, and each query runs in Snowflake as one statement already carrying the reading person's access rule. The other direction sends your own entities into Snowflake tables as they change. This page says how to take the dependency, what to register, what runs as one statement, and what is not translated.

<!-- id: config-snowflake · area: config · stability: preview · html: https://osysharp.com/reference/config/snowflake/ -->

## Summary        {#summary}

`Osysharp.Warehouses.Snowflake` is an optional **kit**: an app that never reads a warehouse pays nothing for it. Taking the
dependency gives you two things that are otherwise separate products — a **data source**, so tables in a Snowflake
account are entities your pages and functions query like any other, and an **export sink**, so an entity of your own
arrives in a Snowflake table minutes after each commit.

The kit is a package of pure Osy#, not a platform feature. It implements the generic seams described in
[data held somewhere else (app.DataSources)](https://osysharp.com/reference/config/data-sources/) and [data sent somewhere else, as it changes (app.Exports)](https://osysharp.com/reference/config/exports/), which is the point: it is also the worked example for anyone writing
a provider for a warehouse we did not choose.

## Signature      {#signature}

```osy title="app.osy — the manifest takes the dependency AND grants it" syntax
use Osysharp.Warehouses.Snowflake {
  egress "*.snowflakecomputing.com";
  secret "SnowflakePrivateKey";
}
```

## Description    {#description}

### Taking the dependency, and what each line is for   {#install}

Two declarations turn it on. The manifest takes the dependency **and grants it what it needs** — a package reaches no
host and reads no secret unless the app says so — and the file that registers a source imports its classes.

```osy title="app.osy — the dependency, and the grant it cannot work without" syntax
app Revenue {
  model "**/*.osy";
  use Osysharp.Warehouses.Snowflake {
    egress "*.snowflakecomputing.com";
    secret "SnowflakePrivateKey";
  }
}
```

```osy title="the app's own file — declare the secret, then register the account" syntax
using Osysharp.Warehouses.Snowflake;

app.Secrets = [ new Secret("SnowflakePrivateKey") ];

app.DataSources = [
  new SnowflakeSource("Warehouse") {
    Account = "MYORG-MYACCOUNT", User = "OSY_READER", PublicKeyFingerprint = "SHA256:…",
    Warehouse = "ANALYTICS_WH", Database = "SALES", Schema = "PUBLIC", Role = "REPORTING"
  }
];
```

`Account` is the account identifier as your account URL spells it — `MYORG-MYACCOUNT` for
`https://myorg-myaccount.snowflakecomputing.com`. An account locator with its region, `XY12345.eu-west-1`, also
works. Register one `SnowflakeSource` per account, database or role you read from; each entity names the one it
lives in.

### Giving it a key pair, because that is how it signs in   {#credentials}

The app authenticates as a Snowflake user with a key pair — there is no password. Create the key, register its public
half on the user, read back the fingerprint, and store the private half as the declared secret:

```console
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_key.p8 -nocrypt
openssl rsa -in snowflake_key.p8 -pubout -out snowflake_key.pub
```

```sql
ALTER USER OSY_READER SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh…';   -- the public key, without its BEGIN/END lines
DESC USER OSY_READER;                                          -- RSA_PUBLIC_KEY_FP is the fingerprint
```

```console
osy secret set SnowflakePrivateKey --file snowflake_key.p8
```

Give the user a role that can read the tables your entities name and nothing else. A role that can write is needed
only for the write verbs below.

### Declaring the tables you read   {#entities}

An entity bound to the source names its table with `[ExternalName]` and the column or columns that identify a row
with `[Key]`. Its `security { }` is the app's own, and it is what the warehouse is told:

```osy title="one table, one entity — the access rule travels with every query" syntax
[DataSource(DataSource.Warehouse), ExternalName("PUBLIC.ORDERS")]
entity Order {
  [Key, ExternalName("ORDER_ID")] string Number;
  [ExternalName("REGION")] string Region;
  [ExternalName("TOTAL")] decimal Total;
  [ExternalName("PLACED")] DateOnly Placed;
  security { allow read where Region == user.Region; }
}
```

Two entities of the same source may reference each other. The reference's `ExternalName` names the column holding the
target's key, and the target declares exactly one `[Key]`:

```osy title="a reference is the column holding the target's key" syntax
[DataSource(DataSource.Warehouse), ExternalName("PUBLIC.CUSTOMERS")]
entity Customer {
  [Key, ExternalName("CODE")] string Code;
  [ExternalName("NAME")] string Name;
  [ExternalName("REGION")] string Region;
  security { allow read where Region == user.Region; }
}

// on Order:
  [ExternalName("CUSTOMER_CODE")] Customer? Customer;
```

Reading through the reference is a `LEFT JOIN` over only the target rows the reading person may read — so an order
whose customer they may not read keeps its row and reads the customer as `null`. A reference to an entity in another
source, or in the platform's own storage, is refused when the app compiles.

### What runs in Snowflake as one statement   {#queries}

`Order.Where(o => o.Total > 100).OrderBy(o => o.Placed).Take(20)` is one statement, and so are `Count`, `Any`, `Sum`,
`Average`, `Min`, `Max`, `GroupBy` with a `.Where` on the groups, `Select(…).Distinct()`, arithmetic, text joined with
`+`, `??` and `?:` over a row's columns, a date's parts (`Year`, `DayOfWeek`, `DayOfYear`, `DayNumber`, `.Date`) and a date moved
(`AddDays` through `AddYears`), `Math.Round` to a number of digits under every `MidpointRounding` (half to even by default, as in C#; a `double` only away from zero), `Floor`, `Ceiling`, `Abs`, `Truncate`, `Sign`,
`Min`, `Max`, `Pow`, `Sqrt`, `Clamp`, `Trim`/`TrimStart`/`TrimEnd`, `Replace`, `IsEmpty`, `IsBlank`, `Like`,
`ToUpper`, `ToLower` and `Length`.

Three of those are worth stating exactly, because the warehouse's own answer differs from C#'s and the kit sends C#'s:

- **case** is mapped by a table the platform sends with the query — .NET's simple mapping, where Snowflake's own
  `UPPER` maps `ß` to `SS`;
- **`Length`** counts UTF-16 units, as .NET counts them;
- **an `Average`** is asked for as its sum and its count and divided the way C#'s `decimal` divides, so it carries
  every digit a `decimal` holds rather than Snowflake's rounding.

Conversions run there too — `Convert.ToInt`, `ToInt64`, `ToDecimal`, `ToDouble`, `ToBool`, `ToString` and a cast such
as `(int)o.Total` — meaning what they mean in C#: toward zero, text read by the platform's number grammar or 0, a
null converted to 0, `false` or `""`, and a value with no answer failing the query.

A date written as text (`"at " + o.Placed`, `o.Placed.ToString("D")`) is one statement as well: the platform sends the
culture's pattern as parts, names and literals, so the text is C#'s in the viewer's culture and never Snowflake's.

Reads of another entity inside a condition, `Union`/`Concat`/`Intersect`/`Except` of two reads of one entity, and
`Join`/`LeftJoin`/`SelectMany` between entities of the one account are each one statement, every joined entity under
its own access rule.

### Changing rows, and creating them   {#writes}

`SnowflakeSource` implements `IWritableEntitySource`, so `Where(…).Update(o => { … })` and `Where(…).Delete()` run as
one `UPDATE` or `DELETE` — with an `OrderBy(…).Take(n)` page before them too — under the reading person's read rule
and their update or delete rule, answering the rows Snowflake reports in `stats`.

`Other.Where(…).Insert(o => new Order { … })` runs as one `INSERT … SELECT` from another table of the account, and
`list.Insert(x => new Order { … })` as one from a `VALUES` table of the list's values, under the create rule of the
entity inserted into. The user the source connects as needs `UPDATE`, `DELETE` or `INSERT` on the tables written to.

### Sending your own entities into Snowflake   {#exports}

The other direction is [data sent somewhere else, as it changes (app.Exports)](https://osysharp.com/reference/config/exports/): declare which entity's changes leave and which columns go with them, and
the platform delivers every change in commit order. Each batch is one `MERGE`, so a redelivery cannot undo it.
`SnowflakeExport` creates the table and widens it as the shape changes.

### What the package contains   {#ships}

| file | what it is |
|---|---|
| `SnowflakeConnection.osy` | the account settings, key-pair authentication and the statement client a source and an export share |
| `SnowflakeSource.osy` | runs a query plan through Snowflake's SQL API and hands back its rows; describes a database's tables |
| `SnowflakeExport.osy` | creates and widens the export's table, and merges each batch into it |
| `SnowflakeSql.osy` | translates a query plan into one Snowflake statement with bound values |
| `SnowflakeValues.osy` | decodes the SQL API's value encodings (dates as day counts, timestamps as epoch seconds) |
| `SnowflakeResponses.osy` | the SQL API's response shapes |

What every statement it writes guarantees:

- each value is a bound parameter, never text spliced into the SQL;
- strings compare, sort and group ordinally, whatever collation the account or table defaults to;
- null sorts as C# sorts it — first ascending, last descending — and `StartsWith`, `EndsWith` and `Contains` match
  their text literally;
- distinct rows compare strings exactly and count `null` as one value, as C#'s `Distinct()` does;
- a computed value means what it does in C#: whole numbers divide truncating toward zero, a remainder keeps the sign
  of the number divided, and arithmetic on `null` is `null`;
- the reading person's access rule is part of the same `WHERE` as the query's own filter, so paging counts only rows
  they may read.

A statement Snowflake reports as still running is checked again after a pause doubling from a quarter of a second to
eight seconds, until it finishes or Snowflake cancels it at `TimeoutSeconds`. A large result is read partition by
partition as it is consumed. A request Snowflake refuses throws `ExternalServiceException` with Snowflake's own
message.

`DescribeSchema` — what `osy import-schema` calls — lists the tables in `Database` (in `Schema` alone when one is set)
with each column's type and each table's primary key. Columns of a type a query plan cannot carry (`VARIANT`,
`OBJECT`, `ARRAY`, `BINARY`, `GEOMETRY`, `VECTOR`) are left out. A `NUMBER` with no scale is described as `long`,
because Snowflake's `INTEGER` is `NUMBER(38,0)`; declare the property `decimal` instead if its values can exceed a
`long`.

### What is not translated, and one thing not yet measured   {#limits}

- A double written as text, text read as a double, and a double converted to a decimal are **not** translated — call
  them in memory after `.ToList()`.
- A decimal read from text is exact to 38 digits. **Whether Snowflake keeps its trailing zeros has not been measured
  against a live account**, so a value whose scale matters should be checked before you depend on it.
- A query needing a feature the provider does not declare is a compile error naming it — see
  [data held somewhere else (app.DataSources)](https://osysharp.com/reference/config/data-sources/) for the full list and the remedy.

### Writing a provider for another warehouse   {#own}

Another account, database or role needs no change to the package: register one `SnowflakeSource` per name. Another
**warehouse product** is an `IEntitySource` of your own, and this package is the worked example — a translator from
the query plan to that engine's SQL, a client for its API over `Http.*`, and the hosts and secrets it needs declared
in its manifest. Fork the repository, rename the package to match where it lives (`package Acme.Warehouse` lives at
`github.com/acme/warehouse`), change the dialect and the endpoints, and `osy publish`. The query plan conformance
cases — every plan, and the rows it must return — are what to hold your translator to.

## Examples       {#examples}

```osy title="the whole dependency: the grant, the secret, the account and one table" test app=config-snowflake
app Revenue {
  use Osysharp.Warehouses.Snowflake {
    egress "*.snowflakecomputing.com";
    secret "SnowflakePrivateKey";
  }
}

using Osysharp.Warehouses.Snowflake;

app.Secrets = [ new Secret("SnowflakePrivateKey") ];

app.DataSources = [
  new SnowflakeSource("Warehouse") {
    Account = "MYORG-MYACCOUNT", User = "OSY_READER", PublicKeyFingerprint = "SHA256:Zx9k",
    Warehouse = "ANALYTICS_WH", Database = "SALES", Schema = "PUBLIC", Role = "REPORTING"
  }
];

[DataSource(DataSource.Warehouse), ExternalName("PUBLIC.ORDERS")]
entity Order {
  [Key, ExternalName("ORDER_ID")] string Number;
  [ExternalName("TOTAL")] decimal Total;
  [ExternalName("PLACED_AT")] DateTime Placed;
}
```

```osy title="a page reading warehouse rows, secured by the app's own rule" syntax
[Route("/orders")]
component Orders() {
  live var mine = Order.Where(o => o.Total > 100m).OrderBy(o => o.Placed).Take(20).ToList();
  render {
    Stack {
      foreach (var o in mine) { Text($"{o.Number} — {o.Total:C} — {o.Placed:yyyy-MM-dd}"); }
    }
  }
}
```

## See also       {#see-also}

- [data held somewhere else (app.DataSources)](https://osysharp.com/reference/config/data-sources/) — the generic seam this kit implements, and what any provider must declare
- [The Databricks kit — lakehouse tables as entities, and exports back](https://osysharp.com/reference/config/databricks/) — the same two shapes for a Databricks lakehouse
- [data sent somewhere else, as it changes (app.Exports)](https://osysharp.com/reference/config/exports/) — sending an entity's changes out, the direction `SnowflakeExport` serves
- [declaring secrets (app.Secrets)](https://osysharp.com/reference/config/secrets/) — declaring the secret the manifest grants
- [use](https://osysharp.com/reference/types/use/) — what `use` does, and why a package is granted its hosts explicitly
- [package.osy](https://osysharp.com/reference/project/package/) — the manifest that makes a repository a publishable package