[Task]: Add transformations for the link one-to-many lookup tables #1749
Comments
chouinar
added a commit
that referenced
this issue
May 7, 2024
Closed
chouinar
added a commit
that referenced
this issue
May 10, 2024
…1947) ## Summary Fixes #1749 ### Time to review: __10 mins__ ## Changes proposed Adds the transformations for the three one-to-many lookup tables * `link_opportunity_summary_funding_category` * `link_opportunity_summary_funding_instrument` * `link_opportunity_summary_applicant_type` These all get populated by 4 separate lookup tables in Oracle system (representing - synopsis, forecast, synopsis history, forecast history). Handles the complex queries to make sure we're joining to the right objects / have the opportunity summary to connect these records to. Added a unique key to the 3 link tables to make sure we never end up with duplicate legacy ID values for a given opportunity summary (note that the column itself cannot be unique as its not unique across say the synopsis and forecast versions of a table). ## Context for reviewers A later follow-up PR is going to cleanup the structure/organization a bit. I know the test file is getting way too long. This change looks much larger than it really is - this is because the three one-to-many tables (our three `LinkOpportunitySummary...` tables) all were populated here and they all get populated in basically the same way. The only difference being the source tables, the transformation for the lookup value, and the name of the columns. Otherwise the pattern is identical and very boilerplate heavy (follow-up PRs will hopefully make a lot of this more re-used but keeping this simple). One interesting observation about these tables, while we support updates, they aren't really something we'll need to deal with as most of the meaningful data is in the primary key on the Oracle tables. Even checking their prod data, there have been ~10 updates across all of these 12 source Oracle tables ever. This is because if you wanted to change the list of categories, you'd delete and add, not modify because the value is a primary key value.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Summary
Handles transforms for the 3 lookup tables
https://app.gitbook.com/o/cFcvhi6d0nlLyH2VzVgn/s/v1V0jIH7mb7Yb3jlNrgk/engineering/learnings/opportunity-endpoint-data-model#opportunity-assistance-listing
Acceptance criteria
No response
The text was updated successfully, but these errors were encountered: