What you will be able to do
- Populate a Unity Catalog feature table with fe.write_table after creating it
- Choose mode='merge' or mode='overwrite' for a given refresh job and predict which rows change
- Diagnose a failed write caused by a DataFrame missing a primary key column
- Keep a feature table fresh from a scheduled job or a streaming DataFrame
- Write new history rows to a time series feature table
Key concept
Merge write (upsert by primary key) — fe.write_table with mode='merge' matches incoming rows to the table on the primary key: it updates rows whose key already exists and inserts rows whose key is new. Rows whose key isn't in the DataFrame stay as they were.
1.Creating a feature table and filling it are separate steps
A feature table doesn't stay correct by itself. Customers make purchases, products change price and sensor readings come in, and each change has to be written to the table before a model can use it. In Unity Catalog, a feature table is a Delta table with a primary key. The FeatureEngineeringClient handles its life in two steps: create_table defines the table (name, primary key, schema) and write_table loads data into it. You'll call the first once and the second many times.
customer_feature_table = fe.create_table(
name='ml.recommender_system.customer_features',
primary_keys='customer_id',
schema=customer_features_df.schema,
description='Customer features'
)After the table exists, every load goes through write_table. create_table can also take a df argument and save that first batch of features when it creates the table. That shortcut covers the initial load only. It is not how you refresh a table that already exists, so a nightly job that calls create_table again isn't refreshing anything. The input to write_table is a Spark DataFrame from your feature computation function, and each row must have a unique primary key. The key can be one column or several.
The primary key decides whether a write succeeds. When write_table updates an existing table, the DataFrame has to include every primary key column, plus the partition columns if the table has any. With a composite key such as (loan_id, as_of_date), a DataFrame that has loan_id and the feature columns but no as_of_date can't be matched to existing rows, so the write fails. Column order and key width don't cause this. The cause is a missing key column.
Using write_table also keeps lineage intact. Feature table metadata records the notebooks and jobs that created or wrote to the table, so you can later see which job last updated the features a model depends on.
Checkpoint 1 of 9· Fill the gap
A team needs to define a new Unity Catalog feature table once, with its name, primary key and schema, before any nightly jobs load data into it. Which FeatureEngineeringClient method completes this call?
customer_feature_table = fe. ? (
name='ml.recommender_system.customer_features',
primary_keys='customer_id',
schema=customer_features_df.schema,
description='Customer features'
)create_table defines the table's name, primary key and schema, and you call it once. write_table loads data into a table that already exists, so the recurring nightly loads use write_table and do not call create_table again.
Source: docs.databricks.comCheckpoint 2 of 9· Exam question
A data scientist runs fe.write_table(name="loan_features", df=updates_df, mode="merge") against a feature table whose primary key is the composite (loan_id, as_of_date), and the call fails during validation. updates_df contains loan_id, credit_score, and utilization_ratio, but no as_of_date column. What is the most likely cause of the failure?
Correct answer: D — The DataFrame is missing one of the feature table's primary key columns, and write_table requires every primary key column to be present so it can determine which rows to update or insert.
- A. This claim about merge mode is invented: merge mode works the same way regardless of whether the primary key is a single column or a composite key, so restricting composite-key tables to overwrite mode is not an actual constraint of the API.
- B. Column order in the incoming DataFrame does not matter to
write_table; it matches columns by name against the feature table's schema, so this is not the reason the call fails. - C. Publishing to an online store does not place a lock on offline writes to the feature table, so a prior online sync would not block this
write_tablecall. - D. Because the feature table's primary key is
(loan_id, as_of_date), every write must supply both columns so the client can identify which existing rows to update and which new rows to insert; omittingas_of_dateleaves the operation unable to resolve the primary key, which is why validation fails.
2.mode='merge' upserts by primary key
Merge is the mode for incremental updates. Each row in the DataFrame is matched to the table by primary key. If the key already exists, the row's feature values are updated. If it doesn't, a new row is inserted. Every other row in the table is left as it was. That makes merge the right choice when a job computes features for only some entities, such as customers who transacted today or devices that reported in the last hour, and the rest of the table has to stay valid.
mock_write_table.assert_called_once_with(
name='ml.recommender_system.customer_features',
df=customer_features_df,
mode='merge'
)Merge can also add columns. Say you extend the feature computation function with a new feature and run write_table on its output. The write adds the new column to the table schema and merges the values by primary key. You can also put the new feature in a separate function. Its DataFrame then only needs the primary key columns and the new feature, and merge adds that feature next to the existing ones on the same keys. In both cases merge only adds: it inserts rows, updates matched rows and adds new feature columns.
Merge matches on customer_id and writes only the columns the DataFrame contains. The existing features and any customers missing from the DataFrame are not part of the write, so they don't change.
Checkpoint 3 of 9· Check yourself
After adding a new feature, days_since_last_login, in its own computation function, an engineer calls fe.write_table on the existing customer_features table with mode='merge'. The DataFrame holds only customer_id and days_since_last_login. What is the result?
Merge matches rows on the primary key and writes only the columns the DataFrame contains. It adds the new feature column next to the existing ones and never removes columns or clears values it was not given.
“your DataFrame must supply values for all features of the feature table, unlike regular feature tables”Source: docs.databricks.com
Sources1
3.mode='overwrite' replaces the whole table
Overwrite is the full-rebuild mode. With mode='overwrite', write_table refreshes the entire feature table from the DataFrame you pass in, so the table afterwards contains exactly that DataFrame. Use it for a job that recomputes every feature for every entity from raw sources, such as a Sunday rebuild of product_features, where the old contents should be thrown away.
Choosing the wrong mode either loses data or keeps stale data. Run overwrite with a DataFrame of only today's changes and every row that isn't in it is gone, because overwrite doesn't preserve rows. Run merge with a full rebuild and nothing is removed, so features dropped from the computation stay in the table. Pick the mode based on how much of the table the DataFrame describes: all of it calls for overwrite, part of it calls for merge.
| Mode | Rows in the DataFrame | Rows not in the DataFrame | Typical job |
|---|---|---|---|
| mode='merge' | Updated if the primary key exists, inserted if it is new | Left unchanged | Nightly or streaming incremental update for a subset of entities |
| mode='overwrite' | Become the new table contents | Removed in the full refresh | Scheduled full rebuild from raw sources |
| create_table(df=...) | Saved as the table's first contents | None exist yet | One-time creation and initial load |
A Unity Catalog feature table is still a Delta table. Once it exists, you can write to it the same way as any other Delta table. When the goal is an upsert by primary key, write_table with merge is the call that does it.
Checkpoint 4 of 9· Check yourself
On Monday, a job meant to apply only today's 500 changed products calls fe.write_table on the 50,000-row product_features table with mode='overwrite'. What does the table contain after the write?
Overwrite refreshes the whole table, so afterwards the table holds exactly the DataFrame that was passed in. To update some products and keep the others, the job should use mode='merge'.
“Overwrite mode does a full refresh of the feature table”Source: docs.databricks.com
Checkpoint 5 of 9· Exam question
A feature engineering team maintains a Unity Catalog feature table customer_features with primary key customer_id. Each night, a batch job computes updated values for only the customers who transacted that day, and the team wants those rows inserted or updated while every other customer's existing feature values stay exactly as they are. Which write call achieves this without touching unrelated rows?
Correct answer: B — Call fe.write_table(name="customer_features", df=nightly_df, mode="merge"), since merge mode inserts new primary keys and updates matching ones while leaving unmatched rows unchanged.
- A. This description of overwrite mode is incorrect: overwrite mode replaces the entire table's schema and data with the incoming DataFrame rather than selectively updating only the rows it contains. Using it here would delete every customer not present in the nightly batch.
- B. Merge mode is correct here because it performs an upsert keyed on the primary key column: rows whose key matches an existing row get updated, rows with a new key get inserted, and rows whose key is absent from the incoming DataFrame are left untouched.
- C.
create_tableis meant to define a brand-new feature table's schema and primary keys, not to update an existing one; calling it again against a table that already exists conflicts with its purpose rather than performing a targeted upsert. - D. Writing through the raw Spark DataFrame API bypasses the feature engineering client entirely, so primary key enforcement and feature table metadata are not applied, and a plain append can create duplicate rows for the same primary key instead of upserting them.
Checkpoint 6 of 9· Exam question
A team rebuilds a product_features feature table from scratch every Sunday by recomputing every feature for every product from raw source tables, and the resulting DataFrame should completely replace the previous week's contents, including dropping any columns that no longer exist. Which write approach is correct for this weekly job?
Correct answer: A — Call fe.write_table(name="product_features", df=full_df, mode="overwrite"), since overwrite mode replaces both the schema and the data of the table with the incoming DataFrame.
- A. Overwrite mode is correct for a full weekly rebuild because it replaces the table's schema as well as its data with whatever the incoming DataFrame contains, including dropping columns that are no longer present, which matches exactly what this job needs.
- B. Merge mode does not drop columns; it only upserts rows by primary key while leaving the existing schema and any untouched rows in place, so it would not remove obsolete columns as this weekly rebuild requires.
- C.
create_tableis meant to define a new feature table once, and calling it repeatedly against a table that already exists conflicts with an existing table rather than performing a clean weekly refresh of its contents. - D. Dropping the Delta table with raw SQL destroys the feature table's Unity Catalog registration and metadata outside of the feature engineering client, and merge mode afterward cannot restore metadata it has no record of, so this approach breaks the table rather than rebuilding it correctly.
4.Keeping features fresh with scheduled jobs and streaming
A feature table is only as current as its last write. You keep it up to date in one of two ways: a regularly scheduled job that writes features, or a stream that writes new feature values as they arrive. A scheduled job is usually a batch computation followed by a merge write. It suits features that change on a daily or hourly schedule, and each run updates only the entities in that batch.
Streaming uses the same method. Instead of a batch DataFrame, you pass a Structured Streaming DataFrame to write_table, and the call returns a StreamingQuery object rather than finishing like a batch write. Your feature computation code turns the raw stream into features and write_table keeps writing them into the table. Choose streaming when features have to reflect events within minutes. Choose a scheduled job when a daily batch is recent enough and you'd prefer not to run a stream continuously.
Checkpoint 7 of 9· Check yourself
Fraud analysts want the card_activity features in an offline feature table to reflect new transactions within minutes. They want to avoid waiting for a daily batch. Which approach keeps the table current?
Streaming uses the same write_table method with a Structured Streaming DataFrame, and it keeps writing features as events arrive. create_table's df argument only covers the first load. Overwrite with a small batch would delete every other row. Moving features to an online store is a separate step and does not fill the offline table.
“In addition to batch writes, Databricks Feature Store supports streaming. You can write feature values to a feature table from a streaming source”Source: docs.databricks.com
5.Writing history to a time series feature table
A time series feature table (timeseries column in the primary key) includes a timestamp in its primary key. That changes what a merge write does. Merge matches on entity key and timestamp together. A row for user_id 42 at today's ts doesn't match the row for user 42 at yesterday's ts, so it's inserted as a new row and yesterday's values stay. Over time each entity builds up a history of feature values, which is what point-in-time lookups need. To get that, every row you write must include the timestamp column.
fe = FeatureEngineeringClient()
# daily_users_batch_df DataFrame contains the following columns:
# - user_id
# - ts
# - purchases_30d
# - is_free_trial_active
fe.write_table(
"ml.ads_team.user_features",
daily_users_batch_df,
mode="merge"
)Time series tables have one extra rule. A regular feature table accepts a merge that carries only some of its features, but a write to a time series table must include values for every feature in the table. The rule keeps feature values from being sparse across timestamps, because a row with half its features missing would be a poor snapshot of that moment. Streaming writes to time series feature tables are also supported, so the same table can be fed by a stream.
Checkpoint 8 of 9· Fill the gap
Each daily batch for the time series table ml.ads_team.user_features should add rows for today's timestamp and keep earlier ones, so every user builds up a history for point-in-time lookups. Which mode completes the call?
fe = FeatureEngineeringClient()
# daily_users_batch_df DataFrame contains the following columns:
# - user_id
# - ts
# - purchases_30d
# - is_free_trial_active
fe.write_table(
"ml.ads_team.user_features",
daily_users_batch_df,
mode=" ? "
)On a time series table, merge matches on the entity key and the timestamp together. A row with today's ts doesn't match yesterday's row, so it is inserted and the history is kept. Overwrite would replace the table with only today's batch and erase that history.
Source: docs.databricks.comCheckpoint 9 of 9· Check yourself
The time series table user_features has primary key (user_id, ts TIMESERIES) and the features purchases_30d and is_free_trial_active. A new job merges a DataFrame that holds only user_id, ts and purchases_30d. What is wrong with this write?
Regular feature tables accept merges that carry only some features, but time series tables don't. Every write has to include all features so that values aren't sparse across timestamps. Merge works on the composite entity-plus-timestamp key, and the ts column is required.
“When writing features to the time series feature tables, your DataFrame must supply values for all features of the feature table, unlike regular feature tables.”Source: docs.databricks.com
Sources4
Sources
(4)
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Populate the feature table using write_table.”
↩︎ Creating a feature table and filling it are separate steps“An alternative is to use `create_table` and specify the `df` argument.”
↩︎ Creating a feature table and filling it are separate steps“The output of each function should be an Apache Spark DataFrame with a unique primary key.”
↩︎ Creating a feature table and filling it are separate steps“must contain the feature tables' primary and partition keys (if defined)”
↩︎ Creating a feature table and filling it are separate steps“Altering them will cause downstream pipelines that use features for training and serving models to break.”
↩︎ Creating a feature table and filling it are separate steps“Rows whose primary key does not exist in the DataFrame sent in the write_table call remain unchanged.”
↩︎ mode='merge' upserts by primary key“This updates the feature table schema and merges new feature values based on the primary key.”
↩︎ mode='merge' upserts by primary key“You can update a feature table in Unity Catalog by adding new features or by modifying specific rows based on the primary key.”
↩︎ mode='merge' upserts by primary key“After the table is created, you can write data to it like other Delta tables, and it can be used as a feature table.”
↩︎ mode='overwrite' replaces the whole table“set up a regularly scheduled job to write features or stream new feature values into the feature table”
↩︎ Keeping features fresh with scheduled jobs and streaming“pass a streaming DataFrame as an argument to write_table. This method returns a StreamingQuery object.”
↩︎ Keeping features fresh with scheduled jobs and streaming“Rows whose primary key does not exist in the DataFrame sent in the write_table call remain unchanged.”
↩︎ Key concept“must contain the feature tables' primary and partition keys (if defined)”
↩︎ Exam trap 2“This updates the feature table schema and merges new feature values based on the primary key.”
↩︎ Exam trap 3“Populate the feature table using write_table.”
↩︎ Exam trap 4 - 2.
“notebooks and jobs that created or wrote to the table”
↩︎ Creating a feature table and filling it are separate steps“feature computation code can utilize Structured Streaming to transform raw data streams into features”
↩︎ Keeping features fresh with scheduled jobs and streaming“You can also stream feature tables from the offline store to an online store.”
↩︎ Keeping features fresh with scheduled jobs and streaming“In addition to batch writes, Databricks Feature Store supports streaming. You can write feature values to a feature table from a streaming source”
↩︎ Checkpoint - 3.https://docs.databricks.com/aws/en/machine-learning/feature-store/workspace-feature-store/feature-tablesOfficial docs
“Overwrite mode does a full refresh of the feature table”
↩︎ mode='overwrite' replaces the whole table“Overwrite mode does a full refresh of the feature table”
↩︎ Exam trap 1 - 4.
“your DataFrame must supply values for all features of the feature table, unlike regular feature tables”
↩︎ Writing history to a time series feature table“This constraint reduces the sparsity of feature values across timestamps in the time series feature table.”
↩︎ Writing history to a time series feature table“Streaming writes to time series feature tables is supported.”
↩︎ Writing history to a time series feature table“your DataFrame must supply values for all features of the feature table, unlike regular feature tables”
↩︎ Checkpoint“When writing features to the time series feature tables, your DataFrame must supply values for all features of the feature table, unlike regular feature tables.”
↩︎ Checkpoint