HOW TO: Use Snowflake COMMENT to Document Objects (2024)

We've all been there—stuck in a database with weird table names and mysterious column names. It's like being lost in a maze with no idea what's going on. This is where good documentation—especially comments—becomes essential. It's like having a clue to solve a tough puzzle. You need labels and context to figure it out. The same goes for data. Each table, column, and database object is a piece of the puzzle. Without good comments and documentation, you're just wildly guessing what it's for, what's in it, and how it all fits together. In Snowflake, the COMMENT feature is a powerful tool that addresses this need. It lets you add descriptions to all Snowflake database objects, making your data easy to understand and work with.

In this article, we're going to dive deep into Snowflake COMMENT. We'll explore its significance in data strategy, provide a technical guide on its implementation, and discuss best practices for leveraging this feature effectively.

Why Use COMMENT in Snowflake?

Snowflake COMMENT command allows users to add or modify comments on existing database objects, such as tables, columns, schemas—and more. This feature helps provide context and documentation within the database schema, enhancing understandability and maintainability.

Before we get into the details, let's talk about why you should care about COMMENT in Snowflake.

1) Readability and Maintainability

Comments make your data structures super easy to read and work with. Think about it—you're the new data analyst on a team, and you're staring at a database with hundreds of tables and thousands of columns. No comments? You're in for a long, confusing ride. But, with good comments, you can quickly figure out what each table and column do. You can also see how the logic behind various other database objects fits together. And it's not just helpful for newbies—even experienced team members appreciate it when they need to revisit a part they haven't touched in a while.

2) Team Collaboration

Teamwork gets a boost when we comment. It's a simple way for data engineers, analysts, and scientists to share what they know, even when they're not in the same room. No need to schedule meetings or send emails to clarify things—just read the comments, and you're good to go. This way, we all understand what's going on, so we make fewer mistakes and get more accurate results. It's like leaving notes for each other, making sure we're all on the same page.

3) Data Governance and Compliance

Today, we need good governance and compliance more than ever. That's where comments come in. They help us keep track of where our data comes from, what we can do with it, and how to keep it private. We can even use comments to mark columns that have personal info, so we don't break rules like GDPR or CCPA. And they help us see where our data has been and what's happened to it—super important for keeping our data in check.

4) Data Discovery and Self-Service Analytics

Well-commented database objects enhance data discovery. Whenever you're searching for specific data, comments help you figure out what you're looking at. It's like having a summary on the back of a book, so you know what's inside. This improved discoverability supports self-service analytics initiatives. Users can more confidently explore and use data when they understand its context and meaning. This reduces the need for constant support teams and speeds up data-driven decision-making processes.

Now we get why Snowflake COMMENT matters, so let's get to the good stuff—how to use it.

Syntax and Basic Usage of Snowflake COMMENT

Basic Syntax

Snowflake COMMENT command is straightforward to use. The basic syntax for adding or modifying a comment in Snowflake is:

COMMENT [IF EXISTS] ON <object_type> <object_name> IS '<string literal>';

It's that simple! You specify the type of object you're commenting on, the name of the object, and then the comment text itself.

To remove a comment in Snowflake, the syntax is (just use empty string):

COMMENT [IF EXISTS] ON <object_type> <object_name> IS '';

Commentable Snowflake Objects

Snowflake COMMENT is really versatile, which is one of its biggest strengths. It lets you add comments to a wide range of objects, giving you the ability to document your entire data ecosystem.

You can comment on objects like:

How Do You Comment in Snowflake?

Now that we understand the basics, let's look at some practical examples of how to use Snowflake COMMENT in various scenarios. We'll cover a range of object types to give you a comprehensive understanding.

Example 1—Adding a Comment to a Warehouse Using Snowflake COMMENT

Warehouses in Snowflake are vital for compute resources. Commenting on them can help users understand their purpose and usage guidelines.

COMMENT ON WAREHOUSE analytics_warehouse IS 'Dedicated warehouse for analytics team. Use for heavy queries + data processing jobs. Auto-suspend after 10 minutes of inactivity.';

You can see that this comment gives you clear-cut info on the warehouse—its purpose, who it's for, and how it's configured.

Example 2—Adding a Comment to a Table Using Snowflake COMMENT

Tables are the backbone of your data structure. Clear comments can significantly enhance their usability.

COMMENT ON TABLE sales IS 'Contains all sales transactions. Updated daily at 10 AM. Includes online and in-store sales.';

Here, we add a comment to a table, this comment succinctly describes the table's content, update frequency, and data sources.

Example 3—Adding a Comment to a Table Column Using Snowflake COMMENT

Column-level comments in Snowflake are extremely important for understanding the specifics of your data.

COMMENT ON COLUMN sales.user_id IS 'Unique identifier for each user/customer. References user id. Maybe NULL for guest checkouts.';

As you can see, this comment in Snowflake explains the column's purpose, its relationship to other tables, and potential NULL values.

Example 4—Adding a Comment to a Schema Using Snowflake COMMENT

Schema comments help organize your database structure.

COMMENT ON SCHEMA sales IS 'Contains all sales data including sales data, product revenue, product expenses, and forecasts. Restricted access—contact Sales team for permissions.';

Here, we added a comment to a sales schema, which outlines the schema's content and includes important access information.

Example 5—Adding a Comment to a Database Using Snowflake COMMENT

Database comments provide high-level information about your data organization.

COMMENT ON DATABASE product_analytics_db IS 'Database for all product usage and performance data. Used for bucket testing analysis and feature adoption tracking process..';

Here's what we did—we added a comment to a database. This comment explains what the database is for and what it's mainly used for.

