Background

Where are Property Active Dates Stored?

Property Active Dates are entered into PostgreSQL a table in claripedia.property_active_dates

What do Property Active Dates entries consist of?

Property Active Dates consist of:

• property_id
• clarivoy_product - must be a valid value in claripedia.products (common products are ‘SA’ and ‘source_rpts’)
• active_dates - date range with a start and (optional) end date.  Must be in a very specific format.  Example value: '{"[2024-02-01,2024-06-30)"}'

When are Property Active Dates used?

See Usage of property_active_dates - Documentation of which DAGs reference the property_active_dates table

The property_active_dates table is used across multiple Python scripts for various purposes, such as determining start dates, filtering active properties, and upserting data.

Entering Property Active Dates

Automated Entry of Property Active Dates

For full details on the process, review the Confluence documentation.

1. 🚨 The following Monday automations cause new items to be created on the Property Active Dates board:
   1. 🚨 When a property's Processing Status is set to Onboarding on the Clarivoy Properties board
   2. 🚨 When a property's Start Date is changed on the Clarivoy Properties board
   3. 🚨 When a property's Final Reporting Date is changed on the Clarivoy Properties board
   4. 🚨 When a property is created on the Source Reports Properties board
   5. 🚨 When a property's Start Date is changed on the Source Reports Properties board
   6. 🚨 When a property's Final Reporting Date is changed on the Source Reports Properties board
2. Every morning at 7:00 AM EST, the property_active_dates_insert_update Airflow DAG will run. This DAG gets all items in the Incoming group on the Property Active Dates board.
3. For all items with Status=New, the DAG inserts the appropriate property active date rows in the claripedia.property_active_dates table, referencing Property ID, Start Date, and Product columns.
   1. If there are errors, the DAG will:
      1. Set the Status to Error
      2. Create an update on the item with the specific error
      3. Alert in #airflow-alerts Slack channel
      4. Log the error in the property_active_dates_insert_update.log file on the Jobs server
4. If successful, the DAG sets the Status to Done.

Note: Items on the Property Active Dates board are upserted by the DAG in sequential order based on when they were created. (This means if a Start Date was updated multiple times in the same day, the updates will be entered in order. The last update will always prevail.)

PostgreSQL Queries and Statements for Property Active Dates

Manual entry of Property Active Dates used to be an Onboarding task when Onboarding was done in Monday.com.  Manual entry of the Property Acitve Dates is no longer required as it has been fully automated as of April 2024.

--Note: clarivoy_product must exist in claripedia.products table.
-- VIEW ALL PRODUCTS
select * from claripedia.products;

--INSERTING SA ACTIVE START DATE, OR UPDATE IF IT ALREADY EXISTS--
INSERT INTO claripedia.property_active_dates(property_id, clarivoy_product, active_dates) VALUES (51054, 'SA','{"[2024-07-01,2025-06-30)"}')
ON CONFLICT (property_id, clarivoy_product) DO UPDATE SET active_dates= EXCLUDED.active_dates;

--INSERTING SR ACTIVE START DATE, OR UPDATE IF IT ALREADY EXISTS--
INSERT INTO claripedia.property_active_dates(property_id, clarivoy_product, active_dates) VALUES (51054, 'source_rpts','{"[2024-02-01,2024-06-30)"}')
ON CONFLICT (property_id, clarivoy_product) DO UPDATE SET active_dates= EXCLUDED.active_dates;


--UPDATING SA ACTIVE START DATE IN PROPERTY ACTIVE DATES TABLE--
UPDATE claripedia.property_active_dates
SET active_dates = '{"[2024-10-01,)"}'
    WHERE property_id = 139
AND clarivoy_product = 'SA';

--VIEWING PROPERTIES IN PROPERTY ACTIVE DATES TABLE--
SELECT * FROM claripedia.property_active_dates_unnested_view;

--SEARCH FOR A SPECIFIC PROPERTY'S ACTIVE DATE
--SELECT * FROM claripedia.property_active_dates_unnested_view WHERE property_id in (${PROPIDS SEPARATED BY COMMAS});
SELECT * FROM claripedia.property_active_dates WHERE property_id in (${PROPIDS SEPARATED BY COMMAS});

--SEARCH FOR A SPECIFIC PROPERTY'S SA ACTIVE DATE
SELECT * FROM claripedia.property_active_dates WHERE property_id in (${PROPIDS SEPARATED BY COMMAS}) and clarivoy_product='SA';


-- VIEW ACTIVE DATE COUNTS BY PRODUCTS
SELECT clarivoy_product, count(*) FROM claripedia.property_active_dates
    group by clarivoy_product;

--ALL NON-SA/SR ACTIVE DATES
SELECT * FROM claripedia.property_active_dates WHERE clarivoy_product not in ('SA','source_rpts');
SELECT * FROM claripedia.property_active_dates WHERE clarivoy_product='CDPL';
SELECT * FROM claripedia.property_active_dates WHERE clarivoy_product='CDPE';


-- PROPERTIES THAT HAVE BOTH SA AND source_rpts ACTIVE DATES --
SELECT property_id, property_name, clarivoy_product, active_dates, client_id from
claripedia.property_active_dates_unnested_view where property_id in (
SELECT property_id from claripedia.property_active_dates_unnested_view
where clarivoy_product in ('SA', 'source_rpts')
and client_id=1831
group by property_id
having count(*) > 1)
and clarivoy_product in ('SA', 'source_rpts')
order by property_name, clarivoy_product asc;

--DELETING FROM THE PROPERTY ACTIVE DATES TABLE--
--Query using this statement, then use the - key in Datagrip to delete the row, followed by ↑ to commit changes--
SELECT * FROM claripedia.property_active_dates WHERE property_id = 52954;