Skip to main content

External SQL API

You can automate onboarding and mining by calling shipped procedures in the app database _APPLICATION schema. This is intended for pipelines, orchestration, and SQL-based automation outside the Streamlit UI.

Prerequisites: Your session role must be allowed to CALL the procedures that are granted to APPLICATION_ADMIN on the installed app (see Security for application roles and the guided privilege flow).

Data source identifiers: references (preferred) vs FQN strings​

Each field under dataSources (and each entry in passthroughTables) may be either a fully qualified name or a reference object.

Preferred — SYSTEM$REFERENCE: Pass an object so the app binds consumer tables and views without granting the installed application direct USAGE on the database or schema or SELECT on those objects. Use the serialized string returned by SYSTEM$REFERENCE, and set objectKind to TABLE or VIEW so manifest references are named correctly (objectKind defaults to TABLE when omitted):

{ "reference": "<string from SYSTEM$REFERENCE(...)>", "objectKind": "TABLE" | "VIEW" }

Alternative — FQN string: Pass a single string DATABASE.SCHEMA.OBJECT. The app resolves each object as a table or view using INFORMATION_SCHEMA.TABLES and registers the matching manifest reference (TABLE_* or VIEW_*). This path requires the consumer grants in the next section.

Consumer account privileges (grants to the application)​

IMPORT_AND_INITIALIZE_SCENARIO runs with owner’s rights in the app and creates persisted references to the objects in your JSON. The role you use in a worksheet (including your default session role) is not always the same principal the procedure runs as, so only granting your personal or org role access to the data is often not enough.

When you use FQN strings for data sources, in the consumer account you must authorize the installed application to use the source database and the objects in dataSources (at least USAGE and SELECT on the right scope). When you use SYSTEM$REFERENCE objects for those sources instead, you avoid granting that direct database/schema/object access; follow Snowflake’s native-app reference and binding model for those objects (see Security).

For FQN-based data sources only, run the following (or the equivalent in your account) with a role that is allowed to grant privileges, replacing placeholders with your database, schema, and installed application name as it appears in Snowflake:

-- Scope: database and schema the event log / dimension tables (or views) live in
GRANT USAGE ON DATABASE <database_name> TO APPLICATION <your_application_name>;
GRANT USAGE ON SCHEMA <database_name>.<schema_name> TO APPLICATION <your_application_name>;

Per object (minimal privilege), grant SELECT on every table or view you reference in the scenario (event log, case and event dimensions, optional passthrough tables, and any optional workdays/holidays/shift config objects).

Convenience (same schema, many base tables and views you may reference over time):

GRANT SELECT ON ALL TABLES IN SCHEMA <database_name>.<schema_name> TO APPLICATION <your_application_name>;
GRANT SELECT ON ALL VIEWS IN SCHEMA <database_name>.<schema_name> TO APPLICATION <your_application_name>;

GRANT … TO APPLICATION … syntax and privilege lists can vary by Snowflake version—see Snowflake’s reference for GRANT … TO APPLICATION. After granting, verify the app can read the objects you put in the scenario (for example with SHOW GRANTS on the application) before you rely on Tasks, ETL, or ad hoc CALL for automation.

One-call onboarding​

High-level flow when you enable optional mining and an optional wait:

Procedure signature:

CALL <app_database>._APPLICATION.IMPORT_AND_INITIALIZE_SCENARIO(
    <scenario_config_json>,
    <warehouse_reference_json>,
    <run_mining>,                    -- optional, default FALSE
    <wait_for_mining_to_complete>,   -- optional, default FALSE
    <mining_wait_timeout_sec>        -- optional, default 600
);

Example (adjust the app database name; pass JSON as your client supports—string literals, session variables, or bound parameters):

CALL MY_PROCESS_MINING_APP._APPLICATION.IMPORT_AND_INITIALIZE_SCENARIO(
    :scenario_config_json,
    :warehouse_reference_json,
    TRUE,   -- run_mining
    TRUE,   -- wait_for_mining_to_complete
    600     -- mining_wait_timeout_sec
);

Parameters​

  • scenario_config_json: Scenario JSON in the same structure as the UI import (see Process scenario configuration (JSON Schema v1) and the minimal case-centric e2e example in the product repository).
    • dataSources.eventLog is required. It must identify the event log either as a three-part FQN string (DATABASE.SCHEMA.OBJECT) or as a reference / objectKind object (see Data source identifiers); the reference form is preferred when you want to avoid direct grants on the source database, schema, and objects.
    • Optional dataSources keys: caseDimensions, eventDimensions, passthroughTables, workdaysConfig, holidaysConfig, shiftTimesConfig. Each may use an FQN string or the same reference object shape; passthroughTables is an array where each element may be an FQN or reference object.
    • name becomes a Snowflake schema name, so it must be 1–20 characters, start with a letter, use only letters, digits, spaces, hyphens, and underscores, and not resolve to a reserved SQL keyword (for example Order, Select, User, Table; see reserved keywords). A reserved name fails with error -20056 and creates nothing; retry with a different name.
  • warehouse_reference_json: JSON object whose keys are manifest reference names and whose values are the reference strings you pass to REGISTER_SINGLE_REFERENCE for ADD. Per Snowflake, those values must be the serialized tokens returned by SYSTEM$REFERENCE—not the plain warehouse name (see Building warehouse_reference_json with SYSTEM$REFERENCE).
    • Example shape: {"WAREHOUSE":"<reference>", "WAREHOUSE_01":"<reference>"}.
    • SCENARIO_WAREHOUSE (reserved key): the new scenario’s id is not known until after import. Use this key to supply the warehouse reference for that scenario’s slot: the procedure expands it to WAREHOUSE_XX where XX is the two-digit scenario id (same pattern as manifest reference names). If you also pass the explicit WAREHOUSE_XX key for that scenario with a non-empty value, that explicit entry wins and SCENARIO_WAREHOUSE is ignored.
  • run_mining (optional, default FALSE): if TRUE, starts the process mining root task after initialization.
  • wait_for_mining_to_complete (optional, default FALSE): when TRUE, blocks until the mining run that was just started has finished (see below). Must be used together with run_mining = TRUE. If wait_for_mining_to_complete is TRUE while run_mining is FALSE, the procedure fails with a user-defined error (invalid combination).
  • mining_wait_timeout_sec (optional, default 600): maximum seconds to wait when wait_for_mining_to_complete is TRUE. On timeout, the procedure raises a user-defined error.

