Preparing Data for a Listing

This topic contains guidance for preparing to create a listing, including how to prepare a share for a listing.

In this Topic:

Prepare to Create a Listing

Before you create a listing, do the following:

  1. Decide what type of listing you want to create. See Types of Listings.

  2. Set up roles and privileges to simplify creating listings. See Set Up Roles and Privileges for Listings.

  3. Identify the objects that you want to share. See Decide What To Put In a Listing.

  4. Prepare the objects to be shared with others. See Prepare the Shares For Your Listing.

  5. Determine if you want to charge for your listing. See Preparing Shares for a Paid Listing.

  6. Choose which cloud region(s) you want to offer your listing in. See Prepare Your Listing to Be Shared in Other Regions.

The listing and data share must be in compliance with the Snowflake Provider Policies.

Set Up Roles and Privileges for Listings

When you create a listing, you create it from the account that has the data in it. Within that account, you can do one of the following to create and manage listings:

  • Use the ACCOUNTADMIN role.

  • Create and use a dedicated role with the global CREATE DATA EXCHANGE LISTING privilege.

The role that creates a share also owns the share, and that role must be used to attach the share to a listing. If the role that created the share is different from the role that you use to create and modify the listing, grant the MODIFY LISTING privilege on that listing to the role that owns the share so that users with that role can publish the listing.

For more information about data sharing privileges, see Granting Privileges to Other Roles.

Decide What To Put In a Listing

As you prepare to share data from your account with a listing, decide what to put in the listing.

First, make sure that the data you want to share is in Snowflake, and that you have the legal and contractual rights to share the data. If needed, load the data that you want to share into Snowflake. See Overview of Data Loading.

Note

To the extent any data in your listing or data set is governed by any laws or contractual obligations, you must ensure that you have the legal and contractual rights to share such data. For example, you can only share protected health information (PHI) through a personalized listing and, to do so, you must: (1) have signed a business associate agreement (BAA) with Snowflake and the Consumer receiving the PHI, and; (2) ensure that the Consumer has also signed a BAA with Snowflake. Also, while you can share personal data through both a free or personalized listing, to do so you must have the applicable legal and contractual rights if the data is not publicly available.

Next, decide how to offer the data that you have as a listing. If you plan to offer listings on the Snowflake Marketplace or only as private listings directly with specific customers, you might make different decisions about what to place inside the listing.

  • Consider the availability of your data.

  • Consider the consumers that you expect to access your listings.

  • Consider the formats of the data that you select for the share, such as a table, view, secure view, or other database object.

For example, if you want to provide listings about dog grooming, you might make decisions like the following:

  • Offer a publicly available free listing on the Snowflake Marketplace with information about dog breeds and fur length.

  • Offer a private listing to a partner organization with insights about the length of time it takes to groom various dogs, and the typical frequency of grooming appointments for different dog breeds.

In this example, you offer valuable data on the Snowflake Marketplace, but offer more specific insights to an organization that you already have a trusted business relationship with.

Prepare the Shares For Your Listing

You can create a share before creating a listing, or select the database, tables, and views to comprise your data product when you create the listing. See Working with Shares.

If you plan to offer many listings, create shares separately from listings so that you can more easily manage your data product. You cannot provide multiple listings from the same share.

Consider How to Keep Shares Updated

Consider the maintenance of the data in your share. Over time, you might need to make changes to your data shares as the information that you want to provide in listings changes.

You also need to consider how to keep the data in shares updated, and make sure that the contents of the share are useful to consumers.

If objects in a share are dropped and later recreated, you need to add the recreated objects to the share so that they remain available to consumers. For example, if you refresh some data in the share by dropping and recreating a table in the database, you need to update the share to include the recreated table.

Prepare the Data to be Shared

Prepare the data that you want to share in your listing to be shared with others.

  • Use unquoted object identifiers for tables, columns, and share names. Use only upper case and alphanumeric characters for object names to let listing consumers use the shared data objects without having to double-quote identifiers. See Identifier Requirements.

  • Protect sensitive data in shared databases. Create secure views and use secure objects to control access to data. See Using Secure Objects to Control Data Access

  • You can add shares that are already shared with a consumer account, such as with a direct share, to a listing.

  • A share can only be attached to one listing. If a share has already been attached to a listing, you cannot attach it to another listing, even if the listing has been deleted.

Preparing to Offer a Paid Listing

If you want to charge for your listing, you must do the following:

  1. Determine if you can offer paid listings in your region. See Where You Can Publish Paid Listings.

  2. Prepare the data to offer a trial of the data. See Preparing Shares for a Paid Listing.

  3. Decide on the pricing plan that best fits your listing. See Paid Listings Pricing Models to review the available pricing plans.

Where You Can Publish Paid Listings

As a provider, you can create paid listings if the billing address on your account is in one of the following countries:

  • Australia

  • France

  • Germany

  • Ireland

  • Israel

  • Netherlands

  • Norway

  • Singapore

  • Sweden

  • Switzerland

  • United Kingdom

  • United States

See Where Paid Listings Are Available to Consumers for information on region availability for consumers.

Preparing Shares for a Paid Listing

When you offer a paid listing on the Snowflake Marketplace, you must offer consumers the ability to trial the listing before they purchase it. Trials are optional for paid private listings. As part of the trial, you can limit consumers to specific data and functionality, a specific time period, or a combination.

