FactorPrism® Headless API Reference

Overview

FACTORPRISM.API.RUN_DECOMPOSITION runs the same analysis as the FactorPrism® app, but from SQL—so you can drive it from a Snowflake task, an alerting pipeline, or any client that can issue a CALL. It returns the ranked driver/contribution table: the same results the app shows, as a queryable table.

It does the work server-side in your account: it aggregates your bound source in the warehouse, runs the analysis engine, returns the driver table, and saves the run so you can fetch it later or open it in the app.

No Cortex required for the API. The procedure returns the structured driver table directly; it does not call an LLM. (As of version 5.0 the interactive app requires Snowflake Cortex for analysis — the headless API is the exception: it runs the analytics engine directly.)

Using an AI agent? Download the FactorPrism agent skill — a single SKILL.md that teaches Snowflake Cortex Code or Claude Code when and how to call this procedure. See Drive it from your agent.

Prerequisites

Install FactorPrism® from the Snowflake Marketplace into your account.
Bind a source — grant the app read access to the table or view you want to analyze (the SOURCE_TABLE or SOURCE_VIEW reference). Do it once in the app's Setup page, or entirely from SQL — see Bind a source from SQL.
Use a warehouse in your calling session. For large inputs, use a Snowpark-optimized warehouse (see Performance).

Bind a source from SQL (no UI)

You can bind the source entirely from SQL — no Setup page — so the whole lifecycle is scriptable. API.SET_SOURCE grants the app read access to a table or view; API.CLEAR_SOURCE removes it. You mint the reference with Snowflake's SYSTEM$REFERENCE, which is what authorizes the app to read that object:

-- Bind a view as SOURCE_VIEW (PERSISTENT survives app upgrades)
CALL FACTORPRISM.API.SET_SOURCE(
  'SOURCE_VIEW',
  SYSTEM$REFERENCE('VIEW','MY_DB.MY_SCHEMA.MY_VIEW','PERSISTENT','SELECT')
);

-- ...run one or more decompositions against it...
CALL FACTORPRISM.API.RUN_DECOMPOSITION('SOURCE_VIEW', /* ...args... */);

-- Optional: unbind (handy for one-off / scheduled runs)
CALL FACTORPRISM.API.CLEAR_SOURCE('SOURCE_VIEW');

Use 'SOURCE_TABLE' or 'SOURCE_VIEW' as the reference name (the two references the app declares). A PERSISTENT binding stays put across app upgrades, so you bind once and schedule freely. Together with the run and the fetch, the full bind → run → fetch flow is pure SQL — drivable from a task, a pipeline, or an agent with no human in the loop.

Why mint a reference? As a Snowflake Native App, FactorPrism can only read data you explicitly grant it. SYSTEM$REFERENCE is how you make that grant in SQL — the same authorization the Setup page performs, just scripted.

Signature

CALL FACTORPRISM.API.RUN_DECOMPOSITION(
  source_ref    => 'SOURCE_VIEW',
  date_field    => 'ORDER_DATE',
  metric_field  => 'REVENUE',        -- required; pass NULL with use_row_count => TRUE
  use_row_count => FALSE,
  rollup        => 'WEEK',
  hierarchies   => PARSE_JSON('[["REGION","DIVISION","STATE"],["CATEGORY","PRODUCT"],["CHANNEL"]]'),
  period_start  => '2025-01-06',
  period_end    => '2025-06-30'
  -- baseline_period and persist are optional (see below) and omitted here
);

These eight arguments are required — metric_field may be NULL for row-count mode. Two optional trailing arguments can simply be omitted:

baseline_period — defaults to the first period of the window (same as NULL); or pass a date / list of dates (below).
persist — defaults to TRUE (the run is saved); pass FALSE for an ad-hoc run that returns the table without writing to SAVED_SESSIONS.

Parameters

