[D6] - Create Archival SCORE Table for Stale Content

[ydaponte]

As a Data Engineer, I want to create an Archival SCORE Table so that we can store and utilize the results of processing raw data to score and prioritize stale content for archiving.

• Design Table Schema: Define and document the schema for the Archival SCORE Table, including columns for content ID, archive score, content type, last accessed date, and any other relevant metadata.
• Performance Optimization: Implement indexing and optimization strategies to ensure efficient querying and retrieval of data from the Archival SCORE Table.
• Documentation: Provide documentation detailing the table schema, ETL process, and any configuration or setup requirements for the Archival SCORE Table.

Acceptance Criteria:

• The Archival SCORE Table is created and accessible in the chosen data store.
• The documentation is complete and includes detailed information on the table schema, and usage instructions.
• Make sure this table can be used as a source data to the AI/ML script

[SQLZane]

Created two objects first a rule table to capture the rules and record the responses for both the PowerBI an chatbot. CREATE TABLE IF NOT EXISTS RuleTable ( RuleID INT, RuleName STRING, RuleDescription STRING, Value STRING, ---Weight INT, Will be important to the finalized product but out of Scope for Hackathon PrescriptionStatement STRING ); I've also inserted some rules for reports older than 90 days.

I've also built the scoring table as well. CREATE TABLE IF NOT EXISTS ScoringTable( ItemID INT, --FK To Denormalized table in SQL DB Scenario RuleID INT, --FK to RuleTable Score INT );

These two items can be joined together and included with the denormalized table to gather any reporting information or details for the copilot. All of the code required is stored within the DenormalizedItemCollection Workbook.

[SQLZane]

I also added Item Type to the rule table so we can create specific rules per each type of Item.