Supported Validations for DBT Segments

To create a segment, use the POST /ssot/segments endpoint. To update a segment, use the PATCH /ssot/segments/{segmentApiName} endpoint.

When creating or updating a segment, the sql property 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.

1---FAIL
2select max(Individual_dense_viv__dlm.age__c) from Individual_dense_viv__dlm
3---FAIL
4select count(Individual_dense_viv__dlm.individualid__c) from Individual_dense_viv__dlm

No select all (*) expression is allowed in the top-level select.
1---FAIL 2select * from Individual_dense_viv__dlm
Only the primary key of the segment on table (the first table in the from clause of the sql statement) can be selected.
1---PASS 2select Individual_dense_viv__dlm.individualid__c from Individual_dense_viv__dlm

Multiple columns can’t be selected even if one of them is the primary key of the table.

1---FAIL
2select Individual_dense_viv__dlm.individualid__c,
3Individual_dense_viv__dlm.age__c from Individual_dense_viv__dlm

No case statements are allowed in the primary select.

1---FAIL
2select
3  case
4      when Individual_dense_viv__dlm.individualid__c > 10 then Individual_dense_viv__dlm.individualid__c
5      else null
6  end
7from
8  Individual_dense_viv__dlm

If the primary key of the segmentOn object 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.
1---PASS 2select Individual__dlm.id__c, Individual__dlm.fq__id__c from Individual__dlm

If the primary key of the segmentOn object has key qualifiers, you can provide an additional condition in the join on condition.

1---PASS
2select Individual__dlm.id__c from Individual__dlm left join Sales__dlm on Individual__dlm.id__c = Sales__dlm.soldToCustomerId__c and Individual__dlm.kq__id__c is not distinct from Sales__dlm.kq__soldToCustomerId__c

All columns must be fully qualified by table name in the query and subselect queries.
1---FAIL 2select individualid__c from Individual_dense_viv__dlm

Subqueries are supported only in a where clause and must emit only one column.

1---FAIL
2select Individual_dense_viv__dlm.individualid__c from (select * from Individual_dense_viv__dlm)

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.
1---PASS 2select t.id__c from Individual__dlm as t where cast(t.id__c as varchar(100)) = t1.name

limit and offset are supported.

1---PASS
2select Individual_dense_viv__dlm.individualid__c from Individual_dense_viv__dlm limit 10

Any SQL statement other than the select statement isn’t allowed.

1---FAIL
2update Individual_dense_viv__dlm set Individual_dense_viv__dlm.individualid__c = 'aa'

Aliases are not supported.

1---FAIL
2select t.id__c from Individual__dlm as t

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.

1---PASS
2select Individual__dlm.id__c from Individual__dlm left join Sales__dlm on Individual__dlm.id__c = Sales__dlm.soldToCustomerId__c
3
4--PASS
5Individual__dlm left join Sales__dlm on Individual__dlm.id__c = Sales__dlm.soldToCustomerId__c and Individual__dlm.kq__id__c is not distinct from Sales__dlm.kq__soldToCustomerId__c