Procedure - property_historical_sales

Note: It is critical that you apply every monthly change file in the correct sequence and without skipping any delivery. Missing even a single month's changes may cause your master dataset to become inconsistent or invalid. We also recommend performing a full data delivery at least once a year to reset the baseline and ensure consistency across all applied changes.

To apply the change file correctly, you must:

Retain your last month’s full dataset.
Have the change-only file for the current month.
Use the HPAID field as the primary key.

Use the CHANGE_TYPE field in the change file:
- A = Add (New record)
- D = Delete (Remove record)
- U = Update (Modify existing record)

Procedure:

Delete Records:
Identify all records in the change file with change_type = 'D'. These represent records that should no longer exist in the dataset. Use the HPAID to match and delete these from your master table.
```
DELETE FROM master_data
WHERE hpaid IN (
    SELECT hpaid FROM change_data WHERE change_type = 'D'
);
```
Insert New Records:
Fetch records from the change file where change_type is 'A'. These entries are new and should be added to your current data.
```
INSERT INTO master_data (hpaid, col1, col2, col3, ...)
SELECT hpaid, col1, col2, col3, ...
FROM change_data
WHERE change_type = 'A';
```
Update Existing Records:
For records where change_type is 'U', update the corresponding attributes in the master dataset with values from the change file, using HPAID as the matching key.
```
UPDATE master_data AS m
SET col1 = c.col1,
    col2 = c.col2,
    col3 = c.col3,
    ...
FROM change_data AS c
WHERE m.hpaid = c.hpaid
AND c.change_type = 'U';
```

Workflow diagram:

Procedure - property_historical_sales - Latest

Property Historical Sales Getting Started Guide