From Process Logic to Query Logic: A Practical Guide to Decision Placement in Informatica

When teams build integrations in Informatica Cloud Application Integration (CAI) or Cloud Data Integration (CDI), the same design question keeps coming up: where should a business rule actually live? Pulling raw columns out of a database and combining them inside a Decision step is tempting, because the process canvas is right there and the logic feels close at hand. Over time, though, that habit spreads the same rule across several processes and hides it from anyone reading the diagram. It also makes every change slower and riskier.

This guide covers a simple, practical alternative: compute the rule in the query, return one derived flag, and let the orchestration layer react to the answer. It also explains when this approach works and when it does not, and ends with a short checklist to help you decide.

The Problem in Brief

A common shape in CAI (or any orchestration-plus-database flow) is a Decision step that inspects several fields pulled back from a database call and branches on a combination of them: is the stock quantity above zero, is the contract still active, does the depot code match, is the price flag set. Every one of those conditions is legitimate business logic. The mistake is where it lives.

When the combination is built out of raw columns inside the orchestration layer, the rule is duplicated the moment it needs to be checked from a second place, and it is invisible to anyone reading the process diagram — they see five field names, not the rule connecting them.

The fix is not to simplify the rule. It is to move it to where the data already sits — the query — and have it return one derived flag instead of the five raw values that make up the rule. The orchestration layer then switches on that single field. This also quietly avoids a platform’s weaker expression handling, since most integration-tool expression editors are far less capable than SQL’s CASE/boolean logic.

Background: Why This Argument Keeps Coming Up

Where a business rule should live is one of the oldest disputes in application architecture. It goes back to the classic three-tier split of presentation, application/business logic, and persistence. One long-standing school of thought holds that the database is for persistence only. It keeps business logic in the application or middle tier, so the logic stays portable across database vendors and is easier to version alongside the rest of the codebase.

The opposing, equally long-standing school argues that a rule which only needs data the database already holds should be evaluated right there, as a view, a stored procedure, or a computed column. That way the rule is enforced consistently no matter which application, report, or ad-hoc script touches that data.

Neither side is simply wrong; the disagreement is really about which failure mode a team is more afraid of. Keeping every rule in the application layer avoids vendor lock-in and keeps logic in a language with proper testing and version control. But it also means every caller of the database (a second service, a nightly job, an analyst’s query) must reimplement the rule correctly and stay in sync as it changes. Keeping the rule in the database avoids exactly that duplication. The cost is logic that is harder to unit-test in isolation and, in tightly coupled systems, harder to move if the database platform ever changes.

Orchestration platforms (BPM engines, iPaaS tools, and integration tools like CAI) sit in an unusual middle position in this debate. As vendors in this space describe it, their whole value is coordinating many independent systems and services into one visible, end-to-end sequence, with built-in visibility into what happened and where something failed. That value depends on the orchestration diagram staying readable as a map of what happens and in what order, not as a second copy of the business rules that decide what happens next.

The moment a Decision step’s expression editor becomes the real home of a multi-field eligibility rule, the orchestration layer stops doing the one thing it is actually good at. It starts silently competing with the database for a job the database already does better.

The Naive Version: Three Conditions in the Process

A Parts Release check is a fair example. The process calls a DAS Connector action that runs a SELECT against stock and contract tables and gets back stockQty, contractActive, and depotMatch. A Decision step in the process itself then combines them:

IF (stockQty > 0) AND (contractActive == "Y") AND (depotMatch == "Y")
    -> APPROVED
ELSE IF (stockQty > 0) AND (contractActive == "N")
    -> CHARGEABLE
ELSE
    -> REFER_ELSEWHERE

This works, but the actual business rule (what counts as “available for release”) now exists only as a sequence of AND/OR blocks buried inside a Decision step’s expression editor. Anyone who needs to know the rule has to open the process and read the branch logic field by field.

A second process (say, a nightly batch reconciliation) may need to apply the same eligibility rule. If it does, someone has to retype that logic by hand into a second Decision step, and the two copies will drift the first time one of them is tuned and the other is forgotten.

Why This Is Worse Than It Looks

The three-field version is not just harder to read; it is also harder to change safely. Adding a fourth condition (say, a regional restriction) means opening the expression editor and editing a nested boolean expression by hand. That editing happens inside a small text box with limited syntax highlighting and no linting. You then have to re-test the whole process end to end, because the platform has no way to test the branch condition in isolation from the process that contains it.

One Derived Flag Instead

The same three raw columns are still selected if they are needed for display, but the query itself also computes the outcome and returns it as a single column:

SELECT
    p.part_id,
    p.stock_qty,
    c.contract_active,
    c.depot_match,
    CASE
        WHEN p.stock_qty > 0
             AND c.contract_active = 'Y'
             AND c.depot_match = 'Y' THEN 'APPROVED'
        WHEN p.stock_qty > 0
             AND c.contract_active = 'N' THEN 'CHARGEABLE'
        ELSE 'REFER_ELSEWHERE'
    END AS release_outcome
FROM parts p
JOIN contracts c ON c.part_id = p.part_id

The process’s Decision step shrinks to a single switch on release_outcome. There is exactly one place where “available for release” is defined. The process diagram now reads as a map of what happens for each outcome, not as a re-derivation of what the outcome even means.

What Actually Changed

