Metadata tags for dbt

Manage your ThoughtSpot Worksheet and table column properties and table joins in dbt by using metadata tags to define column properties and joins within your dbt model schema. dbt stays the source of truth, and you don’t have to manually update the columns and joins in ThoughtSpot when you make changes to your dbt model.

ThoughtSpot has its own set of metadata tags that you can use within the .yml schema files in your dbt model folders. For example, you can specify the column type, synonyms, and currency type for a column, before ThoughtSpot creates its own Worksheets based on your dbt models during dbt integration. For more information about the Worksheet and table column properties, see Data modeling settings.

If there are any changes to the dbt models that you would like the ThoughtSpot Worksheets and tables to reflect after you already integrated dbt with ThoughtSpot, you must run the dbt integration again, which creates a new set of Worksheets, and updates the existing tables.

Add metadata tags

To add metadata tags, follow these steps:

  1. In dbt, navigate to the .yml schema file for the dbt model. The .yml schema file has syntax similar to the following example:

    models:
  - name: dim_retapp_stores
    columns:
      - name: LATITUDE
        description: latitude (data type float)

      - name: ZIPCODE
        description: zipcode (data type text)

      - name: PRODUCTID
        description: PRODUCTID (data type number)
        tests:
        - not_null
        - relationships:
          to: ref('dim_retapp_products')
          field: PRODUCTID
          meta:
            ts_join_cardinality: many_to_one
            ts_join_type: right_outer
            ts_join_name: fact_retapp_sales_join1

  2. If you are adding column properties, add a new line, meta, under the description section for a column. If you are adding joins, add a new line, meta, under the field section for a column relationship:

    models:
  - name: dim_retapp_stores
    columns:
      - name: LATITUDE
        description: latitude (data type float)
        meta:
          <ts_metadata_content>

      - name: PRODUCTID
        description: PRODUCTID (data type number)
        tests:
        - not_null
        - relationships:
          to: ref('dim_retapp_products')
          field: PRODUCTID
          meta:
            <ts_metadata_content>

  3. Specify column properties and joins, using the column properties and joins metadata tags. For example:

    models:
  - name: dim_retapp_stores
    columns:
      - name: LATITUDE
        description: latitude (data type float)
        meta:
          ts_geo_config:
            type : LATITUDE

      - name: QUANTITYPURCHASED
        description: quantitypurchased (data type number)
        meta:
          ts_column_type: measure
          ts_format_pattern : "#,###"
          ts_currency_type :
            type : from_browser

  4. When you integrate with dbt, the tables and Worksheets ThoughtSpot creates will automatically have the column properties you defined.

Column properties and joins metadata tags

ThoughtSpot supports the following metadata tags and values when defining schemas in dbt.

ThoughtSpot does not support a metadata tag for column descriptions, because dbt already has a description tag. ThoughtSpot’s metadata tags all begin with ts_.
ts_additive

Controls the type of aggregation that is available for the column. Determines whether the column values can be used in mathematical operations. For more information on additive columns, see Set ADDITIVE or AGGREGATION.

Possible values

yes or no

Default values

If the column type is measure, the value must be yes, or ThoughtSpot will return an error. If the column type is attribute, the value must be no, or ThoughtSpot will return an error.

Sample
- name: QUANTITYPURCHASED
  description: quantitypurchased (data type number)
  meta:
    ts_column_type: measure
    ts_additive: yes
ts_aggregation

Sets the default aggregation type for a column. For more information on aggregation, see Set ADDITIVE or AGGREGATION.

Possible values

If the column type is attribute: count, count_distinct, min, max, or none. If you try to set a value not in this list, ThoughtSpot will return an error.

If the column type is measure: count, count_distinct, min, max, sum, average, std_deviation, or variance. If you try to set a value not in this list, ThoughtSpot will return an error.

Default values

If the column type is measure, the default is sum. If the column type is attribute, the default is none.

Sample
- name: QUANTITYPURCHASED
  description: quantitypurchased (data type number)
  meta:
    ts_column_type: measure
    ts_additive: yes
    ts_aggregation: sum
ts_attr_dim

Only applies to tables that join over a chasm trap. Designates whether the tables depend on this column for attribution. For more information about attribution dimensions, see Change the Attribution dimension.

Possible values

yes or no

Default values

The default is yes.

Sample
- name: PRODUCTNAME
  description: productname (data type text)
  meta:
    ts_column_type : attribute
    ts_additive : yes
    ts_aggregation : count
    ts_attr_dim : yes
ts_calendar_type

Specifies what type of calendar a date type column uses. It can be the Gregorian calendar (default), a fiscal calendar, or any other custom calendar. For more information about custom calendars, see Custom calendar overview.

Possible values

none, default, or a specified custom calendar

Default values

If the data type is VARCHAR, INT, BIGINT, FLOAT, BOOL, or DOUBLE, the default is none, and this tag is not editable. If the data type is DATE or DATETIME, the default is none, and the tag is editable.

Sample
- name: DATE
  description: date (data type date)
  meta:
    ts_column_type : attribute
    ts_calendar_type : <custom_calendar_name>
ts_column_type

Sets the column type for the column. For more information on column types, see Change column type.

Possible values

attribute or measure

Default values

If the data type is FLOAT, DOUBLE, INT, or BIGINT, the default is measure. If the data type is VARCHAR, BOOL, DATE, or DATETIME, the default is attribute.

Sample
- name: QUANTITYPURCHASED
  description: quantitypurchased (data type number)
  meta:
    ts_column_type: measure
ts_currency_type

Specifies the format to use for currency values in the column. For more information about currency types, see Set currency type.

Possible values

