Snowflake Native App
Introduction
Honeydew enables deep integration in Snowflake with a snowflake native application.
It can be installed from the Snowflake Marketplace Listing.
When the native app is installed, Honeydew API can be accessed directly from a Snowflake connection or the web interface, to do things such as:
- Query data based on a semantic query
- Generate Snowflake SQL query from a semantic query
- Consume any metadata such as field names and descriptions
- Update the semantic layer definitions
Installation
Honeydew account
Honeydew native application requires a Honeydew account. If you don’t have one yet, schedule a 20-min onboarding here. Review the initial setup documentation for a full list of setup steps.
Native App installation name
Note the name you have given to the application (i.e. SEMANTIC_LAYER_ENTERPRISE_EDITION
) and replace SEMANTIC_LAYER_ENTERPRISE_EDITION
in all examples below with the actual application name.
You can find the names of the installed applications by running the following command:
Create API Key
In Honeydew application, navigate to the Settings
page, and generate an API Key
to be used for this integration. Copy the generated API Key
and API Secret
values.
When creating the API key, make sure to assign the appropriate role based on the types of API calls you plan to make. For example:
- If you only need to query data, assign the Viewer role
- If you need to modify semantic layer definitions, assign the Editor role
- If you need full administrative access, assign the Admin role
Set API Credentials
Set the Honeydew API Credentials for the Honeydew application, replacing api_key
and api_secret
with the generated API Key values:
Create Honeydew API Integration
Create a Snowflake integration to the Honeydew API:
This integration is used to allow the Honeydew native application to access the Honeydew API.
The API_NETWORK_RULE
is automatically created by the Honeydew native application setup process.
It is pointing to the api.honeydew.cloud
endpoint
(or to a custom endpoint if you are using a private Honeydew deployment).
Make sure to name the integration HONEYDEW_API_ACCESS_INTEGRATION
- do not change that name.
Grant Integration Access
Grant the Honeydew API integration to the Honeydew application:
Enable External Access
Enable the Honeydew API integration for all Honeydew code functions and procedures:
You will receive the following result upon success:
Grant Native App Access
Optionally, grant native app access to any additional Snowflake roles:
Only the ACCOUNTADMIN role has the CREATE INTEGRATION privilege by default. The privilege can be granted to additional roles as needed.
Upgrade
The Honeydew Snowflake Native Application is upgraded automatically when new versions are released.
Native App Access
Grant native app access to any additional Snowflake roles:
Usage Examples
Note that most calls require to choose the workspace and working branch.
The convention here is to set those as variables, i.e.
Workspace and Branches
List all workspaces and branches:
Required permission: Viewer or higher
Create a new branch named branch
for a given workspace:
Required permission: Editor or higher
Reload a given workspace and branch:
Required permission: Viewer or higher
Reload all existing workspaces:
Required permission: Viewer or higher
Reload a given workspace and branch for all users:
Required permission: Admin
Reload all existing workspaces for all users:
Required permission: Admin
Schema
Parameters
List all global parameters in the given workspace and branch:
Required permission: Viewer or higher
Entities
List all entities in the given workspace and branch:
Required permission: Viewer or higher
List all entity relations in the given workspace and branch:
Required permission: Viewer or higher
Fields Metadata
List all fields in the given workspace and branch:
Required permission: Viewer or higher
List all broken fields (fields with error) in the given workspace and branch:
Required permission: Viewer or higher
List all fields in the given workspace and branch, for a specific domain:
Required permission: Viewer or higher
Fields include metrics and attributes
Fields Add/Update/Delete
Change attribute field
of entity entity
Required permission: Editor or higher
Change metric field
of entity entity
Required permission: Editor or higher
Domains
List all domains in the given workspace and branch:
Required permission: Viewer or higher
Show lookml model for all domains:
Required permission: Viewer or higher
Show lookml model for a specific domain:
Required permission: Viewer or higher
Show lightdash model for all domains:
Required permission: Viewer or higher
Show lightdash model for a specific domain:
Required permission: Viewer or higher
Queries
Get data from a semantic query
The following stored procedure allows to run a semantic query and get the resulting data.
It also possible to only generate the SQL (see next section).
You might need to grant access for the native application to the relevant data, for example:
Required permission: Viewer or higher
If Honeydew parameters are used, their default values will be used. To control parameter values, generate the SQL and set parameter values within it - see the next section.
Attributes and metrics may either refer to named fields in the semantic layer, or to new ad-hoc calculations based on them.
Metric ad-hoc expressions can do anything a metric can do.
In particular may use qualifiers such as FILTER (WHERE ...)
and GROUP BY (...)
to create ad-hoc filtered and/or partial metrics.
All the filters apply.
To allow better performance optimizations, it is recommended to pass multiple filters that will all apply rather
than a single one with multiple conditions and an AND
between them.
When passing filters to the native app, may use both attributes and metrics for filtering.
Attributes filter data similar to how WHERE
behaves in SQL (only rows that match the expression are returned)
When a metric is used in a filter (entity.count > 0
) it will be grouped by attributes before filtering,
similar to how HAVING
behaves in SQL. Only rows that the metric aggregation matches are returned.
Generate SQL for a query
The following function generates SQL for an ad-hoc semantic query
Required permission: Viewer or higher
Using parameters with generated SQL
If a query is using Honeydew parameters then they will be generated as session variables in the query, and can be
set with SET
.
This is typically used for automation, with code calling the API setting parameter values.
A sample Snowflake stored procedure that sets parameters can look like:
Required permission: Viewer or higher
BI SQL Interface wrapper
It is possible to use the native application as a wrapper to the live SQL interface:
Get data from ad-hoc SQL:
Required permission: Viewer or higher
Get compiled SQL query from ad-hoc SQL:
Required permission: Viewer or higher
Dynamic Datasets
Metadata
List all dynamic datasets in the given workspace and branch:
Required permission: Viewer or higher
Add/Update/Delete
Add or update a dynamic dataset dataset
in given workspace and branch, and for a specific domain.
Required permission: Editor or higher
Delete a dynamic dataset dataset
from a given workspace and branch
Required permission: Editor or higher
Get Data or SQL
Get the data for a dynamic dataset dataset
:
Required permission: Viewer or higher
Get the SQL query for a dynamic dataset dataset
:
Required permission: Viewer or higher
Deployment
Deploy the dynamic dataset dataset
according to its deployment settings (can be used for aggregate aware caching):
Required permission: Editor or higher
Deploy the entity entity
according to its deployment settings (to update entity cache):
Required permission: Editor or higher
Clear deployed cache status:
Required permission: Editor or higher
Honeydew scans the data warehouse information schema to check for validity of caches.
If an entity or a dynamic dataset used for caching was deployed without calling DEPLOY_DYNAMIC_DATASET
or DEPLOY_ENTITY
(for example via a third party tool) then can call CLEAR_DEPLOYED_CACHE_STATUS
to notify Honeydew that that a data cache was
updated in the data warehouse.
Plaintext questions to data (using Cortex LLMs)
Ask the semantic layer plaintext questions that are translated to the correct Snowflake query:
Returns a JSON object containing the following keys:
llm_response
: A descriptive response from the language modelllm_response_json
: The JSON representation of the semantic queryperspective
: A JSON representing the attributes, metrics and filters of the querysql
: The generated SQL queryerror
: An error message if an error was encounteredjudge
: A JSON containing the judge evaluation of the answer
Required permission: Viewer or higher
Only the claude-4-sonnet
, claude-3-7-sonnet
, claude-3-5-sonnet
, mistral-large2
, and llama3.1-405b
models demonstrated adequate performance.
Other models may not perform as expected.