The Databricks kit — lakehouse tables as entities, and exports back

Summary#

Osysharp.Warehouses.Databricks is an optional kit: an app that never reads a lakehouse pays nothing for it. Taking the dependency gives you a data source, so tables in a Databricks workspace are entities your pages and functions query like any other, and an export sink, so an entity of your own arrives in a Delta 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) — and it exists partly to prove those seams are not shaped around one vendor, since it landed against the same contract The Snowflake kit — warehouse tables as entities, and exports back did.

Signature#

use Osysharp.Warehouses.Databricks {
  egress "*.cloud.databricks.com";
  secret "DatabricksToken";
}

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. Grant the egress for the cloud your workspace runs in:

app Revenue {
  model "**/*.osy";
  use Osysharp.Warehouses.Databricks {
    egress "*.cloud.databricks.com";
    egress "*.azuredatabricks.net";
    egress "*.gcp.databricks.com";
    secret "DatabricksToken";
  }
}

using Osysharp.Warehouses.Databricks;

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

app.DataSources = [
  new DatabricksSource("Lakehouse") {
    Host = "adb-1234567890123456.7.azuredatabricks.net", WarehouseId = "abcdef1234567890",
    Catalog = "main", Schema = "sales"
  }
];

Host is your workspace's host name as its URL spells it. WarehouseId is the SQL warehouse's id, shown in the warehouse's connection details. Register one DatabricksSource per workspace, warehouse or catalog you read from.

Giving it a token, and the grants that token needs#

osy secret set DatabricksToken     # a personal access token, or an OAuth token for a service principal

Give the identity behind the token CAN USE on the warehouse and SELECT on the tables your entities name — plus MODIFY on the ones the app updates or deletes from — and nothing else.

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.Lakehouse), ExternalName("sales.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.Lakehouse), ExternalName("sales.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 on the warehouse 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 and a date moved (AddDays through AddYears, .Date, DayOfWeek, DayOfYear, DayNumber), Math.Round to a number of digits under every MidpointRounding (half to even by default, as in C#), Floor, Ceiling, Abs, Truncate, Sign, Min, Max, Pow, Sqrt, Clamp, Trim/TrimStart/TrimEnd, Replace, IsNullOrEmpty, IsNullOrWhiteSpace, Like, ToUpper, ToLower and Length.

Case is mapped by .NET's own table, sent with the query, where this engine's own mapping is the full one; Length counts UTF-16 units as .NET counts them; and 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 the warehouse's rounding.

Each of these is one statement too: Where(…).Update(o => { … }) and Where(…).Delete(), with an OrderBy(…).Take(n) page before them, under the reading person's access rule and the update or delete rule; Other.Where(…).Insert(o => new Order { … }) from another table of the warehouse, or list.Insert(x => new Order { … }) from a list the app holds, under the create rule of the entity inserted into; another entity read inside a condition, under that entity's own rule; Union/Concat/Intersect/Except of two reads of one entity; a Window.* value in a projection; and Join, LeftJoin and SelectMany between entities of the one warehouse.

What is refused, and why#

Convert.ToInt32, Convert.ToInt64 and Convert.ToDecimal are refused when the app compiles. They read text into C#'s decimal, whose scale is whatever the text says, and a Databricks DECIMAL has one fixed scale — so the kit declines rather than answer with a different number than the platform would. Convert in memory after .ToList().

Convert.ToString, Convert.ToBoolean, Convert.ToDouble and the numeric casts are translated.

Sending your own entities into the lakehouse#

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. DatabricksExport creates the Delta 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 (COLLATE UTF8_BINARY), whatever collation a schema 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 join on a value, and a read of another entity inside a condition, read that entity under its own access rule;
• what C# throws on — Math.Clamp with its minimum above its maximum, Replace of an empty string, a number with no place in an int — fails the statement with raise_error, whatever the session's ANSI setting;
• a TIMESTAMP column arrives with its UTC offset, so it names the same instant whatever time zone the warehouse uses.

Results are requested inline as JSON and read chunk by chunk as they are consumed. A statement still running is checked again after a pause doubling from a quarter of a second to eight seconds; one still running after TimeoutSeconds (600 by default) is cancelled, so it stops using the warehouse, and the read fails. A failed statement throws ExternalServiceException with the warehouse's own message.

DescribeSchema — what osy import-schema calls — lists the tables in Catalog (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 (BINARY, ARRAY, MAP, STRUCT, VARIANT, INTERVAL) are left out. A TIMESTAMP column is described as DateTimeOffset and a TIMESTAMP_NTZ one as DateTime.

The ordinal collation needs a runtime that supports COLLATE; a SQL warehouse on a current channel does.

Writing a provider for another warehouse#

Another workspace, warehouse or catalog needs no change to the package: register one DatabricksSource per name. Another warehouse product is an IEntitySource of your own, and this package is a 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.Databricks {
    egress "*.cloud.databricks.com";
    egress "*.azuredatabricks.net";
    egress "*.gcp.databricks.com";
    secret "DatabricksToken";
  }
}

using Osysharp.Warehouses.Databricks;

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

app.DataSources = [
  new DatabricksSource("Lakehouse") {
    Host = "adb-1234567890123456.7.azuredatabricks.net", WarehouseId = "abcdef1234567890",
    Catalog = "main", Schema = "sales"
  }
];

[DataSource(DataSource.Lakehouse), ExternalName("sales.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 Snowflake kit — warehouse tables as entities, and exports back — the same two shapes for a Snowflake account
• data sent somewhere else, as it changes (app.Exports) — sending an entity's changes out, the direction DatabricksExport 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