Tutorial: Compound interest calculator operator

In this tutorial, you create a Python UDF operator for Lakeflow Designer that calculates compound interest. Use this example to learn the fundamentals of building operators that transform individual values or columns. To learn more, see User-defined operators in Lakeflow Designer.

Overview

This tutorial steps you through creating a user-defined operator using a Python UDF. The operator computes the future value of an investment using the compound interest formula, A = P × (1 + r/n)^(n×t), where:

• P = Principal (starting amount)
• r = Annual interest rate (as decimal)
• n = Number of compounding periods per year
• t = Time in years

Step 1: Write and test the Python function

First, define the core Python function that performs the calculation. Test it in a notebook cell to make sure it works correctly.

def compound_amount(principal: float,
                    annual_rate: float,
                    compounds_per_year: int,
                    years: float) -> float:
    """
    Compute compound interest future value.

    A = P * (1 + r/n)^(n*t)

    principal: starting amount (P)
    annual_rate: annual nominal rate as decimal (r), e.g. 0.05
    compounds_per_year: compounding periods per year (n), e.g. 12
    years: time in years (t), can be fractional
    """
    import math
    if principal is None or annual_rate is None or compounds_per_year is None or years is None:
        return None

    if compounds_per_year <= 0:
        raise ValueError("compounds_per_year must be > 0")

    return principal * math.pow(1.0 + annual_rate / compounds_per_year,
                                 compounds_per_year * years)

Test the function with a sample investment: $1,000 at a 5% annual rate, compounded monthly for 10 years. The result should be approximately 1647.01.

compound_amount(1000, 0.05, 12, 10)

Step 2: Create the YAML for the operator

The YAML configuration defines how the operator appears in Lakeflow Designer. For this operator:

• Principal uses an expression widget so users can select a column from their data
• Annual rate, Compounds per year, and Years use number widgets with defaults and constraints
• The operator has one input port that provides column data for the expression parameter

schema: user-defined-operator-v0.1.0
type: uc-udf
name: Compound Amount
id: finance.compound_amount
version: '1.0.0'
description: >
  Computes the future value of an investment using compound interest.
  Formula: A = P * (1 + r/n)^(n*t)
config:
  type: object
  properties:
    principal:
      type: string
      format: expression
      title: Principal
      examples:
        - 'Select principal column or expression'
      x-ui:
        widget: expression
        port: in
    annual_rate:
      type: number
      title: Annual rate (decimal)
      default: 0.05
      minimum: 0
      examples:
        - 'e.g. 0.05 for 5%'
      x-ui:
        widget: number
    compounds_per_year:
      type: number
      title: Compounds per year
      default: 12
      minimum: 1
      examples:
        - 'e.g. 12 for monthly'
      x-ui:
        widget: number
    years:
      type: number
      title: Years
      default: 10
      minimum: 0
      examples:
        - 'Time in years (t)'
      x-ui:
        widget: number
  required:
    - principal
    - annual_rate
    - compounds_per_year
    - years
  additionalProperties: false
ports:
  input:
    - name: in
      title: Input
  output:
    - name: out
      title: Output

See User-defined operator YAML reference for a comprehensive guide to all available properties, data types, widgets, and options.

Step 3: Create the Unity Catalog function

Combine the YAML schema and Python function into a single CREATE FUNCTION statement. The YAML configuration goes in the docstring at the beginning of the function body.

The example creates the function in main.example_output. Create the schema first if it doesn't exist:

CREATE SCHEMA IF NOT EXISTS main.example_output

CREATE OR REPLACE FUNCTION main.example_output.compound_amount(
    principal DOUBLE,
    annual_rate DOUBLE,
    compounds_per_year INT,
    years FLOAT)
RETURNS DOUBLE
LANGUAGE PYTHON
AS $$
  """
  schema: user-defined-operator-v0.1.0
  type: uc-udf
  name: Compound Amount
  id: finance.compound_amount
  version: "1.0.0"
  description: >
    Computes the future value of an investment using compound interest.
    Formula: A = P * (1 + r/n)^(n*t)
  config:
    type: object
    properties:
      principal:
        type: string
        format: expression
        title: Principal
        examples:
          - "Select principal column or expression"
        x-ui:
          widget: expression
          port: in
      annual_rate:
        type: number
        title: Annual rate (decimal)
        default: 0.05
        minimum: 0
        examples:
          - "e.g. 0.05 for 5%"
        x-ui:
          widget: number
      compounds_per_year:
        type: number
        title: Compounds per year
        default: 12
        minimum: 1
        examples:
          - "e.g. 12 for monthly"
        x-ui:
          widget: number
      years:
        type: number
        title: Years
        default: 10
        minimum: 0
        examples:
          - "Time in years (t)"
        x-ui:
          widget: number
    required:
      - principal
      - annual_rate
      - compounds_per_year
      - years
    additionalProperties: false
  ports:
    input:
      - name: in
        title: Input
    output:
      - name: out
        title: Output
  """

  def compound_amount(principal: float,
                      annual_rate: float,
                      compounds_per_year: int,
                      years: float) -> float:
      import math
      if principal is None or annual_rate is None or compounds_per_year is None or years is None:
          return None

      if compounds_per_year <= 0:
          raise ValueError("compounds_per_year must be > 0")

      return principal * math.pow(1.0 + annual_rate / compounds_per_year,
                                   compounds_per_year * years)

  return compound_amount(principal, annual_rate, compounds_per_year, years)
$$

Step 4: Test the function

Test the UC function directly with SQL. These queries check three scenarios:

• $1,000 at 5% compounded monthly for 10 years, which returns approximately 1647.01
• $1,000 at 5% compounded annually for 1 year, which returns 1050.00
• $1,000 at 15% compounded monthly for 1 year, which returns approximately 1160.75

SELECT main.example_output.compound_amount(1000, 0.05, 12, 10);
SELECT main.example_output.compound_amount(1000, 0.05, 1, 1);
SELECT main.example_output.compound_amount(1000, 0.15, 12, 1);

Step 5: Register the operator

Add the operator to your .user_defined_operators.yaml file:

operators:
  - catalog: main
    schema: example_output
    functionName: compound_amount

Step 6: Set up permissions

Grant access to users who need to use this operator:

GRANT USE SCHEMA ON SCHEMA main.example_output TO `<user>`;
GRANT EXECUTE ON FUNCTION main.example_output.compound_amount TO `<user>`;

Use the operator in Lakeflow Designer

After it's registered, the operator appears in Lakeflow Designer with:

• A dropdown to select the principal column from your input data
• Number inputs for rate, compounding frequency, and years (with sensible defaults)

Users can apply this operator to calculate future values for entire columns of investment data.