_mart_transit_database.yml

version: 2

models:
  - name: dim_components
    description: '{{ doc("components_table") }}'
    tests: &mutually_exclusive_ranges
      - dbt_utils.mutually_exclusive_ranges:
          lower_bound_column: _valid_from
          upper_bound_column: _valid_to
          partition_by: source_record_id
          gaps: required
    columns:
      - &key
        name: key
        tests:
          - not_null
          - unique
        meta:
          metabase.semantic_type: type/PK
      - &valid_from_placeholder
        name: _valid_from
        description: |
          Starting timestamp from which this record is in effect (inclusive).
          Because this table has not actually been fully versioned,
          this is a constant placeholder value representing the first
          time that this table was extracted from the source data.
      - &valid_to_placeholder
        name: _valid_to
        description: |
          End timestamp until which this record is in effect (inclusive).
          Because this table has not actually been fully versioned,
          this is a constant placeholder value in the distant future.
      - &is_current_placeholder
        name: _is_current
        description: |
          Flag indicating whether this is the most recent version of
          this record.
          Because this table has not actually been fully versioned,
          this will always be True.
  - name: dim_contracts
    description: '{{ doc("contracts_table") }}'
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - name: contract_holder_organization_key
        description: |
          Unversioned Airtable record ID for contract holder organization; versioned bridge
          table to come later.
        tests:
          - relationships:
             to: ref('dim_organizations')
             field: source_record_id
      - name: contract_vendor_organization_key
        description: |
          Unversioned Airtable record ID for contract vendor organization; versioned bridge
          table to come later.
        tests:
          - relationships:
             to: ref('dim_organizations')
             field: source_record_id
      - *valid_from_placeholder
      - *valid_to_placeholder
      - *is_current_placeholder
  - name: dim_data_schemas
    description: '{{ doc("data_schemas_table") }}'
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - *valid_from_placeholder
      - *valid_to_placeholder
      - *is_current_placeholder
  - name: dim_organizations
    description: '{{ doc("organizations_table") }}'
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - name: ntd_id
        description: |
          The organization's ID in the National Transit Database
      - name: name
        description: Organization name
      - name: organization_type
        description: |
          Categorical data; example values "City/Town",
          "Joint Powers Agency"
      - name: roles
        description: |
          Array of categorical choices; example values
          "Regional Transportation Planning Agency,
          Metropolitan Planning Organization";
          "Metropolitan Planning Organization"
      - name: itp_id
        description: Cal-ITP ITP ID
        meta:
          metabase.semantic_type: type/FK
      - name: details
        description: Text description related to the organization
      - name: reporting_category
        description: |
          Categories we want to hold ourselves accountable to:
          Core: funded by Caltrans/can control or influence somewhat
          Other Public Transit: (publicly available or managed) and publicly funded transit
          Other Transit: transit that isn't the above
      - name: alias
      - name: gtfs_static_status
        description: |
          Computed value from source data: "Static OK" indicates that the number
          of associated (managed) `service` records is equal to the number of associated (managed) `service`
          records with static GTFS. Otherwise, status is "Static Incomplete".
      - name: gtfs_realtime_status
        description: |
          Computed value from source data: "RT OK" indicates that the number
          of associated (managed) `service` records is equal to the number of associated (managed) `service`
          records with realtime GTFS and at least one managed service has complete RT coverage (i.e.,
          all three RT feed types: vehicle positions, trip updates, and service alerts).
          Otherwise, status is "RT Incomplete".
      - name: hubspot_company_record_id
        description: |
          Organization's ID in Hubspot CRM.
      - name: assessment_status
        description: |
          Is either "Yes" or "No" and indicates whether this Organization
          manages qualifying assessed services
      - &valid_from_actual
        name: _valid_from
        description: |
          Starting timestamp from which this record is in effect (inclusive).
      - &valid_to_actual
        name: _valid_to
        description: |
          End timestamp until which this record is in effect (inclusive).
      - &is_current_actual
        name: _is_current
        description: |
          Flag indicating whether this is the most recent version of
          this record.
  - name: dim_services
    description: '{{ doc("services_table") }}'
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - name: name
        description: Service name
      - name: service_type
        description: |
          Array of categorical choices; example
          values "ADA paratransit, on-demand"; "fixed-route"
      - name: mode
        description: |
          Array of categorical choices; example
          values "bus, car/van"; "ferry"
      - name: currently_operating
        description: |
          Boolean for whether service is currently active
      - name: gtfs_schedule_status
        description: |
          Options are: `needed`, `research`,  `ok`, `need long-term solution`,
          or `not public`.
          This field may also be empty (null).
          **Note: This field is manually entered in the Transit Database,
          so it may be subject to some gaps in coverage and it is not updated automatically.
          Data in this field may not align with data from the GTFS pipeline,
          for example in `views.gtfs_schedule_dim_feeds`.**
      - name: gtfs_schedule_quality
        description: |
          Status of overall GTFS quality for a given transit service. Options are:
          `0 - Does not have GTFS Schedule`;
          `1 - Has URL for GTFS downloading`;
          `2 - GTFS data has active service`;
          `3 - GTFS data has zero validation errors`;
          `4 - GTFS data has zero validation warnings`;
          `5 - GTFS data satisfies first tier of GTFS Guidelines`;
          `6 - GTFS data satisfies all GTFS Guidelines`.
          This field can also be empty (null). Sometimes this field is null (missing) even
          for services that **do** have GTFS schedule data.
          **Note: This field is manually entered in the Transit Database,
          so it may be subject to some gaps in coverage and it is not updated automatically.
          Data in this field may not align with validations data from the
          GTFS pipeline, for example in `views.validation_fact_daily_feed_notices`,
          or GTFS guidelines checks implemented in `mart_gtfs_guidelines.fact_daily_guideline_checks`.
          Use those tables directly for the most up-to-date information on individual GTFS feeds'
          validation and guideline statuses.**
      - name: assessment_status
        description: |
          Is either "Yes" or "No" and indicates whether this Service qualifies as
          being an assessed service
      - name: deprecated_date
      - *valid_from_actual
      - *valid_to_actual
      - *is_current_actual
  - name: dim_products
    tests: *mutually_exclusive_ranges
    description: '{{ doc("products_table") }}'
    columns:
      - *key
      - *valid_from_placeholder
      - *valid_to_placeholder
      - *is_current_placeholder
  - name: dim_properties_and_features
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - *valid_from_placeholder
      - *valid_to_placeholder
      - *is_current_placeholder
  - name: dim_contract_attachments
    description: |
      Attachment links to images of contracts.
      One contract can have multiple attachments.
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - name: contract_key
        tests:
          - relationships:
             to: ref('dim_contracts')
             field: key
      - *valid_from_placeholder
      - *valid_to_placeholder
      - *is_current_placeholder
  - name: dim_gtfs_datasets
    description: '{{ doc("gtfs_datasets_table") }}'
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - name: type
        description: |
          GTFS data type ("schedule", "trip_updates",
          "vehicle_positions", or "service_alerts")
      - name: data_quality_pipeline
        description: |
          If "true", indicates that this dataset should be ingested by the
          Cal-ITP data pipeline. Nulls indicate dataset should not be ingested.
      - name: regional_feed_type
        description: |
          Describes whether this feed is a combined regional feed or has a relation
          to a combined regional feed in some manner.
          For example for MTC 511, the combined regional feed has type "Combined
          Regional Feed", and the MTC-published subfeeds have type "Regional Subfeed".
          If you are performing an analysis where using a regional combined feed is
          inappropriate (even though that is the customer-facing data), this field can
          help you assess other alternative feeds for the same services and organizations.
          Not specified (null) for feeds with no relationship to regional feeds.
      - name: backdated_regional_feed_type
        description: |
          This field exists to support assessment determinations for reports.calitp.org.

          For record versions before the regional_feed_type field was added in the source database,
          this field contains the first value of that field that appeared for this source_record_id.
          For record versions that had regional_feed_type populated in the source database, this field
          contains the value current for that version.

          So, for example, if the regional_feed_type field was first populated for source_record_id A
          on September 1 with value "Regional Subfeed", this field will contain that "Regional Subfeed"
          value for the source_record_id A record that was active on August 31, even though the raw
          regional_feed_type field was not populated for that record version.
      - name: base64_url
      - name: deprecated_date
        description: |
          If populated, indicates that dataset was deprecated on listed date
          and is no longer active.
      - *valid_from_actual
      - *valid_to_actual
      - *is_current_actual
  - name: dim_gtfs_service_data
    description: '{{ doc("gtfs_service_data_table") }}'
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - name: service_key
        description: |
          Service record key for this service / dataset relationship.
        tests:
          - relationships:
             to: ref('dim_services')
             field: key
      - name: gtfs_dataset_key
        description: |
          GTFS dataset record key for this service / dataset relationship.
        tests:
          - relationships:
             to: ref('dim_gtfs_datasets')
             field: key
      - name: customer_facing
        description: |
          Indicates that this gtfs service data relationship represents the primary way that customers
          should be receiving GTFS data in trip planning applications. Every service/dataset
          type combo with a record in this table should have exactly one of those records
          marked as customer-facing.
          This field is "true" for customer-facing feeds and otherwise false.
          This field is intended to replace the `category` field over time.
      - name: category
        description: |
          "Primary" (indicating that this is the canonical dataset for this service) or
          "precursor" (this dataset is an input to some other primary
          canonical dataset for this service). Null or "unknown" indicate unknown.
      - name:  fares_v2_status
        # self relationship test breaks metabase connector: https://github.com/JarvusInnovations/dbt-metabase/blob/master/dbtmetabase/parsers/dbt_manifest.py#L202
        # TODO: make an aliased version of the relationship test
        # tests:
        #   - relationships:
        #      to: ref('dim_gtfs_service_data')
        #      field: key
      - *valid_from_actual
      - *valid_to_actual
      - *is_current_actual
  - name: dim_fare_systems
    description: '{{ doc("fare_systems_table") }}'
    tests: *mutually_exclusive_ranges
    columns:
      - *key
      - *valid_from_placeholder
      - *valid_to_placeholder
      - *is_current_placeholder
  - name: dim_service_components
    description: '{{ doc("service_components_table") }}'
    columns:
      - *key
      - name: component_key
        tests:
          - relationships:
             to: ref('dim_components')
             field: key
      - name: product_key
        tests:
          - relationships:
             to: ref('dim_products')
             field: key
      - name: service_key
        tests:
          - relationships:
             to: ref('dim_services')
             field: key
      - name: product_vendor_organization_key
        tests:
          - relationships:
             to: ref('dim_organizations')
             field: key
  - name: dim_modes
    description: Fully-historical modes table.
    columns:
      - *key
      - name: mode
      - name: super_mode
      - name: description
      - name: link_to_formal_definition
      - *valid_from_actual
      - *valid_to_actual
      - *is_current_actual
  - name: bridge_components_x_products
    description: |
      Mapping table between components and products.
      Relationship indicates that product can act as that component.
      This is a many-to-many relationship.
      Currently, this is not a genuinely historical table because component and product data is current-only.
      Versioning columns are provided for future schema consistency.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - component_key
            - product_key
    columns:
      - name: component_key
        tests:
          - relationships:
             to: ref('dim_components')
             field: key
      - name: product_key
        tests:
          - relationships:
             to: ref('dim_products')
             field: key
  - name: dim_provider_gtfs_data
    description: |
      Each record links together an organization with a service and a set of up to four
      GTFS datasets (schedule, RT service alerts, RT vehicle positions, and RT trip updates),
      where the organization manages the service.
      This table can be used to determine what GTFS dataset(s) represent a given provider's service(s);
      i.e., this table can be used to determine whether a given "agency" "has" GTFS.

      Because this table is versioned and represents many-to-many relationships (for example, one organization manages
      multiple services or a given service is represented in multiple sets of GTFS datasets),
      joins using this table can result in fanout, even when joining with reference to a specific date.

      For best results, join with reference to a specific date, and make sure to choose a specific
      output grain (organizations, services, customer-facing vs. not); you will likely need to filter
      or group to get the desired output.
    columns:
      - *key
      - name: service_key
        tests:
          - relationships:
             to: ref('dim_services')
             field: key
          # is there a way to partition this by date?
          # - unique:
          #     config:
          #       where: gtfs_service_data_customer_facing
      - name: schedule_gtfs_dataset_key
        tests:
          - &ref_gtfs_datasets_key
            relationships:
              to: ref('dim_gtfs_datasets')
              field: key
      - name: public_customer_facing_fixed_route
        description: |
          If "Yes", this service should be considered "assessed" by Cal-ITP for guideline/quality purposes.
      - name: public_customer_facing_or_regional_subfeed_fixed_route
        description: |
          If "Yes", this service should be considered "assessed" by Cal-ITP for the reports site.
      - name: service_alerts_gtfs_dataset_key
        tests:
          - *ref_gtfs_datasets_key
      - name: vehicle_positions_gtfs_dataset_key
        tests:
          - *ref_gtfs_datasets_key
      - name: trip_updates_gtfs_dataset_key
        tests:
          - *ref_gtfs_datasets_key
      - name: organization_key
        tests:
          - relationships:
              to: ref('dim_organizations')
              field: key
      - name: service_name
      - name: organization_name
      - name: schedule_gtfs_dataset_name
      - name: service_alerts_gtfs_dataset_name
      - name: vehicle_positions_gtfs_dataset_name
      - name: trip_updates_gtfs_dataset_name
      - name: gtfs_service_data_customer_facing
        description: |
          If true, we believe this GTFS data is customer-facing, i.e., ingested
          by trip-planning applications for this service/organization.
          If false, we believe that this GTFS data is not customer-facing, i.e., it
          is data that is not ingested by trip-planning applications.
          Generally, customer-facing data should be used for analysis, because it
          represents the data that is being used for decision-making by customers and
          should be the highest-quality/most accurate data available.
      - name: regional_feed_type
        description: |
          This is only populated for GTFS datasets that have some relationship with a regional,
          combined feed like MTC 511 or VCTC.
          The types are:
          * "Combined Regional Feed" - These GTFS datasets contain information
            about services managed by a variety of different organizations. If the
            customer-facing feed for an organization or service is this category,
            users may need to filter within feed data when doing more granular (ex.
            agency-level) analysis.
          * "Regional Subfeed" - Currently this category only applies to MTC 511, which provides
            an option to query the combined regional feed for a given agency's data.
          * "Regional Precursor Feed" - This category applies to agencies whose data appears in a
            combined regional feed but who produce some other independent dataset.
            The independent dataset may be an input into the combined regional feed.
      - name: organization_ntd_id
        description: |
          Five digit NTD ID for this organization.
          Organization <--> NTD mappings are based on NTD 2018 data.
      - name: organization_hubspot_company_record_id
        description: |
          Hubspot CRM record ID for this organization.
  - name: dim_county_geography
    description: '{{ doc("county_geography_table") }}'
    columns:
      - *key
      - name: source_record_id
      - name: name
      - name: fips
      - name: msa
      - name: caltrans_district
      - name: caltrans_district_name
      - name: place_geography
      - name: organization_key
      - name: service_key
      - name: _is_current
      - name: _valid_from
      - name: _valid_to
  - name: bridge_components_x_properties_and_features
    description: |
      Mapping table between components and properties_and_features.
      Relationship indicates that component has specified property or feature.
      This is a many-to-many relationship.
      Currently, this is not a genuinely historical table because component and properties and features data is current-only.
      Versioning columns are provided for future schema consistency.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - component_key
            - property_feature_key
    columns:
      - name: component_key
        tests:
          - relationships:
             to: ref('dim_components')
             field: key
      - name: property_feature_key
        tests:
          - relationships:
             to: ref('dim_properties_and_features')
             field: key
  - name: bridge_data_schemas_x_products_inputs
    description: |
      Mapping table between data schemas and products.
      Relationship indicates that data schema is an accepted input into
      given product.
      This is a many-to-many relationship.
      Currently, this is not a genuinely historical table because data schema and product data is current-only.
      Versioning columns are provided for future schema consistency.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - data_schema_key
            - product_key
    columns:
      - name: data_schema_key
        tests:
          - relationships:
             to: ref('dim_data_schemas')
             field: key
      - name: product_key
        tests:
          - relationships:
             to: ref('dim_products')
             field: key
  - name: bridge_data_schemas_x_products_outputs
    description: |
      Mapping table between data schemas and products.
      Relationship indicates that data schema can be produced as output
      of given product.
      This is a many-to-many relationship.
      Currently, this is not a genuinely historical table because data schema and product data is current-only.
      Versioning columns are provided for future schema consistency.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - data_schema_key
            - product_key
    columns:
      - name: data_schema_key
        tests:
          - relationships:
             to: ref('dim_data_schemas')
             field: key
      - name: product_key
        tests:
          - relationships:
             to: ref('dim_products')
             field: key
  - name: bridge_organizations_x_services_managed
    description: |
      Mapping table between organizations and services.
      Relationship indicates that organization manages (provides)
      the given mobility service.
      This is a versioned, many-to-many relationship.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - organization_key
            - service_key
    columns:
      - name: organization_key
        tests:
          - relationships:
             to: ref('dim_organizations')
             field: key
      - name: service_key
        tests:
          - relationships:
             to: ref('dim_services')
             field: key
  - name: bridge_parent_organizations
    description: |
      Mapping table between organizations and their parents
      (map between two organization records.)
      This is a versioned, many-to-many relationship.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - organization_key
            - parent_organization_key
    columns:
      - name: organization_key
        tests:
          - relationships:
             to: ref('dim_organizations')
             field: key
      - name: parent_organization_key
        tests:
          - relationships:
             to: ref('dim_organizations')
             field: key
  - name: bridge_paratransit_services
    description: |
      Mapping table between services and services for which
      they provide paratransit service
      (map between two service records.)
      This is a versioned, many-to-many relationship.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - service_key
            - paratransit_for_service_key
    columns:
      - name: service_key
        tests:
          - relationships:
             to: ref('dim_services')
             field: key
      - name: paratransit_for_service_key
        tests:
          - relationships:
             to: ref('dim_services')
             field: key
  - name: bridge_organizations_x_gtfs_datasets_produced
    description: |
      Mapping table between organizations and GTFS datasets
      produced by that organization.
      The organization that produces a dataset may be a vendor.
      For the organization that manages the service represented in a dataset, see dim_provider_gtfs_data.
      This is a versioned, many-to-many relationship.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - organization_key
            - gtfs_dataset_key
    columns:
      - name: organization_key
        tests:
          - relationships:
             to: ref('dim_organizations')
             field: key
      - name: gtfs_dataset_key
        tests:
          - relationships:
             to: ref('dim_gtfs_datasets')
             field: key
  - name: bridge_organizations_x_funding_programs
    description: |
      Mapping table between organizations and associated
      funding programs.
      This is a partially versioned, many-to-many relationship.
      Because organization records are versioned and funding programs are current-only this represents relationships between
      versioned organization records and unversioned funding program records.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - organization_key
            - funding_program_key
    columns:
      - name: organization_key
        tests:
          - relationships:
             to: ref('dim_organizations')
             field: key
      - name: funding_program_key
        tests:
          - relationships:
             to: ref('dim_funding_programs')
             field: key
  - name: bridge_fare_systems_x_services
    description: |
      Mapping table between fare systems and services.
      Relationship indicates that service utilizes the given fare system.
      This is a versioned, many-to-many relationship.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - fare_system_key
            - service_key
    columns:
      - name: fare_system_key
        tests:
          - relationships:
             to: ref('dim_fare_systems')
             field: key
      - name: service_key
        tests:
          - relationships:
             to: ref('dim_services')
             field: key
  - name: bridge_contracts_x_components
    description: |
      Mapping table between contracts and components.
      Relationship indicates that contract covers the given component.
      This is a versioned, many-to-many relationship.
      Joins via this table can result in fanout.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - contract_key
            - component_key
    columns:
      - name: contract_key
        tests:
          - relationships:
             to: ref('dim_contracts')
             field: key
      - name: component_key
        tests:
          - relationships:
             to: ref('dim_components')
             field: key
  - name: bridge_schedule_dataset_for_validation
    description: |
      Mapping table between GTFS RT datasets and the schedule dataset that is used to validate it
      (i.e., the schedule dataset that the RT dataset references.)
      This is a versioned relationship.
      While an RT dataset can only use one schedule at a time, because the records are versioned independently,
      a given `gtfs_dataset_key` can appear multiple times in this table with different versions of its accompanying schedule.
      Thus, joins via this table can result in fanout if they don't limit to validity on a specific date.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - gtfs_dataset_key
            - schedule_to_use_for_rt_validation_gtfs_dataset_key
    columns:
      - name: gtfs_dataset_key
        tests:
          - dbt_utils.relationships_where:
             to: ref('dim_gtfs_datasets')
             field: key
             to_condition: "type != 'schedule'"
      - name: schedule_to_use_for_rt_validation_gtfs_dataset_key
        tests:
          - dbt_utils.relationships_where:
             to: ref('dim_gtfs_datasets')
             field: key
             to_condition: "type = 'schedule'"
  - name: bridge_services_x_modes
    description: |
      Mapping table between services and associated
      modes records.
      This is a versioned relationship, with one service having one primary mode.
      Joins via this table can result in fanout because of versioning.
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - service_key
            - mode_key
    columns:
      - name: service_key
        tests:
          - relationships:
             to: ref('dim_services')
             field: key
      - name: mode_key
        tests:
          - relationships:
             to: ref('dim_modes')
             field: key
  - name: bridge_organizations_x_headquarters_county_geography
    description: |
      Mapping table between organizations and associated
      headquarters county geography records.
      This is a partially versioned relationship.
      Because organization records are versioned and headquarters county geography records
      are current-only this represents relationships between versioned organization
      records and unversioned headquarters county geography records.
      Joins via this table can result in fanout.
  - name: bridge_services_x_operating_county_geographies
    description: |
      Mapping table between services and associated
      operating county geography records.
      This is a partially versioned relationship.
      Because services records are versioned and operating county geography records
      are current-only this represents relationships between versioned service
      records and unversioned operating county geography records.
      Joins via this table can result in fanout.