Snapshot or Ledger

Table Types

Dependent on the source some of our datasets will have two variants. Snapshot, most recent view of the data, and Ledger list of transactions up to the snapshot view. Snapshots are best used to view Current status and Ledgers are best used to track historical changes.

Currently, only DoordaBiz product has a Ledger offering.

DoordaBiz

DoordaBiz contains information on companies and associated appointments. All tables can be joined by company_number column

1. Snapshots

   Data at the point in time with each row identifying a unique entry.

   Example:
   Company Profile table has a unique company per row of data. Each company can have their confirmation statement date updated or address updated for example. So the snapshot consists of the latest entry for each company on the fixed day of each month.

   Catalog = doordabiz_snapshot
   Schema = doordabiz_snapshot
   Query Format: SELECT {col} FROM doordabiz_snapshot.doordabiz_snapshot.{table_name}

   Evaluation
   Catalog = doordabiz_snapshot_evaluation
   Schema = doordabiz_snapshot_evaluation
   Query Format: SELECT {col} FROM doordabiz_snapshot_evaluation.doordabiz_snapshot_evaluation.{table_name}

2. Ledgers

   Ledgers are records of transactions for each unique entry. Similar to the function of an accounting ledger, it is meant to maintain a verifiable, immutable history of the changes of each tables over time.

   Each unique entry will have an insert action into the ledger followed subsequently by update/delete (deletes are often not done unless it is an error made by the source provider)

   All tables in Snapshots will have an equivalent Ledger table to track changes. 

   Example:

   In the Company Profile table, an incorporation will be shown as a insert and subsequent change of address, status, sic codes etc will be shown as update.

   Key Columns:

   • values

     Given as a key value pair and shows how the Snapshot entry looks at the indicated date. Key maps to column names of the Snapshot table.

   • urn

     Fixed unique row identifier of Snapshot

   • action

     Description of data manipulation type. Can be insert/update/delete

   • change_date

     Same as change_date column in snapshot tables. Refers to the date that the entry was added/amended by the source provider.

   • date_added

     Date the entry was added into the Ledger. Typically the date after the source provider releases the data.

   • orders

     The order that the entry was amended, if multiple amendments were made to an entry on the same day.

     For example, if ABC LIMITED filed for a change of address and change of company name on 2019-01-01, the date_added will be the same for both entries in the ledger but the change of address will have orders=1 and change_of_name will have orders=2, depending on which entry was filed with the source provider first.

   • Use Case Query Examples:
   • 
     
   • 
     
   • 
     
   1. Return how the Company Profile snapshot entry will look for company 09231049 on 2019-01-01

   2. SELECT urn, date_added, change_date, orders, action, "values"
      FROM (SELECT *, rank() over (partition by company_number order by date_added desc, change_date desc, orders desc, action desc) as rnk
              FROM register_company_profile_ledger
              WHERE company_number = '09231049' and date_added <= date '2019-01-01') as iq
      WHERE (rnk = 1 or rand() < 0);

   3. 
      
   4. 
      
   5. 
      

   2. Recreate Snapshot for Company Profile table on 2019-03-01

      Warning:

      Recreating the Snapshot from the Ledger is a process-intensive task. Hence large tables like the Company Filings (157 million rows), Company Profile (12 million rows) may take some time to process.

   3. SELECT A."values"
      FROM register_company_profile_ledger as A
      INNER JOIN (SELECT urn, max(date_added) AS date_added,
                  max_by(change_date, date_added) AS change_date,
                  max_by(orders, date_added) AS orders,
                  max_by(action, date_added) AS action
                  FROM register_company_profile_ledger
                  WHERE date_added <= date '2019-03-01' GROUP BY 1) as B
      ON A.urn = B.urn
      AND A.date_added = B.date_added
      AND A.orders = B.orders
      AND A.change_date = B.change_date
      AND A.action = B.action

   4. 
      
   5. 
      

   Catalog = doordabiz_ledger
   Schema = doordabiz_ledger
   Query Format: SELECT {col} FROM doordabiz_ledger.doordabiz_ledger.{table_name}

Public Procurement

1. Snapshots
   Data at the point in time with each row identifying a unique entry.

   Catalog = public_procurement_snapshot
   Schema = public_procurement_snapshot
   Query Format: SELECT {col} FROM public_procurement_snapshot.public_procurement_snapshot.{table_name}

   Evaluation
   Catalog = public_procurement_snapshot_evaluation
   Schema = public_procurement_snapshot_evaluation
   Query Format: SELECT {col} FROM public_procurement_snapshot_evaluation.public_procurement_snapshot_evaluation.{table_name}

2. Ledger

   Catalog = public_procurement_ledger
   Schema = public_procurement_ledger
   Query Format: SELECT {col} FROM public_procurement_ledger.public_procurement_ledger.{table_name}

DoordaStats

DoordaStats contains location-based data. All tables can be joined by postcode column.

1. Snapshots
   Data at the point in time with each row identifying a unique entry.

   Catalog = doordastats_v2_snapshot
   Schema = doordastats_v2_snapshot
   Query Format: SELECT {col} FROM doordastats_v2_snapshot.doordastats_v2_snapshot.{table_name}

   Evaluation
   Catalog = doordastats_v2_snapshot_evaluation
   Schema = doordastats_v2_snapshot_evaluation
   Query Format: SELECT {col} FROM doordastats_v2_snapshot_evaluation.doordastats_v2_snapshot_evaluation.{table_name}

DoordaProperty

DoordaProperty contains address based data. All tables can be joined by udprn column.

1. Snapshots
   Data at the point in time with each row identifying a unique entry.

   Catalog = doordaproperty_snapshot
   Schema = doordaproperty_snapshot
   Query Format: SELECT {col} FROM doordaproperty_snapshot.doordaproperty_snapshot.{table_name}

   Evaluation
   Catalog = doordaproperty_snapshot_evaluation
   Schema = doordaproperty_snapshot_evaluation
   Query Format: SELECT {col} FROM doordaproperty_snapshot_evaluation.doordaproperty_snapshot_evaluation.{table_name}

Commercial Location

Commercial Location contains address based data (in particular, property that pays commercial rates). All tables can be joined by udprn column.

1. Snapshots
   Data at the point in time with each row identifying a unique entry.

   Catalog = commercial_location_snapshot
   Schema = commercial_location_snapshot
   Query Format: SELECT {col} FROM commercial_location_snapshot.commercial_location_snapshot.{table_name}

   Evaluation
   Catalog = commercial_location_snapshot_evaluation
   Schema = commercial_location_snapshot_evaluation
   Query Format: SELECT {col} FROM commercial_location_snapshot_evaluation.commercial_location_snapshot_evaluation.{table_name}