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

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) and data sent somewhere else, as it changes (app.Exports), which is the point: it is also the worked example for anyone writing a provider for a warehouse we did not choose.

Signature#

use Osysharp.Warehouses.Snowflake {
  egress "*.snowflakecomputing.com";
  secret "SnowflakePrivateKey";
}

Description#

Taking the dependency, and what each line is for#

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.

app Revenue {
  model "**/*.osy";
  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:…",
    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#

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:

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

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

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#

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:

[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]:

[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#

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#

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#

The other direction is data sent somewhere else, as it changes (app.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#

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#

• 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) for the full list and the remedy.

Writing a provider for another warehouse#

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#

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;
}

[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#

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