SEMANTIC_VIEW

Supported DBMS products: Snowflake, Databricks SQL

Starting from 26.0.650.patch3, entity descriptions are no longer used as
COMMENTs on SEMANTIC VIEWs in Snowflake, as they caused syntax errors during
creation. If you need a COMMENT on the SEMANTIC VIEW, add it manually in your
creation clause.

Good to know

In Snowflake SEMANTIC_VIEW physical type will generate a SEMANTIC VIEW. The logic is given in SQL.

In Databricks SQL SEMANTIC_VIEW physical type will generate a METRICS VIEW. The logic is given in YAML. Based on view generation specifics, Databricks Metrics view does support attributes, entity description and entity database tags. See example below.


See also


Snowflake example

Case: Defined entity publish.TAXI_TRIP_CONTENT with physical type SEMANTIC_VIEW.

Adding load with entity mappings:

  • pub.F_TRIP

  • pub.D_TAXI_ZONE_PICKUP

  • pub.D_TAXI_ZONE_DROPOFF

Adding View SQL like this:

SQL
TABLES (
  trips AS pub.F_TRIP
    UNIQUE (trip_wid)
    WITH SYNONYMS ('rides')
    COMMENT = 'All NYC metered taxi trips (yellow and green services)',
  pickup_zones AS pub.D_TAXI_ZONE_PICKUP UNIQUE (taxi_zone_wid),
  dropoff_zones AS pub.D_TAXI_ZONE_DROPOFF UNIQUE (taxi_zone_wid)
)
  
RELATIONSHIPS (
  trips(taxi_zone_pickup_wid) REFERENCES pickup_zone,
  trips(taxi_zone_dropoff_wid) REFERENCES dropoff_zone
)

FACTS (
  trips.fare_amount AS fare_amount
    WITH SYNONYMS = ('base fare')
    COMMENT = 'Time-and-distance fare in dollars calculated by the meter',
  trips.total_amount AS total_amount
    WITH SYNONYMS = ('total charge', 'trip total')
    COMMENT = 'Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)',
  trips.trip_distance AS trip_distance
    COMMENT = 'Total distance traveled in miles'
)

DIMENSIONS (
  trips.taxi_type AS taxi_type
    WITH SYNONYMS = ('service type', 'taxi service')
    COMMENT = 'Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)',
  pickup_zones.pickup_taxi_zone_name as taxi_zone
    COMMENT = 'Official NYC TLC taxi zone name for the pickup location',
  dropoff_zones.dropoff_taxi_zone_name as taxi_zone
    COMMENT = 'Official NYC TLC taxi zone name for the dropoff location',
  pickup_zones.pickup_borough as borough,
  dropoff_zones.dropoff_borough as borough,
)

METRICS (
  trips.total_revenue AS SUM(total_amount)
    WITH SYNONYMS = ('gross revenue'),
  trips.fare_revenue AS SUM(fare_amount),
  trips.total_distance AS SUM(trip_distance),
  trips.average_trip_total AS AVG(total_amount)
    WITH SYNONYMS = ('average revenue per trip')
)

COMMENT = 'Semantic view for taxi data revenue analysis'

Will generate a Snowflake SEMANTIC VIEW like this:

SQL
CREATE SEMANTIC VIEW publish.TAXI_TRIP_CONTENT

TABLES (
  trips AS pub.F_TRIP
    UNIQUE (trip_wid)
    WITH SYNONYMS ('rides')
    COMMENT = 'All NYC metered taxi trips (yellow and green services)',
  pickup_zones AS pub.D_TAXI_ZONE_PICKUP UNIQUE (taxi_zone_wid),
  dropoff_zones AS pub.D_TAXI_ZONE_DROPOFF UNIQUE (taxi_zone_wid)
)
  
RELATIONSHIPS (
  trips(taxi_zone_pickup_wid) REFERENCES pickup_zone,
  trips(taxi_zone_dropoff_wid) REFERENCES dropoff_zone
)

FACTS (
  trips.fare_amount AS fare_amount
    WITH SYNONYMS = ('base fare')
    COMMENT = 'Time-and-distance fare in dollars calculated by the meter',
  trips.total_amount AS total_amount
    WITH SYNONYMS = ('total charge', 'trip total')
    COMMENT = 'Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)',
  trips.trip_distance AS trip_distance
    COMMENT = 'Total distance traveled in miles'
)
  
DIMENSIONS (
  trips.taxi_type AS taxi_type
    WITH SYNONYMS = ('service type', 'taxi service')
    COMMENT = 'Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)',
  pickup_zones.pickup_taxi_zone_name as taxi_zone
    COMMENT = 'Official NYC TLC taxi zone name for the pickup location',
  dropoff_zones.dropoff_taxi_zone_name as taxi_zone
    COMMENT = 'Official NYC TLC taxi zone name for the dropoff location',
  pickup_zones.pickup_borough as borough,
  dropoff_zones.dropoff_borough as borough,
)

