v0.3.0 dbt_servicenow

PR #10 includes the following updates:

Breaking Changes

• The following fields were removed from the servicenow__task_enhanced model as problem_first_reported_by_task_value was incorrectly assumed to have corresponding sys_user values, but in actuality is related to the task table and therefore have no user fields.
  • problem_reporter_email
  • problem_reporter_manager_value
  • problem_reporter_department_value
  • problem_reporter_name
  • problem_reporter_roles

Feature Updates

Display Names

• The following fields were added to the servicenow__incident_enhanced model to add relevant display names to the caller_id_value.

  • caller_email
  • caller_manager_value
  • caller_department_value
  • caller_name
  • caller_roles

• The following fields were added to the servicenow__user_enhanced model to add relevant display names to the company_value and manager_value.

  • company_name
  • manager_name

• The following label fields were added to respective choice fields in order to bring the display value (dv), or human-readable text available in the ServiceNow UI, displayed for each choice option:

  • dv_priority_label
  • dv_impact_label
  • dv_urgency_label
  • dv_task_state_label
  • dv_problem_state_label
  • dv_change_request_type_label
  • dv_change_request_phase_state_label
  • dv_change_request_risk_label
  • dv_incident_category_label
  • dv_incident_subcategory_label
  • dv_incident_severity_label
  • dv_incident_state_label
  • dv_business_impact_label

• Certain choice fields have labels that can depend on the dependent_value field in the sys_choice table. This dependency typically arises when the same choice field can have different labels based on the context in which it is used. Therefore some elements from sys_choice are also joined in on the additional dependent_value field. For example, here is a common example where dependent_value might influence the label:

  • incident_category and incident_subcategory: These fields often depend on each other. For instance, if the incident_category is "Software," the incident_subcategory might have different options compared to when the category is "Hardware." The dependent_value in this case could represent the incident_category and influence the incident_subcategory labels.
  • For more information about the logic used to attach labels to choice fields, refer to the DECISIONLOG where a section has been added (titled Methodology for Adding Labels for Choice Fields).

New Staging Model

• To include labels, we used a new source table sys_choice. This is reflected upstream, in the newly added stg_servicenow__sys_choice and stg_servicenow__sys_choice_base.

Aggregations in User Models

• The following fields, which previously only existed in intermediate CTEs, have been persisted through to the servicenow__user_aggregated and servicenow__user_enhanced models to add more user metadata:
  • count_distinct_sys_user_group_ids
  • count_distinct_sys_user_role_ids
  • count_distinct_sys_user_role_names
  • count_distinct_included_roles
  • count_distinct_sys_user_group_roles
• Additionally, we've realized the way these fields were previously aggregated could unintentionally also include blank values that were not-null, so we have updated the logic accordingly.

Under The Hood

• Added explicit casts to timestamp fields, as well as string casts to choice fields in order to join them later downstream on sys_choice.element.
• Added consistency and integrity validation tests within integration_tests folder for all current end models.

Full Changelog: v0.2.0...v0.3.0