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:
In the Cloud Console, go to the Dataform page.
Select a repository.
Select a development workspace.
In the Files pane, click the table definition SQLX file that you want to edit.
In the
config
block of the file, enter the table description in the following format:description: "Description of the table",
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:
- In the
config
block of your table definition file, entercolumns: {}
. Inside
columns: {}
, enter column descriptions in the following format:column_name: "Description of the column",
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" } }
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.
- To create a reusable a column description, define a JavaScript include constant with the name of the column and its description.
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
- To learn how to create and use includes in Dataform, see Reuse variables and functions with includes in Dataform.
- To learn how to configure table partitions and clusters, see Create table partitions and clusters.
- To learn how to test table data with assertions, see
Test tables with assertions.