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:CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SET_API_CREDENTIALS('api_key', 'api_secret');
Create Honeydew API Integration
Create a Snowflake integration to the Honeydew API:CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION HONEYDEW_API_ACCESS_INTEGRATION
ALLOWED_NETWORK_RULES = (
SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_EXTERNAL_ACCESS.API_NETWORK_RULE)
ALLOWED_AUTHENTICATION_SECRETS = (
SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_EXTERNAL_ACCESS.HONEYDEW_API_HOSTNAME,
SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_EXTERNAL_ACCESS.HONEYDEW_API_USERNAME_PASSWORD)
ENABLED = TRUE;
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:GRANT USAGE ON INTEGRATION HONEYDEW_API_ACCESS_INTEGRATION TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
Enable External Access
Enable the Honeydew API integration for all Honeydew code functions and procedures:CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ENABLE_EXTERNAL_ACCESS();
You will receive the following result upon success:[Row(anonymous block=None)]
Grant Native App Access
Optionally, grant native app access to any additional Snowflake roles:GRANT APPLICATION ROLE SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_APP_PUBLIC TO ROLE MY_ROLE;
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:
GRANT APPLICATION ROLE SEMANTIC_LAYER_ENTERPRISE_EDITION.HONEYDEW_APP_PUBLIC TO ROLE MY_ROLE;
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.
SET WORKSPACE='tpch';
SET BRANCH='prod';
Workspace and Branches
List all workspaces and branches:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_WORKSPACES());
Required permission: Viewer or higher
Create a new branch named branch
for a given workspace:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.CREATE_WORKSPACE_BRANCH($WORKSPACE, 'branch');
Required permission: Editor or higher
Reload a given workspace and branch:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.RELOAD_WORKSPACE($WORKSPACE, $BRANCH);
Required permission: Viewer or higher
Reload all existing workspaces:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.RELOAD_ALL_WORKSPACES();
Required permission: Viewer or higher
Reload a given workspace and branch for all users:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.RELOAD_WORKSPACE_FOR_ALL_USERS($WORKSPACE, $BRANCH);
Required permission: Admin
Reload all existing workspaces for all users:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.RELOAD_ALL_WORKSPACES_FOR_ALL_USERS();
Required permission: Admin
Schema
Parameters
List all global parameters in the given workspace and branch:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_GLOBAL_PARAMETERS($WORKSPACE, $BRANCH));
Required permission: Viewer or higher
Entities
List all entities in the given workspace and branch:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_ENTITIES($WORKSPACE, $BRANCH));
Required permission: Viewer or higher
List all entity relations in the given workspace and branch:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_RELATIONS($WORKSPACE, $BRANCH));
Required permission: Viewer or higher
List all fields in the given workspace and branch:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_FIELDS($WORKSPACE, $BRANCH));
Required permission: Viewer or higher
List all broken fields (fields with error) in the given workspace and branch:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_FIELDS($WORKSPACE, $BRANCH)) where error is not NULL;
Required permission: Viewer or higher
List all fields in the given workspace and branch, for a specific domain:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_FIELDS($WORKSPACE, $BRANCH, 'domain_name'));
Required permission: Viewer or higher
Fields include metrics and attributes
Fields Add/Update/Delete
Change attribute field
of entity entity
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.CREATE_ATTRIBUTE($WORKSPACE, $BRANCH, 'entity', 'field', 'expression');
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ALTER_ATTRIBUTE($WORKSPACE, $BRANCH, 'entity', 'field', 'expression');
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DROP_ATTRIBUTE($WORKSPACE, $BRANCH, 'entity', 'field');
Required permission: Editor or higher
Change metric field
of entity entity
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.CREATE_METRIC($WORKSPACE, $BRANCH, 'entity', 'field', 'expression')
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ALTER_METRIC($WORKSPACE, $BRANCH, 'entity', 'field', 'expression')
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DROP_METRIC($WORKSPACE, $BRANCH, 'entity', 'field')
Required permission: Editor or higher
Domains
List all domains in the given workspace and branch:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_DOMAINS($WORKSPACE, $BRANCH));
Required permission: Viewer or higher
Show lookml model for all domains:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_LOOKML_MODELS($WORKSPACE, $BRANCH));
Required permission: Viewer or higher
Show lookml model for a specific domain:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_LOOKML_MODELS($WORKSPACE, $BRANCH, 'domain'));
Required permission: Viewer or higher
Show lightdash model for all domains:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_LIGHTDASH_MODELS($WORKSPACE, $BRANCH));
Required permission: Viewer or higher
Show lightdash model for a specific domain:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_LIGHTDASH_MODELS($WORKSPACE, $BRANCH, '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:GRANT USAGE ON DATABASE MY_DATABASE TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
GRANT USAGE ON SCHEMA MY_DATABASE.MY_SCHEMA TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
GRANT SELECT ON ALL TABLES IN SCHEMA MY_DATABASE.MY_SCHEMA TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
GRANT SELECT ON ALL VIEWS IN SCHEMA MY_DATABASE.MY_SCHEMA TO APPLICATION SEMANTIC_LAYER_ENTERPRISE_EDITION;
call SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SELECT_FROM_FIELDS(
-- workspace / branch
$WORKSPACE, $BRANCH,
-- domain (can be NULL)
'domain',
-- dimensions - can be ad-hoc expressions
['entity.attr', ...],
-- metrics - can be named or ad-hoc expressions
['entity.metric', 'SUM(entity.measure)', ...],
-- filters - can be named attributes or ad-hoc expressions
['entity.attr', 'entity.attr > 0', 'entity.name like ''%cheese%''', ...],
-- optional: transform SQL (e.g. add ORDER BY clause or LIMIT clause)
'ORDER BY "entity.attr" LIMIT 10'
);
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
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.GET_SQL_FOR_FIELDS(
-- workspace / branch
$WORKSPACE, $BRANCH,
-- domain (can be NULL)
'domain',
-- dimensions - can be ad-hoc expressions
['entity.attr', ...],
-- metrics - can be named or ad-hoc expressions
['entity.metric', 'SUM(entity.measure)', ...],
-- filters - can be named attributes or ad-hoc expressions
['entity.attr', 'entity.attr > 0', 'entity.name like ''%cheese%''', ...],
-- optional: transform SQL (e.g. add ORDER BY clause or LIMIT clause)
'ORDER BY "entity.attr" LIMIT 10'
);
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:
-- Build a wrapper that gets SQL from Honeydew and executes it
-- immediately with paramters coming from session variables
CREATE OR REPLACE PROCEDURE
SELECT_FROM_FIELDS_WITH_SESSION_VARIABLES_AS_PARAMETERS(
workspace string, branch string, domain string,
attributes variant, metrics variant, filters variant)
RETURNS TABLE()
-- Pass session variables into the procedure
EXECUTE AS CALLER
AS
$$
DECLARE
query VARCHAR;
rs RESULTSET;
BEGIN
-- Get the query from Honeydew
SELECT HONEYDEW_APP.API.GET_SQL_FOR_FIELDS(
:workspace, :branch, :domain, :attributes, :metrics, :filters) INTO :query;
-- Run the query
rs := (EXECUTE IMMEDIATE :query);
RETURN TABLE(rs);
END;
$$
;
-- Set a Honeydew parameter called $PARAM
SET param=10;
-- Get data based on Honeydew generated SQL
CALL SELECT_FROM_FIELDS_WITH_SESSION_VARIABLES_AS_PARAMETERS(
-- workspace / branch
$WORKSPACE, $BRANCH,
-- domain (can be NULL)
'domain',
-- dimensions - can be ad-hoc expressions
['entity.attr', ...],
-- metrics - can be named or ad-hoc expressions
['entity.metric', 'SUM(entity.measure)', ...],
-- filters - can be named attributes or ad-hoc expressions
['entity.attr', 'entity.attr > 0', 'entity.name like ''%cheese%''', ...]
);
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:
CALL SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SELECT_FROM_QUERY($WORKSPACE, $BRANCH, 'select ... from world');
Required permission: Viewer or higher
Get compiled SQL query from ad-hoc SQL:
SELECT SEMANTIC_LAYER_ENTERPRISE_EDITION.API.GET_SQL_FOR_QUERY($WORKSPACE, $BRANCH, 'select ... from world');
Required permission: Viewer or higher
Dynamic Datasets
List all dynamic datasets in the given workspace and branch:
select * from table(SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_DYNAMIC_DATASETS($WORKSPACE, $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.
call SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ALTER_DYNAMIC_DATASET(
-- workspace / branch
$WORKSPACE, $BRANCH,
-- dataset name
'dataset',
-- domain (can be NULL for a global-level dataset)
'domain',
-- dimensions - can be ad-hoc expressions
['entity.attr', ...],
-- metrics - can be named or ad-hoc expressions
['entity.metric', 'SUM(entity.measure)', ...],
-- filters - can be named attributes or ad-hoc expressions
['entity.attr', 'entity.attr > 0', ...]
);
Required permission: Editor or higher
Delete a dynamic dataset dataset
from a given workspace and branch
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DROP_DYNAMIC_DATASET($WORKSPACE, $BRANCH, 'dataset');
Required permission: Editor or higher
Get Data or SQL
Get the data for a dynamic dataset dataset
:
call SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SELECT_FROM_DYNAMIC_DATASET($WORKSPACE, $BRANCH, 'dataset');
Required permission: Viewer or higher
Get the SQL query for a dynamic dataset dataset
:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.GET_SQL_FOR_DYNAMIC_DATASET($WORKSPACE, $BRANCH, 'dataset');
Required permission: Viewer or higher
Deployment
Deploy the dynamic dataset dataset
according to its deployment settings (can be used for aggregate aware caching):
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DEPLOY_DYNAMIC_DATASET($WORKSPACE, $BRANCH, 'dataset');
Required permission: Editor or higher
Deploy the entity entity
according to its deployment settings (to update entity cache):
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.DEPLOY_ENTITY($WORKSPACE, $BRANCH, 'entity');
Required permission: Editor or higher
Clear deployed cache status:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.CLEAR_DEPLOYED_CACHE_STATUS($WORKSPACE, $BRANCH);
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:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.ASK_QUESTION(
workspace => $WORKSPACE,
branch => $BRANCH,
/* The plaintext data question to ask */
question => 'show me revenue broken down by month and city, for the last 2 years',
/* The following parameters are optional: */
/* History of previous questions and answers, to support a chat experience.
* The argument is an array of objects representing a conversation in chronological order.
* Each object contains a role key and a content key.
* The content value is a prompt or a response, depending on the role.
* The role can be either 'user' or 'assistant'.
*/
history => [
{'role': 'user', 'content': 'My previous question'},
{'role': 'assistant', 'content': 'The answer to the previous question'}
],
domain => $MY_LLM_DOMAIN, /* A dedicated domain, narrowing down the semantics for the llm */
/* Any model that is supported by CORTEX.COMPLETE */
cortex_llm_name => 'claude-4-sonnet', /* default is 'claude-4-sonnet' */
/* The default results limit for the query */
default_results_limit => 10000, /* default is 10,000 */
/* A value from 0 to 1 (inclusive) that controls the randomness of the output of the language model.
* A higher temperature (for example, 0.7) results in more diverse and random output,
* while a lower temperature (such as 0.2) makes the output more deterministic and focused.
*/
temperature => 0.2, /* Default is 0.2 */
/* Sets the maximum number of output tokens in the response. Small values can result in truncated responses. */
max_tokens => 8192, /* Default is 8192 */
/* Whether to include judge information in the response.
* This is useful for evaluating the answer and validating its correctness. Note that this will increase the duration of the call.
* The default is FALSE, which means that the judge information will not be included in the response.
*/
include_judge => TRUE /* Default is FALSE */
);
Returns a JSON object containing the following keys:
llm_response
: A descriptive response from the language model
llm_response_json
: The JSON representation of the semantic query
perspective
: A JSON representing the attributes, metrics and filters of the query
sql
: The generated SQL query
error
: An error message if an error was encountered
judge
: A JSON containing the judge evaluation of the answer
Required permission: Viewer or higher
Only the Claude Sonnet models family (claude-4-sonnet
, claude-3-7-sonnet
, claude-3-5-sonnet
) models demonstrated adequate performance.
Other models may not perform as expected.
Use Cases
Semantic Querying
The native app allows to query data using the semantic layer, without the need to write SQL queries.
This is useful for users who are not familiar with SQL or want to leverage the semantic layer’s
capabilities to simplify their queries. You can find some examples here.
The native app can be used to integrate Honeydew with various BI tools.
You can find some examples here.
Building custom AI analyst applications
The native app can be used to build custom AI analyst applications that leverage the semantic layer
to answer user questions in natural language.
For more details, see the AI Analyst API documentation
and AI Workflow - Evaluation documentation.
The native app interface can be used to integrate Honeydew with dbt as well as other ETL tools, for use cases such as:
- Automatically generating and refreshing datasets (tables/views) based on semantic layer definitions.
- Maintaining semantic layer pre-aggregations freshness.
- Deploying semantic layer definitions to production environments.
For more details, see the ETL Tools integration documentation
and specifically the dbt integration documentation.
Development and Testing
The native app can be used to develop and test semantic layer definitions.
Developers can use their favorite development tools to edit the semantic layer definitions, push them directly to git,
and use the native app APIs to validate the definitions.
These validations can also be integrated into CI/CD pipelines to ensure that the semantic layer definitions
are valid before deploying them to production.
Example workflow
- Create a new branch in the semantic layer for development
- Make changes to the semantic layer definitions using your favorite development tools
- Push the changes to git
- Run the following native app calls to validate the changes:
-- Reload the workspace and branch based on the latest git changes
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.RELOAD_WORKSPACE($WORKSPACE, $BRANCH);
-- Validate the semantic layer definitions
select * from table(
SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_ENTITIES($WORKSPACE, $BRANCH)) where error is not NULL;
select * from table(
SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_FIELDS($WORKSPACE, $BRANCH)) where error is not NULL;
select * from table(
SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_DOMAINS($WORKSPACE, $BRANCH)) where error is not NULL;
select * from table(
SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_GLOBAL_PARAMETERS($WORKSPACE, $BRANCH)) where error is not NULL;
select * from table(
SEMANTIC_LAYER_ENTERPRISE_EDITION.API.SHOW_DYNAMIC_DATASETS($WORKSPACE, $BRANCH)) where error is not NULL;
- If there are no errors, create a pull/merge request in git to merge the changes into the main branch.
- Once the pull/merge request is approved, merge the changes into the main branch.
- Run the following native app call to reload the workspace and branch for all users:
select SEMANTIC_LAYER_ENTERPRISE_EDITION.API.RELOAD_WORKSPACE_FOR_ALL_USERS($WORKSPACE, $BRANCH);