AWS Builder Center

Multi-Dialect SQL to One Python Rules Engine

Three SQL dialects, one discount logic. AWS Transform unifies PL/pgSQL, T-SQL, and PL/SQL into a single Python rules engine with 28 tests green.

Three stored procedures in three SQL dialects, all implementing the same discount logic. One ATX session. A single Python rules engine with 28 passing tests, a clean class interface, and zero database dependencies. Here's how to eliminate multi-dialect maintenance forever.



The Problem: Same Logic, Three Maintenance Burdens

Here's a scenario every enterprise database team recognizes: you have business logic (discount calculations, pricing rules, eligibility checks) implemented as stored procedures. But because your organization runs Oracle, SQL Server, AND PostgreSQL (acquisition history, multi-cloud strategy, team preferences), that same logic exists in three different SQL dialects.
Every time the business changes a rule ("add a new loyalty tier", "change the volume threshold"), you make the change three times. In three different syntaxes. With three different testing approaches. And hope they stay in sync.
This isn't hypothetical. According to a 2025 Percona survey, 67% of enterprises run two or more database engines in production [1]. The maintenance cost of keeping business logic synchronized across dialects is a hidden tax that compounds every quarter.
The fix isn't migrating to one database. It's extracting the logic from the database layer entirely into a single, testable, portable module that any application can call regardless of which database it uses.
AWS Transform Custom can do this extraction automatically: reading stored procedures in multiple SQL dialects, identifying the common business rules, and producing a unified Python module with comprehensive tests.

The Starting Point: Same Logic, Three Syntaxes

The discount calculation exists in:
  • Oracle PL/SQL ( plsql/calculate_discount.sql )
  • SQL Server T-SQL ( tsql/calculate_discount.sql )
  • PostgreSQL PL/pgSQL ( pgsql/calculate_discount.sql )
All three implement identical business rules:
  • Tier discounts: GOLD = 15%, SILVER = 10%, BRONZE = 5%
  • Volume discounts: Order > $1000 = 10%, Order > $500 = 5%
  • Promotional: Black Friday = 20%
  • Stacking: All discounts are additive
  • Cap: Maximum combined discount = 40%
The difference is syntax. Oracle uses  NVL ,  SYSDATE ,  DBMS_OUTPUT . T-SQL uses  ISNULL ,  GETDATE() ,  PRINT . PostgreSQL uses  COALESCE ,  NOW() ,  RAISE NOTICE . Three ways to say the same thing.

The Transformation Flow

ATX Multi-Dialect SQL to Python Rules Engine


Running the Transformation

cd sql-discount-rules
git init && git add . && git commit -m "initial: discount rules in 3 SQL dialects"

aws configure # configure AWS credentials (or use env vars / a profile)
atx
I describe the goal:
> Convert these stored procedures (written in 3 SQL dialects: Oracle PL/SQL,
T-SQL, and PostgreSQL PL/pgSQL) into a unified Python rules engine. All 3
implement the same discount calculation business logic. Extract the business
rules into a single Python module with a clean interface: a DiscountCalculator
class with a calculate(order_total, customer_tier, is_black_friday) method.
Include unit tests. Validation command: python3 -m pytest tests/ -v
ATX finds related TDs (PL-SQL-to-Python-FastAPI, SQL-to-PySpark, T-SQL-to-CSharp) but none that handle multi-dialect consolidation into a rules engine. It creates a custom TD.
ATX asks intelligent clarifying questions:
  1. Interface: Should I match the existing SQL parameters ( order_total ,  customer_tier ,  is_black_friday ) or add new ones?
  1. DB dependency: Pure logic (tier as string param) or include a data access layer?
  1. Output structure: Just discount percentage, or both percentage and final total?
  1. Python version: 3.10+ with modern features?
  1. Test coverage: Happy path only, or comprehensive with edge cases?
  1. File structure: Flat or pip-installable package?
I accept ATX's defaults (which are the right choices) and it proceeds.

What ATX Produced

A complete Python package:
src/
├── __init__.py Exports DiscountCalculator, DiscountResult, CustomerTier
└── discount_calculator.py The rules engine
tests/
├── __init__.py
└── test_discount_calculator.py 28 comprehensive tests
pyproject.toml pip-installable, pytest-configured
archive/sql/ Original SQL files preserved for reference
├── plsql/calculate_discount.sql
├── tsql/calculate_discount.sql
└── pgsql/calculate_discount.sql

The Rules Engine

"""Discount calculation rules engine.

Consolidates business rules from Oracle PL/SQL, T-SQL, and PostgreSQL PL/pgSQL
stored procedures into a single Python module.
"""

import dataclasses
import enum

class CustomerTier(enum.Enum):
GOLD = "GOLD"
SILVER = "SILVER"
BRONZE = "BRONZE"
STANDARD = "STANDARD"

@dataclasses.dataclass
class DiscountResult:
discount_pct: float
final_total: float

class DiscountCalculator:
"""Calculates discounts based on customer tier, order volume, and promotions."""

