Creating a data product from a complex query

You can customize a view of your data by creating a data product with a complex query. To create a data product with a complex query, you must create and define an SQL query asset in a project and then add the asset to the data product. By using a complex query, you can create and then drop temporary or transient tables. Complex queries require a Snowflake connection.

Required roles
Collaborator roles: Editor or Admin

Creating a complex SQL query

With complex queries, you can create data products that include Snowflake tables or contain SQL assets that query a Snowflake database. The SQL assets can optionally include a custom parameter set. You can create temporary and transient tables for your query, join them if needed, and then drop them after the query executes.

The first step is to add a SQL asset to a project. Complex queries execute in stages and each stage requires certain SQL commands.

  1. From the Data Product Hub navigation menu, click Projects > View all projects.
  2. Add a connection asset for Snowflake. A Snowflake connection is required to create a complex query.
  3. Create an optional parameter set.
  4. Open or create a project. Then, click SQL: Create a dynamic view of data to add a query asset to the project.
  5. Select your Snowflake connection. Select the parameter set if you defined one.
  6. Complete the indicated fields to define the complex query.
    • For the first stage, Define tables, enter the code in the tab to create the tables that you need for the query. Create all tables: persistent, temporary, and transient.
    • For the next stage, Setup SQL, re-enter the code to create the temporary and transient tables. Insert the data into the tables and join the tables if needed.
    • For the next stage, Query, enter the SELECT statements for your query. This stage is required.
    • For the final stage, Cleanup SQL, drop the transient tables and clean up as needed.
  7. Click Create to connect your query to the Snowflake connection. You can verify that your query was validated successfully by locating it in your project's assets page.

Adding a query to a data product

  1. From the Data Product Hub navigation menu, click New data product.
  2. Enter a name for the data product and click the Add from project tile.
  3. Click Select items and add your query. You can use the advanced filters to locate and add your query quickly from your list of data assets.
  4. Verify the connections of all assets. To verify a connection, click the connection status and complete the fields by providing credentials. Make sure that you use an appropriate set of credentials as these credentials are used to deliver the data product to consumers following a subscription.
  5. Click Create draft to confirm your data asset selection. When your draft is successfully created, a static visualization of your data product is generated and viewable by the consumer.

Next steps

After you create your data product draft, see Completing a data product to finish preparing your data product for publication.

Learn more

Parent topic: Creating a data product