Example 6—Adding a Comment to a View Using Snowflake COMMENT

Views often encapsulate complex logic, making comments particularly valuable.

COMMENT ON VIEW marketing.campaign_performance IS 'Aggregates daily campaign performance across all channels. Includes cost, impressions + clicks, and conversions. Refreshed daily.';

Here, the comment in Snowflake explains what the view does, what it includes, and how often it's updated.

Example 7—Adding a Comment to a Sequence Using Snowflake COMMENT

Sequences might seem simple, but comments can prevent misuse.

COMMENT ON SEQUENCE order_id_sequence IS 'Generates unique order IDs. Do not reset or alter without consulting the Operation team.';

In this example, we added a comment to a sequence, describing the sequence's purpose and includes a warning about modification.

Example 8—Adding a Comment to a User-Defined Function Using Snowflake COMMENT

Functions often require more detailed explanations.

COMMENT ON FUNCTION my_function IS 'Function to calculate customer age from birthdate';

Here, we add a comment to a user-defined function, explaining what it does.

Viewing and Managing Comments

You need to know how to deal with comments too, not just add them. Viewing, managing, and using them wisely is just as crucial.

1) SHOW Commands to View Comments

Snowflake provides SHOW commands to view comments on various objects. For example:

SHOW TABLES;
SHOW TABLES LIKE 'my_table';
SHOW COLUMNS IN TABLE my_table;

These commands will display the comments along with other metadata about the objects.

2) Querying INFORMATION_SCHEMA Views for Comments

For more flexible querying of comments, you can also use the INFORMATION_SCHEMA views. For example, to view all comments on tables in a particular schema:

SELECT table_name, comment
FROM information_schema.tables
WHERE table_schema = 'my_schema';

This query retrieves the names and comments of tables in a specific schema.

3) Modifying Existing Comments

To modify an existing comment, simply run the COMMENT command again with the new text. Snowflake will overwrite the old comment:

COMMENT ON TABLE my_table IS 'Updated comment text';

4) Removing Comments

To remove comments, set the comment text to the empty string “”. For example:

COMMENT ON TABLE my_table IS '';

As you can see, this effectively removes the comment from the object.

Best Practices for Using Snowflake COMMENT

Now that we've covered the how-to's, let's discuss some best practices to make the most of Snowflake COMMENT.

1) Keep your comments consistent and easy to read

Think about creating a style guide for your team so you're all on the same page. That way, comments are easy to understand.

2) Be clear and descriptive, not vague

Avoid vague comments. Be specific about what the object does, why it exists, and any important considerations for its use.

3) Update comments regularly

Data structures and their purposes can change over time. Make updating comments part of your regular maintenance routine.

4) Make comments useful and relevant

Think about who's reading your comments. What would they want to know about this object?

5) Don't overdo it or underdo it

Avoid over-commenting (which can clutter your metadata) and under-commenting (which defeats the purpose). Focus on providing valuable information.

6) Use comments to explain tricky logic

For views, functions, and procedures, use comments to explain the logic, especially if it's not immediately obvious from the code.

7) Add some business context

If it makes sense, include info about how the object relates to business processes or decisions.

8) Document data quality or known issues

Use comments to note any data quality considerations or known limitations of the data.

9) Use comments to track data lineage

Include info about data sources or transformations, especially for views and materialized views.

10) Make commenting a habit

Make adding and updating comments a standard part of your development and change management processes.

Conclusion

And that's a wrap! Snowflake COMMENT is like a beacon of clarity. Without it, your Snowflake objects are a jumbled mess. But with comments, you get to add your two cents, explain the complicated stuff, and highlight what it does behind the scenes. Suddenly, your data makes sense and becomes super useful.

In this article, we have covered:

• Why use COMMENT in Snowflake?
• How do you comment in Snowflake?
• Viewing and Managing comments in Snowflake
• Best practices for using Snowflake COMMENT

… and more!

FAQs

What is the Snowflake COMMENT command?

Snowflake COMMENT command adds or overwrites a comment for an existing database object.

What types of objects can I comment on in Snowflake?

You can comment on a wide range of objects in Snowflake, including account objects, database objects, and other class-level objects.

Can comments be added to specific table columns?

Yes, using the syntax COMMENT ON COLUMN <tbl_name>.<col_name> IS '<comment>';.

Can I update existing comments in Snowflake?

Yes, you can update existing comments by using the COMMENT command with the new comment text.

How do you remove a comment from a table column?

Set the comment text to the empty string “” to remove a comment.

Is it possible to add a comment while creating a table?

Yes, comments can be added during creation with the COMMENT parameter.

Can comments be added to roles and users in Snowflake?

Yes, comments can be added to all object types, including roles and users.

Why should I use comments in Snowflake?

Comments are super helpful. They make your objects more readable, help your team work together, and keep you compliant with the rules. Plus, they make it easier to find the particular object you're looking for.

Can comments be included in ALTER statements?

Yes, the COMMENT parameter can be used in ALTER commands to modify comments.

Are comments stored as metadata in Snowflake?

Yes, comments are part of the object’s metadata.

Are comments case-sensitive?

No, comments themselves are not case-sensitive, but object names may be, depending on how they were created.

How do I comment multiple lines in Snowflake?

In the Snowflake Worksheet Web Interface, you can comment multiple lines by highlighting them and pressing CMD + / (Mac) or CTRL + / (Windows)

What is the use of / Multiline comment?

The /* ... */ syntax is used to comment out multiple lines in SQL scripts, allowing you to include explanations or temporarily disable code without deleting it.