Srinimf

How to Create a Generic Stored Procedure for KPI Calculation (SQL + AWS Lambda)

·

Tags: ,
How to Create a Generic Stored Procedure for KPI Calculation (SQL + AWS Lambda)

In modern data engineering, building scalable and reusable systems is essential. Writing separate SQL queries for every KPI quickly becomes messy and hard to maintain.

A better approach?
👉 Use a Generic Stored Procedure powered by Dynamic SQL, and trigger it using AWS Lambda.

In this blog, you’ll learn:

  • What a generic stored procedure is
  • Why dynamic SQL is important
  • Step-by-step KPI implementation
  • How to trigger it using AWS Lambda

What is a Generic Stored Procedure?

A generic stored procedure is a reusable database program that works dynamically based on input parameters.

Instead of writing multiple queries like:

  • Total Revenue
  • Total Orders
  • Average Order Value

You create one procedure and pass inputs like:

  • Aggregation function (SUM, COUNT, AVG)
  • Column name
  • Table name
  • Conditions

Use Case: KPI Calculation

Let’s assume we have a simple table:

sales_data (
    order_id INT,
    customer_id INT,
    amount DECIMAL,
    order_date DATE,
    region VARCHAR
)

We want to calculate:

  • Total Revenue
  • Total Orders
  • Revenue by Region

Step 1: Basic Stored Procedure (Static Approach)

CREATE OR REPLACE PROCEDURE calculate_kpi(
    kpi_name TEXT,
    start_date DATE,
    end_date DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
    result NUMERIC;
BEGIN    IF kpi_name = 'TOTAL_REVENUE' THEN
        SELECT SUM(amount)
        INTO result
        FROM sales_data
        WHERE order_date BETWEEN start_date AND end_date;    ELSIF kpi_name = 'TOTAL_ORDERS' THEN
        SELECT COUNT(*)
        INTO result
        FROM sales_data
        WHERE order_date BETWEEN start_date AND end_date;    ELSE
        RAISE EXCEPTION 'Invalid KPI';
    END IF;    RAISE NOTICE 'Result: %', result;END;
$$;

Problem ❌

  • Not scalable
  • Every new KPI requires code change
  • Hard to maintain

Step 2: Dynamic SQL (Generic Approach)

This is where the real power comes in 🚀

CREATE OR REPLACE PROCEDURE dynamic_kpi(
    agg_function TEXT,
    column_name TEXT,
    table_name TEXT,
    condition TEXT
)
LANGUAGE plpgsql
AS $$
DECLARE
    query TEXT;
    result NUMERIC;
BEGIN    query := 'SELECT ' || agg_function || '(' || column_name || ') 
              FROM ' || table_name || ' 
              WHERE ' || condition;    EXECUTE query INTO result;    RAISE NOTICE 'KPI Result: %', result;END;
$$;

Why Dynamic SQL is Used

Dynamic SQL allows queries to be built at runtime, making your procedure fully flexible.

Without Dynamic SQL ❌

  • 10 KPIs → 10 stored procedures
  • Frequent deployments
  • Duplicate logic

With Dynamic SQL ✅

  • 1 procedure → unlimited KPIs
  • No code changes needed
  • Fully reusable

Example Execution

CALL dynamic_kpi(
    'SUM',
    'amount',
    'sales_data',
    'order_date BETWEEN ''2024-01-01'' AND ''2024-12-31'''
);CALL dynamic_kpi(
    'COUNT',
    'order_id',
    'sales_data',
    'region = ''APAC'''
);

Step 3: Make It Enterprise-Ready (Metadata Driven)

Create a config table:

kpi_config (
    kpi_name TEXT,
    agg_function TEXT,
    column_name TEXT,
    table_name TEXT,
    condition TEXT
)

Now your procedure can read from this table and execute dynamically.

👉 This is how real-world data platforms work.

Step 4: Architecture with AWS Lambda

Client/API → API Gateway → Lambda → PostgreSQL → Stored Procedure

Step 5: Lambda Function (Python)

import psycopg2
import osdef lambda_handler(event, context):    conn = psycopg2.connect(
        host=os.environ['DB_HOST'],
        database=os.environ['DB_NAME'],
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD'],
        port=5432
    )    cursor = conn.cursor()    query = f"""
    CALL dynamic_kpi(
        '{event['agg_function']}',
        '{event['column_name']}',
        '{event['table_name']}',
        '{event['condition']}'
    )
    """    cursor.execute(query)
    conn.commit()    cursor.close()
    conn.close()    return {
        "statusCode": 200,
        "body": "KPI executed successfully"
    }

Step 6: Trigger Lambda

1. API Gateway (Real-Time KPI)

{
  "agg_function": "SUM",
  "column_name": "amount",
  "table_name": "sales_data",
  "condition": "order_date >= CURRENT_DATE - INTERVAL '1 day'"
}

2. Scheduler (Automated KPI)

  • Daily KPI refresh
  • Weekly reports

3. Event-Based

  • Trigger when new data lands in S3
  • Trigger after ETL pipeline

Security Best Practices ⚠️

Dynamic SQL is powerful but risky.

Avoid SQL Injection:

Use PostgreSQL format():

query := format(
    'SELECT %I(%I) FROM %I WHERE %s',
    agg_function,
    column_name,
    table_name,
    condition
);

Additional Tips:

  • Validate inputs (whitelist functions like SUM, COUNT)
  • Use AWS Secrets Manager for credentials
  • Add logging in Lambda

Real-World Enhancements

You can extend this system to:

  • Store KPI results in a table
  • Build dashboards (Power BI / Tableau)
  • Create REST APIs for KPI access
  • Integrate with Databricks or data lakes

Final Thoughts

A Generic Stored Procedure + Dynamic SQL + AWS Lambda is a powerful design pattern for modern data platforms.

It helps you:

  • Reduce duplicate SQL
  • Build scalable KPI systems
  • Enable real-time and automated analytics

👉 If you’re building production-grade pipelines, this approach is a must-have in your toolkit.

Srini's avatar

Srini

Data Engineer with deep AI and Generative AI expertise, crafting high-performance data pipelines in PySpark, Databricks, and SQL. Skilled in Python, AWS, and Linux—building scalable, cloud-native solutions for smart applications.

Start Discussion

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts

Jooble.org jobs