Metrics from your own data warehouse

The metrics that matter most to a claims or underwriting initiative, such as days to settle a claim, quotes per underwriter, or submissions by line of business, rarely live in a help desk or a CRM. They live in your data warehouse, already modeled. A metric query reads one of them from there, every night, so a metric Roiva would otherwise ask you to type in fills itself.

Metric queries run on Snowflake, Databricks and BigQuery. Write the SQL in that warehouse's own dialect.

What a query returns

A metric query is one SELECT that returns one row per month:

period: a date in the month. Any day in the month will do; Roiva reads the month.
value: the month's figure, as a number in the metric's own unit. A percentage metric runs 0 to 100, so 12.5 means twelve and a half percent.
entity: only when the query breaks the metric down, by line of business, region or team. You name the breakdown when you make the query, and every row then needs one. A breakdown has at most 200 values. It is never a list of claimants, policies or people.
sample_size: optional. It records how many records an average was taken over.

For example, the average days to settle a closed claim:

SELECT DATE_TRUNC('month', closed_on) AS period,
       AVG(days_to_settle)            AS value,
       COUNT(*)                       AS sample_size
FROM ROIVA_METRICS.PUBLIC.CLAIMS_CLOSED
GROUP BY 1

Each query feeds one metric. It can feed any metric Roiva ships that is recorded by hand or imported (every insurance metric in the Metric registry is one), or a metric your account defined itself. A metric that one of your connections already feeds isn't offered, because Roiva would then have two readings of each month and no rule for which one counts.

A query feeds the account, so every initiative reading the metric sees it, or it feeds one initiative. An initiative is offered once it's linked to the connection as an activity source. See Linking connections to initiatives.

Give Roiva a schema to read, and nothing else

Each warehouse's setup script, on its platform page, gives Roiva a place of its own to read and nothing else:

Snowflake: a database, ROIVA_METRICS, whose PUBLIC schema's views Roiva's role may SELECT from. A Snowflake view runs with its owner's rights, so the role reads what each view returns, not the tables behind it. One exception comes from Snowflake itself: every role holds whatever your account grants to the PUBLIC role, Roiva's included. Snowflake's sample data is granted to PUBLIC by default, and anything else your account grants there is readable too.
Databricks: a schema, workspace.roiva_metrics (or one in another catalog, such as main in an older workspace), on which the service principal gets USE SCHEMA and SELECT. On a SQL warehouse, Unity Catalog reads a view's tables with its owner's rights. Grants to a group the service principal is in, such as all account users, reach it too, and every workspace lets all account users read its samples catalog. The service principal's OAuth secret needs the sql scope.
BigQuery: a dataset of views (roiva_metrics, say), authorized on the datasets its views read, with BigQuery Data Viewer for the service account on that dataset alone. The views can read your source tables; the service account can't.

A good split is to keep the views plain and put the logic in the query:

The views do the minimizing. A view like CLAIMS_CLOSED(closed_on, line_of_business, days_to_settle) carries the columns a metric needs and no names, addresses or policy numbers.
The query holds the definition. The rollup to months, the average and any filter live in the query, where Roiva versions them.

A view can change without anyone telling Roiva. If you name the schema on the connection (Metrics Schema, or Metrics Dataset for BigQuery, in its settings), Roiva records something of each view in it with every result, and a view that changed since the last result shows up in the query's history. BigQuery shows a view's text to anyone who may read it, so Roiva records a digest of the text. Snowflake and Databricks show it only to its owner, so Roiva records when each view was created and last altered: that it changed, not what changed.

Write, test and save a query

Only account Owners and Admins write, test or pause a query. Everyone in the account can read a query, its versions and what each returned.

Open the warehouse's connection under Integrations, then its Metric Queries tab, and click New Query.
Choose the metric and what it feeds, and name a breakdown if it has one.
Paste the SQL and click Test Query. Roiva runs it the way the nightly sync will, shows the months it would read, and keeps nothing. A test runs on your warehouse at your usual cost.
Click Save Query. It runs straight away, and then every night with the connection's other syncs.

The Test Query panel says why Roiva wouldn't read a query: a column that isn't there, two rows for one month, a value off the metric's range. It quotes the warehouse's own message when the warehouse refused the query.

Versions, and what a reading remembers

Nothing edits a query's text in place. A changed query is saved as the next version, with a line saying what changed. What a query feeds (its metric, its initiative and its breakdown) is fixed once it's made. For another metric, make another query.

Every reading a query writes names the version and the rows that version returned. Its What's behind it page shows those rows, with its own row marked, beside the SQL that produced them. Every run is labeled roiva-metric-query-<id>-v<version> in the warehouse's own history (Snowflake's query tag, a Databricks query tag keyed roiva, a BigQuery job label keyed roiva), so you can find the same statement there.

A value formula keeps that too. An entry computed from a query's reading names the version and result it was computed from, and still does after the query changes. So an approved figure reproduces from the rows it was actually computed from. See What makes a figure auditable.

Changing a query's text changes the definition of the metric. So two more things happen:

The before-value is recalculated. A baseline Roiva calculated from the old version's readings is calculated again from the new version's, so before and after stay one definition. A baseline you entered yourself is never replaced.
The next entry is held for review. Where auto-approval is on, an entry computed from a version the formula's last approved entry didn't read waits for a person, however close its amount is to the last one.

A month the new version no longer returns loses its reading. Absence means the query has no figure for that month now, not that the figure is zero.

How it stays read-only

Roiva never runs your text as written. It places your query inside a SELECT of its own and reads only the period, value, entity and sample_size columns from it, so no other column leaves your warehouse. The warehouse's parser refuses anything else in that position, such as an INSERT, a DELETE, a CREATE, a CALL or a second statement. Roiva doesn't inspect your SQL itself.

The grants are still what decide what a query can reach, which is why each setup script grants read access to one schema and nothing more. The connection page's setup check warns when Roiva's role, service principal or service account can write. Beyond that, each warehouse says a different amount about what a statement did:

Snowflake doesn't report a statement's type. If it ever reports that a run changed rows, Roiva keeps nothing from that run and pauses the query. The check also looks at PUBLIC, and ignores Snowflake's own SNOWFLAKE database.
Databricks doesn't report a statement's type either, so after each run Roiva asks the workspace's query history, and pauses the query if it names anything but a SELECT. The check reads only what's granted to the service principal directly, not through a group.
BigQuery says what a statement is before it runs. Roiva dry-runs every query first, which bills nothing, and refuses anything BigQuery doesn't call a SELECT, or anything that would read more than 10 GB. The run itself can't bill past 10 GB. Roiva's token for the service account is also minted with a read-only scope. The check reads write permissions granted on the project, not on a single dataset.

Each run stops after 120 seconds, reads the last 36 months, and returns at most 5,000 rows. Roiva stores only the rows a query returns.

Metric keys, and where a value comes from: what a metric, a baseline and a reading are
What makes a figure auditable: why a past figure still reproduces
Metric registry: every metric Roiva ships, and which a warehouse query can feed