SQL reference¶

This page covers the procedures the app grants you. See also the data dictionary for input/output schema definitions.

Replace <app> with the name given to the app after installation.

Privileges¶

Before the first run, grant the app the two task privileges:

GRANT EXECUTE TASK ON ACCOUNT TO APPLICATION <app>;
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO APPLICATION <app>;

SRC.UPDATE_REFERENCE¶

Binds one of your views to an app input reference, or clears the binding.

CALL <app>.SRC.UPDATE_REFERENCE(ref_name STRING, operation STRING, ref_or_alias STRING)

Argument	Type	Description
`ref_name`	STRING	Which input to bind. `input_portfolio` for Emissions and Climate Risk, `input_assets` for Geospatial Risk.
`operation`	STRING	`ADD` to bind a view, `REMOVE` to unbind a specific one, `CLEAR` to unbind whatever is currently bound.
`ref_or_alias`	STRING	For `ADD`, a `SYSTEM$REFERENCE` handle for your view. For `CLEAR`, an empty string.

Returns NULL on success, or a message beginning unknown operation: if operation is not one of the three above.

Each reference holds one view at a time. Binding a second view to the same reference replaces the first.

-- your portfolio holdings, for Emissions and Climate Risk
CALL <app>.SRC.UPDATE_REFERENCE(
  'input_portfolio', 'ADD',
  SYSTEM$REFERENCE('VIEW', '<your_db>.<your_schema>.<portfolio_view>', 'PERSISTENT', 'SELECT')
);

-- your physical assets, for Geospatial Risk
CALL <app>.SRC.UPDATE_REFERENCE(
  'input_assets', 'ADD',
  SYSTEM$REFERENCE('VIEW', '<your_db>.<your_schema>.<assets_view>', 'PERSISTENT', 'SELECT')
);

-- unbind when you are finished
CALL <app>.SRC.UPDATE_REFERENCE('input_portfolio', 'CLEAR', '');

Check what is currently bound with SHOW REFERENCES IN APPLICATION <app>;.

SRC.GET_INPUT_CONTRACT¶

Returns the machine-readable input contract: every column the app accepts, its type, units and description, which columns are required for which asset class, and the accepted industry classification systems.

CALL <app>.SRC.GET_INPUT_CONTRACT()

Takes no arguments. Returns a JSON string. Alongside the column definitions it carries asset_classes, identifier_meaning (what identifier means for each class), required_by_class (which classes require each column) and risk_required_fields (the columns climate risk needs).

SRC.VALIDATE_INPUT¶

Checks the bound input against the input schema definition.

CALL <app>.SRC.VALIDATE_INPUT(params OBJECT)

Params key	Type	Description
`classification_system`	string	The industry classification system your `industry` column uses. One of `anzsic`, `factset`, `gics_l4`, `rbics_l3`, `rbics_l4`, matched case-insensitively. Applied to every row, overriding an `industry_classification_system` column in your view.
`asset_class`	string	Validate every row as this asset class. One of `Private Equity`, `Private Debt`, `Public Equity`, `Public Debt`, `Property`, `Infrastructure`, spelled exactly as shown, including capitals. An alternative to `asset_class_column`.
`asset_class_column`	string	The input column that contains the asset class for each row when the input mixes classes. Each row's value must be one of the six asset class names above. An alternative to `asset_class`.
`column_map`	object	Maps contract columns onto your column names, keyed by the contract column: `{"identifier": "<col name>"}` reads the contract's `identifier` from your column. Names are matched case-insensitively.
`outputs`	array	Which outputs the run will request. Any non-empty combination of `"emissions"` and `"risk"`. Defaults to `["emissions"]`.
`source`	string	Where to read the rows from. `"reference"` (the default) reads the view bound to `input_portfolio`; `"upload"` reads rows staged by `SRC.STAGE_UPLOAD`.
`upload_id`	number	The id returned by `SRC.STAGE_UPLOAD`. Required when `source` is `"upload"`, ignored otherwise.

Returns a JSON string shaped {"ok": true, "n_rows": 120, "issues": []}. Each entry in issues names the problem and the rows it affects. On a trial listing, an issue is added when the run would exceed the trial's record allowance.

CALL <app>.SRC.VALIDATE_INPUT(PARSE_JSON('{"classification_system": "factset"}')::OBJECT);

SRC.SUBMIT_TASK¶

Submits a run. Returns the run id immediately; the work continues in the background.

CALL <app>.SRC.SUBMIT_TASK(task_type STRING, params OBJECT)

Argument	Type	Description
`task_type`	STRING	`emissions` or `geospatial`. Any other value fails with error -20002.
`params`	OBJECT	Run parameters. Pass `{}` for the defaults.

Returns the run id as a string. Use it to filter DATA.TASKS and the result tables.

Climate risk is not a separate task type. It is an output of the emissions task, requested through the outputs parameter.

