search

WSWCWaterDataExchange / WaDE2.0

WaDE Data System Code: Database and APIs
http://schema.westernstateswater.org/diagrams/index.html
BSD 3-Clause "New" or "Revised" License
3 stars 4 forks source link

Remove **Water Source** elements from VaribleAmounts_fact table to reduce risk of creating duplicate consumptive use time series records in WaDE. #199

Open rwjam opened 8 months ago

rwjam commented 8 months ago

With current approach, we force a POU to share the water source type as any relating PODs (we do this in a pre-processing the input data phase where we marry POD and POU related info). We do this to support the logic of withdrawal -to- use logic we want to build within WaDE. This is okay as we support the 1:M relationship between sites and water sourses. However, we don't support this 1:M within our VaribleAmounts_fact (record) timeseries info.

In the future, may want to consider removing the WaterSourceID element / linkage to the Core.WaterSources_dim from the Core.VaribleAmounts_fact table. This relationship is already being covered by the join table we created between Core.Sites_dim & the Core.WaterSources_dim.

As it stands now, we risk creating duplicate Amount entries for consumptive use values of a POU, if that POU happens to have multiple POD sources that rely on different water sources (e.g., surface water PODs vs a groundwater PODs, different water source names, etc).

See UTssps (system vs source data) example img below)

image image

rwjam commented 8 months ago

Simple Solution(s):

  1. For time series data and entries into WaDE (i.e., ss data), we "COULD" abandon forcing POUs to share the same water source / water source type as PODs, if water source is not provided with POU entry data. That would prevent this duplication issue on Amount for consumptive use. Would mean the water source type in this UTssps ex would be "Unspecified"
  2. For this particular UTssps ex, we were not provided water source names and our water source info is very bare minimum with this data set (.e.g., only a groundwater and a surface water entry). We could try and create a new, arbitrary temp filler value for water source entry (*.e.g, create a WaterSourceUUID that would be a combined "Surface Groundwater" entry) when this mixing of related water source info occurs as the POU / system level. That solves this specific UTssps example. However this will not always work. If we were given more water source info to work with (i.e., name of source) this arbitrary filler value approach could get complicated and not reflect the true withdrawal-to-use logic we want to illustrate in WaDE.