Views & references¶

Two kinds of asset besides the one that writes rows. A view materializes its definition: its destination creates a warehouse view from what data() returns. A reference materializes nothing: it points at a table that already exists and reads it. Both are assets in every other respect, with lineage, relations, a schema and a place in the catalog.

Views¶

@il.source
class Reporting(il.Source):
    @il.view(relations={"orders": il.Relation("asset", "shop.orders", many=True)})
    def order_totals(self, orders: list[il.Upstream]) -> str:
        return " UNION ALL ".join(f"SELECT date, total FROM {upstream.data.name}" for upstream in orders)

A view has exactly one destination, declared like any asset's. Each upstream parameter receives the destination's reference to that upstream in data: on a SQL warehouse a TableReference, whose name is the qualified table, quoted for the dialect. The function composes the definition from those references; a many-valued relation is a list, and the author writes the UNION ALL.

A raw-SQL view is written in one dialect. dialect="bigquery" pins it to BigQuery destinations; the default, None, is the author's assertion that the SQL is portable, and it is checked against no dialect.

A view is unpartitioned and reads no upstream data, so it may sit on partitioned upstreams: it refers to the whole table, and the warehouse resolves the query. It is recreated on every run, cheaply and idempotently, so it follows the accounts bound to it. Put it in a job after its connectors.

What the graph checks, when it is built: the view has one destination; its dialect, when declared, is the destination's; every bound upstream is reachable from that destination (reaches, decided from configuration: same BigQuery location, same DuckDB database). Anything the query gets wrong is the warehouse's verdict when the view is created, reported as the view's failure.

Portable views¶

interloper-sql adds ibis_view: each upstream's data is an ibis.Table typed from the upstream's declared schema, the function returns an Ibis expression, and the destination compiles it for its own dialect. The expression's columns must match the view's declared schema. The base package depends on Ibis only; the SQLAlchemy destination lives behind interloper-sql[postgres], so a package that defines ibis_views pulls in no database driver.

from interloper_sql import ibis_view, union

@ibis_view(schema=Totals, relations={"orders": il.Relation("asset", "shop.orders", many=True)})
def totals(self, orders: list[il.Upstream]) -> ibis.Table:
    rows = union(orders, account_id=lambda upstream: upstream.asset.source.discriminator)
    return rows.group_by([rows.date, rows.account_id]).aggregate(total=rows.total.sum())

Every upstream of a ibis_view must declare a schema: that is what types its table.

References¶

@il.source
class GoogleAnalytics4Export(il.Source):
    property_id: str = il.InputField(discriminator=True)

    @il.reference(table="events_*", dataset="analytics_{property_id}", schema=Events)
    def events(self) -> None:
        """The property's raw export, read and never written."""

A reference names its table as written, wildcard and all; the dataset is the asset's, the source's by default. Either may name a field of the source in braces (dataset="analytics_{property_id}"), filled per instance, which is how the GA4 export points events at the property's own dataset. Nothing is created or written. Downstream assets read it like any asset, and views build on it like any table.

A source whose tables exist in one warehouse only says so with a narrowed destinations relation, checked when a destination is bound: the GA4 export declares relations={"destinations": il.Relation("destination", "bigquery_destination", many=True, optional=True)} on its @il.source, so the app offers it BigQuery destinations only.

Backends¶