PacFIN Biological Data System (BDS) Redevelopment

Brad Stenberg 

As of January 1, 2020, the phased implementation of the redevelopment of the PacFIN Biological Data System (BDS) is complete.  Phase one consisted of migrations of the legacy BDS tables from the PACFIN schema into the new BDS schema tables with implementation completed in the Fall of 2018.  The updated design included the development of a new COMPREHENSIVE user table to enable users to query all BDS source data from a single “flat” table, eliminating the need for more complex table joins.  The user table, like its predecessors COMPREHENSIVE_FT and COMPREHENSIVE_NPAC, is located in the PACFIN_MARTS schema as COMPREHENSIVE_BDS_COMM. With the completion of phase one, the PACFIN and BDS schema tables were maintained in sync, allowing users to get accustomed to the new COMPREHENSIVE_BDS_COMM table while continuing to use existing scripts to query the legacy BDS tables. 

Phase two of the redevelopment process moved from a centralized PacFIN specification to an enhanced ETL design to acquire more comprehensive data sets from each data source without coastwide restrictions.  This enhancement provided the opportunity for the incorporation of expanded data sets from each source. One example is the inclusion of “special samples” (sample_type_code ‘SPL’) from ODFW, where non-standard samples not included in the commercial or market sample sets are now available to users as an additional resource outside of the standard protocol for biological samples. Another example is the inclusion of supplemental measurements from WDFW, which provides for the possibility of multiple measurements per fish.

The data capture and update cycles for Washington and California data are now automated, with refreshes from the WDFW Web Services (API) and the CALCOM database scheduled for weekly uploads.  Oregon datafeeds from ODFW are received as needed and uploaded manually. As with the other COMPREHENSIVE tables the COMPREHENSIVE_BDS_COMM table is refreshed nightly.  

 As of January 1, 2020, the legacy BDS tables in the PACFIN schema are no longer being maintained.  The current production database is exclusively in the BDS schema, which supports the PACFIN_MARTS.COMPREHENSIVE_BDS_COMM table.  The PacFIN Query Builder application can be used to create summary reports from COMPREHENSIVE_BDS_COMM, COMPREHENSIVE_FT (coastwide fish ticket data), and COMPREHENSIVE_NPAC (At-Sea Whiting fishery).  Documentation for both the COMPREHENSIVE_BDS_COMM table and the PacFIN/RecFIN Query Builder Application can be found on the DOCUMENTATION page of the PacFIN website.