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

• 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)

• 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'