from_isocode, from_browser, from_column, none. For a list of supported ISO codes, view or download this file.

Default values

The default is none.

Samples
- name: SALES
  description: sales (data type number)
  meta:
    ts_column_type: measure
    ts_currency_type :
      type : from_isocode
      isocode : USD

- name: DISCOUNT
  description: discount (data type number)
  meta:
    ts_column_type: measure
    ts_currency_type :
      type : from_browser

- name: REVENUE
  description: revenue (data type number)
  meta:
    ts_column_type: measure
    ts_currency_type :
      type : from_column
      column : <column_name>
ts_format_pattern

Specifies the format to use for numeric values or dates in the column. For more information about format patterns, see Set number, date, and currency formats.

Possible values

Refer to the number and date formats specified in Set number, date, and currency formats. You can only set this value if the column data type is INT, BIGINT, DOUBLE, DATE, or DATETIME, or ThoughtSpot will return an error.

Default values

By default, ThoughtSpot shows the data as it appears in the table or Worksheet, without any number or date formatting.

Sample
- name: QUANTITYPURCHASED
  description: quantitypurchased (data type number)
  meta:
    ts_column_type: measure
    ts_format_pattern : "#,###"
ts_geo_config

Enables a column to be used in geo map visualizations. For more information about geo configuration, see Add a geographical data setting.

Possible values

none, latitude, longitude, country, sub_nation_region

The sub_nation_region value depends on the country you specify. For example, for the United States, ThoughtSpot supports State, County, and Zip Code. For Denmark, ThoughtSpot supports Region, Municipality, and Postal Code. To determine which sub-nation regions ThoughtSpot supports for the relevant country, see Geo map reference. If you specify a sub-nation region that is not supported for that country, such as Principality for United States, ThoughtSpot will return an error.
Default values

The default is none.

Samples
- name: STATE
  description: state data (data type text)
  meta:
    ts_geo_config :
      type : sub_nation_region
      country : United States
      region_type : State

- name: LONGITUDE
  description: longitude data (data type float)
  meta:
    ts_geo_config :
      type : longitude
ts_hidden

Sets the column visibility. For more information about hidden columns, see Hide a column.

Possible values

yes or no

Default values

The default is no.

Sample
- name: QUANTITYPURCHASED
  description: quantitypurchased (data type number)
  meta:
    ts_column_type: measure
    ts_hidden: yes
ts_index_priority

Sets the indexing priority the column uses. For more information about column indexing, see Manage suggestion indexing.

Possible values

Any whole number between 1-10. Use a value between 8-10 for important columns to improve their search ranking. Use 1-3 for low priority columns.

Default values

The default is 1.

Sample
- name: QUANTITYPURCHASED
  description: quantitypurchased (data type number)
  meta:
    ts_column_type: measure
    ts_index_priority : 2
ts_index_type

Sets the type of indexing the column uses. For more information about column indexing, see Manage suggestion indexing.

Possible values

default or dont_index

Default values

If the data type is INT, BIGINT, DATE, DATETIME, or DOUBLE, the default is dont_index. For all other data types, the default is default.

Sample
- name: QUANTITYPURCHASED
  description: quantitypurchased (data type number)
  meta:
    ts_column_type: measure
    ts_synonym: Purchased,Quantity
    ts_index_type : dont_index
ts_join_cardinality

Sets the cardinality for the join. For more information about join cardinality, see Join cardinality.

Possible values

one_to_one, many_to_one, or one_to_many

Default values

The default is one_to_one.

Sample
- name: PRODUCTID
  description: PRODUCTID (data type number)
  tests:
  - not_null
  - relationships:
    to: ref('dim_retapp_products')
    field: PRODUCTID
    meta:
      ts_join_cardinality: many_to_one
      ts_join_type: right_outer
      ts_join_name: fact_retapp_sales_join
ts_join_name

Specifies the name for the join. For more information about joins, see Join a worksheet to another data source.

Possible values

any text string

Default values

There is no default.

Sample
- name: PRODUCTID
  description: PRODUCTID (data type number)
  tests:
  - not_null
  - relationships:
    to: ref('dim_retapp_products')
    field: PRODUCTID
    meta:
      ts_join_cardinality: many_to_one
      ts_join_type: right_outer
      ts_join_name: fact_retapp_sales_join
ts_join_type

Specifies the join type. For more information about join types, see Join types.

Possible values

inner, left_outer, right_outer, and full_outer

Default values

The default is inner.

Sample
- name: PRODUCTID
  description: PRODUCTID (data type number)
  tests:
  - not_null
  - relationships:
    to: ref('dim_retapp_products')
    field: PRODUCTID
    meta:
      ts_join_cardinality: many_to_one
      ts_join_type: right_outer
      ts_join_name: fact_retapp_sales_join
ts_spotiq_pref

Excludes specified columns from SpotIQ analyses. By default, ThoughtSpot includes all columns in SpotIQ analysis. For more information about SpotIQ preferences, see Exclude columns from SpotIQ analyses.

Possible values

default or exclude

Default values

The default is default, in which ThoughtSpot includes all columns in SpotIQ analysis.

Sample
- name: PRODUCTNAME
  description: productname (data type text)
  meta:
    ts_column_type : attribute
    ts_additive : yes
    ts_aggregation : count
    ts_spotiq_pref : exclude
ts_synonym

Specifies synonyms that can be used in the search bar to refer to a column. For more information about column synonyms, see Create synonyms for a column.

Possible values

comma-separated text values

Default values

There is no default.

Sample
- name: QUANTITYPURCHASED
  description: quantitypurchased (data type number)
  meta:
    ts_column_type: measure
    ts_synonym: Purchased,Quantity
Was this page helpful?
×