fix: Refine SQL registry schema_mode behavior and docs

Signed-off-by: Aditya Patil <adityapatil7649@gmail.com>
feast-dev · nquinn408 · Aug 9, 2026 · Aug 5, 2026 · Aug 5, 2026 · Aug 6, 2026
commit 50c9cf6304174e3cd635aa3b8d00a5e88249dfc1
@@ -94,7 +94,7 @@ registry:
 | Value | Behavior |
 |---|---|
 | `auto` (default) | Creates tables if they don't exist. Current behavior, no breaking change. |
-| `verify` | Skips DDL. Checks that all expected tables exist on startup; raises an error listing missing tables if any are absent. |
+| `verify` | Skips DDL. Checks that all expected tables exist on startup; raises an error listing missing tables if any are absent. Note: this is a table-level check only — it does not verify individual columns. A schema created by an older Feast version (missing newer columns) will pass verification but may fail at query time. |
 | `skip` | Skips both creation and verification. Use when schema is managed entirely outside Feast (e.g. by a migration tool). |
 
 ### Pre-creating the schema
@@ -109,7 +109,7 @@ This reads `feature_store.yaml`, connects to the configured database, and create
 
 There are some things to note about how the SQL registry works:
 - When `schema_mode` is `auto` (the default), the Registry ensures the tables needed to store data exist, and creates them if they do not.
-- Upon tearing down the feast project, the registry ensures that the tables are dropped from the database.
+- Upon tearing down the feast project, the registry deletes all rows from the registry tables (it does not drop the tables themselves). This runs regardless of `schema_mode` and requires only DML (`DELETE`) privileges, not DDL.
 - The schema for how data is laid out in tables can be found in the table definitions in [`sdk/python/feast/infra/registry/sql.py`](https://github.com/feast-dev/feast/blob/master/sdk/python/feast/infra/registry/sql.py). It is intentionally simple, storing the serialized protobuf versions of each Feast object keyed by its name.
 
 ## MySQL: serialized-proto columns use `LONGBLOB`

@@ -326,7 +326,7 @@ def validate_read_path(cls, read_path: Optional[str]) -> Optional[str]:
 
 
 class FeastRegistrySchemaError(Exception):
-    def __init__(self, missing_tables: list[str]) -> None:
+    def __init__(self, missing_tables: List[str]) -> None:
         tables = ", ".join(missing_tables)
         super().__init__(
             f"SQL registry schema is incomplete — missing tables: {tables}. "
@@ -362,6 +362,8 @@ def __init__(
             metadata.create_all(self.write_engine)
         elif registry_config.schema_mode == "verify":
             self._verify_schema(self.write_engine)
+            if self.read_engine is not self.write_engine:
+                self._verify_schema(self.read_engine)
         self._warn_if_narrow_blob_columns(self.write_engine)
         if self.read_engine is not self.write_engine:
             # A read replica can be on a different schema version (e.g. mid

@@ -13,7 +13,6 @@
 # limitations under the License.
 
 import logging
-import os
 import sys
 import tempfile
 import types
@@ -593,43 +592,39 @@ def test_list_feature_views_updated_since_naive_treated_as_utc(sqlite_registry):
 
 
 class TestSchemaMode:
-    def test_schema_mode_auto_creates_tables(self):
+    def test_schema_mode_auto_creates_tables(self, tmp_path):
         """Default schema_mode='auto' creates tables on init (existing behavior)."""
-        fd, path = tempfile.mkstemp()
-        os.close(fd)
+        db_file = tmp_path / "auto.db"
         config = SqlRegistryConfig(
             registry_type="sql",
-            path=f"sqlite:///{path}",
+            path=f"sqlite:///{db_file}",
             schema_mode="auto",
         )
         registry = SqlRegistry(config, "test_project", None)
         from sqlalchemy import create_engine, inspect
 
-        engine = create_engine(f"sqlite:///{path}")
+        engine = create_engine(f"sqlite:///{db_file}")
         tables = set(inspect(engine).get_table_names())
         expected = set(registry_metadata.tables.keys())
         assert expected.issubset(tables)
         engine.dispose()
         registry.teardown()
 
-    def test_schema_mode_verify_raises_when_tables_missing(self):
+    def test_schema_mode_verify_raises_when_tables_missing(self, tmp_path):
         """schema_mode='verify' raises FeastRegistrySchemaError on empty database."""
-        fd, path = tempfile.mkstemp()
-        os.close(fd)
+        db_file = tmp_path / "verify_empty.db"
         config = SqlRegistryConfig(
             registry_type="sql",
-            path=f"sqlite:///{path}",
+            path=f"sqlite:///{db_file}",
             schema_mode="verify",
         )
         with pytest.raises(FeastRegistrySchemaError, match="missing tables"):
             SqlRegistry(config, "test_project", None)
 
-    def test_schema_mode_verify_passes_when_tables_exist(self):
+    def test_schema_mode_verify_passes_when_tables_exist(self, tmp_path):
         """schema_mode='verify' succeeds when schema was pre-created."""
-        fd, path = tempfile.mkstemp()
-        os.close(fd)
-        db_url = f"sqlite:///{path}"
-        # Pre-create the schema
+        db_file = tmp_path / "verify_ok.db"
+        db_url = f"sqlite:///{db_file}"
         from sqlalchemy import create_engine
 
         engine = create_engine(db_url)
@@ -644,25 +639,30 @@ def test_schema_mode_verify_passes_when_tables_exist(self):
         registry = SqlRegistry(config, "test_project", None)
         registry.teardown()
 
-    def test_schema_mode_skip_does_not_run_ddl(self):
-        """schema_mode='skip' skips DDL — tables must already exist."""
-        fd, path = tempfile.mkstemp()
-        os.close(fd)
-        db_url = f"sqlite:///{path}"
-        # Pre-create the schema externally
+    def test_schema_mode_skip_does_not_run_ddl(self, tmp_path):
+        """schema_mode='skip' calls neither create_all nor _verify_schema."""
+        from unittest.mock import patch
+
+        db_file = tmp_path / "skip.db"
+        db_url = f"sqlite:///{db_file}"
         from sqlalchemy import create_engine
 
         engine = create_engine(db_url)
         registry_metadata.create_all(engine)
         engine.dispose()
 
-        config = SqlRegistryConfig(
-            registry_type="sql",
-            path=db_url,
-            schema_mode="skip",
-        )
-        registry = SqlRegistry(config, "test_project", None)
-        registry.teardown()
+        with (
+            patch.object(registry_metadata, "create_all") as mock_create,
+            patch.object(SqlRegistry, "_verify_schema") as mock_verify,
+        ):
+            config = SqlRegistryConfig(
+                registry_type="sql",
+                path=db_url,
+                schema_mode="skip",
+            )
+            SqlRegistry(config, "test_project", None)
+            mock_create.assert_not_called()
+            mock_verify.assert_not_called()
 
     def test_schema_mode_invalid_value_rejected(self):
         """schema_mode only accepts 'auto', 'verify', 'skip'."""