Skip to content

9. Custom logic

Add a shortfall percentage to the reusable city report. Use SQL for the pipeline, then compute the same score with a custom node and a Python Script. The complete script includes the previous reader, child flow, and Gate.

scored = ff.sql("SELECT *, ROUND(100.0 * shortfall / target, 2) AS shortfall_pct FROM report", report=report)

Place scored into the Gate where chapter 8 used report. The SQL reads the supplied frame under the name report; it uses Polars SQL, not a database connection. Positive percentages still mean below target.

A configurable custom node

The script imports polars as pl and from flowfile import node_designer as nd at the top.

class ShortfallSettings(nd.NodeSettings):
    options: nd.Section = nd.Section(
        title="Scoring",
        target_column=nd.TextInput(label="Target column", default="target"),
    )


class ShortfallPercent(nd.CustomNodeBase):
    node_name: str = "Tutorial Shortfall Percent"
    settings_schema: ShortfallSettings = ShortfallSettings()

    def process(self, *inputs: pl.LazyFrame) -> pl.LazyFrame:
        target = self.settings_schema.options.target_column.value
        return inputs[0].with_columns((100.0 * pl.col("shortfall") / pl.col(target)).round(2).alias("shortfall_pct"))


ff.custom_nodes.install(ShortfallPercent, overwrite=True)
score = ff.custom_node(ShortfallPercent)
custom_scored = score(report, target_column="target")

process() receives Polars lazy frames. The settings schema exposes Target column as a text input, and the factory maps target_column= to that setting. This is useful when the calculation needs a reusable node with an editable form. For this report, SQL already expresses the calculation in one line.

A class defined in a script exists only in that Python process. ff.custom_nodes.install writes it to your custom nodes folder as tutorial_shortfall_percent.py, so the Designer and saved flows can use the node; without that file the node opens in the Designer as Custom node not installed. overwrite=True lets the script run again, replacing the file the previous run wrote. Another machine that runs the flow needs the node installed too.

A Python Script version

The Python Script node needs a kernel. Create one in the Designer; ff.kernels.list() then returns it, and the script passes the first kernel in that list as kernel=.

my_kernels = ff.kernels.list()
kernel = my_kernels[0] if my_kernels else None


@ff.python_script(
    kernel=kernel,
    returns={
        "city": ff.String,
        "orders": ff.UInt32,
        "revenue": ff.Float64,
        "target": ff.Float64,
        "shortfall": ff.Float64,
        "shortfall_pct": ff.Float64,
    },
)
def score_in_python(city_report: pl.LazyFrame) -> pl.LazyFrame:
    return city_report.with_columns((100.0 * pl.col("shortfall") / pl.col("target")).round(2).alias("shortfall_pct"))


script_scored = score_in_python(report)

Calling the decorated function places a deferred Python Script node. returns= declares the output columns so downstream nodes can be built. The function body runs on the kernel when the graph executes that branch.

ff.kernels.list() is sorted by kernel id. With several kernels, pick one by id instead, for example ff.kernels["tutorial"]. Without any kernel, kernel is None: the script still builds, and the node opens in the Designer with an empty kernel picker (Select a kernel...).

Running the Python Script

The script builds this node but does not run it. With Docker running and a kernel created, script_scored.collect() runs the function on that kernel.

Check it

routed.flow_graph.print_tree()
print(scored.collect())
print(custom_scored.collect())

The script prints:

Output
● Read data (11)  Supermarket invoices
│
● Drop duplicates (12)
│
● Filter data (13)
│
● Formula (14)
├─┐
● │ Filter data (15)
│ ● Filter data (16)
├─┘
● Union data (17)
│ ● Manual input (18)
├─┘
● Join (19)
│
● Run Flow (27)
├─┬─┐
│ ● │ Tutorial Shortfall Percent (35)
│   ● Python Script (36)
● SQL Query (28)  SELECT *, ROUND(100.0 * shortfall / target, 2) ...
│
● Gate (29)
├─┐
● │ Formula (31)  [then]
│ ● Formula (33)  [else]
├─┘
● Union data (34)
shape: (5, 6)
┌───────────┬────────┬──────────┬─────────┬───────────┬───────────────┐
│ city      ┆ orders ┆ revenue  ┆ target  ┆ shortfall ┆ shortfall_pct │
│ ---       ┆ ---    ┆ ---      ┆ ---     ┆ ---       ┆ ---           │
│ str       ┆ u32    ┆ f64      ┆ f64     ┆ f64       ┆ f64           │
╞═══════════╪════════╪══════════╪═════════╪═══════════╪═══════════════╡
│ Bago      ┆ 59     ┆ 28592.7  ┆ 30000.0 ┆ 1407.3    ┆ 4.69          │
│ Mandalay  ┆ 73     ┆ 36937.24 ┆ 35000.0 ┆ -1937.24  ┆ -5.53         │
│ Naypyitaw ┆ 51     ┆ 23733.16 ┆ 25000.0 ┆ 1266.84   ┆ 5.07          │
│ Taunggyi  ┆ 63     ┆ 32710.95 ┆ 30000.0 ┆ -2710.95  ┆ -9.04         │
│ Yangon    ┆ 68     ┆ 34096.0  ┆ 35000.0 ┆ 904.0     ┆ 2.58          │
└───────────┴────────┴──────────┴─────────┴───────────┴───────────────┘
shape: (5, 6)
┌───────────┬────────┬──────────┬─────────┬───────────┬───────────────┐
│ city      ┆ orders ┆ revenue  ┆ target  ┆ shortfall ┆ shortfall_pct │
│ ---       ┆ ---    ┆ ---      ┆ ---     ┆ ---       ┆ ---           │
│ str       ┆ u32    ┆ f64      ┆ f64     ┆ f64       ┆ f64           │
╞═══════════╪════════╪══════════╪═════════╪═══════════╪═══════════════╡
│ Bago      ┆ 59     ┆ 28592.7  ┆ 30000.0 ┆ 1407.3    ┆ 4.69          │
│ Mandalay  ┆ 73     ┆ 36937.24 ┆ 35000.0 ┆ -1937.24  ┆ -5.53         │
│ Naypyitaw ┆ 51     ┆ 23733.16 ┆ 25000.0 ┆ 1266.84   ┆ 5.07          │
│ Taunggyi  ┆ 63     ┆ 32710.95 ┆ 30000.0 ┆ -2710.95  ┆ -9.04         │
│ Yangon    ┆ 68     ┆ 34096.0  ┆ 35000.0 ┆ 904.0     ┆ 2.58          │
└───────────┴────────┴──────────┴─────────┴───────────┴───────────────┘