TIER_DISCOUNTS = {
CustomerTier.GOLD: 15.0,
CustomerTier.SILVER: 10.0,
CustomerTier.BRONZE: 5.0,
CustomerTier.STANDARD: 0.0,
}

VOLUME_THRESHOLDS = [
(1000.0, 10.0),
(500.0, 5.0),
]

PROMO_BLACK_FRIDAY = 20.0
MAX_DISCOUNT = 40.0

def calculate(
self,
order_total: float,
customer_tier: str,
is_black_friday: bool = False,
) -> DiscountResult:
if order_total <= 0:
return DiscountResult(discount_pct=0.0, final_total=order_total)

tier = self._resolve_tier(customer_tier)
base_discount = self.TIER_DISCOUNTS[tier]

volume_discount = 0.0
for threshold, discount in self.VOLUME_THRESHOLDS:
if order_total > threshold:
volume_discount = discount
break

promo_discount = self.PROMO_BLACK_FRIDAY if is_black_friday else 0.0

total_discount = base_discount + volume_discount + promo_discount
if total_discount > self.MAX_DISCOUNT:
total_discount = self.MAX_DISCOUNT

final_total = order_total * (1 - total_discount / 100.0)
return DiscountResult(discount_pct=total_discount, final_total=final_total)

def _resolve_tier(self, customer_tier: str) -> CustomerTier:
if not customer_tier:
return CustomerTier.STANDARD
try:
return CustomerTier(customer_tier.upper())
except ValueError:
return CustomerTier.STANDARD
Clean, readable, no database dependency. The rules are explicit constants. When the business changes a threshold, you change one number in one place.

Test Coverage: 28 Tests

ATX generated comprehensive tests covering every scenario:
tests/test_discount_calculator.py::TestTierDiscounts::test_gold_tier PASSED
tests/test_discount_calculator.py::TestTierDiscounts::test_silver_tier PASSED
tests/test_discount_calculator.py::TestTierDiscounts::test_bronze_tier PASSED
tests/test_discount_calculator.py::TestTierDiscounts::test_unknown_tier PASSED
tests/test_discount_calculator.py::TestTierDiscounts::test_empty_tier PASSED
tests/test_discount_calculator.py::TestTierDiscounts::test_none_tier PASSED
tests/test_discount_calculator.py::TestTierDiscounts::test_case_insensitive_lower PASSED
tests/test_discount_calculator.py::TestTierDiscounts::test_case_insensitive_mixed PASSED
tests/test_discount_calculator.py::TestVolumeDiscounts::test_above_1000 PASSED
tests/test_discount_calculator.py::TestVolumeDiscounts::test_above_500_below_1000 PASSED
tests/test_discount_calculator.py::TestVolumeDiscounts::test_below_500 PASSED
tests/test_discount_calculator.py::TestBoundaryConditions::test_exactly_500 PASSED
tests/test_discount_calculator.py::TestBoundaryConditions::test_exactly_1000 PASSED
tests/test_discount_calculator.py::TestBoundaryConditions::test_just_above_1000 PASSED
tests/test_discount_calculator.py::TestBoundaryConditions::test_just_above_500 PASSED
tests/test_discount_calculator.py::TestPromotionalDiscounts::test_black_friday PASSED
tests/test_discount_calculator.py::TestPromotionalDiscounts::test_no_promo PASSED
tests/test_discount_calculator.py::TestDiscountStacking::test_gold_plus_volume PASSED
tests/test_discount_calculator.py::TestDiscountStacking::test_silver_plus_black_friday PASSED
tests/test_discount_calculator.py::TestDiscountStacking::test_bronze_plus_volume_plus_black_friday PASSED
tests/test_discount_calculator.py::TestDiscountCap::test_gold_volume_black_friday_capped PASSED
tests/test_discount_calculator.py::TestDiscountCap::test_silver_volume_black_friday_capped PASSED
tests/test_discount_calculator.py::TestEdgeCases::test_zero_order_total PASSED
tests/test_discount_calculator.py::TestEdgeCases::test_negative_order_total PASSED
tests/test_discount_calculator.py::TestFinalTotalCalculation::test_simple_discount PASSED
tests/test_discount_calculator.py::TestFinalTotalCalculation::test_stacked_discount PASSED
tests/test_discount_calculator.py::TestFinalTotalCalculation::test_capped_discount PASSED
tests/test_discount_calculator.py::TestFinalTotalCalculation::test_no_discount PASSED

28 passed in 0.12s
Categories: tier discounts (8), volume discounts (3), boundary conditions (4), promotions (2), stacking (3), cap (2), edge cases (2), final total (4).

Cross-Check: Python vs. Original SQL

I verified the Python output matches what the SQL would produce:
Inputs
Expected (SQL)
Python Result
Status
$1500, GOLD, Black Friday
40% (capped), $900
40%, $900
✅ MATCH
$750, SILVER, no promo
15%, $637.50
15%, $637.50
✅ MATCH
$300, BRONZE, no promo
5%, $285
5%, $285
✅ MATCH
$100, UNKNOWN, no promo
0%, $100
0%, $100
✅ MATCH
Business logic 100% preserved.

What You Gain

Architecture: Before (logic in DB) vs After (logic in code)

