Breadcrumbs

SEMANTIC_VIEW

Supported DBMS products: Snowflake

Good to know

SEMANTIC_VIEW physical type will generate a SEMANTIC VIEW in Snowflake.

Current implementation supports the definition to be given as SQL (ie. no mappings based SQL generation is support yet, neither the YAML format of Snowflake specification).

See also

Example

Defined entity publish.TAXI_TRIP_CONTENT with physical type SEMANTIC_VIEW.

Adding load with entity mappings:

  • pub.F_TRIP

  • pub.D_RATECODE

  • pub.D_TAXI_ZONE

Adding OVERRIDE VIEW SQL statement 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 UNIQUE (taxi_zone_wid),
  dropoff_zones AS pub.D_TAXI_ZONE 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 UNIQUE (taxi_zone_wid),
  dropoff_zones AS pub.D_TAXI_ZONE 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'
;