All 13 Jobs snapshot · 2026-06-08 ~9am MST
| Job | Last Run | Status | 30d Runs | Success % | Avg Duration | In etl/ |
|---|---|---|---|---|---|---|
Mileone_reward_member ThreeBirds.Analytics.Mileone.reward |
May 28, 11pm | Failed | 20 | 0% | 4s | — |
ThreeBirds.Analytics.BlueSky |
Jun 7, 6:30pm | Failed | 30 | 93% | 2h 37m | etl/SSIS/BlueSky/ |
JuiceReporting_CDXPCustomers |
Jun 7, 9pm | Succeeded | 7 | 71% | 7m 7s | — |
ThreeBirds.Analytics.CRM |
Jun 8, 8:01am | Running | 30 | 93% | 3h 20m | etl/SSIS/CRM/ |
appointment-process ThreeBirds.Analytics.Appointment |
Jun 8, 5:30am | Succeeded | 31 | 97% | 2h 53m | etl/SSIS/DMS/ |
Conquest_Mailing ThreeBirds.Analytics.Conquest |
Jun 7, 12pm | Succeeded | 30 | 100% | 57s | — |
MileOne Weekly Unsubscribe |
Jun 8, 6am | Succeeded | 60 | 100% | 2m 54s | — |
ThreeBirds.Analytics.CustomerValue |
Jun 7, 11:30pm | Succeeded | 30 | 100% | 8m 41s | — |
ThreeBirds.Analytics.CVH |
Jun 7, 4pm | Succeeded | 30 | 100% | 2h 4m | etl/SSIS/CVH/ |
ThreeBirds.Analytics.DDE |
Jun 8, 3am | Succeeded | 31 | 97% | 1h 54m | — |
ThreeBirds.Analytics.Email |
Jun 7, 12:30pm | Succeeded | 30 | — | 4h 25m | etl/SSIS/Email/ |
ThreeBirds.Analytics.LeadSource_LeadPerformance |
Jun 7, 11pm | Succeeded | — | — | 1h 28m | — |
ThreeBirds.Analytics.SmartSuppression |
— | Unknown | — | — | — | — |
Active Failures
Mileone_reward_member — 0% success over 20 runs
Last run was May 28. Nobody on the DAS team knows why it's still in the pipeline. The job completes in 4 seconds (no-op or immediate failure). No stored procedures in
etl/ — purpose entirely unknown. Owner: Rick / Eng team.ThreeBirds.Analytics.BlueSky — Duplicate object collision
93% success rate historically but currently failed. Classic SSIS truncate-rebuild failure: the job tries to recreate a table that was partially created in a prior run. Fix: add
DROP TABLE IF EXISTS before recreate, or migrate to incremental upsert. Stored procedures are in etl/SSIS/BlueSky/.
sp_juice_data_sync_bluesky_ssis: Error: Executing the query "EXEC sp_juice_data_sync_bluesky_ssis" failed with the following error: "There is already an object named 'JuiceReporting_CDXPCustomers' in the database."
Stored Procedure Inventory authoritative · etl/ submodule · 2026-06-09
Source code for 5 of 13 jobs is in etl/SSIS/ (Database-Processes repo, added as submodule 2026-06-09). The remaining 8 jobs have no stored procedures committed — descriptions from Rick still needed.
| Job | Module | What It Does | Key Tables |
|---|---|---|---|
ThreeBirds.Analytics.CRM |
etl/SSIS/CRM/ | Processes CRM data from eLeads, DealerSocket, VinSolutions, DriveCentric, Momentum. Per-provider prestaging → merged staging → main CRM table. Also handles daily loads (RR/VS clients), unsubscribes, and lead source/status lookups. | prestaging_CRM_*, staging_CRM, CRM |
ThreeBirds.Analytics.CVH |
etl/SSIS/CVH/ | Merges CRM and DMS staging data into centralized Customer Vehicle History (CVH) tables — the current platform-wide unified customer record. CDP does not ingest from CVH — this is the post-processed merge layer that the CDP replaces. | staging_CVH, CVH, CVH_List |
appointment-process ThreeBirds.Analytics.Appointment |
etl/SSIS/DMS/ | Processes DMS data — appointments, sales, service records, address mapping, CRM-DMS recipient matching, declined services. Prestaging → staging → target DMS tables. Includes Alteryx address enrichment and cellphone normalization. | prestaging_DMS_*, staging_DMS, DMS_Fact_* |
ThreeBirds.Analytics.Email |
etl/SSIS/Email/ | Processes email campaign data from the legacy platform: sends, recipients, opens, clicks, bounces, links, GA/customer journey tracking, 60-day matchbacks, MileOne summaries, and JuiceReporting OEM/parent group inserts. | Email_Sends, Email_Events, Email_Recipients, Email_Links |
ThreeBirds.Analytics.BlueSky |
etl/SSIS/BlueSky/ | Generates data for BlueSky dashboards: contacts from legacy email platform, customer vehicles, engagement stats, vehicle lifecycle position (predicted mileage / next service), product engagement, and stream campaign messages. | Analytics_BlueSky_Contact, Analytics_BlueSky_Engagement, Analytics_BlueSky_LifecyclePosition |
No source code in etl/: Mileone_reward_member · JuiceReporting_CDXPCustomers · CustomerValue · DDE · LeadSource_LeadPerformance · SmartSuppression · Conquest_Mailing · MileOne Weekly Unsubscribe
Inferred Job Dependencies partial · derived from stored procedure analysis · unconfirmed
ThreeBirds.Analytics.CRM
→
ThreeBirds.Analytics.CVH
CRM staging tables are a direct input to CVH merge
appointment-process (DMS)
→
ThreeBirds.Analytics.CVH
DMS staging tables are a direct input to CVH merge
ThreeBirds.Analytics.Email
→
ThreeBirds.Analytics.BlueSky
BlueSky contact/engagement data draws from legacy email platform tables
Full 13-job DAG remains undocumented — the dashboard shows job health but no dependency graph exists. These three dependencies are inferred from stored procedure input/output table names and have not been confirmed by engineers.
Architecture Notes
All jobs use a truncate-rebuild pattern — they drop/recreate target tables rather than upsert, creating fragility whenever a prior run leaves partial state.
Jobs run in waves from midnight through ~9am MST. CRM (~3h) and Email (~4h) are the longest-running and most likely to block dependent jobs.
3–4 offshore engineers manually monitor and remediate failures daily, with no automated alerting yet and limited documentation.
The CDXP Job Status Dashboard (localhost:3000) was built ~1 week before the 2026-06-08 session — the first centralized visibility layer added to the pipeline.
AWS Glue was evaluated and rejected — it relocates the truncate-rebuild problem without fixing it. Airflow is confirmed as the replacement path.
CDP ingestion target: raw source tables (EDW_Staging or upstream source DBs), not post-processed CVH or DWRPT views. See
memory/decisions.md.Action Items
| Priority | Action | Owner |
|---|---|---|
| High | Investigate why Mileone_reward_member has 0% success rate over 20 runs — no stored procedures committed, purpose unknown | Rick / Eng team |
| High | Fix BlueSky "already an object" failure — add DROP TABLE IF EXISTS or switch to upsert in etl/SSIS/BlueSky/ |
Eng team |
| High | Implement failure alerting — engineers are manually catching failures with no automated notifications | Rick / Leo's team |
| Med | Get job descriptions for the 8 jobs not in etl/: CustomerValue · DDE · LeadSource_LeadPerformance · SmartSuppression · Conquest_Mailing · MileOne Weekly Unsubscribe · JuiceReporting_CDXPCustomers · Mileone_reward_member | Rick |
| Med | Confirm inferred job dependency DAG with engineers — CRM/DMS → CVH and Email → BlueSky chains need verification | Rick / Eng team |
| Med | Airflow migration replaces SSIS — etl/SSIS/ becomes the authoritative stored procedure reference during migration planning |
Conflict team |
| Low | Audit unused databases (BirdBath, DigitalBundle) — dependency-map before deleting | Eng team |
Source Captures ssis-review-2026-06-08
Screencaps extracted from the session recording by Planopticon. Data in the job table above was verified against these frames.
