--- updatedAt: 2025-08-28T17:13:47.000Z agentTools: projectIndex: https://docs.scope3.com/v1.3/llms.txt --- # Snowflake (version 1) *The Snowflake v2 development work is in progress. For now, please use[v1](https://docs.scope3.com/v1/docs/snowflake-integration) for all Snowflake requests.* # Create Scope3 API Integration ```sql create or replace api integration scope3_api api_provider = google_api_gateway google_audience = '' api_allowed_prefixes = ('https://scope3-snowflake-api-gateway-5qbjzj9o.uc.gateway.dev') enabled = true; ``` # Create Scope3 External Function ## Function Call ```sql create or replace external function scope3_v1( date date, inventory_id string, device string, country string, region string, impressions int, creative_format string, creative_bytes int, creative_width int, creative_height int, creative_duration int, inventory_type string) returns OBJECT api_integration = scope3_api headers = ( 'AccessClientId'='****', 'AccessClientSecret'='****' ) as 'https://scope3-snowflake-api-gateway-5qbjzj9o.uc.gateway.dev/api/v1/snowflake'; ``` | Field | Description | Example | | ------------------ | --------------------------------------------------------------------------------------------- | ----------- | | date | Date in UTC | 2022-05-04 | | inventory\_id | Fully qualified domain name or app ID (same as inventoryId in campaign API) | nytimes.com | | country | ISO 3166-1 alpha-2 country code OR null When null default global emission value will be used. | US | | region | ISO 3166-2 region code OR null When null default country value will be used. | NY | | device | mobile | desktop | | impressions | Sum of impressions for these dimensions | 10000 | | creative\_format | banner | video | | creative\_bytes | Creative size in Bytes OR null | 3024 | | creative\_width | Width of creative OR null | 300 | | creative\_height | Height of creative OR null | 250 | | creative\_duration | Length of creative in seconds OR null | 30 | | inventory\_type | app or site | site | ## Response The function returns the following object. Each field may be missing/null depending if the data is available. If `totalEmissions` and `error` is `null` then scope3 does noes not have information for this impression. ```json { "total_emissions": "", "base_emissions": "", "supply_path_emissions": "", "creative_emissions": "", "domain_coverage": "", "error": "", } ``` | Field | Description | Example | | ------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------- | | totalEmissions | Total Impressions in gCO2e | 300.99 | | baseEmissions | Base Emissions in gCO2e | 100.33 | | supplyPathEmissions | Supply Path Emissions in gCO2e | 100.33 | | creativeEmissions | Creative Emissions in gCO2e | 100.33 | | domainCoverage | Modeled domains are in the dataset with reasonable confidence of accuracy. Missing domains have not been modeled yet and will automatically be queued for analysis. | modeled | | error | null / missing if no error. If error this will contain a string error message | creative\_width without creative\_height supplied | | Term | Definition | | --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Base Emissions | The Base Emissions figure is the total emissions of an impression, excluding the AdTech portion. This includes content creation and end-user device emissions. Content creation is the publisher's scope 1,2,3 emissions proportioned and attributed on an ad slot basis -- we are exploring a new model for how we attribute this in the next version. End-User device emissions cover the power consumption (CPU, Networking, etc.) for rendering the publisher's content considering the user's location and power grid mix. | | Creative Emissions | The creative emissions figure is the total emissions associated with the creative delivery. This includes the networking and CPU costs of delivering and rendering the creative. | | Supply Path Emissions | The supply path emissions figure is the total emissions associated with the supply path from the inventory ad slot and the complete graph of all advertising parties involved. This includes the Scope 1,2,3 emissions data of each party involved broken down into a per impression number. | # Example Usage ## Select a single Example Row ```sql SELECT scope3_v1(DATE('2022-05-04'), 'bbc.com', 'desktop', 'US', 'NY', 1000, 'banner', null, 300, 250, null, 'site'); ``` ## Using in a JOIN ```sql SELECT d.app_url, d.country, d.imps, scope3_v1(d.date, d.app_url, 'desktop', d.country, null, d.imps, 'banner', null, 300, 250, null, 'site'):total_emissions FROM delivery_data d; ``` ## How to extract single fields * [\](https://docs.snowflake.com/en/sql-reference/data-types-semistructured.html#label-data-type-object) * [\](https://docs.snowflake.com/en/user-guide/querying-semistructured.html#label-traversing-semistructured-data) ```sql SELECT res:total_emissions, res:base_emissions, res:supply_path_emissions, res:creative_emissions FROM (SELECT scope3_v1(DATE('2022-05-04'), 'bbc.com', 'desktop', 'US', 'NY', 1000, 'banner', null, 300, 250, null) res) ; ``` ### Dealing with errors If your use case requires special handling for errors, you can use `IFF` and `IS_NULL_VALUE`. ```sql SELECT parse_json('{error: NULL, totalEmissions: 123}') as V, V:error, V:totalEmissions, IS_NULL_VALUE(v:error), IS_NULL_VALUE(v:totalEmissions), IFF(IS_NULL_VALUE(v:non_existing_key) is null, 'Key does not exist', 'Key exists'), IFF(IS_NULL_VALUE(v:error) is null, 'Key does not exist', 'Key exists'), IFF(IS_NULL_VALUE(v:totalEmissions) is null, 'Key does not exist', 'Key exists') ; ```