Build a pipeline with Genie Code
Build a Lakeflow pipeline by describing it in natural language. In the Lakeflow Pipelines Editor, Genie Code is an AI data engineering partner. It searches for the tables that you need, plans the pipeline, writes the SQL or Python source files, runs pipeline updates, and fixes errors. You review its plan and its code changes as it works.
Your Unity Catalog permissions limit what Genie Code can access and do. For all of its pipeline capabilities, see Use Genie Code for pipeline development.
This page builds the bakehouse_e2e_pipeline that the rest of the Starter Journey uses. It reads the samples.bakehouse sample data, writes raw tables to bakehouse_bronze, and writes cleaned tables to bakehouse_silver. The Query and explore and Genie Ontology pages read the silver tables by name, so keep the table and column names in the prompt.
The following video introduces Genie Code for data engineering, data science, and analytics:
Requirements
Confirm the following before you start:
- Partner-powered AI features enabled for both the account and the workspace. See Partner-powered AI features.
- A workspace in a region that supports Genie Code. See Geo availability of Genie Code features.
- The
USE CATALOGprivilege on the target catalog, and theUSE SCHEMA,CREATE TABLE, andCREATE MATERIALIZED VIEWprivileges on the target schemas. See Requirements. - The
starterjourney_catalogcatalog, and thebakehouse_bronzeandbakehouse_silverschemas. The steps below create them if they don't exist, which requires theCREATE CATALOGprivilege on the metastore, and theUSE CATALOGandCREATE SCHEMAprivileges on the catalog.
Set up the catalog and schemas
starterjourney_catalog is a sandbox catalog for this walkthrough. It plays the role of the optional sandbox catalog in the small organization pattern, so it isn't one of the dev, staging, and prod catalogs. In your own organization, build the pipeline in the catalog that your governance plan assigns to the project.
Run the following statements in the SQL editor, or check that the objects exist in Catalog Explorer:
CREATE CATALOG IF NOT EXISTS starterjourney_catalog;
CREATE SCHEMA IF NOT EXISTS starterjourney_catalog.bakehouse_bronze;
CREATE SCHEMA IF NOT EXISTS starterjourney_catalog.bakehouse_silver;
Build the pipeline
Create the pipeline, describe it to Genie Code, and run it.
Create an ETL pipeline
Open a new extract, transform, and load (ETL) pipeline in the editor, then name it and choose where it writes its tables:
-
In your workspace, click
New at the top of the sidebar, and then select
ETL pipeline.
Databricks creates a pipeline that uses Unity Catalog, and opens it in the Lakeflow Pipelines Editor. The pipeline uses serverless compute if serverless is enabled for your workspace. If Databricks can't find a default catalog that you can use, it prompts you to choose a catalog and schema.
-
At the top of the editor, enter
bakehouse_e2e_pipelineas the pipeline name. -
Next to the name, click the default catalog and schema, and select
starterjourney_catalogandbakehouse_bronze.
Describe the pipeline to Genie Code
Tell Genie Code what to build by naming the source data, the tables, and the quality rules:
-
In the blank
my_transformationfile, clickCreate with Genie Code. You can also open the Genie Code panel at any time by clicking
Genie Code in the upper-right corner of the workspace.
-
Paste this prompt:
promptBuild a medallion pipeline from samples.bakehouse. Land sales_transactions and sales_franchisesas bronze tables in starterjourney_catalog.bakehouse_bronze, unchanged. Then create two silvertables in starterjourney_catalog.bakehouse_silver:- transactions, with transaction_timestamp (a timestamp from dateTime), total_price (from totalPrice),franchise_id (from franchiseID), and product. Drop rows where transactionID is null.- franchises, with franchise_id (from franchiseID) and franchise_name (from name).Use snake_case column names for the silver tables.Use this code if Genie Code names the columns differently
The following SQL produces the exact bronze and silver tables that the next sections use. Put it in the
my_transformation.sqlfile, or in a new file in thetransformationsfolder. The bronze tables keep every source column, includingcardNumber, which holds card data. Restrict access to the bronze schema. The silvertransactionstable leaves outcardNumberandpaymentMethod, because the downstream sections don't need them.SQLCREATE OR REFRESH STREAMING TABLE starterjourney_catalog.bakehouse_bronze.sales_transactionsAS SELECT * FROM STREAM samples.bakehouse.sales_transactions;CREATE OR REFRESH STREAMING TABLE starterjourney_catalog.bakehouse_bronze.sales_franchisesAS SELECT * FROM STREAM samples.bakehouse.sales_franchises;CREATE OR REFRESH STREAMING TABLE starterjourney_catalog.bakehouse_silver.transactions(CONSTRAINT valid_transaction_id EXPECT (transactionID IS NOT NULL) ON VIOLATION DROP ROW)AS SELECTtransactionID,CAST(dateTime AS TIMESTAMP) AS transaction_timestamp,totalPrice AS total_price,franchiseID AS franchise_id,productFROM STREAM starterjourney_catalog.bakehouse_bronze.sales_transactions;CREATE OR REFRESH STREAMING TABLE starterjourney_catalog.bakehouse_silver.franchisesAS SELECTfranchiseID AS franchise_id,name AS franchise_nameFROM STREAM starterjourney_catalog.bakehouse_bronze.sales_franchises;
Review the plan and approve actions
For more complex tasks, Genie Code can create a step-by-step plan and ask clarifying questions. Answer the questions to refine the plan.
When Genie Code needs to use a tool, such as editing a source file or running the pipeline, it asks for your approval. In auto-approve mode, an AI classifier approves or blocks each action instead. Click Allow to approve a request, or Decline to refuse it. When you first use Genie Code, the default mode for every chat is Auto-approve. To change it, open the Genie Code settings from the gear icon in the pane header. For the approval modes, see Approve tool actions.
Genie Code can generate and run code in your pipeline. Use it only with data that you trust, and review its code changes before you run them. Auto-approve isn't a security boundary, so turn it off when you work with production or sensitive data.
Genie Code shows the code diff for each file that it changes. To stop Genie Code while it works, click the stop button.
Run or dry-run the pipeline
If Genie Code hasn't already run the pipeline, click Run pipeline in the editor. To validate the code without updating any data, click Dry run instead. See Run pipeline code and Check a pipeline for errors without waiting for tables to update.
If the update fails, ask Genie Code to fix the error. It reads the error, edits the affected files, and runs the update again with your approval.
Verify the pipeline
Check the schemas, the pipeline graph, and the expectation metrics to confirm the output matches your intent:
- Click Catalog in the sidebar, and then open
starterjourney_catalog. Confirm thatbakehouse_bronzecontainssales_transactionsandsales_franchises, and thatbakehouse_silvercontainstransactionsandfranchises. - In the pipeline graph, confirm that each table that you asked for appears, and that the update completed successfully.
- Click the silver
transactionstable in the pipeline graph, and check its expectation metrics for the number of records that failed and were dropped. - Ask Genie Code to describe the pipeline, and compare its summary with what you intended.
The Query and explore page reads these silver columns by name: transaction_timestamp, franchise_id, product, and total_price on transactions, and franchise_id and franchise_name on franchises. If Genie Code named any of them differently, rename them now, before you continue.
Put the pipeline under source control
Before you run the pipeline in production, put its source files under version control so that you can review changes and deploy the same pipeline to development, staging, and production. See Create a source-controlled pipeline and Operations and CI/CD.
Next steps
-
- Query and explore your data
- Query and explore the silver tables that your pipeline produces
-
- Develop and debug ETL pipelines with the Lakeflow Pipelines Editor
- Learn about the pipeline graph, data previews, selective execution, and other features of the editor