search

uace-azmet / azmet-forecast-qa

Developing QA/QC routines for AZMet
0 stars 1 forks source link

Define uniqueness constraints for db tables to prevent duplicate and potentially conflicting records #2

Closed dlebauer closed 1 year ago

dlebauer commented 1 year ago

Is your feature request related to a problem? Please describe.

It might be possible to have two records for the same time and station. We want to prevent this from happening.

Describe the solution you'd like

This should be prevented, by defining natural keys / sets of fields that should be unique for each table and ensuring that this uniqueness is enforced.

e.g. enforcing that all [site_id, datetime] pairs be unique, e.g. ALTER TABLE tablename ADD CONSTRAINT unique_observation_per_station_per_time UNIQUE (station_id, datetime);

If records are replaced by adding a new record and indicating the old one has been replaced, then this could be handled by adding the field that records this relationship.

Aariq commented 1 year ago

This pipeline has code for de-duplicating records (https://github.com/uace-azmet/azmet-forecast-qa/blob/a9fa0613af50a6ab8b3ba43c13ef275e7ba244e6/R/pin_forecast.R#L16). I'll mention this issue to Matt Harmon next time we talk though.