Anatomy of a Semantic View

Snowflake recently announced the General Availability of the Semantic Views. In this post we will cover the key aspects of a Semantic View and use a real-world Salesforce Marketing Cloud (SFMC) dataset to demonstrate the power of Semantic Views. If you are not familiar with Salesforce Marketing Cloud, you can checkout the following documentation:

The three Objects (tables) that we will be using from Salesforce Marketing Cloud are Send, OpenEvent and ClickEvent. The OpenEvent is event when the Marketing email recipient opens the email and ClickEvent is the event captured when the recipient Clicks on one of the links in the Marketing email.

Let’s start by re-creating a production dataset. The email addresses have been masked for Privacy reasons.

insert into Send values (1, '2025-01-01', 'Prada Paradoxe Coupon');
insert into Send values (2, '2025-01-01', 'Juliette Has A Gun Coupon');
insert into Send values (3, '2025-01-01', 'Gucci Flora Coupon');
insert into Send values (4, '2025-01-01', 'Miss Dior Coupon');
insert into Send values (5, '2025-01-01', 'Delina Exclusif Coupon');
insert into Send values (6, '2025-01-01', 'Mojave Ghost Coupon');
insert into Send values (7, '2025-01-01', 'Club De Nuit Coupon');


insert into OpenEvent values (1, 1, 'maryam@email.tld', current_timestamp);
insert into OpenEvent values (2, 1, 'fatima@email.tld', current_timestamp);
insert into OpenEvent values (3, 1, 'uroosa@email.tld', current_timestamp);
insert into OpenEvent values (4, 1, 'hannah@email.tld', current_timestamp);
insert into OpenEvent values (5, 1, 'zainab@email.tld', current_timestamp);
insert into OpenEvent values (6, 1, 'aliya@email.tld', current_timestamp);
insert into OpenEvent values (7, 2, 'aliya@email.tld', current_timestamp);
insert into OpenEvent values (8, 4, 'hannah@email.tld', current_timestamp);
insert into OpenEvent values (9, 1, 'hannah@email.tld', current_timestamp);


insert into ClickEvent values (1, 1, 'maryam@email.tld', current_timestamp);
insert into ClickEvent values (2, 1, 'fatima@email.tld', current_timestamp);
insert into ClickEvent values (3, 1, 'uroosa@email.tld', current_timestamp);
insert into ClickEvent values (4, 1, 'hannah@email.tld', current_timestamp);
insert into ClickEvent values (5, 2, 'aliya@email.tld', current_timestamp);

Now let’s create a Semantic View that captures the relationships with the Send and the ClickEvent and the OpenEvent

create or replace semantic view sfmc_insights 

  TABLES( 
    Send as send PRIMARY KEY (id)
    , OpenEvent as OpenEvent PRIMARY KEY (id)
    , ClickEvent as ClickEvent PRIMARY KEY (id)
  )
  
  RELATIONSHIPS (
    OpenEvent (SendID) references Send (Id)
    , ClickEvent (SendID) references Send (Id)

  )
  
  DIMENSIONS (
    Send.id as id
    , Send.Subject as Subject
    , ClickEvent.SubscriberKey as SubscriberKey
    , OpenEvent.SubscriberKey as SubscriberKey

  )

  METRICS (
    ClickEvent.total_clicks as count(id)
    , OpenEvent.total_opens as count(id)
  )

;

Now let’s SELECT from the Semantic View. Let’s say we want to see how each Marketing email performed, i.e. the Number of Open and the Number of Clicks. We can use the following SELECT for that:

SELECT * FROM SEMANTIC_VIEW(
  sfmc_insights
    DIMENSIONS 
      Subject
    METRICS 
          total_opens
        , total_clicks
)

Now let’s say we interested in the individual recipients and how they reacted to each Marketing Email i.e. how many times they “Opened” the email. We can use the following SELECT:

SELECT * FROM SEMANTIC_VIEW(

  sfmc_insights
    DIMENSIONS 
      OpenEvent.SubscriberKey
      , Subject
    METRICS 
          total_opens
      
)
order by 1 desc

You can do the same for number of clicks by each receipt by the Marketing email:

SELECT * FROM SEMANTIC_VIEW(

  sfmc_insights
    DIMENSIONS 
      ClickEvent.SubscriberKey
      , Subject

    METRICS 
          total_clicks
      
)
order by 1 desc;

One thing to note is that in a Semantic View you have to define the relationship. In this dataset there is no relationship between the OpenEvent and ClickEvent.

SELECT * FROM SEMANTIC_VIEW(

  sfmc_insights
    DIMENSIONS 
      OpenEvent.SubscriberKey
      , ClickEvent.SubscriberKey 

    METRICS 
          total_clicks
         , total_opens
      
)

The above query will result in an error:

Invalid dimension specified: The dimension entity 'OPENEVENT' must be related to and have an equal or lower level of granularity compared to the base metric or dimension entity 'CLICKEVENT'.