If you choose to limit trial consumers to specific data and functionality, create a single share for your paid listing and use secure views and a system function provided by Snowflake, SYSTEM$IS_LISTING_PURCHASED, to control which data is visible to trial consumers and which data is available only to paying consumers.

Note

If your listing includes a secure user-defined function (UDF), you cannot limit access to the UDF. Both paying customers and trial customers of your listing can access the secure UDF.

Refer to the following examples to create your own secure views to display different data to paying consumers and trial consumers.

If you want to allow trial consumers to use all data in your listing for a limited period of time, do not use the SYSTEM$IS_LISTING_PURCHASED function in your view definitions for your share.

Example 1: Return Data Based on the Purchase Status of the Account

Create a secure view that selects all columns in a table. The view returns rows only when queried within a consumer account that has purchased your paid listing.

CREATE SECURE VIEW paid_v
  AS
  SELECT
    *
  FROM
    paid_t
  WHERE
    SYSTEM$IS_LISTING_PURCHASED() = TRUE;

Example 2: Return a Subset of Rows Based on the Purchase Status of the Account

Create a secure view that returns a subset of rows based on the boolean value of a specific column in the data. In this example, the underlying table contains a column named is_free that is used to determine which data to show to which consumers.

Some rows have is_free set to TRUE, indicating that the data in those rows can be shown to trial consumers. Other rows have is_free set to FALSE, indicating that the data in those rows should be shown only to paying consumers.

This example view is set up to return all rows only when it is queried by a consumer account that has purchased the paid listing, otherwise it returns only the rows where is_free is set to TRUE.

CREATE SECURE VIEW paid_v
  AS
  SELECT
    *
  FROM
    paid_t
  WHERE
    is_free
    OR
    SYSTEM$IS_LISTING_PURCHASED() = TRUE;

Example 3: Return Only the Most Recent Rows Based on the Purchase Status of the Account

Create a secure view that returns only rows from the previous 7 days to a consumer account that is trialing, but has not yet purchased, your paid listing.

This example uses a column with a timestamp data type to filter the data, but you can use other column data types in your secure view definition.

CREATE SECURE VIEW paid_v
  AS
  SELECT *
  FROM
    paid_t
  WHERE
    (timestamp > current_timestamp() - interval '7 days')
    OR
    SYSTEM$IS_LISTING_PURCHASED() = TRUE;

Validating Secure Views for Paid and Trial Data

After you prepare your secure views, validate that you set them up correctly by simulating the experiences of paid and trial consumer accounts. Run queries against the secure views to confirm that each type of consumer has access to the expected data.

Important

This method does not validate whether consumers can securely access your data. This method only validates whether the share works as expected for your consumers.

To validate your shares, execute a query against a secure view using SHARE_CONTEXT(SYSTEM$IS_LISTING_PURCHASED):

EXECUTE USING SHARE_CONTEXT(SYSTEM$IS_LISTING_PURCHASED=>{ 'TRUE' | 'FALSE' })
  AS <query>

Where:

  • SYSTEM$IS_LISTING_PURCHASED specifies whether you want to validate as a paid consumer, or as a trial or unpaid consumer. The valid values are:

    • TRUE, to validate the share as a paid consumer.

    • FALSE, to validate the share as a trial or unpaid consumer.

  • <query> is the SQL query that you want to run against the secure view.

When you use the command to run your query, the query is executed against the share as though you are a consumer.

For example, suppose you have a share that you want to validate. Your share includes a secure view named PURCHASED_VIEW, which protects all data from a table named SHARE_TABLE. You want to validate that the data can be accessed only by a consumer that purchased the listing.

To confirm that trial consumers cannot access any data in the secure view, run the following query:

EXECUTE USING share_context(system$is_listing_purchased=>'FALSE')
  AS
    SELECT
      *
    FROM
      example_database.example_schema.PURCHASED_VIEW

If the secure view works as expected and no data is accessible to trial consumers, your query returns the following response:

Query produced no results

To confirm that your paid consumers have access to the data, run the following query:

EXECUTE USING share_context(system$is_listing_purchased=>'TRUE')
  AS
    SELECT
      *
    FROM
      example_database.example_schema.PURCHASED_VIEW

If the secure view works as expected, your query returns all of the columns and rows in SHARE_TABLE, the desired outcome for paid consumers.

SYSTEM$IS_LISTING_PURCHASED

Returns TRUE if the consumer account querying data has purchased the listing, otherwise returns FALSE. If an account is trialing the listing, the function returns FALSE. Use this system function in a secure view to manage access to the data in a share and display certain data only to paying customers.

This function infers the listing associated with the database that contains the view and determines whether the account running the query has purchased the listing.

Syntax
SYSTEM$IS_LISTING_PURCHASED()
Arguments

None.

Example

Create a secure view that selects all columns in a table. The view returns rows only when queried within a consumer account that has purchased a paid listing:

CREATE SECURE VIEW paid_view
  AS
  SELECT
    *
  FROM
    paid_table
  WHERE
    SYSTEM$IS_LISTING_PURCHASED();

Consumers trialing the paid listing see no rows in this view.

For additional examples, see Preparing Shares for a Paid Listing.

Prepare Your Listing to Be Shared in Other Regions

When you configure your listing, you can choose to offer it in different regions. Offering listings in other regions requires replicating data.

Consider the time it takes to replicate data and the costs involved in replication.

All cross-region data sharing at Snowflake uses Snowflake’s data replication functionality. See Sharing Data Securely Across Regions and Cloud Platforms.

Back to top