Skip to main content
Last updated on

Tutorial: Build a visual data prep

In this tutorial, you build a visual data prep in Lakeflow Designer that answers a business question: which types of vacation rental property earn the most from confirmed bookings? You use the Wanderbricks sample dataset, a simulated travel booking platform that's preloaded in every Unity Catalog-enabled workspace.

You learn how to:

  • Add data sources to the canvas.
  • Filter, join, aggregate, and sort data with built-in operators.
  • Check each step's results in the output pane.
  • Write the results to a Unity Catalog table.

Prerequisites​

  • A Databricks workspace with Unity Catalog enabled.
  • CAN USE permission on at least one general purpose compute resource (serverless or all-purpose).
  • USE CATALOG, USE SCHEMA, and CREATE TABLE privileges on a schema where you can write the output table, such as main.default.

Step 1: Create a visual data prep​

  1. Click Plus icon. New in the sidebar and select Visual data prep. Designer opens with a welcome screen.
  2. Click the file name at the top of the editor and rename the file to Wanderbricks revenue.
  3. In the compute picker next to the Schedule button, select the compute to use.

Step 2: Add the bookings data​

Every visual data prep starts with one or more data sources, which the Source operator represents on the canvas.

  1. On the welcome screen, click Select source operator.
  2. In the Source configuration pane, search for samples.wanderbricks.bookings and select the table. A Source operator for the table appears on the canvas.
  3. Select the Source operator. The output pane at the bottom of the editor shows a preview of the bookings data, including the property_id, total_amount, and status columns.

You can also drag a table from the Catalog pane onto the canvas to add it as a source. For other ways to bring in data, see Ingest data into Lakeflow Designer.

Step 3: Keep only confirmed bookings​

  1. Hover over the bookings Source operator and click the Plus icon. button next to it. Select Filter. The new Filter operator is already connected to the source.
  2. In the Filter configuration pane, add a condition: select the status column, select Is equal to, and enter confirmed.
  3. Click Apply.
  4. Select the Filter operator and check the output pane. The Included output contains only confirmed bookings.

The Filter operator has two outputs. Included holds the rows that meet the condition, and Excluded holds the rest. See Filter.

Step 4: Join bookings to properties​

Each booking has a property_id. To group bookings by property type, join them to the properties table.

  1. Add a second Source operator for samples.wanderbricks.properties. In the Operators pane, click Source, then search for and select the table.
  2. In the Operators pane, click Join to add a Join operator.
  3. Connect the Included output of the Filter operator to the Join operator's first input. Then connect the properties Source operator to the Join operator's second input. To connect two operators, drag from an output port to an input port.
  4. In the Join configuration pane, set the join condition to match property_id in both tables.
  5. For Join type, select Inner join to produce a single joined output.
  6. Click Apply, then select the Join operator to preview the joined rows.

For more about connecting operators, see Connect operators.

Step 5: Total the revenue by property type​

  1. Click the Plus icon. button next to the Join operator and select Aggregate.
  2. Under Aggregate by, add two aggregations:
    • Select total_amount, select SUM, and name the output column total_revenue.
    • Click + Add aggregation. Select booking_id, select COUNT, and name the output column booking_count.
  3. Under Group by, select property_type.
  4. Click Apply.

Step 6: Sort the results​

  1. Click the Plus icon. button next to the Aggregate operator and select Sort.
  2. Select total_revenue and DESC, then click Apply.

Step 7: Check the results​

Select the Sort operator. The output pane shows one row for each property type, with the highest-earning type first.

By default, previews process a sample of the input rows, so the totals reflect only that sample. To see totals for the full dataset, click Run all in the toolbar. On the full dataset, the results show four property types, with Urban Year-Round earning the most revenue. To inspect the distribution of a column, select it and open the data profile. See Preview and refine results in Lakeflow Designer.

Step 8: Write the results to a table​

  1. Click the Plus icon. button next to the Sort operator and select Output.
  2. In the Output configuration pane, select Table for Output type.
  3. Enter wanderbricks_revenue_by_property_type for Table name, and select the catalog and schema for Output location, such as main.default.
  4. Leave Write mode set to Overwrite.
  5. Click Run. Designer runs the visual data prep and creates the table.

Your finished visual data prep reads two tables, filters and joins them, totals revenue by property type, and writes the result to Unity Catalog.

Optional: Try it with Genie Code​

You can also build visual data preps by describing what you want. In a new visual data prep, use the Genie Code prompt above the canvas.

prompt

Tell Genie Code to do this for you:

Using samples.wanderbricks.bookings and samples.wanderbricks.properties, show total revenue and booking count by property type for confirmed bookings, highest revenue first.

Genie Code adds and connects the operators for you. Select each operator to check its configuration and results. See Use Genie Code in Lakeflow Designer.

Next steps​

On this page