Parameter	Type	Required	Description
`source_ref`	STRING	Yes	Which bound source to read: `'SOURCE_TABLE'` or `'SOURCE_VIEW'` (case-insensitive). This is the reference you bound when connecting your data.
`date_field`	STRING	Yes	Name of the date/timestamp column to analyze over time, e.g. `'ORDER_DATE'`.
`metric_field`	STRING	No	The numeric column to analyze (summed per period), e.g. `'REVENUE'`. Pass `NULL` when `use_row_count => TRUE`.
`use_row_count`	BOOLEAN	Yes	`TRUE` counts rows (record volume) instead of summing a metric—use it to analyze transaction/order counts. `FALSE` sums `metric_field`.
`rollup`	STRING	Yes	How to bucket dates: `'DAY'`, `'WEEK'`, `'MONTH'`, or `'YEAR'` (case-insensitive). An unrecognized value is rejected with an error rather than silently defaulting.
`hierarchies`	VARIANT (array of arrays)	Yes	The dimensions to analyze, as a JSON array of arrays. Detailed below—this is the parameter worth understanding.
`period_start`	STRING (date)	Yes	Start of the analysis window (inclusive), e.g. `'2025-01-06'`. The whole window is analyzed.
`period_end`	STRING (date)	Yes	End of the analysis window (inclusive), e.g. `'2025-06-30'`.
`baseline_period`	STRING	No (default `NULL`)	The reference the window is compared against. Detailed below. Omit it (or pass `NULL`) to use the first period of the window.
`persist`	BOOLEAN	No (default `TRUE`)	Whether to save the run to `APP_RESULTS.SAVED_SESSIONS`. `TRUE` (default) saves it (fetchable, openable in the app); `FALSE` just returns the driver table without writing — ideal for ad-hoc / agent-driven runs.

The `hierarchies` parameter

This tells FactorPrism® which dimensions to break your metric down by. It is a JSON array of arrays—PARSE_JSON('[[...],[...]]')—and the structure carries real meaning. There are two distinct concepts:

Levels (the columns inside one array) describe a single drill-down: a parent and its sub-parts, ordered broad → narrow. ["REGION","DIVISION","STATE"] means State rolls up into Division, which rolls up into Region. Each value belongs to exactly one parent.
Groups (the separate arrays) are independent dimensions. FactorPrism® automatically analyzes every level of every group and the cross-dimension intersections between them.

The canonical example shows both ideas at once—a real geography drill-down, a separate product hierarchy, and a standalone dimension:

[["REGION","DIVISION","STATE"], ["CATEGORY","PRODUCT"], ["CHANNEL"]]

That is: State → Division → Region (one hierarchy), Product → Category (a second, independent hierarchy), and Channel on its own. FactorPrism® analyzes every level of each, plus their cross-dimension intersections.

Common mistake: cramming independent dimensions into one array. Writing [["REGION","PRODUCT","CHANNEL"]] tells FactorPrism® that Product nests inside Region and Channel nests inside Product—a single three-level hierarchy, almost never what you want. Compare it to the canonical example above: each independent dimension gets its own array, and only true parent→child levels (like State → Division → Region) share one.

Rule of thumb: if value A always lives under exactly one value of B (every State is in one Division), they are levels of the same hierarchy. If they vary independently (a product sells across regions and channels), they are separate groups.

Examples

You want to analyze...	Use
One dimension	`[["REGION"]]`
Three independent dimensions (region, product, channel)	`[["REGION"],["PRODUCT"],["CHANNEL"]]`
One true drill-down (geography)	`[["REGION","DIVISION","STATE"]]`
A geography hierarchy + a product hierarchy + a standalone channel (canonical example)	`[["REGION","DIVISION","STATE"],["CATEGORY","PRODUCT"],["CHANNEL"]]`

What FactorPrism® does with it

From your dimensions it considers the full lattice: each level of each hierarchy (e.g. a whole Region, or one State within it), and the meaningful intersections across hierarchies (e.g. "Electronics in the West", or "the Online channel for Phones in the Northeast"). It then locates each cause at the level where it actually acts and reports only the drivers that clear a significance gate. In the results, the FACTOR column names that location, joining the specified values with :: — for example West, Northeast :: New England, or South :: jewelry.

The `baseline_period` parameter

The analysis window (period_start…period_end) is always analyzed in full. baseline_period is the reference that window is compared against, and it is optional:

You pass...	Meaning
`NULL`	The first period of the analysis window is the baseline; the rest of the window is measured against it.
A single date, e.g. `'2024-12-30'`	That period is the baseline; the entire window is compared to it.
Several dates—a comma-separated list `'2024-11-04,2024-12-02'` or a JSON array `'["2024-11-04","2024-12-02"]'`	Those periods are averaged into the baseline. Useful for comparing against a "normal" reference rather than a single, possibly noisy, period.

Each baseline period you name must contain data; if one resolves to an empty period, the run returns an error naming it rather than silently dropping it.

The window must not overlap the baseline. If a baseline period falls inside the analysis window, the run returns an error naming the overlapping periods—those periods would be both the reference and an analyzed period, which contaminates the result with the baseline's own variation. Pick baseline date(s) entirely before period_start so the window covers only the period(s) you're analyzing.

What it returns

A table of ranked drivers—identical to the app's results grid:

