data_modeling_standards

Data Modeling Standards and Business Rules

This skill serves as the single source of truth for organizational standards, naming conventions, and repeating data modeling patterns within the Cortex Framework. All agents creating or modifying data products MUST consult this guide to ensure consistency, design integrity, and compliance across the workspace.

---

1. Column Naming Conventions (Traceability)

In Cortex, column names must provide both business readability and technical traceability back to the source system.

• Rule: Target columns MUST be named in lowercase snake_case.
• Traceability Suffix: Column names MUST end with the original SAP field name as a suffix (e.g., _<sap_field_name>).
• Examples:
  • mandt (Client) -> client_mandt
  • bukrs (Company Code) -> company_code_bukrs
  • belnr (Accounting Document Number) -> document_number_belnr
  • waers (Currency Key) -> currency_key_waers

---

2. Currency Decimal Shifts

SAP stores currency amounts as integer/decimal values without decimals, shifting the actual values depending on the specific currency key (e.g., Japanese Yen JPY has 0 decimals, whereas USD has 2). We must adjust these amounts using the standard currency decimal shift configuration.

Implementation Checklist

1. Require the Currency helper:

   const currency = require("includes/cortex/currency.js");
   

2. Define the currency_decimal CTE at the top of the SQL query, passing the dataset reference for the replicated tcurx table:

   WITH currency_decimal AS (
     ${currency.currencyDecimalShift(ctx.ref(moduleConfig.sources.sapModule.datasetId, "tcurx"))}
   )
   

3. Apply the decimal shift expression to amount fields in the SELECT block:

   ${currency.amountWithDecimalShift("bseg.dmbtr", "currency_decimal_hwaer")} AS amount_in_local_currency_dmbtr,
   ${currency.amountWithDecimalShift("bseg.wrbtr", "currency_decimal_waers")} AS amount_in_document_currency_wrbtr,
   

4. Join the currency_decimal CTE in the FROM / JOIN section for each currency key:

   LEFT JOIN currency_decimal AS currency_decimal_waers
     ON bkpf.waers = currency_decimal_waers.currkey
   LEFT JOIN currency_decimal AS currency_decimal_hwaer
     ON bkpf.hwaer = currency_decimal_hwaer.currkey