Documenting Your Data Warehouse

Craig Kerstiens wrote a great post a while back about documenting your PostgreSQL database. We’ve recommended this technique to many of our customers along with creating a dashboard that surfaces this documentation to end users. Below we’ll detail how to document each of our supported databases. We’ll also demonstrate how this documentation can be accessed via SQL which allows you to create documentation dashboards inside of Chartio for your end users!

PostgreSQL

Command

Postgres supports the comment command for documenting objects. Please refer to the documentation.

Supported Objects

Postgres supports comments on an extensive set of objects. Please refer to the documentation for the full list.

System Table

Postgres stores comments in the pg_description system table. Please refer to the documentation for this table.

Example

Redshift

Command

Redshift supports the comment command for documenting objects. Please refer to the documentation.

Supported Objects

Redshift supports comments on the following objects:

• Tables
• Columns
• Constraints
• Databases
• Views

System Table

Despite what the docs say, comments should be retrievable from the pg_description system table.

MySQL

Command

MySQL supports adding comments to objects at creation time. Refer to the create table documentation for an example of the syntax.

Supported Objects

Since MySQL does not have a comment command, refer to the create command for the object of interest to determine if it supports comments.

System Table

MySQL stores comments in information_schema. For example, table comments can be found in information_schema.tables and column comments can be found in information_schema.columns.

Oracle

Command

Oracle supports the comment command for documenting objects. Please refer to the documentation.

Supported Objects

Oracle supports comments on the following objects:

• Tables
• Columns
• Unified audit policies
• Editions
• Indextypes
• Materialized views
• Mining models
• Operators
• Views

System Table

Oracle stores comments in static data dictionary views such as the user_*, all_* and dba_* views. For use with Chartio, it’s probably best to use the all_* views. For example, table comments can be found in all_tab_comments and column comments can be found in all_col_comments. Please refer to the full documentation on these views.

Microsoft SQL Server

Command

SQL Server supports comments on database objects through Extended Properties. The stored procedure sp_addextendedproperty is used to actually add comments. Here is the documentation for [sp_addextendedproperty](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql?view=sql-server-2017).

Supported Objects

SQL Server supports comments on an extensive set of objects. Please refer to the documentation for the full list.

System Table

SQL Server stores comments in the sys.extended_properties catalog view. They can also be accessed using the [fn_listextendedproperty](https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-listextendedproperty-transact-sql?view=sql-server-2017) function. Please refer to the General information about viewing Extended Properties.

Google BigQuery

Unfortunately, Google BigQuery does not provide a way to create or retrieve comments via SQL. However, Google BigQuery does support commenting objects through the API and web UI on datasets, tables, and fields. You can also retrieve comments through the API. See the description field in the API documentation for more information.

Google Cloud SQL

Google Cloud SQL is MySQL as a managed service. See the section on MySQL above for details.

Snowflake

Command

Snowflake supports the comment command for documenting objects. Snowflake also supports comments at object creation time by adding the comment='...' parameter to the create command. Please refer to the documentation.

Supported Objects

Snowflake supports comments on the following objects:

• Users
• Roles
• Warehouses
• Databases
• Schemas
• Tables
• Columns
• Views
• Sequences
• File formats
• Stages
• User-defined functions (UDFs)

System Table

Currently, Snowflake does not provide SQL access to comments defined on database objects. We will update this section once support for this has been released.