Params key	Type	Description
`classification_system`	string	The industry classification system your `industry` column uses. One of `anzsic`, `factset`, `gics_l4`, `rbics_l3`, `rbics_l4`, matched case-insensitively. Applied to every row, overriding an `industry_classification_system` column in your view.
`asset_class`	string	Process every row as this asset class. One of `Private Equity`, `Private Debt`, `Public Equity`, `Public Debt`, `Property`, `Infrastructure`, spelled exactly as shown, including capitals. An alternative to `asset_class_column`.
`asset_class_column`	string	The input column that contains the asset class for each row when the input mixes classes. Each row's value must be one of the six asset class names above. An alternative to `asset_class`.
`column_map`	object	Maps contract columns onto your column names, keyed by the contract column: `{"identifier": "<col name>"}` reads the contract's `identifier` from your column. Names are matched case-insensitively.
`outputs`	array	Which outputs the run will request. Any non-empty combination of `"emissions"` and `"risk"`. Defaults to `["emissions"]`.
`source`	string	Where to read the rows from. `"reference"` (the default) reads the view bound to `input_portfolio`; `"upload"` reads rows staged by `SRC.STAGE_UPLOAD`.
`upload_id`	number	The id returned by `SRC.STAGE_UPLOAD`. Required when `source` is `"upload"`, ignored otherwise.
`prefer_reported`	emissions	Prefer reported emissions over modelled ones. When the requested year's record is entirely modelled and the previous year has reported emissions, the previous year's record is used instead. Off by default.
`scenarios`	emissions	Which climate scenarios to run risk for. Any non-empty subset of the 16 ids under Climate scenarios. Defaults to all 16. An id outside that list fails the run before any compute.
`risk_years`	emissions	Which analysis years to report risk for. Defaults to 2030, 2035, 2040, 2045 and 2050.
`submission_id`	geospatial	Identifies rows staged by the user interface. Omit it for a SQL run, which reads the view bound to `input_assets`. A blank value is an error, not a fallback.

asset_class and asset_class_column are mutually exclusive: name the class once for the whole run, or name the column that carries it per row, not both. With neither, every row runs as Private Equity, the default.

Climate risk needs total_assets, ebitda and net_debt on your holdings for the classes that supply financials. Rows missing them still appear, with empty risk metrics.

-- emissions only, using the view bound to input_portfolio
CALL <app>.SRC.SUBMIT_TASK('emissions', {});

-- public equity holdings, identified by ISIN
CALL <app>.SRC.SUBMIT_TASK('emissions', OBJECT_CONSTRUCT('asset_class', 'Public Equity'));

-- a mixed portfolio, with each row's class read from your asset_class column
CALL <app>.SRC.SUBMIT_TASK('emissions', OBJECT_CONSTRUCT('asset_class_column', 'asset_class'));

-- emissions and climate risk, for two scenarios and two analysis years
CALL <app>.SRC.SUBMIT_TASK('emissions', OBJECT_CONSTRUCT(
  'classification_system', 'factset',
  'outputs',   ARRAY_CONSTRUCT('emissions', 'risk'),
  'scenarios', ARRAY_CONSTRUCT('ngfs_net_zero', 'ipcc_ar6_bau'),
  'risk_years', ARRAY_CONSTRUCT(2030, 2050)
));

-- geospatial risk for every asset in the view bound to input_assets
CALL <app>.SRC.SUBMIT_TASK('geospatial', {});

Polling and reading results¶

-- watch the run
SELECT TASK_ID, TASK_TYPE, STATUS, ERROR,
       CONVERT_TIMEZONE('UTC', CREATED_DATETIME)   AS CREATED_UTC,
       CONVERT_TIMEZONE('UTC', COMPLETED_DATETIME) AS COMPLETED_UTC
FROM <app>.DATA.TASKS
ORDER BY TASK_ID DESC
LIMIT 10;

-- results for one run
SELECT * FROM <app>.DATA.EMISSIONS           WHERE TASK_ID = <task_id>;
SELECT * FROM <app>.DATA.CLIMATE_RISK        WHERE TASK_ID = <task_id>;
SELECT * FROM <app>.DATA.GEOSPATIAL_RESULTS  WHERE TASK_ID = <task_id>;

-- holdings the run could not process, and why
SELECT TASK_ID, EXCLUSIONS FROM <app>.DATA.TASKS WHERE EXCLUSIONS IS NOT NULL;

Results accumulate across runs, so always filter by TASK_ID. Emissions and climate risk rows for the same run join on TASK_ID and INPUT_ROW.

A run that stays PENDING for more than a minute usually means the two task privileges above were not granted.

SRC.CANCEL_TASK¶

Cancels a queued or running task.

CALL <app>.SRC.CANCEL_TASK(task_id INT)

Argument	Type	Description
`task_id`	INT	The run id returned by `SRC.SUBMIT_TASK`.

Returns a message saying what happened, for example Cancelled queued task 42 (emissions). A run that has already finished is left alone and the message says so. Cancelling one run does not affect others still queued.

CALL <app>.SRC.CANCEL_TASK(42);

SRC.GET_TRIAL_STATUS¶

Reports the limited-trial state and how much of the trial allowance has been used.

CALL <app>.SRC.GET_TRIAL_STATUS()

Takes no arguments. Returns a JSON string shaped {"is_trial": true, "max_records_per_run": 25, "max_total_records": 100, "records_used": 40, "records_remaining": 60}.

On a full listing, is_trial is false and the limits do not apply. On a trial listing the limits are enforced when a run executes, so a run that exceeds one fails with an error naming the limit whether or not you called this first. Only completed runs count towards records_used.

CALL <app>.SRC.GET_TRIAL_STATUS();

SRC.STAGE_UPLOAD¶

Stages rows for a run instead of binding a view. This is the path the user interface uses when you upload a file, and it is available from SQL if you have rows to hand rather than a view to bind.

CALL <app>.SRC.STAGE_UPLOAD(input_rows ARRAY)

Argument	Type	Description
`input_rows`	ARRAY	One object per input row, keyed by contract column name.

Returns the upload id as an integer. Pass it to SRC.SUBMIT_TASK with "source": "upload" and "upload_id": <id>. Staged rows are cleaned up automatically once the run finishes.

For most SQL pipelines, binding a view with SRC.UPDATE_REFERENCE is simpler.

SRC.GRANT_CALLBACK¶

Snowflake calls this for you as each account privilege is granted, and it provisions the background run tasks, the compute pool and the warehouse the app needs. You do not need to call it yourself. It is listed here because it appears in the app's privilege prompts and in SHOW PROCEDURES.