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 CATALOG privilege on the target catalog, and the USE SCHEMA, CREATE TABLE, and CREATE MATERIALIZED VIEW privileges on the target schemas. See Requirements.
  • The starterjourney_catalog catalog, and the bakehouse_bronze and bakehouse_silver schemas. The steps below create them if they don't exist, which requires the CREATE CATALOG privilege on the metastore, and the USE CATALOG and CREATE SCHEMA privileges 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:

  1. In your workspace, click Plus icon. New at the top of the sidebar, and then select Pipeline icon. 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.

  2. At the top of the editor, enter bakehouse_e2e_pipeline as the pipeline name.

  3. Next to the name, click the default catalog and schema, and select starterjourney_catalog and bakehouse_bronze.

Describe the pipeline to Genie Code

Tell Genie Code what to build by naming the source data, the tables, and the quality rules:

  1. In the blank my_transformation file, click Sparkle genie code icon. Create with Genie Code. You can also open the Genie Code panel at any time by clicking Sparkle genie code icon. Genie Code in the upper-right corner of the workspace.

  2. Paste this prompt:

    Tip

    Build a medallion pipeline from samples.bakehouse. Land sales_transactions and sales_franchises
    as bronze tables in starterjourney_catalog.bakehouse_bronze, unchanged. Then create two silver
    tables 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.sql file, or in a new file in the transformations folder. The bronze tables keep every source column, including cardNumber, which holds card data. Restrict access to the bronze schema. The silver transactions table leaves out cardNumber and paymentMethod, because the downstream sections don't need them.

    CREATE OR REFRESH STREAMING TABLE starterjourney_catalog.bakehouse_bronze.sales_transactions
    AS SELECT * FROM STREAM samples.bakehouse.sales_transactions;
    
    CREATE OR REFRESH STREAMING TABLE starterjourney_catalog.bakehouse_bronze.sales_franchises
    AS 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 SELECT
      transactionID,
      CAST(dateTime AS TIMESTAMP) AS transaction_timestamp,
      totalPrice AS total_price,
      franchiseID AS franchise_id,
      product
    FROM STREAM starterjourney_catalog.bakehouse_bronze.sales_transactions;
    
    CREATE OR REFRESH STREAMING TABLE starterjourney_catalog.bakehouse_silver.franchises
    AS SELECT
      franchiseID AS franchise_id,
      name AS franchise_name
    FROM 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.

Important

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 icon. 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:

  1. Click Catalog in the sidebar, and then open starterjourney_catalog. Confirm that bakehouse_bronze contains sales_transactions and sales_franchises, and that bakehouse_silver contains transactions and franchises.
  2. In the pipeline graph, confirm that each table that you asked for appears, and that the update completed successfully.
  3. Click the silver transactions table in the pipeline graph, and check its expectation metrics for the number of records that failed and were dropped.
  4. Ask Genie Code to describe the pipeline, and compare its summary with what you intended.

Tip

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

Resource Description
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