Data marketplace and listings in Snowflake - Step-by-Step Execution

Process Flow - Data marketplace and listings

Create Data Marketplace

↓

Add Data Listings

↓

Publish Listings

↓

Users Browse Listings

↓

Users Request Access

↓

Grant or Deny Access

↓

Users Query Data

↓

Marketplace Logs Usage

This flow shows how a data marketplace is created, listings are added and published, users browse and request access, and then query data with usage logged.

Execution Sample

Snowflake

CREATE DATA MARKETPLACE my_marketplace;
CREATE DATA LISTING my_listing ON MARKETPLACE my_marketplace
  AS SELECT * FROM sales_data;
PUBLISH DATA LISTING my_listing;
-- User queries listing
SELECT * FROM my_marketplace.my_listing;

This code creates a data marketplace, adds a data listing from a table, publishes it, and then a user queries the listing.

Process Table

Step	Action	Object	State Change	Result
1	Create marketplace	my_marketplace	Marketplace created	my_marketplace exists
2	Create data listing	my_listing	Listing added to marketplace	my_listing linked to sales_data
3	Publish listing	my_listing	Listing published	my_listing visible to users
4	User browses listings	my_marketplace	Listings displayed	my_listing shown
5	User requests access	my_listing	Access request logged	Pending approval
6	Grant access	my_listing	Access granted	User can query listing
7	User queries listing	my_listing	Query executed	Data from sales_data returned
8	Log usage	my_listing	Usage recorded	Query logged for billing
9	End	-	-	Process complete

💡 Process ends after user queries data and usage is logged.

Status Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 6	Final
my_marketplace	None	Created	Created	Created	Created	Created
my_listing	None	None	Created (unpublished)	Published	Published	Published with user access
User Access	None	None	None	None	Granted	Granted
Usage Logs	Empty	Empty	Empty	Empty	Empty	Contains query log

Key Moments - 3 Insights

Why can't users query the data listing before it is published?

Because as shown in step 3 and 7 of the execution_table, the listing must be published to be visible and accessible for querying. Before publishing, the listing is not available to users.

What happens if access is not granted after a user requests it?

According to step 5 and 6, if access is not granted, the user cannot query the listing. The listing remains inaccessible until approval is given.

How is usage tracked in the marketplace?

Step 8 shows that after a user queries data, the marketplace logs the usage for billing and auditing purposes.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the data listing made visible to users?

A Step 5

B Step 2

C Step 3

D Step 7

💡 Hint

Check the 'State Change' column for when the listing becomes published and visible.

According to variable_tracker, what is the state of 'User Access' after Step 4?

A Granted

B None

C Pending

D Revoked

💡 Hint

Look at the 'User Access' row and the column 'After Step 4' in variable_tracker.

If the listing was never published, what would happen at Step 7 when the user tries to query?

A Query fails due to no access

B Query returns empty result

C Query returns data

D Query logs usage anyway

💡 Hint

Refer to the execution_table steps 3 and 7 about publishing and querying.

Concept Snapshot

Data Marketplace and Listings in Snowflake:
- Create a marketplace with CREATE DATA MARKETPLACE.
- Add listings with CREATE DATA LISTING linked to source data.
- Publish listings to make them visible.
- Users browse and request access.
- Access must be granted before querying.
- Usage is logged for billing and auditing.

Full Transcript

This visual execution trace shows how a data marketplace is created in Snowflake, how data listings are added and published, and how users interact with the marketplace. The marketplace is first created, then listings are added from existing data tables. Listings must be published to be visible to users. Users browse listings and request access. Access must be granted before users can query the data. Each query is logged for usage tracking. The variable tracker shows the state changes of the marketplace, listings, user access, and usage logs at each step. Key moments clarify why publishing is necessary before querying and how access controls work. The quiz tests understanding of when listings become visible, user access state, and query behavior if listings are unpublished.

Practice

1. What is the primary purpose of a data marketplace in Snowflake?

easy

A. To group data listings for easy sharing and discovery

B. To store raw data files permanently

C. To run complex SQL queries faster

D. To manage user access permissions

Solution

1. Step 1: Understand the role of a data marketplace

   A data marketplace is designed to organize and share data listings so users can find and use data easily.

2. Step 2: Compare options with this purpose

   Options A, B, and D describe other Snowflake features but not the marketplace's main role.

3. Final Answer:

   To group data listings for easy sharing and discovery -> Option A

4. Quick Check:

   Marketplace = Group data listings [OK]

Hint: Marketplaces group data listings for sharing [OK]

Common Mistakes:

• Confusing marketplace with data storage
• Thinking marketplace speeds up queries
• Mixing marketplace with access control

2. Which SQL command correctly and safely creates a new data marketplace named my_marketplace in Snowflake?

easy

A. CREATE NEW MARKETPLACE my_marketplace;