Column	Meaning
`RANK`	Driver rank by importance (1 = largest).
`FACTOR`	The cause location, e.g. `West`, `South :: jewelry`, or a group like `West :: Subcategory A`.
`TYPE`	`Factor` (an ongoing effect), `Launch` (a location that started from zero in the window), `Group` (several dispersed sibling locations that moved coherently, compressed into one row labelled `Subcategory A/B…` so a spread-out cause stays attributed instead of scattering), or `Diffuse` (the "All other" residual not localized to any single location or group).
`ORIGIN`	`Broad-based` (a market-wide force) or `Localized` (specific to this segment).
`PATTERN`	The shape of the movement, e.g. steady, a spike, or a step change since a date.
`BASELINE`	The baseline value the driver acts on.
`AVG_FACTOR_EFFECT_PCT`	Average multiplicative effect of this factor, in percent.
`AVG_MOVEMENT_PER_PERIOD`	Average units this factor moved per period (absolute). Read this as the activity indicator when the net change is immaterial and the net share is blank.
`NET_CONTRIBUTION_PER_PERIOD`	Net signed units per period—how much of the overall change this factor accounts for.
`SHARE_OF_NET_CHANGE_PCT`	This factor's share of the overall net change, in percent—the headline "how much does this driver explain." Blank when the net change is immaterial relative to the churn (read `AVG_MOVEMENT_PER_PERIOD` instead).
`COLLINEAR_FACTORS`	Other factor locations that would explain this row's movement identically (e.g. a payer that maps one-to-one to a code); "; "-joined, and empty on normal data.

Because every result reconciles exactly, the net contributions sum to the total change with no unexplained residual.

Saved runs & fetching

By default every call is persisted to FACTORPRISM.APP_RESULTS.SAVED_SESSIONS under a timestamped name, so you can fetch the result later, open it in the app, or point a BI tool at it. (Pass persist => FALSE to skip this and just get the table back.) The flat driver table is embedded under session_blob:headless_drivers for easy querying:

SELECT d.value:RANK::int                           AS rank,
       d.value:FACTOR::string                      AS factor,
       d.value:NET_CONTRIBUTION_PER_PERIOD::float  AS net_contribution,
       d.value:SHARE_OF_NET_CHANGE_PCT::float      AS share_pct
FROM   FACTORPRISM.APP_RESULTS.SAVED_SESSIONS s,
       LATERAL FLATTEN(input => s.session_blob:headless_drivers) d
WHERE  s.session_id = '<id from your run>'
ORDER  BY rank;

Automate it (tasks)

Because it is just SQL, you can run FactorPrism® on a schedule—for example, decompose last quarter's revenue every Monday morning:

CREATE OR REPLACE TASK my_db.my_schema.weekly_decomposition
  WAREHOUSE = my_wh
  SCHEDULE  = 'USING CRON 0 8 * * MON America/New_York'
AS
  CALL FACTORPRISM.API.RUN_DECOMPOSITION(
    source_ref      => 'SOURCE_VIEW',
    date_field      => 'ORDER_DATE',
    metric_field    => 'REVENUE',
    use_row_count   => FALSE,
    rollup          => 'WEEK',
    hierarchies     => PARSE_JSON('[["REGION","DIVISION","STATE"],["CATEGORY","PRODUCT"],["CHANNEL"]]'),
    period_start    => '2025-01-06',
    period_end      => '2025-06-30',
    baseline_period => NULL
  );

ALTER TASK my_db.my_schema.weekly_decomposition RESUME;

Pair this with the saved-run query above to route the latest drivers into a dashboard, an alert, or a notification.

Performance & notes

Server-side aggregation. Aggregation runs in the warehouse of the calling session, and only the aggregated result is materialized—so a large source never pulls millions of raw rows into memory.
Large inputs. Call the procedure on a Snowpark-optimized warehouse so the analysis has ample memory.
Consistency with the app. The headless API and the app use the same engine and the same aggregation, so they return the same results for the same inputs. The headline and driver ranking are stable run-to-run.
Your data stays put. Everything runs inside your Snowflake account, under your governance, with read-only access to the source you bind.

Looking for the guided walkthrough instead? See the Documentation, or the Programmatic API quick start.

Headless API Reference

On this page

Overview

Prerequisites

Bind a source from SQL (no UI)

Signature

Parameters

The `hierarchies` parameter

Examples

What FactorPrism® does with it

The `baseline_period` parameter

What it returns

Saved runs & fetching

Automate it (tasks)

Performance & notes

Questions?

Headless API Reference

On this page

Overview

Prerequisites

Bind a source from SQL (no UI)

Signature

Parameters

The hierarchies parameter

Examples

What FactorPrism® does with it

The baseline_period parameter

What it returns

Saved runs & fetching

Automate it (tasks)

Performance & notes

Questions?

The `hierarchies` parameter

The `baseline_period` parameter