Deprecation Plan: Old dbt Pipeline

With the V2 Edge Function architecture in place, the old Bronze→Silver→Gold dbt pipeline can be deprecated. This document outlines what to remove and in what order.

Current State (January 2026)

Active (V2):

• sable_btig_upload_v2 Edge Function → sable.pnl_daily_v2, sable.perf_daily_v2
• sable_trans_upload_v2 Edge Function → sable.trans_daily_v2
• CLI calls Edge Functions directly

Deprecated (V1 - to remove):

• dbt pipeline (Bronze→Silver→Gold)
• file_watcher.py polling service
• worker.py job processor
• Vercel API routes for uploads

Deprecation Phases

Phase 1: Verify V2 is Stable (DO FIRST)

Before removing anything, confirm V2 works reliably for 2+ weeks:

• Daily uploads complete without errors
• No data discrepancies between upload and display
• file_upload records created correctly
• Empty files tracked properly
• Reconciliation status accurate

Checkpoint: Run sable uploads pipeline daily for 2 weeks with zero failures.

Phase 2: Remove Vercel Upload Routes

These Next.js API routes are no longer needed (CLI bypasses Vercel):

aic-sable-sb/app/api/storage/
├── upload-trans-v2/route.ts    # DELETE - replaced by Edge Function
├── upload-pnl/route.ts         # DELETE - replaced by Edge Function
├── upload-perf/route.ts        # DELETE - replaced by Edge Function
└── files/route.ts              # KEEP - still used for file listing

Steps:

1. Grep codebase for any remaining references
2. Delete route files
3. Test frontend still works (file listing, etc.)

Phase 3: Remove dbt Pipeline Code

The entire Bronze→Silver→Gold pipeline in sable-data repo:

sable-data/
├── pipelines/
│   ├── btig_pnl.py            # DELETE
│   └── btig_perf.py           # DELETE
├── services/
│   ├── file_watcher.py        # DELETE
│   └── worker.py              # DELETE
├── dbt/
│   └── models/
│       ├── bronze/            # DELETE entire directory
│       ├── silver/            # DELETE entire directory
│       └── gold/
│           ├── pnl_daily_dbt  # DELETE (replaced by v2 views)
│           └── ...

Steps:

1. Ensure no queries reference bronze.*, silver_intermediate.*, or gold.pnl_daily_dbt
2. Stop file_watcher and worker services
3. Archive or delete the code
4. Drop database schemas (see Phase 5)

Phase 4: Remove Old Frontend References

Frontend may still query old tables. Find and update:

# Search for old table references
grep -r "pnl_daily_dbt" aic-sable-sb/
grep -r "bronze\." aic-sable-sb/
grep -r "silver_intermediate" aic-sable-sb/

Update to use V2 views:

• gold.pnl_daily_dbt → gold.v_pnl_daily (which reads from sable.pnl_daily_v2)
• Remove any bronze.* or silver_intermediate.* queries

Phase 5: Database Cleanup

Once code is removed, clean up database objects:

-- DANGER: Only run after confirming no dependencies

-- Drop old schemas (after backing up if needed)
DROP SCHEMA IF EXISTS bronze CASCADE;
DROP SCHEMA IF EXISTS silver_intermediate CASCADE;

-- Drop old tables
DROP TABLE IF EXISTS gold.pnl_daily_dbt;

-- Drop old process_job infrastructure
DROP TABLE IF EXISTS sable.process_job;
DROP TABLE IF EXISTS sable.process_job_file;

-- Clean up old file_upload records (optional)
-- Keep for audit trail, or archive to cold storage

Before running:

1. Export data if needed for historical reference
2. Verify no active queries against these tables
3. Run during maintenance window

Phase 6: Documentation Cleanup

Remove or archive old documentation:

• Update data-pipeline/architecture.md - mark dbt flow as deprecated
• Archive old runbooks for file_watcher/worker
• Update CLAUDE.md if it references old pipeline

Files to Delete (Summary)

sable-data repo

pipelines/btig_pnl.py
pipelines/btig_perf.py
services/file_watcher.py
services/worker.py
dbt/models/bronze/*
dbt/models/silver/*
dbt/models/gold/pnl_daily_dbt.sql

Sable repo (aic-sable-sb)

app/api/storage/upload-trans-v2/route.ts
app/api/storage/upload-pnl/route.ts
app/api/storage/upload-perf/route.ts

Database objects

DROP SCHEMA bronze CASCADE;
DROP SCHEMA silver_intermediate CASCADE;
DROP TABLE gold.pnl_daily_dbt;
DROP TABLE sable.process_job;
DROP TABLE sable.process_job_file;

Rollback Plan

If V2 fails catastrophically:

1. Re-deploy old Vercel routes from git history
2. Restart file_watcher and worker services
3. V1 tables still exist (not dropped yet)
4. Update CLI to use old endpoints

Note: Keep V1 infrastructure running in parallel for at least 2 weeks before removal.

Success Criteria

Deprecation is complete when:

• Zero references to bronze.*, silver_intermediate.*, pnl_daily_dbt in codebase
• file_watcher and worker services stopped and removed
• Old database schemas dropped
• CLI and browser both use Edge Functions
• Documentation updated

Timeline Recommendation

Week	Action
1-2	Monitor V2 stability
3	Remove Vercel routes
4	Stop dbt services, archive code
5	Drop database objects
6	Final documentation cleanup

Related Tickets

Create tickets for each phase:

• "Remove Vercel upload routes"
• "Archive dbt pipeline code"
• "Drop deprecated database schemas"
• "Update frontend queries to V2 views"