Modular Query Design with CTEs

Break the query into logical and modular steps using Common Table Expressions (CTEs).
Each CTE should handle a specific and clearly-defined task, such as:

• filtering,
• transformation,
• deduplication, or
• combining datasets.

This ensures that the logic is easy to follow and modular, allowing for easier troubleshooting, debugging, and future upgrades.
In addition, follow these structured steps to design queries:

1. Preparation CTEs: Filter data from source tables and apply point-in-time logic (e.g. SCD2 logic with valid_from and valid_to columns).
2. Transformation CTEs: Perform joins, unions, aggregations, or other transformations.
3. Final Query: Select the required fields and apply any final transformations or calculations.

Best Practices

Logical Segmentation

• Each CTE should perform a single, well-defined operation (e.g. filtering records, applying transformations, deduplication, etc)
• Separate Preparation, Transformation, and Final Query steps to maintain clarity and structure.
• Avoid mixing multiple transformations or unrelated operations in a single CTE.

Descriptive Naming

• Use descriptive and self-explanatory names for CTEs to indicate their purpose (e.g. filtered_triggers, transformed_data, deduplicated_records).
• Good naming conventions improve readability, maintainability, and debugging.

Simplify Complex Logic

• Break down complex logic into smaller, manageable CTEs. Use strep-by-step instructions to make the query more readable and maintainable.
• Avoid overly complex CTEs or combining too many operations in a single step.

Reuse CTEs

• Reuse intermediate CTE’s instead of duplicating logic in multiple downstream steps.
• Centralize filtering and other preparation steps in a single CTE to minimize redundancy.

Combine Related Transformations

• If transformations are related, group them into a single CTE to simplify the query and reduce the number of intermediate steps.