How we decide whether a transformation belongs in SQLX, code, or orchestration

Put logic in the layer that owns the decision it expresses.

SQLX owns relational meaning. Code owns algorithms and external interaction. Orchestration owns when and under what operational conditions work runs.

Most confusion comes from choosing the layer by convenience during the first implementation. The query became awkward, so part of the business rule moved into a helper. The helper needed a date, so the scheduler started choosing the date. Six months later, understanding one table requires reading all three.

SQLX owns the data contract

Keep logic in SQLX when it defines what the output means:

• grain and row identity
• joins between analytical entities
• business filters and classifications
• measures and dimensional attributes
• deduplication and latest-state rules
• incremental affected sets and merge behavior
• assertions about the resulting data

SQL is designed to describe sets and relationships. A named Dataform model also exposes dependencies, materialization, and assertions around that description. When the work can be stated clearly in that form, moving it elsewhere makes the contract harder to find.

A complex query isn’t automatically a reason to leave SQL. First improve its shape with staged models, named common table expressions, explicit gates, or a separate reusable model. Build a graph whose nodes have clear responsibilities instead of one heroic file.

Use code when the problem stops being relational

Use code when it materially clarifies work such as:

• parsing formats SQL handles badly
• complex text, graph, statistical, or cryptographic algorithms
• calling external APIs or handling files
• generating repetitive declarations from controlled metadata
• interacting with libraries whose behavior is the actual implementation

The code should expose a narrow input and output contract back to the modeling layer. If Python computes a classification, write the classification and its version into a table that SQLX can consume. Don’t leave a hidden service call inside the middle of a transformation path with no reproducible output.

Code isn’t a refuge for business logic somebody found tedious to express in SQL. If the helper determines what revenue, active customer, or eligible order means, reviewers must be able to see and test that semantic decision as clearly as they would in a model.

Generated SQL deserves the same review as handwritten SQL. A macro that expands into two hundred lines hasn’t removed complexity. It’s changed where the complexity hides.

Orchestration owns coordination

Orchestration should decide:

• schedules and event triggers
• dependency execution across systems
• retries and timeout policy
• concurrency and mutual exclusion
• credentials and runtime placement
• parameterized backfill windows
• notification and incident paths
• external side-effect ordering

It shouldn’t decide which customers qualify, how a metric is calculated, which source wins a deduplication conflict, or what one output row represents.

A workflow can pass start_date and end_date to the same model logic. It shouldn’t choose a different business definition for manual runs. A retry can rerun an idempotent model. It shouldn’t switch to a looser cleanup path because the first attempt failed.

The graph should explain operational flow. The model graph should explain data meaning.

Use the review-location test

Ask where a competent reviewer would naturally look for the decision.

If they are reviewing the definition of gross margin, they should find it in the analytical model. If they are reviewing a PDF parser, they should find code and tests. If they are reviewing why a backfill can’t overlap the daily run, they should find orchestration policy.

When the answer is “read all three and remember which one wins,” the boundary has failed.

Keep dependencies flowing in one direction

A clean shape looks like this:

orchestration invokes
  code at external or algorithmic boundaries
  declarative model graph for relational transformations

code produces explicit data or metadata contracts
models consume them and define analytical meaning

Avoid helpers that call the orchestrator, models that depend on runtime side effects nobody declares, or workflows that rewrite SQL text to alter semantics. Cyclic ownership makes local changes impossible to reason about.

Accept duplication before hiding meaning

Two readable SQL expressions can be cheaper than one generic macro with six parameters and conditional behavior. Repeated orchestration configuration can be cheaper than a framework that obscures the actual retry policy. Abstraction should follow a stable shared concept, not discomfort with seeing similar lines.

The same standard applies to reviewability: fewer lines are irrelevant when a reviewer must carry more hidden state.

The default and its exceptions

Start in SQLX for warehouse transformations. Move a bounded part to code when the non-relational mechanism becomes clearer and testable there. Put only coordination in orchestration.

Break the default when another layer can express the behavior more honestly while preserving an explicit contract. Never break it merely because the current layer is inconvenient.

The right placement lets one reviewer find the decision, understand its blast radius, and change it without reconstructing the platform’s history.