The CLAIMS Direct data warehouse is updated continuously. On average, 1-3% of the entire database is touched weekly. These 1-3 million updates fall into two categories: new documents (roughly 20%) and updated documents (roughly 80%).  New documents are, of course, new publications from issuing authorities, US, EP etc. Updates are generally, but not limited to, CPC changes, family changes (adding family ID), IFI integrated content Snapshots (patent status, standard names and claims summaries), reassignments, legal status, et al. I suggest reading about content updates for a better understanding of load sources and issuing authorities. This blog will describe how to determine what has been updated and further, differentiating new content from updated content at the document level as well as container level.

Load Identifiers

As described in the content update documentation,

...

• modified_load_id – the load-id that last modified the document, whether it be addition, correction or deletion of content
• deleted_load_id – the load-id that the entire document was marked deleted
• created_load_id – the load-id when the document was added to the data warehouse

...

Each satellite table also contains a modified_load_id column that is specific to the content in that table and will always be <= xml.t_patent_document_values(modified_load_id). The following table should clarify the sequence of update events and the effect on load-id values for three database tables.

This granularity offers the ability to trace modifications to individual containers (tables) as well as the overall modification status of the document as a whole.

select count(*) as docs_with_modified_cpc
from xml.t_patent_document_values as a
  inner join xml.t_classifications_cpc as b
    on (a.publication_id=b.publication_id)
where a.country='US'
  and a.published='2016-10-04'
  and a.deleted_load_id is null -- disregard deleted documents
  and a.withdraw != true        -- disregard withdrawn documents
  /*
    If b.modified_load_id > a.created_load_id,
    the table has been touched and we can therefore
    assume CPC values have been modified
  */
  and b.modified_load_id > a.created_load_id;
 
 docs_with_modified_cpc 
------------------------
                   6145

Reporting

Knowing which tables and containers have changed is important, but the when and why are also critically important. The information gathered during data warehouse updates is stored in reporting.t_client_load_process. For a detailed description of the contents of this table, see CLAIMS Direct Design Documentation. Of the many questions that can be posed, for our purposes in this blog, we are only concerned with answering the following questions:

...

select count(*) as updated_docs
from xml.t_patent_document_values
where created_load_id < modified_load_id
    and modified_load_id in (
      select load_id
      from reporting.t_client_load_process
      where date_trunc('day', completed_stamp) = '2016-10-04'::date
    );

 updated_docs 
----------
   362388

...