• The rule is now a first-class, nameable object (a CASE expression, or better, a view) instead of an anonymous blob inside a Decision step.
• The rule can be tested with a plain SELECT against real or seeded data, independent of running the whole orchestration process.
• The rule can be reused by anything that can run SQL (a report, a second process, an ad-hoc audit query) without copying orchestration logic.
• SQL’s CASE/boolean expressions support more operators, better null handling, and clearer operator precedence than most integration-tool expression editors.

Where the Rule Now Lives, and Why That Is Easier to Change

The rule used to live in an expression editor with limited operators, no version history worth mentioning, and no way to unit-test it in isolation. Now it lives in a SQL CASE statement. You can run it directly against the database and test it with a plain SELECT. A report or a second process can reuse it without copying any orchestration logic. You change it in one file, instead of hunting through every process that happens to branch on the same fields.

In practice, this also means a rule change (say, adding a fourth eligibility condition) is a one-line edit to the view or query, followed by re-testing the SELECT. It is not a re-wire of a Decision step’s branches, retested by re-running the whole process end to end.

A Further Step: Push the CASE Into a View

Once the rule is proven as a CASE expression inline in one query, the natural next step is to wrap it in a view. Where full auditability and history matter, you can use a materialized or persisted business ruleset instead. This has long been established as good database practice.

A view keeps the rule queryable by its own name, separate from any one process’s SQL. Every future caller can simply select from a name like v_parts_release_eligibility instead of retyping the CASE statement, including callers nobody anticipated when the rule was first written. The ruleset becomes a small, testable, database-native asset, separate from both the raw tables underneath it and the orchestration process above it. That layering is exactly what keeps a rule from drifting into two disagreeing copies.

When This Is the Wrong Call

Pushing every condition into SQL is not free, and it is not always correct. The rule belongs in the process, not the query, when the condition depends on something the database does not know. Examples include a value from the inbound REST payload, a response from a separate service call made earlier in the same process, or a runtime check like a feature flag or a business-hours window.

It is also the wrong call when the branch is genuinely about orchestration rather than business rules. Deciding which of two downstream systems to call next is a process concern, even if a database column happens to drive the choice. Finally, it is the wrong call when the query would need to reach into systems it should not touch, just to compute a flag the process could derive in two lines from data it already has.

There is also a portability cost worth naming honestly. A CASE expression tied to one vendor’s SQL dialect, or a stored procedure using vendor-specific syntax, is harder to move if the organization ever changes database platforms. This is precisely the objection the application-layer school of thought raises, and it is a real cost, not an imagined one.

The right response is not to avoid the database for every rule. It is to keep the SQL simple and portable for the class of rule this article is about: a small, self-contained decision computable entirely from one query’s own columns. In practice, that means standard CASE/boolean expressions and, where possible, no vendor-specific procedural extensions.

A Short Checklist Before Moving a Rule Into SQL

• Does the rule only use columns already returned by one query? If yes, it is a strong candidate to move.
• Will a second process, report, or job need the exact same rule? If yes, moving it removes a future duplication risk.
• Does the rule need a value that only exists at runtime (a payload field, a prior service response, or a feature flag)? If yes, keep it in the process.
• Is the branch actually about which system to call next, not about a data-derived business state? If yes, keep it in the process.
• Would the SQL need vendor-specific syntax that breaks portability for a rule this small? If yes, weigh that cost before moving it.

Three Examples of the Same Pattern Across Different Flows

The same shift, from several raw fields checked in the process to one derived flag returned by the query, repeats across very different kinds of flows. The three examples below come from three different interaction patterns: a synchronous request/reply lookup, a nightly batch reconciliation, and an asynchronous poller with its own pending-state table.

What the Three Examples Have in Common

Each flow has a different trigger and a different shape of state: a synchronous call with no memory, a nightly batch job writing a run table, and a timer-driven poller reading a pending table. But in every case, the underlying data was already sitting in one query result before the process ever touched it.

The rule in each case reduces to a single expression over fields the database already has: date arithmetic in the poller, a tolerance comparison in the reconciliation, a boolean combination in the release check. None of the three actually needed the orchestration layer’s expression engine at all; each only needed the orchestration layer to react to an answer.

How Ksolves Can Help

Ksolves provides end-to-end Informatica consulting and implementation services, including Cloud Application Integration (CAI) and Cloud Data Integration (CDI). Our team helps organizations review existing orchestration processes and find business rules that are duplicated or buried inside Decision steps. Where it makes sense, we move those rules into clean, reusable, testable SQL logic and views.

We also design new integration flows with the right balance between process logic and query logic from day one. As a result, your processes stay readable, your rules stay consistent, and future changes become far quicker. Ksolves can help with untangling a complex CAI process, building a reconciliation pipeline in CDI, or planning a broader integration modernization. Get in touch with our Informatica experts to discuss your project.

Wrapping Up

The shape repeats because the underlying cause repeats: a database is a better place than an orchestration canvas to hold a rule that only needs the data the database already has. This is not an argument for moving every rule into the database. Runtime-only conditions, cross-system routing decisions, and anything the query genuinely cannot see still belong in the process. Vendor lock-in is also a real cost worth weighing before writing complex procedural SQL.

The habit this article recommends is narrower and safer. Sometimes a Decision step’s branch condition is built entirely out of columns that already came back from the same query. When that happens, derive the answer where the data lives, return one flag, and let the process do what it is actually good at. That means deciding what happens next, not re-deriving what already happened.