The MLOps Seam Nobody Talks About — and How Vertica Closes It - Part 4

Version 2 — more data + more features: UNDER_REVIEW → STAGING → PRODUCTION (v1 auto-archived)

Six months later the team collects data on 10 additional vehicles. They also add hp (horsepower) as a third predictor — lighter, high-horsepower cars tend to be manuals, and the feature should sharpen the decision boundary.

Step 2a: Add the new cars to a versioned training table.

CREATE TABLE mtcars_train_v2 AS SELECT * FROM mtcars_train;

INSERT INTO mtcars_train_v2

(car_model, mpg, cyl, disp, hp, drat, wt, qsec, vs, am, gear, carb)

VALUES

('Alpine A110', 28.7, 4, 79.7, 113, 3.94, 1.615, 16.2, 0, 1, 5, 2),

('Alfa 147', 26.5, 4, 107.0, 90, 4.10, 1.850, 17.1, 0, 1, 5, 1),

('BMW 2002', 24.4, 4, 108.0, 100, 3.54, 2.780, 18.9, 1, 1, 4, 2),

('Fiat 128 S', 30.2, 4, 79.0, 66, 4.08, 1.920, 18.0, 1, 1, 4, 1),

('Cadillac CT6', 18.1, 8, 472.0, 335, 3.08, 5.250, 17.8, 0, 0, 3, 4),

('Ford Galaxy', 19.7, 6, 223.0, 150, 3.15, 3.780, 19.2, 1, 0, 3, 1),

('Dodge Viper', 15.1, 8, 488.0, 400, 3.07, 3.570, 14.5, 0, 1, 6, 2),

('Audi TT', 27.3, 4, 121.0, 177, 3.91, 2.600, 15.9, 0, 1, 6, 4),

('VW Jetta', 29.0, 4, 140.0, 115, 3.76, 2.500, 17.5, 1, 0, 5, 2),

('Chrysler 300', 17.3, 8, 360.0, 300, 3.15, 4.200, 18.6, 0, 0, 3, 2);

COMMIT;

SELECT COUNT(*) FROM mtcars_train_v2; -- 30 rows (20 original + 10 new)

COUNT

-------

30

(1 row)

Step 2b: Build a logistic regression on cyl, hp and wt as v2

SELECT LOGISTIC_REG(

'lr_transmission_v2',

'mtcars_train_v2',

'am',

'cyl, wt, hp' -- hp added as third predictor

USING PARAMETERS

optimizer = 'newton',

max_iterations = 100,

epsilon = 1e-6

);

LOGISTIC_REG

---------------------------

Finished in 8 iterations

(1 row)

Step 2c: Evaluate the model on the test set using CONFUSION_MATRIX.

CREATE TABLE mtcars_pred_v2 AS

SELECT car_model, am,

PREDICT_LOGISTIC_REG(cyl, wt, hp

USING PARAMETERS model_name = 'lr_transmission_v2') AS prediction

FROM mtcars_test;

SELECT CONFUSION_MATRIX(obs::int, pred::int USING PARAMETERS num_classes = 2) OVER()

FROM (SELECT am AS obs, prediction AS pred FROM mtcars_pred_v2) t;
actual_class | predicted_0 | predicted_1 | comment

--------------+-------------+-------------+---------------------------------------------

0 | 6 | 1 |

1 | 1 | 4 | Of 12 rows, 12 were used and 0 were ignored

(2 rows)

10 of 12 correct. The additional training rows and hp feature fixed the Maserati Bora car misclassification.

Step 2d: Register the trained model to an application name, it creates version 2.

eonv261=> SELECT REGISTER_MODEL('lr_transmission_v2', 'transmission_predictor');

REGISTER_MODEL

-----------------------------------------------------------------------------------

Model [lr_transmission_v2] is registered as [transmission_predictor], version [2]

(1 row)

SELECT registered_version, model_name, status FROM REGISTERED_MODELS WHERE registered_name = 'transmission_predictor';

