Use ALTER statements with pipeline datasets

Lakeflow pipelines are defined in dedicated source code that you author in either SQL or Python, for example, in the Lakeflow Pipelines Editor.

Lakeflow Connect creates pipelines that ingest data, and create ingestion streaming tables.

Azure Databricks also provides a SQL environment called Databricks SQL. You can create materialized views and streaming tables with Databricks SQL using pipeline functionality outside of Lakeflow (see Standalone pipelines). Typically, Databricks SQL is not used to create update Lakeflow pipelines.

However you can use ALTER SQL statements in Databricks SQL to modify the properties of a dataset created with either Lakeflow pipelines, Databricks SQL, or Lakeflow Connect. Use these SQL statements from any Databricks SQL environment, whether you are modifying Lakeflow pipeline datasets, standalone pipeline datasets, or Lakeflow Connect datasets.

For datasets backed by a standalone pipeline created in Databricks SQL, you can also change the owner with SET OWNER TO.

Note

You can't modify the schedule or trigger of a dataset defined in Lakeflow pipelines with an ALTER statement.

Limitation: Pipeline updates and changes made with ALTER

There are cases where ALTER statements conflict with the definition of the pipeline-created datasets. The SQL that defines a table or view in a pipeline is re-run on each update. This can undo changes you make with an ALTER statement.

For example, if you have a SQL statement that defines a materialized view, like the following:

CREATE OR REPLACE MATERIALIZED VIEW masked_view (
    id int,
    name string,
    region string,
    ssn string MASK catalog.schema.ssn_mask_fn
  )
  WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
  AS SELECT id, name, region, ssn
       FROM employees;

Then you try to remove the mask from the ssn column using an ALTER statement, like this:

ALTER MATERIALIZED VIEW masked_view ALTER COLUMN ssn DROP MASK;

The mask is removed, but the next time the materialized view is updated the SQL definition adds it back.

To safely remove the mask, you must edit the SQL definition to remove the mask and then run the ALTER command to DROP the mask.

Note

To edit the definition of a pipeline defined in Lakeflow pipelines, edit your pipeline source using the pipeline editor. To edit the definition of a standalone pipeline, run the modified SQL statement in any Databricks SQL environment.

Additional resources

  • Last updated on