Unified Schema Design

  • The goal is to create a single, unified schema across datasets. This schema aims to strike a balance between flexibility to accommodate arbitrarily shaped data along with consistency in core tables.
  • Datasets are built around two concepts: entities and timeseries.
    • Entities are concrete things or objects (a geography, a company, a mortgage application).
    • Timeseries are abstract measures (ie. statistics) related to an entity and a date.
  • The core tables are:
    • entities: Contains the entities that are being tracked. For example, Spain, Madrid, etc.
      • Should be something like province_index or weather_station_index to be able to join with the timeseries.
      • This table contains permanent characteristics describing an entity. E.g: for Provinces, the name, the region.
      • Each row represents a distinct entity. The table is wide, in that immutable characteristics are expressed in their own fields.
    • attributes: Attributes are descriptors of a timeseries. An attribute is the equivalent of a characteristic except for the abstract timeseries rather than the concrete entity.
      • Columns:
        • variable_id: Unique identifier for the attribute
        • name: Name of the attribute
        • description: Description of the attribute
        • unit: Unit of the attribute
        • source: Source of the attribute
        • frequency: Frequency of the attribute (daily, monthly, etc.)
        • measurement_type: Type of measurement (e.g. nominal, ordinal, interval, ratio, percentage)
        • Metadata columns;
          • category: Category of the attribute
          • namespace: Namespace of the attribute
          • tags: JSON with tags of the attribute?
          • aggregation_function: Aggregation function to use when aggregating the attribute
    • timeseries: Timeseries are abstract measures (ie. statistics, metrics) related to an entity and a date. Timeseries are temporal statistics or measures centered around an entity and timestamp. For example, GDP of Spain, population of Madrid, etc. Timeseries are abstract concepts (ie. a measure) rather than a concrete thing.
      • Could be something like weather_timeseries to be able to join with the entities.
      • Columns:
        • variable_id: Unique identifier for the attribute
        • geography_id: Unique identifier for the geography
        • date: Date of the metric
        • value: Value of the metric
    • relationships: Contains the relationships between entities. For example, Spain is composed of provinces, Madrid is a province, etc.
      • Relationships can also be temporal – valid for an interval defined by specific start and end dates.
    • characteristics: Descriptors of an entity that are temporal. They have a start date and end date.