Snowflake Reader Account Views Overview – Verizon Connect Enterprise Help

Snowflake is our analytics warehouse, and we have several pipelines that "pull" information from our underlying databases into Snowflake. As such, there is a delay between any changes made in Fleet, and the data being available in Snowflake. In general, this delay is 2 hours for facts, and 8 hours for dimensions. More detailed values can be found in the Views Overview table, below.

It is assumed that all consumers know about how Star Schemas and Slow Changing Dimensions work.

Views Overview Table Column Descriptions

Refer to the following sections for a detailed description of the columns in the Views Overview table.

Frequency

This column refers to how often we re-pull data and update the views we share, and can be taken as the "average time" it takes for a view to reflect changes made upstream (that is, changes made in Fleet). These times are only estimates, and can vary due to load, processing delays, and data issues upstream, for example.

Population Notes

This column refers to any additional notes when it comes to how a view is created, updated, or deleted. In general, this is mainly determined by whether the underlying table is a fact or dimension:

Fact: Fact views are usually a snapshot of a given time. Creates/updates occur as the underlying data changes, but then are never further updated/deleted. For example, unit_driver_history rows get created once a unit is assigned to a driver, and are further updated once that assignment finishes. Afterwards, a completed assignment should never get updated or deleted.
Dimension: Dimension views are usually a representation of an underlying "entity" that the customer can directly create, update, or delete. Dimension views can contain an is_current column, so if a user only wants to fetch the latest version of an entity, they should use WHERE is_current. Dimension views can also contain "current" and "historic" columns, which refer to the underlying slow changing dimension type, so that customers will only fetch the "latest" value for certain column. For example, the vehicle view contains the vehicle_name and vehicle_name_history columns.
- vehicle_name is current, which means all rows for that vehicle (that is, same vehicle_id) will always have the same value for vehicle_name, and any time the vehicle's name is updated, all rows will get updated. This ensures queries/reports run in the future will use the new vehicle name, not the old one.
- vehicle_name_history is historic, which means it will contain the value for the column at the time it was created and should never be updated.

Views Overview

The Snowflake views are summarized in the following table. For a pictorial image of how the views relate to one another, refer to the Entity Relationship Diagram.