METRICS (
  trips.total_revenue AS SUM(total_amount)
    WITH SYNONYMS = ('gross revenue'),
  trips.fare_revenue AS SUM(fare_amount),
  trips.total_distance AS SUM(trip_distance),
  trips.average_trip_total AS AVG(total_amount)
    WITH SYNONYMS = ('average revenue per trip')
)

COMMENT = 'Semantic view for taxi data revenue analysis'
;


Databricks SQL example (no attributes/descriptions/tags)

Case: Defined entity publish.TAXI_TRIP_CONTENT with physical type SEMANTIC_VIEW.

Adding load with entity mappings:

  • catalog.pub.F_TRIP

  • catalog.pub.D_TAXI_ZONE_PICKUP

  • catalog.pub.D_TAXI_ZONE_DROPOFF

Adding View SQL like this:

SQL
version: 1.1
comment: "All NYC metered taxi trips (yellow and green services)"

source: catalog.pub.F_TRIP

joins:
  - name: pickup_zones
    source: catalog.pub.D_TAXI_ZONE_PICKUP
    on: source.taxi_zone_pickup_wid = pickup_zones.taxi_zone_wid
    rely:
      at_most_one_match: true

  - name: dropoff_zones
    source: catalog.pub.D_TAXI_ZONE_DROPOFF
    on: source.taxi_zone_dropoff_wid = dropoff_zones.taxi_zone_wid
    rely:
      at_most_one_match: true

fields:
  - name: trip_type
    expr: trip_type
    synonyms:
      - service type
      - taxi service
    comment: "Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)"

  - name: pickup_zone
    expr: pickup_zones.pickup_zone
    comment: "Official NYC TLC taxi zone name for the pickup location"

  - name: dropoff_zone
    expr: dropoff_zones.dropoff_zone
    comment: "Official NYC TLC taxi zone name for the dropoff location"

  - name: pickup_borough
    expr: pickup_zones.pickup_borough
    comment: "The borough of the pickup location"

  - name: dropoff_borough
    expr: dropoff_zones.dropoff_borough
    comment: "The borough of the dropoff location"

  # Include these fields if users should also be able to group or filter
  # by the individual numeric values.
  - name: fare_amount
    expr: fare_amount
    comment: "Time-and-distance fare in dollars calculated by the meter"

  - name: total_amount
    expr: total_amount
    comment: "Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)"

  - name: trip_distance
    expr: trip_distance
    comment: "Total distance traveled in miles"

measures:
  - name: total_revenue
    expr: SUM(total_amount)
    synonyms:
      - gross revenue

  - name: fare_revenue
    expr: SUM(fare_amount)

  - name: total_distance
    expr: SUM(trip_distance)
    synonyms:
      - combined distance

Will generate a metrics view as:

SQL
CREATE VIEW publish.TAXI_TRIP_CONTENT WITH METRICS LANGUAGE YAML AS 
$$
version: 1.1
comment: "All NYC metered taxi trips (yellow and green services)"

source: catalog.schema.trips

joins:
  - name: pickup_zones
    source: catalog.pub.D_TAXI_ZONE_PICKUP
    on: source.taxi_zone_pickup_wid = pickup_zones.taxi_zone_wid
    rely:
      at_most_one_match: true

  - name: dropoff_zones
    source: catalog.pub.D_TAXI_ZONE_DROPOFF
    on: source.taxi_zone_dropoff_wid = dropoff_zones.taxi_zone_wid
    rely:
      at_most_one_match: true

fields:
  - name: trip_type
    expr: trip_type
    synonyms:
      - service type
      - taxi service
    comment: "Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)"

  - name: pickup_zone
    expr: pickup_zones.pickup_zone
    comment: "Official NYC TLC taxi zone name for the pickup location"

  - name: dropoff_zone
    expr: dropoff_zones.dropoff_zone
    comment: "Official NYC TLC taxi zone name for the dropoff location"

  - name: pickup_borough
    expr: pickup_zones.pickup_borough
    comment: "The borough of the pickup location"

  - name: dropoff_borough
    expr: dropoff_zones.dropoff_borough
    comment: "The borough of the dropoff location"

  # Include these fields if users should also be able to group or filter
  # by the individual numeric values.
  - name: fare_amount
    expr: fare_amount
    comment: "Time-and-distance fare in dollars calculated by the meter"

  - name: total_amount
    expr: total_amount
    comment: "Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)"

  - name: trip_distance
    expr: trip_distance
    comment: "Total distance traveled in miles"

measures:
  - name: total_revenue
    expr: SUM(total_amount)
    synonyms:
      - gross revenue

  - name: fare_revenue
    expr: SUM(fare_amount)

  - name: total_distance
    expr: SUM(trip_distance)
    synonyms:
      - combined distance
$$
;

Databricks SQL example (with attributes,descriptions,tags)

Case: Defined entity publish.TAXI_TRIP_CONTENT with physical type SEMANTIC_VIEW.

Adding load with entity mappings:

  • catalog.pub.F_TRIP

  • catalog.pub.D_TAXI_ZONE_PICKUP

  • catalog.pub.D_TAXI_ZONE_DROPOFF

