Supported Validations for DBT Segments

You can create a segment using the /ssot/segments resource with the Data Cloud Segment Input request body. Similarly, you can update a segment using the /ssot/segments/segmentApiName resource with the same request body. The Data Cloud Segment Dbt Model Input request body, which is nested in the Data Cloud Segment Input request body, provides validation for the SQL.

The sql property of the Data Cloud Segment Dbt Model Input is subject to these validations.

• Only the primary key of the SegmentOn DMO is allowed in the select statement. The first table in the join clause must be a profile table.
  • No aggregation (min, max, avg, count) is allowed at the top-level select.
  • No select all (*) expression is allowed in the top-level select.
  • Only the primary key of the segment on table (the first table in the from clause of the sql statement) is allowed to be selected.
  • Multiple columns can’t be selected even if one of them is the primary key of the table.
  • No case statements are allowed in the primary select.
  • If the primary key of the segmentOn entity has key qualifiers, you must project the key qualifiers, as well, in the primary select. First project the primary key and then the qualifier. Group bys must also include the key qualifiers.
  • If the primary key of the segmentOn entity has key qualifiers, you can provide an additional condition in the join on condition.
• All columns must be fully qualified by tablename in the query and subselect queries.
• Subqueries are supported only in a where clause and must emit only one column.
• Compare columns of the same data type. To compare columns of different data types, cast one or both of the operands so that they have the same type.
• Limit and offset are supported.
• Any sql statement other than the select statement isn’t allowed.
• Aliases are supported only for DMOs in the from block of the query. Columns can’t be aliased.
• To join two DMOs, there must be a relationship between the DMOs, and you must use one of their related join keys in the join on condition. The join on condition can contain only an equality comparison between the joining keys and an optional additional condition for comparing FQK fields.