Create a Feature Table in Unity Catalog with SQL or FeatureEngineeringClient

1.What makes a table a feature table

With the legacy Workspace Feature Store, a feature table was something the feature store client created and tracked. Unity Catalog works differently. A feature table is a Delta table with a primary key. The Feature Store overview lists this as the second step of the standard workflow, right after you turn raw data into a Spark DataFrame of features: create a Delta table in Unity Catalog that has a primary key.

Two rules follow. First, the primary key is required. Each feature table needs one, and Databricks keeps metadata alongside the Delta table that records which data sources built it and which notebooks and jobs wrote to it. Second, a feature table is an ordinary Unity Catalog data asset, so you address it with the usual three-level name <catalog-name>.<schema-name>.<table-name>. The examples in this lesson use ml.recommender_system.customer_features: catalog ml, schema recommender_system, table customer_features.

There are three ways to create one: Databricks SQL, the Python FeatureEngineeringClient, or Lakeflow pipelines. This page covers the first two. Whichever you pick, the result is a Delta table with a primary key, and that table can then be updated, browsed and governed through Unity Catalog.

Sources12

2.Prerequisites: runtime, client and privileges

Check four things before you create anything.

Runtime and metastore. Feature Engineering in Unity Catalog needs Databricks Runtime 13.2 or above, and the Unity Catalog metastore must use Privilege Model Version 1.0.

Python client. The client class is FeatureEngineeringClient, from the databricks-feature-engineering package on PyPI. It comes pre-installed in Databricks Runtime 13.3 LTS ML and above. On a non-ML runtime you install it yourself with %pip install databricks-feature-engineering and then run dbutils.library.restartPython(). The client runs only on Databricks. You can install it locally for unit tests with mocks, but it can't call Feature Engineering APIs from outside Databricks.

Catalog. Feature tables need a catalog. Creating a new one with CREATE CATALOG IF NOT EXISTS <catalog-name> requires the CREATE CATALOG privilege on the metastore. Using an existing one requires USE CATALOG on that catalog.

Schema. Feature tables have to live in a schema. Creating one with CREATE SCHEMA IF NOT EXISTS <schema-name> requires the CREATE SCHEMA privilege on the catalog.

Which client you use depends on where the table lives, not on the runtime version:

Sources23

3.Creating a feature table with Databricks SQL

The SQL route is a normal CREATE TABLE with two additions. The key column is declared NOT NULL, and a named CONSTRAINT ... PRIMARY KEY clause says which column or columns identify each row. Nothing marks the table as a feature table. The constraint does that on its own.

CREATE TABLE ml.recommender_system.customer_features (
  customer_id int NOT NULL,
  feat1 long,
  feat2 varchar(100),
  CONSTRAINT customer_features_pk PRIMARY KEY (customer_id)
);

Once the table exists you load data into it the same way as any other Delta table, and it already works as a feature table. The constraint name follows a convention: the table name without catalog and schema, plus _pk. That gives customer_features_pk here.

Sources2

4.Creating a feature table with fe.create_table

In Python you start from the features. Write a function that computes them and returns an Apache Spark DataFrame with a unique primary key, which can be a single column or several. Then instantiate FeatureEngineeringClient and call create_table. You give it the three-level name, the primary key, and the table's shape. There are two ways to supply the shape:

- schema=customer_features_df.schema creates an empty table with the DataFrame's schema. - df=customer_features_df creates the table and also saves the features to the underlying Delta table straight away.

The documented arguments:

customer_feature_table = fe.create_table(
  name='ml.recommender_system.customer_features',
   ? ='customer_id',
  schema=customer_features_df.schema,
  description='Customer features'
)

Sources2

5.Populating the table with fe.write_table

If you created the table with schema=, it's empty. You fill it, and later refresh it, with write_table, passing the table name and a DataFrame of feature values. The documented examples use mode="merge", both in the time series write sample below and in the unit-test example for FeatureEngineeringClient.write_table.

fe.write_table(
  "ml.ads_team.user_features",
  daily_users_batch_df,
  mode="merge"
)

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Only tables created with fe.create_table count as feature tables in Unity Catalog.Why is that wrong?

   Any Unity Catalog Delta table with a primary key constraint is a feature table, including one created in plain SQL. It appears in the Features UI automatically.

   Covered in What makes a table a feature table

2. 2.FeatureStoreClient is the client for creating feature tables in Unity Catalog.Why is that wrong?

   FeatureStoreClient is for the legacy Workspace Feature Store. Unity Catalog feature tables use FeatureEngineeringClient on every runtime version.

   Covered in Prerequisites: runtime, client and privileges