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

VERTICAMACHINE LEARNINGMODEL VERSIONING

8/10/20263 min read

End-to-end example: predicting car transmission type with three model versions

📌 Scope of this post This post focuses specifically on model versioning — registration, lifecycle transitions, promotion, rollback, and audit history. Model evaluation details such as coefficient interpretation, confusion matrix analysis, feature selection rationale, and hyperparameter tuning are intentionally kept brief. The goal is to illustrate how Vertica's versioning system works end to end, not to build the most accurate transmission classifier

We will use Vertica's built-in mtcars sample dataset — 32 cars with attributes like cylinders (cyl), weight (wt), horsepower (hp), and displacement (disp). The prediction target is am: whether a car has a manual (1) or automatic (0) transmission.

The three-version story maps naturally to a real-world model evolution:

v1 — quick baseline using two features (cyl, wt) on the original 20-row training set
v2 — retrain after adding 10 new car records, expand to three features (cyl, wt, hp)
v3 — try all five available features; model overfits on the small dataset, fails staging, gets declined

Setup: load the sample data and verify the splits

Load Vertica's ML sample data if you haven't already. This creates mtcars_train (20 rows) and mtcars_test (12 rows) in the public schema.

git clone https://github.com/vertica/Machine-Learning-Examples

Cloning into 'Machine-Learning-Examples'...

remote: Enumerating objects: 513, done.

remote: Counting objects: 100% (119/119), done.

remote: Compressing objects: 100% (39/39), done.

remote: Total 513 (delta 86), reused 89 (delta 80), pack-reused 394 (from 1)

Receiving objects: 100% (513/513), 13.29 MiB | 248.00 KiB/s, done.

Resolving deltas: 100% (271/271), done.

After cloning the repository, we then create a table for the training data and load the data from the mtcars.csv file. This creates mtcars_train (20 rows) and mtcars_test (12 rows) in the public schema.

eonv261=> CREATE TABLE mtcars (car_model varchar(30), mpg float, cyl int,disp float, hp int, drat float, wt float,qsec float, vs float, am float, gear int,carb int, tf VARCHAR(5));

CREATE TABLE

eonv261=> COPY mtcars FROM LOCAL 'mtcars.csv' DELIMITER ',' ENCLOSED BY '"' SKIP 1;

Rows Loaded

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

32

(1 row)

eonv261=> CREATE TABLE mtcars_train AS (SELECT * FROM mtcars WHERE tf = 'train');

CREATE TABLE

eonv261=> CREATE TABLE mtcars_test AS (SELECT * FROM mtcars WHERE tf = 'test');

CREATE TABLE

eonv261=>

eonv261=> SELECT COUNT(*) FROM mtcars_train;

COUNT

-------

20

eonv261=> SELECT COUNT(*) FROM mtcars_test;

COUNT

-------

12

(1 row)

Version 1 — two-feature baseline: UNDER_REVIEW → STAGING → PRODUCTION

Step 1a: Build a logistic regression on cyl and wt

SELECT LOGISTIC_REG(

'lr_transmission_v1', -- model name stored in v_catalog.models

'mtcars_train', -- training table

'am', -- response column: 0 = automatic, 1 = manual

'cyl, wt'

USING PARAMETERS

optimizer = 'newton',

max_iterations = 100,

epsilon = 1e-6

);

LOGISTIC_REG

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

Finished in 20 iterations

(1 row)

Step 1b: Inspect the model summary

eonv261=> SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name = 'lr_transmission_v1');

GET_MODEL_SUMMARY

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

=======

details

=======

predictor|coefficient| std_err |z_value |p_value

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

Intercept| 262.39898 |44745.77338| 0.00586| 0.99532

cyl | 16.75892 |5987.23236 | 0.00280| 0.99777

wt |-119.92116 |17237.03154|-0.00696| 0.99445

==============

regularization

==============

type| lambda

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

none| 1.00000

===========

call_string

===========

logistic_reg('public.lr_transmission_v1', 'mtcars_train', '"am"', 'cyl, wt'

USING PARAMETERS optimizer='newton', epsilon=1e-06, max_iterations=100, regularization='none', lambda=1, alpha=0.5, fit_intercept=true)

===============

Additional Info

===============

Name |Value

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

iteration_count | 20

rejected_row_count| 0

accepted_row_count| 20

(1 row)

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

CREATE TABLE mtcars_pred_v1 AS SELECT car_model, am, PREDICT_LOGISTIC_REG(cyl, wt USING PARAMETERS model_name = 'lr_transmission_v1') 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_v1) t;

actual_class | predicted_0 | predicted_1 | comment

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

0 | 6 | 1 |

1 | 2 | 3 | Of 12 rows, 12 were used and 0 were ignored

(2 rows)

9 of 12 correct are predicted correctly. So model is performing at 75% accuracy which is solid enough for an initial production baseline.

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

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

REGISTER_MODEL

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

Model [lr_transmission_v1] is registered as [transmission_predictor], version [1]

(1 row)

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

registered_version | model_name | status

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

1 | lr_transmission_v1 | UNDER_REVIEW

(1 row)

Step 1e: Promote the model to staging and then production. Both calls require the MLSUPERVISOR role.

eonv261=> SELECT CHANGE_MODEL_STATUS('transmission_predictor', 1, 'staging');

CHANGE_MODEL_STATUS

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

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

(1 row)

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

CHANGE_MODEL_STATUS

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

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

(1 row)

eonv261=>

Now, we can see that model is under PRODUCTION status.

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

registered_version | model_name | status

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

1 | lr_transmission_v1 | PRODUCTION

(1 row)

Step 1f: Score new cars using the production model.

SELECT car_model, PREDICT_LOGISTIC_REG(cyl, wt USING PARAMETERS model_name = 'lr_transmission_v1') AS predicted_am, PREDICT_LOGISTIC_REG(cyl, wt USING PARAMETERS model_name = 'lr_transmission_v1', type = 'probability') AS prob_manual FROM mtcars_test;