Daily Sync Process
The daily sync process keeps the portal hierarchy in sync with CieTrade data.
Workflow Overview
1
DIAGNOSE
get_missing_locations.sql
2
ADD MISSING
add_new_locations_to_existing_cursor.sql
3
FILL BLANKS
update_blank_udf.sql
4
UPDATE DIFFERENCES
update_different_udf.sql
Click or hover steps for details
Execution Order
Execute the sync scripts in this exact order:
Step 1: Diagnose
Script: get_missing_locations.sql
- Identify missing locations
- Find name mismatches
- Check recent changes
1
DIAGNOSE
get_missing_locations.sql
2
ADD MISSING
add_new_locations_to_existing_cursor.sql
3
FILL BLANKS
update_blank_udf.sql
4
UPDATE DIFFERENCES
update_different_udf.sql
Step 2: Add Missing
Script: add_new_locations_to_existing_cursor.sql
- Insert new locations from CieTrade
- Uses cursor to process each account
- Idempotent (safe to re-run)
1
DIAGNOSE
get_missing_locations.sql
2
ADD MISSING
add_new_locations_to_existing_cursor.sql
3
FILL BLANKS
update_blank_udf.sql
4
UPDATE DIFFERENCES
update_different_udf.sql
Step 3: Fill Blanks
Script: update_blank_udf.sql
- Sync UDF fields when WG is blank but CieTrade has value
- Updates: udf1-4, address, city, state, zip
1
DIAGNOSE
get_missing_locations.sql
2
ADD MISSING
add_new_locations_to_existing_cursor.sql
3
FILL BLANKS
update_blank_udf.sql
4
UPDATE DIFFERENCES
update_different_udf.sql
Step 4: Update Differences
Script: update_different_udf.sql
- Sync when values DIFFER (not just blank)
- Updates: udf1-4, cietrade_active_status
- CieTrade is source of truth
1
DIAGNOSE
get_missing_locations.sql
2
ADD MISSING
add_new_locations_to_existing_cursor.sql
3
FILL BLANKS
update_blank_udf.sql
4
UPDATE DIFFERENCES
update_different_udf.sql
Key Concepts
| Concept | Description |
|---|---|
| Idempotent | All sync scripts can be run repeatedly without duplicating data |
| Source of Truth | CieTrade (new_ct schema) is always authoritative |
| Matching Logic | Uses cietrade_address_id + location_name for joins |
| Exclusions | See Quick Reference for CPIDs to ignore |
Automation Status
| Status | Description |
|---|---|
| Current | Manual daily execution |
| Future | Candidates for scheduled automation once validation is complete |
Automation Candidate
These sync scripts are candidates for Prefect orchestration once the manual process has been fully validated.