One place to change rules. When the business says "add a PLATINUM tier at 20%," you add one line to  TIER_DISCOUNTS . Not three lines in three dialects in three databases.
Testability. 28 tests run in 0.12 seconds. No database required. No test fixtures to set up. No data to seed. Pure logic, pure tests.
Portability. This module runs anywhere Python runs: in a Lambda, in a container, in a microservice, in a CLI tool. No database connection needed.
Auditability. The rules are explicit constants, not buried in procedural SQL. Auditors can read  MAX_DISCOUNT = 40.0  and understand the cap immediately.
No more drift. The original SQL implementations can and do drift apart over time (someone fixes a bug in Oracle but forgets SQL Server). One implementation means one truth.

Real-World Applications

This pattern extends beyond discount calculations. Any stored procedure that implements business rules is a candidate for extraction:
Pricing engines. E-commerce systems often have pricing logic in stored procedures that consider customer segment, volume, promotional periods, and geographic region. These calculations frequently exist in multiple databases (one for the web app, one for the mobile backend, one for the analytics pipeline). Extracting to a single Python module means one pricing truth across all channels.
Eligibility checks. Insurance underwriting rules, loan approval criteria, benefits eligibility logic. These are often implemented as complex stored procedures with dozens of conditional branches. A Python rules engine makes them testable, versionable, and deployable independently from the database.
Tax computations. Tax rules change frequently and vary by jurisdiction. When the logic lives in stored procedures across multiple databases, keeping them synchronized during tax season is a multi-week project. A single Python module with comprehensive tests reduces that to a code review.
Compliance rules. Regulatory logic (KYC checks, trade compliance, data residency rules) is often duplicated across systems that grew independently. Consolidating into a rules engine creates a single auditable source of truth.
The common thread: business logic that should be in your application layer, not your data layer. Database stored procedures made sense when application servers were expensive and network round-trips were costly. In a world of microservices, containers, and serverless functions, the calculus has changed. The logic belongs where it can be tested, versioned, and deployed independently.
ATX handles the extraction regardless of which SQL dialect the logic is written in. Oracle, SQL Server, PostgreSQL, or all three simultaneously. The output is always a clean Python module with tests.

Scaling This

Publish the TD

atx custom def publish \
--transformation-name "sql-stored-procs-to-python-rules-engine" \
--description "Convert multi-dialect SQL stored procedures to unified Python rules engine" \
--sd .

Apply to Other Business Logic

This pattern works for any stored procedure that implements business rules:
  • Pricing calculations
  • Eligibility checks
  • Tax computations
  • Fee calculations
  • Compliance rules
Any logic that's trapped in the database and duplicated across dialects is a candidate.

Continual Learning

When this transformation definition runs across multiple repositories with different rule patterns, ATX's lessons capture the variations it encounters. One codebase might implement discount tiers using  CASE  statements instead of  IF/ELSIF  blocks. Another might use table-valued functions to return result sets instead of scalar returns with  OUT  parameters. A third might combine discount logic with eligibility checks in a single procedure, requiring decomposition into separate modules.
Each of these variations becomes a learned pattern. After ATX processes a repository that uses  CASE -based branching for tier resolution, subsequent runs recognize that construct and handle it correctly on the first pass. Similarly, once ATX encounters a table-valued function that returns a discount schedule (rather than computing a single discount), it learns to produce an equivalent Python generator or list-returning method. The lessons accumulate: "PL/SQL procedures that mix discount and eligibility logic should be split into separate classes," or "T-SQL procedures using temp tables for intermediate results map to dataclass collections in Python."
This means the transformation improves with organizational usage. The first team to run it against their stored procedures might encounter one or two patterns that need a feedback cycle. The fifth team benefits from all prior corrections and typically gets a clean extraction on the first pass. Over time, the TD evolves from handling the common 80% of patterns to covering 95%+ of the SQL-to-rules-engine conversion space your organization uses.
Cost: In one example run this took a few agent minutes at $0.035/agent-minute, on the order of ~$0.15. Agent-minutes are non-deterministic and vary per run and codebase, so treat this as a ballpark and run a pilot to establish your own baseline. Either way it's negligible next to the ongoing cost of maintaining three SQL implementations indefinitely.

Getting Started

# Install ATX (requires Node.js 22+)
curl -fsSL https://transform-cli.awsstatic.com/install.sh | bash

# Authenticate (configure AWS credentials)
aws configure # or set AWS_ACCESS_KEY_ID / AWS_SECRET_ACCESS_KEY / AWS_REGION

# Run against your SQL procedures
cd your-stored-procedures
atx

# Describe the consolidation...
Move your business logic out of the database. Let ATX handle the extraction. Maintain one implementation instead of three.



Resources

References

[1] Percona, "State of Open Source Databases Report 2025." Content rephrased for compliance.  https://www.percona.com/open-source-data-management-software-survey 
[2] AWS, "AWS Transform pricing."  https://aws.amazon.com/transform/pricing/ 


Disclaimer: Opinions expressed are personal and do not represent the official position of any organization. All claims about ATX capabilities are verified against the  official documentation .
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article