How versioning actually works

Questions I had about how schema versioning works in practice, explained simply.

Question 1: How do we push data to their pipelines?

Not APIs. APIs are for real-time, one-record-at-a-time requests.

For bulk data, we write files and drop them somewhere:

Our system                              Their system
─────────────────                       ─────────────────
Plentific database                      Their S3 bucket
       │                                      │
       ▼                                      ▼
[Export job runs]                       [Their Airflow picks up files]
       │                                      │
       ▼                                      ▼
Creates Parquet files ───────────────▶  Loads into their Snowflake
       │
       └──── We PUSH files to their bucket (using S3 SDK)
             or they PULL from a bucket we host
    

The "push" is:

• We use AWS SDK to write files to their S3 bucket
• They gave us credentials (IAM role) that allows write access
• Every night at 2am, our job runs, creates files, uploads them

No API calls involved. Just file transfer.

Question 2: Do they need to test the new version before switching?

Yes, ideally. Here's the timeline:

How do they test?

• We give them access to both versions in parallel during the transition
• They can export sample data from the new version into a test environment
• They run their pipeline, see what breaks, fix it
• When ready, they flip the switch

Question 3: Are we keeping two copies of the database?

No. There's only ONE database with the real data.

The "versions" are views or queries that sit on top of it.

The actual database (one copy)

-- This is the REAL table. Only one copy exists.
CREATE TABLE repairs (
    id UUID,
    client_id UUID,
    description TEXT,
    resident_name TEXT,     -- This is the current name
    status TEXT,
    created_at TIMESTAMP
);
    

The versioned views (no data duplication)

-- Version 2023-02-01 view (old name)
CREATE VIEW repairs_v2023_02_01 AS
SELECT 
    id,
    description,
    resident_name AS tenant_name,  -- Old name, mapped from new name
    status,
    created_at
FROM repairs
WHERE client_id = :current_client;

-- Version 2026-10-01 view (new name)
CREATE VIEW repairs_v2026_10_01 AS
SELECT 
    id,
    description,
    resident_name,  -- New name, direct
    status,
    created_at
FROM repairs
WHERE client_id = :current_client;
    

Question 4: What about adding a new column?

Same principle. Let's say we add uprn:

The actual database

ALTER TABLE repairs ADD COLUMN uprn TEXT;
-- Now the real table has: id, description, resident_name, status, uprn
    

The views

-- Version 2023-02-01 (old) - doesn't include uprn
CREATE VIEW repairs_v2023_02_01 AS
SELECT 
    id,
    description,
    resident_name AS tenant_name,
    status
    -- NO uprn here
FROM repairs;

-- Version 2026-10-01 (new) - includes uprn
CREATE VIEW repairs_v2026_10_01 AS
SELECT 
    id,
    description,
    resident_name,
    status,
    uprn  -- NEW column
FROM repairs;
    

Clients on old version never see uprn. It's filtered out by the view.

Question 5: What about new workflow states? (the hard one)

This is where it gets tricky. Let's use the real example:

Before: Booking status could be: scheduled, completed, cancelled

After: Booking status can be: scheduled, arrived, in_progress, attended, not_attended, no_access, completed, cancelled

The data itself now has the new statuses. We can't "hide" them like we hide a column.

Option A: Map new statuses back to old ones

-- Version 2023-02-01 view
CREATE VIEW bookings_v2023_02_01 AS
SELECT 
    id,
    CASE 
        WHEN status IN ('arrived', 'in_progress') THEN 'scheduled'
        WHEN status IN ('attended', 'not_attended', 'no_access') THEN 'completed'
        ELSE status
    END AS status
FROM bookings;
    

This "collapses" the new statuses back into the old ones. Old clients see what they expect.

Option B: Don't map, just warn

-- Version 2023-02-01 view
-- Status column unchanged. New values will appear.
-- Changelog says: "New status values added. Your pipeline must handle unknown values."
    

Option C: Call it a breaking change, force migration

If the status change is fundamental, you declare it a breaking change:

• Old version is deprecated
• Clients must migrate to new version
• No attempt to maintain compatibility

Question 6: How does "one schema per version" actually work in real life?

Here's the complete real-life setup:

1. The real database (one copy, always current)

┌─────────────────────────────────────┐
│  repairs (real table)               │
│  ─────────────────────              │
│  id, description, resident_name,    │
│  status, uprn, created_at           │
└─────────────────────────────────────┘
    

2. The version layer (views, no data copied)

┌─────────────────────────────────────┐
│  repairs_v2023_02_01 (view)         │
│  ─────────────────────              │
│  SELECT id, description,            │
│         resident_name AS tenant_name│
│  FROM repairs                       │
└─────────────────────────────────────┘

┌─────────────────────────────────────┐
│  repairs_v2026_10_01 (view)         │
│  ─────────────────────              │
│  SELECT id, description,            │
│         resident_name, uprn         │
│  FROM repairs                       │
└─────────────────────────────────────┘
    

3. The export job (reads from views)

export_config = {
    "client": "NHG",
    "version": "v2023-02-01",   # <-- This client is on old version
    "destination": "s3://nhg-bucket/..."
}

# Export job does:
SELECT * FROM repairs_v2023_02_01 WHERE client_id = 'nhg'
# Writes result to Parquet
# Uploads to their bucket
    

4. The client config (just one field different)

When NHG is ready to upgrade

1. They test with v2026-10-01 data
2. They tell us "ready"
3. We change their config row from v2023-02-01 to v2026-10-01
4. Next export uses the new view
5. Done

The key insight

The cost is not storage. The cost is:

• Writing the mapping logic
• Testing that both views work
• Documenting what's different
• Supporting clients on old versions