The three implementations branch off Run Flow. The custom node and the Python Script end there; SQL Query continues into the Gate. The SQL and custom-node results are identical. Keep the SQL branch for the shipped report; the other two demonstrate alternative implementations of the same calculation.

Complete script

Show the complete runnable script
tutorial_09.py
"""Add a shortfall percentage with SQL, a custom node, or a Python Script."""

import polars as pl

import flowfile as ff
from flowfile import node_designer as nd

# Chapters 1-3: read, clean, and enrich the invoices.
SALES = "https://raw.githubusercontent.com/edwardvaneechoud/flowfile/main/data/templates/supermarket_sales.csv"
sales = ff.read_csv(SALES, description="Supermarket invoices")

valid = sales.unique().filter(flowfile_formula="[quantity] > 0 and [unit_price] > 0")
orders = valid.with_columns(flowfile_formulas=["[unit_price] * [quantity]"], output_column_names=["revenue"])

members = orders.filter(ff.col("customer_type") == "Member")
regulars = orders.filter(ff.col("customer_type") == "Normal")
combined = ff.concat([members, regulars], how="diagonal_relaxed")

targets = ff.from_dict(
    {
        "city": ["Bago", "Mandalay", "Naypyitaw", "Taunggyi", "Yangon"],
        "target": [30000.0, 35000.0, 25000.0, 30000.0, 35000.0],
    }
)
enriched = combined.join(targets, on="city", how="left")

# Chapter 8: the report comes from the reusable child flow.
incoming = ff.FlowInput("invoices", schema=enriched.schema)
child_minimum = ff.add_flow_parameter(incoming, ff.Parameter("min_quantity", default=8, type="integer"))
child_summary = (
    incoming.filter(ff.col("quantity") >= child_minimum)
    .group_by("city")
    .agg(
        ff.col("invoice_id").count().alias("orders"),
        ff.col("revenue").sum().alias("revenue"),
        ff.col("target").first().alias("target"),
    )
)
child_report = child_summary.with_columns((ff.col("target") - ff.col("revenue")).alias("shortfall")).sort("city")
child_report.to_flow_output("city_report")
report_flow = ff.register_flow(child_report, name="Tutorial city summary")

minimum = ff.add_flow_parameter(enriched, ff.Parameter("min_quantity", default=8, type="integer"))
report = ff.RunFlow(report_flow, invoices=enriched, params={"min_quantity": minimum})["city_report"]

# Chapter 9: score each city's shortfall as a percentage of its target.
scored = ff.sql("SELECT *, ROUND(100.0 * shortfall / target, 2) AS shortfall_pct FROM report", report=report)

# Chapter 6: the Gate now reads the scored report.
gate = ff.Gate(scored, ff.col("shortfall") > 0)
review = gate.then.with_columns(ff.lit("review").alias("route"))
ready = gate.otherwise.with_columns(ff.lit("ready").alias("route"))
routed = ff.concat([review, ready], how="diagonal_relaxed")


class ShortfallSettings(nd.NodeSettings):
    options: nd.Section = nd.Section(
        title="Scoring",
        target_column=nd.TextInput(label="Target column", default="target"),
    )


class ShortfallPercent(nd.CustomNodeBase):
    node_name: str = "Tutorial Shortfall Percent"
    settings_schema: ShortfallSettings = ShortfallSettings()

    def process(self, *inputs: pl.LazyFrame) -> pl.LazyFrame:
        target = self.settings_schema.options.target_column.value
        return inputs[0].with_columns((100.0 * pl.col("shortfall") / pl.col(target)).round(2).alias("shortfall_pct"))


ff.custom_nodes.install(ShortfallPercent, overwrite=True)
score = ff.custom_node(ShortfallPercent)
custom_scored = score(report, target_column="target")


my_kernels = ff.kernels.list()
kernel = my_kernels[0] if my_kernels else None


@ff.python_script(
    kernel=kernel,
    returns={
        "city": ff.String,
        "orders": ff.UInt32,
        "revenue": ff.Float64,
        "target": ff.Float64,
        "shortfall": ff.Float64,
        "shortfall_pct": ff.Float64,
    },
)
def score_in_python(city_report: pl.LazyFrame) -> pl.LazyFrame:
    return city_report.with_columns((100.0 * pl.col("shortfall") / pl.col("target")).round(2).alias("shortfall_pct"))


script_scored = score_in_python(report)

routed.flow_graph.print_tree()
print(scored.collect())
print(custom_scored.collect())

Download tutorial_09.py

Recap

The report now includes a percentage for comparing shortfalls across different targets. Next, save this pipeline, inspect it in the Designer, and run it without the UI.

Next: Ship it