Defining attributes:

  • trip_type

  • pickup_zone

  • dropoff_zone

  • pickup_borough

  • dropoff_borough

  • fare_amount

  • total_amount

  • trip_distance

  • total_revenue

  • fare_revenue

  • total_distance

Defining entity description:

All the NYC metered taxi trips (yellow and green services) for now

Defining entity database tag:

cost_center:analytics

Adding View SQL like this:

SQL
version: 1.1
comment: "All NYC metered taxi trips (yellow and green services)"

source: catalog.pub.F_TRIP

joins:
  - name: pickup_zones
    source: catalog.pub.D_TAXI_ZONE_PICKUP
    on: source.taxi_zone_pickup_wid = pickup_zones.taxi_zone_wid
    rely:
      at_most_one_match: true

  - name: dropoff_zones
    source: catalog.pub.D_TAXI_ZONE_DROPOFF
    on: source.taxi_zone_dropoff_wid = dropoff_zones.taxi_zone_wid
    rely:
      at_most_one_match: true

fields:
  - name: trip_type
    expr: trip_type
    synonyms:
      - service type
      - taxi service
    comment: "Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)"

  - name: pickup_zone
    expr: pickup_zones.pickup_zone
    comment: "Official NYC TLC taxi zone name for the pickup location"

  - name: dropoff_zone
    expr: dropoff_zones.dropoff_zone
    comment: "Official NYC TLC taxi zone name for the dropoff location"

  - name: pickup_borough
    expr: pickup_zones.pickup_borough
    comment: "The borough of the pickup location"

  - name: dropoff_borough
    expr: dropoff_zones.dropoff_borough
    comment: "The borough of the dropoff location"

  # Include these fields if users should also be able to group or filter
  # by the individual numeric values.
  - name: fare_amount
    expr: fare_amount
    comment: "Time-and-distance fare in dollars calculated by the meter"

  - name: total_amount
    expr: total_amount
    comment: "Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)"

  - name: trip_distance
    expr: trip_distance
    comment: "Total distance traveled in miles"

measures:
  - name: total_revenue
    expr: SUM(total_amount)
    synonyms:
      - gross revenue

  - name: fare_revenue
    expr: SUM(fare_amount)

  - name: total_distance
    expr: SUM(trip_distance)
    synonyms:
      - combined distance

Will generate a metrics view as:

SQL
CREATE VIEW publish.TAXI_TRIP_CONTENT (
    trip_type
    , pickup_zone
    , dropoff_zone
    , pickup_borough
    , dropoff_borough
    , fare_amount
    , total_amount
    , trip_distance
    , total_revenue
    , fare_revenue
    , total_distance
) WITH METRICS LANGUAGE YAML
COMMENT 'All the NYC metered taxi trips (yellow and green services) for now'
AS $$
version: 1.1
comment: "All NYC metered taxi trips (yellow and green services)"
source: ade_test.pub.F_TRIP_DB
joins:
  - name: pickup_zones
    source: ade_test.pub.D_TAXI_ZONE_DB_PICKUP
    on: source.taxi_zone_pickup_wid = pickup_zones.taxi_zone_wid
    rely:
      at_most_one_match: true
  - name: dropoff_zones
    source: ade_test.pub.D_TAXI_ZONE_DB_DROPOFF
    on: source.taxi_zone_dropoff_wid = dropoff_zones.taxi_zone_wid
    rely:
      at_most_one_match: true
fields:
  - name: trip_type
    expr: trip_type
    synonyms:
      - service type
      - taxi service
    comment: "Taxi type: yellow (citywide street-hail) or green (outer-borough street-hail)"
  - name: pickup_zone
    expr: pickup_zones.pickup_zone
    comment: "Official NYC TLC taxi zone name for the pickup location"
  - name: dropoff_zone
    expr: dropoff_zones.dropoff_zone
    comment: "Official NYC TLC taxi zone name for the dropoff location"
  - name: pickup_borough
    expr: pickup_zones.pickup_borough
    comment: "The borough of the pickup location"
  - name: dropoff_borough
    expr: dropoff_zones.dropoff_borough
    comment: "The borough of the dropoff location"
  # Include these fields if users should also be able to group or filter
  # by the individual numeric values.
  - name: fare_amount
    expr: fare_amount
    comment: "Time-and-distance fare in dollars calculated by the meter"
  - name: total_amount
    expr: total_amount
    comment: "Total amount paid by the passenger (including tips, tolls, MTA tax and other surcharges)"
  - name: trip_distance
    expr: trip_distance
    comment: "Total distance traveled in miles"
measures:
  - name: total_revenue
    expr: SUM(total_amount)
    synonyms:
      - gross revenue
  - name: fare_revenue
    expr: SUM(fare_amount)
  - name: total_distance
    expr: SUM(trip_distance)
    synonyms:
      - combined distance
$$
;

ALTER VIEW publish.TAXI_TRIP_CONTENT SET TAGS ('cost_center' = 'analytics');