cancel
Showing results for 
Search instead for 
Did you mean: 
Community Articles
Dive into a collaborative space where members like YOU can exchange knowledge, tips, and best practices. Join the conversation today and unlock a wealth of collective wisdom to enhance your experience and drive success.
cancel
Showing results for 
Search instead for 
Did you mean: 

Lakeflow Designer: Your First Visual Data Prep, With Code Underneath

SrinuB
New Contributor III

Data engineering · Lakeflow · Let analysts build pipelines on a canvas without losing code, Git or jobs

By @SrinuB  in collaboration with @sridharplv 

An analyst needs a cleaned, joined table every morning. The options used to be a ticket to the data engineering team, or a spreadsheet nobody trusts. Lakeflow Designer is the Databricks answer: a visual canvas where you build the flow from operators, while every step is still backed by code.

This post walks through building a first one, what each operator type is for, and where a person with SQL or Python can step in.

What it is

Lakeflow Designer is a visual canvas for analysts to do data analytics, preparation and basic automation. You build a visual data prep file from a graph of operators such as filter, join and transform. Three facts matter:

  • Code-backed: all transformations are backed by code, so you can version files in Git and schedule them as jobs for production
  • Previews on a sample: each operator is previewed on a sample of rows, so you can check interim steps without running the whole file
  • Genie Code: the built-in assistant adds or changes operators from a plain-language description

blog12_lakeflow_designer.png

Before you start

  • A workspace with Unity Catalog enabled
  • CAN USE on at least one general purpose compute resource, serverless or all-purpose
  • USE CATALOG, USE SCHEMA and CREATE TABLE on the schema where the output table will be written

Build one in six steps

  1. In the sidebar click New, then Visual data prep, and rename the file
  2. Pick compute in the picker next to Schedule
  3. Add a source: drag a table from the Catalog pane onto the canvas. Each table becomes a Source operator
  4. Add operators such as Filter, Join, Aggregate or Sort, and connect them by dragging from an output port to an input port. Filter has two outputs, Included and Excluded
  5. Add an Output operator, set Output type to Table, and choose the catalog, schema and table name. Write mode defaults to Overwrite
  6. Click Run all. Previews only process a sample, so Run all is what processes the full dataset

Where your data can come from

  • Unity Catalog tables, volumes and metric views
  • External databases through a Unity Catalog connection and foreign catalog, including PostgreSQL, MySQL, SQL Server, Oracle, Snowflake, BigQuery and Redshift
  • Files: CSV, JSON, Excel, Parquet and PDF. A folder of structured files is combined into one table, and each PDF is read as one row
  • Single files from Google Drive and SharePoint through a Unity Catalog connection
  • For SaaS apps and streaming sources Designer does not read directly, ingest with Lakeflow Connect first, then use the resulting table as a Source

The operators

Group Operators

Input and outputSource, Enter Data, Output
CleanFilter, Guardrails, Limit, Prepare, Select, Sort, Unique
CombineCombine, Join
TransformAI Function, Aggregate, Multi-Row Formula, Pivot
CodePython, SQL
ReportHTML, Visualization
Canvas toolsGroup, Note

The AI Function operator wraps functions such as ai_classify, ai_extract, ai_summarize, ai_translate and ai_parse_document, so an analyst can classify or extract from text without writing a query.

When the canvas is not enough

The SQL operator takes a SELECT, and you refer to upstream operators by their names as table names. Parameters use named markers such as :environment.

The Python operator takes inputs as inputs["data"] and expects you to assign one DataFrame to result:

df = inputs["data"]
result = df.filter("amount > 0").dropDuplicates(["order_id"])

One subtlety from the docs: previews run your Python too. Check config["is_preview"] to skip side effects such as calls to an external API. You can also write your own operators as Python functions, Unity Catalog functions or table functions.

Behaviours worth knowing

  • Filter's Excluded output includes rows where the condition evaluates to null
  • Join with three or more inputs uses inner joins and produces a single output
  • Multi-Row Formula can return different results across runs when rows tie on Order by. Add a unique sort column for deterministic output
  • Changing a Source invalidates the output cache of every downstream operator
  • Output to Google Drive or SharePoint files is capped at 2.75 MiB

From prototype to production

Because the file is code-backed, the path is the same as for other code: put it in Git, parameterise anything that differs by environment, and schedule it as a job. The docs link to pages for scheduling as a job and for parameters, which I did not walk through here, so read those before you rely on a schedule.

A checklist

  1. Does the person building it hold CREATE TABLE on the target schema, and not more?
  2. Is the source a Unity Catalog table, so access and lineage are governed?
  3. Did you check results on Run all, not only on the sample preview?
  4. Does the Python operator avoid side effects when is_preview is true?
  5. Is the output table's write mode, Overwrite by default, what you want?
  6. Is the file in Git, and scheduled as a job rather than run by hand?

What I would do (opinion, not from the docs)

Use Designer for flows an analyst owns end to end, and keep complex or heavily tested transformations in code owned by engineering. The overlap is where it earns its keep: the analyst prototypes on the canvas, and engineering reviews the same file in Git instead of rewriting it.

The takeaway

  1. Visual, but code-backed: version it in Git and schedule it as a job
  2. Preview is a sample. Run all to process everything
  3. Python and SQL operators are the escape hatch, and previews run your code too
  4. Governance stays in Unity Catalog: sources and outputs are tables with normal privileges

Which recurring analyst request in your team could be a visual data prep this month?

References

  • What is Lakeflow Designer, Databricks documentation
  • Create a visual data prep in Lakeflow Designer, Databricks documentation
  • Ingest data into Lakeflow Designer, Databricks documentation
  • Built-in operators in Lakeflow Designer, Databricks documentation
0 REPLIES 0