B. CREATE DATA MARKETPLACE my_marketplace;

C. CREATE MARKETPLACE my_marketplace;

D. CREATE MARKETPLACE IF NOT EXISTS my_marketplace;

Solution

1. Step 1: Recall correct syntax for creating marketplace

   Snowflake uses CREATE MARKETPLACE [IF NOT EXISTS] name; to create marketplaces safely.

2. Step 2: Check each option

   CREATE MARKETPLACE my_marketplace; lacks IF NOT EXISTS and will fail if the marketplace already exists; Options B and D use invalid syntax; CREATE MARKETPLACE IF NOT EXISTS my_marketplace; is correct and safe.

3. Final Answer:

   CREATE MARKETPLACE IF NOT EXISTS my_marketplace; -> Option D

4. Quick Check:

   CREATE MARKETPLACE IF NOT EXISTS = safe creation [OK]

Hint: Use IF NOT EXISTS to avoid errors creating marketplace [OK]

Common Mistakes:

• Adding extra keywords like DATA or NEW
• Omitting IF NOT EXISTS causing errors if exists
• Using incorrect command structure

3. Given the SQL query:

SHOW LISTINGS IN MARKETPLACE my_marketplace;

What will this command do?

medium

A. Create a new listing inside my_marketplace

B. Delete all listings from my_marketplace

C. Display all data listings available in the marketplace named my_marketplace

D. Show all marketplaces in the account

Solution

1. Step 1: Understand the SHOW LISTINGS command

   This command lists all data listings inside a specified marketplace.

2. Step 2: Match command with options

   Display all data listings available in the marketplace named my_marketplace matches the command's purpose; Options A and C describe create/delete actions which SHOW does not do; Show all marketplaces in the account shows all marketplaces in the account, not listings.

3. Final Answer:

   Display all data listings available in the marketplace named my_marketplace -> Option C

4. Quick Check:

   SHOW LISTINGS = list data sets [OK]

Hint: SHOW LISTINGS lists data sets in a marketplace [OK]

Common Mistakes:

• Confusing SHOW with CREATE or DELETE
• Thinking it lists marketplaces instead of listings
• Assuming it modifies data

4. You run the command:

CREATE LISTING my_listing IN MARKETPLACE my_marketplace;

But get an error saying the marketplace does not exist. What is the most likely cause?

medium

A. The marketplace my_marketplace was not created before creating the listing

B. The listing name my_listing is invalid

C. You need to use DROP MARKETPLACE before CREATE LISTING

D. Listings cannot be created inside marketplaces

Solution

1. Step 1: Analyze the error message

   The error says the marketplace does not exist, so the system cannot find my_marketplace.

2. Step 2: Check command dependencies

   You must create the marketplace before adding listings inside it. If missing, this error occurs.

3. Final Answer:

   The marketplace my_marketplace was not created before creating the listing -> Option A

4. Quick Check:

   Create marketplace first, then listing [OK]

Hint: Create marketplace before listings to avoid errors [OK]

Common Mistakes:

• Assuming listing name causes error
• Trying to drop marketplace before listing
• Believing listings can't be inside marketplaces

5. You want to share a dataset with external users by creating a listing in a marketplace. Which sequence of commands correctly achieves this?

hard

A. CREATE LISTING my_listing; CREATE MARKETPLACE my_marketplace; GRANT USAGE ON MARKETPLACE my_marketplace TO ROLE external_role;

B. CREATE MARKETPLACE my_marketplace; CREATE LISTING my_listing IN MARKETPLACE my_marketplace; GRANT USAGE ON LISTING my_listing TO ROLE external_role;

C. CREATE MARKETPLACE my_marketplace; GRANT USAGE ON MARKETPLACE my_marketplace TO ROLE external_role; CREATE LISTING my_listing IN MARKETPLACE my_marketplace;

D. GRANT USAGE ON LISTING my_listing TO ROLE external_role; CREATE MARKETPLACE my_marketplace; CREATE LISTING my_listing IN MARKETPLACE my_marketplace;

Solution

1. Step 1: Create the marketplace first

   You must create the marketplace before adding listings inside it.

2. Step 2: Create the listing inside the marketplace

   After marketplace creation, create the listing specifying the marketplace.

3. Step 3: Grant usage on the listing to external users

   Granting usage on the listing allows external roles to access the shared data.

4. Final Answer:

   CREATE MARKETPLACE my_marketplace; CREATE LISTING my_listing IN MARKETPLACE my_marketplace; GRANT USAGE ON LISTING my_listing TO ROLE external_role; -> Option B

5. Quick Check:

   Create marketplace -> listing -> grant usage [OK]

Hint: Create marketplace, then listing, then grant access [OK]

Common Mistakes:

• Creating listing before marketplace
• Granting usage before creating listing
• Granting usage on marketplace instead of listing