registered_version | model_name | status

--------------------+--------------------+--------------

2 | lr_transmission_v2 | UNDER_REVIEW

1 | lr_transmission_v1 | PRODUCTION

(2 rows)

Step 2e: Promote the V2 model to staging

SELECT CHANGE_MODEL_STATUS('transmission_predictor', 2, 'staging');

CHANGE_MODEL_STATUS

------------------------------------------------------------------------------------

The status of model [transmission_predictor] - version [2] is changed to [STAGING]

(1 row)

Step 2f: Run both models on the same test set and compare predictions side by side before promoting.

SELECT car_model,

am AS actual,

PREDICT_LOGISTIC_REG(cyl, wt

USING PARAMETERS model_name = 'lr_transmission_v1') AS pred_v1,

PREDICT_LOGISTIC_REG(cyl, wt

USING PARAMETERS model_name = 'lr_transmission_v1',

type = 'probability') AS prob_v1,

PREDICT_LOGISTIC_REG(cyl, wt, hp

USING PARAMETERS model_name = 'lr_transmission_v2') AS pred_v2,

PREDICT_LOGISTIC_REG(cyl, wt, hp

USING PARAMETERS model_name = 'lr_transmission_v2',

type = 'probability') AS prob_v2

FROM mtcars_test

ORDER BY car_model;

car_model | actual | pred_v1 | prob_v1 | pred_v2 | prob_v2

----------------+--------+---------+----------------------+---------+---------------------

AMC Javelin | 0 | 0 | 1.93335235265226e-07 | 0 | 0.107505272745863

Camaro Z28 | 0 | 0 | 2.22044604925031e-16 | 0 | 0.028646658893347

Datsun 710 | 1 | 1 | 1 | 1 | 0.965518202388919

Honda Civic | 1 | 1 | 1 | 1 | 0.999769741198628

Hornet 4 Drive | 0 | 0 | 1.54006273992367e-10 | 0 | 0.0789979142537606

Maserati Bora | 1 | 0 | 2.22044604925031e-16 | 1 | 0.648610811180898

Merc 280 | 0 | 0 | 2.22044604925031e-16 | 0 | 0.0168310883414676

Merc 450SL | 0 | 0 | 2.22044604925031e-16 | 0 | 0.0187229565495093

Porsche 914-2 | 1 | 1 | 1 | 1 | 0.991810378101076

Toyota Corona | 0 | 1 | 1 | 1 | 0.9004634196614

Valiant | 0 | 0 | 2.22044604925031e-16 | 0 | 0.00985008151479519

Volvo 142E | 1 | 0 | 0.0189621628155345 | 0 | 0.454361689023424

(12 rows)

eonv261=>

Two things stand out. First, v2 fixes the Maserati Bora misclassification — v1 assigned it essentially 0% probability of being manual, while v2 correctly classifies it with 64.9% confidence. Second, v1 exhibits extreme overconfidence — probabilities of exactly 0 or 1 on most rows — while v2 produces far more calibrated scores. For Volvo 142E, both models are wrong, but v1 is only 1.9% confident it is manual while v2 reaches 45.4%, showing the hp feature is picking up a real signal even where the threshold isn't crossed. Hence, we can now promote v2 to production.

eonv261=> SELECT CHANGE_MODEL_STATUS('transmission_predictor', 2, 'production');

CHANGE_MODEL_STATUS

---------------------------------------------------------------------------------------

The status of model [transmission_predictor] - version [2] is changed to [PRODUCTION]

(1 row)

eonv261=> SELECT registered_version, model_name, status FROM REGISTERED_MODELS WHERE registered_name = 'transmission_predictor';

registered_version | model_name | status

--------------------+--------------------+------------

2 | lr_transmission_v2 | PRODUCTION

1 | lr_transmission_v1 | ARCHIVED

(2 rows)

The archival is atomic. There is no window where zero models or two models simultaneously hold PRODUCTION status.