View Name	Description	View Type	Join Fields	Average Update Frequency	Population Notes
address	Contains key details related to an address, including state, city, and suburb. Note that addresses are immutable.	Dimension	address_id	Every 8 hours	Created/updated upstream, new rows pulled in per frequency.
app_event	Contains details of events from miscellaneous mobile applications within the platform.	Fact	app_event_key, driver_key, event_type_key	Every 2 hours	Created/updated upstream, new rows pulled in per frequency.
condition	Contains data around the configured conditions that can trigger Insight Alerts.	Dimension	condition_key, condition_id	Every 8 hours	Created/updated upstream, new rows pulled in per frequency.
device	Contains information about the devices (hardware) installed in vehicles.	Dimension	device_id	Every 8 hours	Created/updated upstream, new rows pulled in per frequency.
device_link_history	Contains information about the link history of devices with units.	Fact	unit_id, device_id	Every 8 hours	Created/updated upstream, new rows pulled in per frequency.
driver	Contains key details related to a driver, including hierarchy information and other user details.	Dimension	driver_key	Every 8 hours	Created/updated upstream, new rows pulled in per frequency.
driver_day	Contains day-level aggregate KPIs related to a driver.	Fact	driver_key	Full update every 24 hours, partial updates every 4 hours	Complete rows computed every 24 hours during "full aggregation processing". Regular updates every 4 hours only process up to 72 hours in the past.
driver_form_instance	Contains information about the driver forms related to the customer account.	Fact	form_instance_xml_id	Every 2 hours	Created/updated upstream, new rows pulled in per frequency.
eld_driving_period	Contains key details related to ELD driving period, including start time, end time and events.	Fact	driving_period_id, vehicle_key, unit_id	Every 2 hours	Created/updated upstream, new rows pulled in per frequency.
eld_driving_period_action	Contains key details related to ELD driving period actions including time of action and action_id.	Fact	driving_period_action_id, driving_period_id	Every 2 hours	Created/updated upstream, new rows pulled in per frequency.
event	Contains individual event data relating to units.	Fact	unit_id	Full update every 24 hours, partial updates every 4 hours	Complete rows computed every 24 hours during "full aggregation processing". Regular updates every 4 hours only process up to 72 hours in the past.
hierarchy_node	Contains information about the hierarchy configured for an account.	Dimension	hierarchy_node_id	Every 8 hours	Created/updated upstream, new rows pulled in per frequency. Will only contain latest hierarchy nodes.
insight_alert	Contains data around Insight Alerts that have been triggered in the VZC Fleet platform.	Fact	condition_id, condition_key, vehicle_key, driver_key	Every 2 hours	Created/updated upstream, new rows pulled in per frequency.
install_type_diagnostic_parameter	Contains information about the diagnostic parameters configured for a particular install type.	Dimension	install_type_id	Every 8 hours	Created/updated upstream, new rows pulled in per frequency. Will only contain rows that aren't deleted.
job_pva	Contains planned vs actual details for a job including its status, associated route and territory.	Fact	job_id, route_id, territory_key, marker_key	Every 2 hours	Created/updated upstream, new rows pulled in per frequency.
marker	Contains details of the markers for an account, including category and geometry details.	Dimension	marker_key	Every 8 hours	Created/updated upstream, new rows pulled in per frequency. Will only contain rows that aren't deleted.
mobile_event_type	Contains details of the different mobile event types that can be present in the app_event fact	Dimension	mobile_event_type_key	Every 8 hours	Created/updated upstream, new rows pulled in per frequency.
nru_event	Details around time periods where units were determined to be non-reporting.	Fact	unit_id, vehicle_key, driver_id, driver_key	Every 24 hours	Created/updated upstream, new rows pulled in per frequency.
point_with_vehicle	Contains point data from vehicles, including GPS location, diagnostics, and other sensor data.	Fact	vehicle_key	Every 3 hours	Created/updated upstream, new rows pulled in per frequency. Point views have further processing delays.
point_with_vehicle_and_driver	Contains point data from vehicles, including driver, GPS location, diagnostics, and other sensor data.	Fact	driver_key, vehicle_key	Every 3 hours	Created/updated upstream, new rows pulled in per frequency. Point views have further processing delays.
route_pva	Contains planned vs actual details for a route including its status, job count and territory.	Fact	route_id, territory_key, vehicle_key, driver_key	Every 2 hours	Created/updated upstream, new rows pulled in per frequency.
territory	Contains details of the territory for an account, including its name and timezone.	Dimension	territory_key	Every 8 hours	Created/updated upstream, new rows pulled in per frequency. Will only contain rows that aren't deleted.
time_on_site	Contains details about the duration a vehicle spent within markers at certain times.	Fact	vehicle_key, marker_key	Full update every 24 hours, partial updates every 3 hours	Complete rows computed every 24 hours, separate process to other aggregation tables. Regular updates every 3-4 hours, but can vary in how far they look back.
trip	Contains trip-level aggregate KPIs related to a driver and associated vehicle.	Fact	driver_key, vehicle_key	Full update every 24 hours, partial updates every 4 hours	Complete rows computed every 24 hours during "full aggregation processing". Regular updates every 4 hours only process up to 72 hours in the past.
trip_by_state	Contains trip-level aggregate KPIs related to a driver and associated vehicle. The data is the same as in the “trip” fact, with the exception that rows are further split when the vehicle crosses a state boundary.	Fact	driver_key, vehicle_key	Full update every 24 hours, partial updates every 4 hours	Complete rows computed every 24 hours during "full aggregation processing". Regular updates every 4 hours only process up to 72 hours in the past.
unit_driver_history	Contains information relating to the assignment of drivers to vehicles.	Fact	unit_id, driver_id	Every 2 hours	Created and updated upstream as vehicles/drivers are assigned to each other. New rows pulled in per frequency.
vehicle	Contains key details related to a vehicle, including year, make, model, and time zone.	Dimension	vehicle_key, unit_id	Every 8 hours	Created upstream, new rows pulled in per frequency.
vehicle_day	Contains day-level aggregate KPIs related to a vehicle.	Fact	vehicle_key	Full update every 24 hours, partial updates every 4 hours	Complete rows computed every 24 hours during "full aggregation processing". Regular updates every 4 hours only process up to 72 hours in the past.
video_course	Contains information about video courses that may be taken by a driver via the Coach app.	Dimension	video_course_key	Every 8 hours	Created upstream, new rows pulled in per frequency.

Note

The data available through Self-Service Analytics is shared using secure views and not tables. Querying with analytics tools that are trying to find the primary and foreign keys to map the relationships between tables does not work. Data and structure can change at any time, without notice. This includes the information in the table above, and in the following diagram.

Entity Relationship Diagram