Building warehouse_reference_json with SYSTEM$REFERENCE​

SYSTEM$SET_REFERENCE (used inside the app when registering references) expects reference_string to be the value returned by SYSTEM$REFERENCE for the consumer object. JSON like {"WAREHOUSE":"XSMALL"} passes only the name of the warehouse; that is not the same as the serialized reference string and can fail at bind time with errors such as Object … does not exist or not authorized, even when the warehouse exists.

In the consumer account, grant USAGE on the warehouse to the installed application (see Consumer account privileges), then build the warehouse JSON from SYSTEM$REFERENCE in SQL:

SET warehouse_reference_json = TO_JSON(
  OBJECT_CONSTRUCT(
    'WAREHOUSE',
    SYSTEM$REFERENCE('warehouse', 'MY_WH', 'PERSISTENT', 'USAGE')
  )
);

CALL MY_PROCESS_MINING_APP._APPLICATION.IMPORT_AND_INITIALIZE_SCENARIO(
  :scenario_config_json,
  :warehouse_reference_json,
  TRUE,   -- run_mining
  TRUE,   -- wait_for_mining_to_complete
  600
);

Use the manifest reference name as the key (WAREHOUSE, WAREHOUSE_01, …). Object type should be 'warehouse'; pass the warehouse identifier as SYSTEM$REFERENCE’s second argument (for example 'MY_WH'). Include 'PERSISTENT' scope and 'USAGE' so the token matches typical Native App warehouse bindings.

To fill the scenario-specific slot without knowing XX until after import, use the reserved key in the object:

SET warehouse_reference_json = TO_JSON(
  OBJECT_CONSTRUCT(
    'SCENARIO_WAREHOUSE',
    SYSTEM$REFERENCE('warehouse', 'MY_WH', 'PERSISTENT', 'USAGE')
  )
);

Return value​

Returns the new scenario_id (NUMBER).

When you wait for completion​

If wait_for_mining_to_complete = TRUE, the procedure waits until the new root task run has ended. Concretely, after import the scenario row in _APPLICATION.SCENARIOS includes SC_SCHEMA (the scenario’s Snowflake schema name). The app stores execution metadata in EXECUTION_INFO, in the schema whose name is SC_SCHEMA concatenated with _CONFIG (for example SCENARIO_01_CONFIG.EXECUTION_INFO when SC_SCHEMA is SCENARIO_01). The waiter looks at the row for PROCESS_MINING_ROOT_TASK: the run is complete when that row has a non-NULL CEI_END_TIME and CEI_START_TIME is after a snapshot taken immediately before the task was triggered. That covers both successful and failed terminal states (not only “mining summary succeeded”).

Run mining for an existing scenario​

Procedure signature:

CALL <app_database>._APPLICATION.RUN_PROCESS_MINING_FOR_SCENARIO(<scenario_id>);

This executes SC_SCHEMA.PROCESS_MINING_ROOT_TASK for a scenario that is already initialized (SC_INITIALIZED in _APPLICATION.SCENARIOS). Replace SC_SCHEMA with the value from that table for your scenario_id.

The call returns immediately after EXECUTE TASK; the graph still runs asynchronously unless you wait programmatically (next subsection).

Wait for a run to finish (separate from one-call import)​

If you trigger mining with RUN_PROCESS_MINING_FOR_SCENARIO (or EXECUTE TASK yourself), you can block until that new run completes using:

CALL <app_database>._APPLICATION.WAIT_FOR_MINING_RUN_TO_COMPLETE(
    <scenario_id>,
    <timeout_sec>,
    <started_after_time>
);

started_after_time: pass a TIMESTAMP_NTZ captured in the same session, immediately before you start the root run. Recommended pattern:

  1. started_after := SYSDATE()::TIMESTAMP_NTZ
  2. CALL ...RUN_PROCESS_MINING_FOR_SCENARIO(...)
  3. CALL ...WAIT_FOR_MINING_RUN_TO_COMPLETE(..., started_after)

This lets the waiter distinguish the newly triggered run from older completed rows.

Substitute your scenario schema using SC_SCHEMA from _APPLICATION.SCENARIOS when building identifiers (see When you wait for completion).

To monitor execution state and errors without blocking, query EXECUTION_INFO in that …_CONFIG schema and use Snowflake task history.