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.
    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
    5---

  • 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) is allowed to 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 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.
    1---PASS
    2select Individual__dlm.id__c, Individual__dlm.fq__id__c from Individual__dlm

  • If the primary key of the segmentOn entity 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 tablename 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---FAIL
  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 supported only for DMOs in the from block of the query. Columns can’t be aliased.
  1---PASS
  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

  1--PASS
  2Individual__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