Add table documentation

This document shows you how to add descriptions of a table and its columns and records to a Dataform core SQLX file.

You can add table, column, and record descriptions to all table types in Dataform: tables, incremental tables, and views.

You might want to document the following :

  • The purpose of the table.
  • The content or role of columns or records in the table.
  • Relationship of the table and other objects of your SQL workflow, for example, the tables or views that depend on the current table.
  • Assertions applied to the table.
  • Pre-operations or post-operations applied to the table.
  • Owner of the table, meaning the user who created it. This might be useful if multiple team members work on a workflow.

Before you begin

Before you begin, create a table.

Required roles

To get the permissions that you need to document a table, ask your administrator to grant you the Dataform Editor (roles/dataform.editor) IAM role on workspaces. For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Add a table description

To add a description to a table in a SQLX file, follow these steps:

  1. In the Cloud Console, go to the Dataform page.

    Go to the Dataform page

  2. Select a repository.

  3. Select a development workspace.

  4. In the Files pane, click the table definition SQLX file that you want to edit.

  5. In the config block of the file, enter the table description in the following format:

    description: "Description of the table",

  6. Optional: Click Format.

The following code sample shows a table description added to the config block of a SQLX table definition file:

config {
  type: "table",
  description: "Description of the table",
 }

Add column and record descriptions

To add descriptions of individual columns and records to a SQLX file, follow these steps:

  1. In the config block of your table definition file, enter columns: {}.

  2. Inside columns: {}, enter column descriptions in the following format:

    column_name: "Description of the column",

  3. Inside columns: {}, enter record descriptions in the following format:

      record_name: {
      description: "Description of the record",
      columns: {
        record_column_name: "Description of the record column"
      }
}

  4. Optional: Click Format.

The following code sample shows table, column, and record descriptions in the config block of a SQLX table definition file:

config {
  type: "table",
  description: "Description of the table.",
  columns: {
    column1_name: "Description of the first column",
    column2_name: "Description of the second column",
    column3_name: "Description of the third column",
    record_name: {
      description: "Description of the record.",
      columns: {
       record_column1_name: "Description of the first record column",
       record_column2_name: "Description of the second record column",
      }
    }
  }
}
SELECT
  "first_column_value" AS column_1_name,
  "second_column_value" AS column_2_name,
  "third_column_value" AS column_3_name,
  STRUCT("first" AS record_column1_name,
    "second" AS record_column2_name) AS record_name

Reuse column documentation in Dataform with includes

You can reuse description of columns across your SQL workflow with JavaScript includes. You might want to reuse column documentation if you have multiple columns with the same name and description in your SQL workflow.

You can define a constant with a description of a single column, or a constant with a set or column description to reuse descriptions of all columns in a table. For more information on creating and using includes in Dataform, see Reuse variables and functions with includes in Dataform.

The following code sample shows multiple constants with descriptions of individual columns defined in the includes/docs.js JavaScript file:


// filename is includes/docs.js

const user_id = `A unique identifier for a user`;
const age = `The age of a user`;
const creation_date = `The date this user signed up`;
const user_tenure = `The number of years since the user's creation date`;
const badge_count = `The all-time number of badges the user has received`;
const questions_and_answer_count = `The all-time number of questions and answers the user has created`;
const question_count = `The all-time number of questions the user has created`;
const answer_count = `The all-time number of answers the user has created`;
const last_badge_received_at = `The time the user received their most recent badge`;
const last_posted_at = `The time the user last posted a question or answer`;
const last_question_posted_at = `The time the user last posted an answer`;
const last_answer_posted_at = `The time the user last posted a question`;

module.exports = {
   user_id,
   age,
   creation_date,
   user_tenure,
   badge_count,
   questions_and_answer_count,
   question_count,
   answer_count,
   last_badge_received_at,
   last_posted_at,
   last_question_posted_at,
   last_answer_posted_at,
};

The following code sample shows the user_id and age constants, defined in includes/docs.js, used in the definitions/my_table.sqlx SQLX table definition file to generate documentation for selected columns in the table:

config {
  type: "table",
  description: "Table description.",
  columns: {
    user_id: docs.user_id,
    column2_name: "Description of the second column",
    column3_name: "Description of the third column",
    age: docs.age,
  }
}

SELECT ...

The following code sample shows a constant with a set of column descriptions defined in the includes/docs.js JavaScript file:


// filename is includes/docs.js

const columns = {
    user_id = `A unique identifier for a user`,
    age = `The age of a user`,
    creation_date = `The date this user signed up`,
    user_tenure = `The number of years since the user's creation date`,
    badge_count = `The all-time number of badges the user has received`,
    questions_and_answer_count = `The all-time number of questions and answers the user has created`,
    question_count = `The all-time number of questions the user has created`,
    answer_count = `The all-time number of answers the user has created`,
    last_badge_received_at = `The time the user received their most recent badge`,
    last_posted_at = `The time the user last posted a question or answer`,
    last_question_posted_at = `The time the user last posted an answer`,
    last_answer_posted_at = `The time the user last posted a question`,
}


module.exports = {
  columns
};

The following code sample shows the columns constant, defined in includes/table_docs.js, used in the definitions/my_table.sqlx SQLX table definition file to generate documentation for all columns in the table:

config { type: "table",
description: "My table description",
columns: docs.columns
}

SELECT 1 AS one

What's next