create new data model for multiple supply sources

[dmarulli]

No description provided.

[dmarulli]

Any objections or general thoughts on this? Note the note on pieces of infrastructure this would touch. I can start making the updates if not.

cc: @christophertull @mike-amodeo @patwater

---

Since we want to move from a Reservoir Explorer world to a more general Supply Explorer world, we should update the data model to reflect this.

Here is the current table definition:

CREATE TABLE public.reservoir_reading
(
  reservoir_reading_id bigint NOT NULL DEFAULT nextval('reservoir_reading_seq'::regclass),
  storage_capacity double precision,
  name text,
  dam_id text,
  latitude double precision,
  longitude double precision,
  date text,
  res_ele double precision,
  reservoir_storage double precision,
  percent double precision,
  CONSTRAINT reservoir_reading_pkey PRIMARY KEY (reservoir_reading_id)
)

This data model is basically tailored to the CDEC reservoir data (see example here).

Here are my proposed updates:

• reservoir_reading RENAME TO supply_reading
• ADD COLUMN "supply_type" text (e.g. reservoir, snowpack, lake--or whatever categories make the most sense)
• RENAME "reservoir_reading_id" TO "supply_reading_id"
• RENAME "name" TO "supply_name"
• RENAME "dam_id" TO "supply_data_source_id"
• RENAME "date" TO "supply_reading_date"
• RENAME "res_ele" TO "supply_elevation"
• RENAME "reservoir_storage" TO "supply_storage"

At a minimum, this update with affect:

• get_daily_extracts_from_state_and_load_to_scuba (current airflow task that parsers reservoir data from CDEC and loads it to scuba)
• get_viz_year_extract_from_scuba (airflow task that extracts data from scuba to load to s3)
• reservoir explorer (currently looking for old column names)

I am not 100% sure how Carto handles situations in which a table on their servers is hooked to an s3 table and the table on s3 changes cols...but we can cross that bridge when we get there.

[christophertull]

~Stamp of approval~

[mike-amodeo]

that all makes sense. we could also change the name of the table in Carto. it's currently reservoir_reading_extract, which could become supply_reading_extract or something, and that would bypass possible column name issues

[dmarulli]

Good thinking Mike. That sounds good to me.

I'm going to start on this. (Will probably save carto/viz side of things for last.)