- 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
relationshipts
: 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.