Supported Validations for DBT Segments

You can create a segment using the createSegment(input) method with the ConnectApi.CdpSegmentInput class. Similarly, you can update a segment using the updateSegment(segmentApiName, input) method with the same input class. The ConnectApi.CdpSegmentDbtModelInput input class, which is nested in the ConnectApi.CdpSegmentInput class, provides validation for the SQL.

The sql property of the ConnectApi.CdpSegmentDbtModelInput 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'