cdk_daily_update_insert_into_sales_raw_new Airflow DAG runs daily at approx. 8:00 AM EST and queries data for all CDK subscription IDs entered in the DMUI where vendor_name='cdk' and status=active.

Get Backdata for New IDs entered within Past Day

For all new IDs entered in the DMUI within the past day (created_ts is within the previous day in clarivoy.prod_third_party_data_config table) where vendor_name='cdk' and status=active:

• Gets active_date for the property from claripedia.property_active_dates table
• If no active date exists, we assume active_date=30 days ago

• IF active_date value <= now [in the past] THEN
  -- 1 initial run of Get FI Sales Closed Bulk for 31 days
  • Get FI Sales Closed Bulk - request past 31 days of sales as follows:
    subscription ID for call = vendor_dealer_id value for the row in third_party_data_config
  • IF result of this call is a WARNING containing “Received data is empty”, broadcast the error in Slack #cdk-api-dag-alerts channel as follows:
    Dag name: cdk_daily_update_insert_into_sales_raw_new
    Property ID: XXXX
    Property Name: XXXXXXXXXXX
    Subscription ID: xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
    WARNING XXXXXXX : Received data is empty.During this period : YYYY-MM-DD-YYYY-MM-DD for this subscription_id:xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
• END IF
• IF active_date value >= 90 days ago AND active_date value < 31 days ago THEN
  -- If start date is within the past 90 days and >31 days ago, then get the rest of the backdata needed between the start date and 31 days ago
  • Call API: CDK Drive History Setup FI Sales
  • Get Historical Vehicle Sales - request sales as follows:
    subscription ID for call = vendor_dealer_id value for the row in third_party_data_config
    qparamStartDate = active_date value
    qparamEndDate = 31 days ago
• IF result of this call is a WARNING containing “Received data is empty”, broadcast the error in Slack #cdk-api-dag-alerts channel as follows:
  • Dag name: cdk_daily_update_insert_into_sales_raw_new
    Property ID: XXXX
    Property Name: XXXXXXXXXXX
    Subscription ID: xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
    WARNING XXXXXXX : Received data is empty.During this period : YYYY-MM-DD-YYYY-MM-DD for this subscription_id:xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
• END IF

Get Delta Data for Existing IDs

For all other IDs where vendor_name='cdk' and status=active:

1. Gets active_date for the property from claripedia.property_active_dates table
   1. If no active date exists, we assume active_date=30 days ago
2. IF active_date value <= now [in the past] THEN
   -- This way we avoid calling for data if the property active date is in the future (we do not need data to start until the property active date occurs)
   1. Get FI Closed Sales Delta [new or changed] for the past 5 days where:
      subscription ID for call = vendor_dealer_id value for the row in third_party_data_config
   2. FOR EACH ROW RETURNED
      1. ONLY insert the row into cdk_raw or attempt to update an existing row in cdk_raw IF (deal.accountingDate >= active_date) AND (deal.accountingDate > the first day of the month 4 months ago)
         -- This way we avoid ingesting unneeded data if the sale date was prior to the property active date or is older than what we are currently processing (the last 3 complete months)
      2. IF updating a row, update the source_document value to the new source_document name. This updated source_document value should also update in the existing sales_raw row when it is updated.
         -- This way we know when the last update was made to that particular sale row

END IF

-- For existing (not new) IDs, if “received data is empty” for a delta call, this is not considered an “error” that is broadcast in Slack. Some dealers may batch entry/processing of deals and it could be normal for them to not have any new or updated Finalized deals within the past 6 days.