Phase 05 — Data Preparation & Transformation
Architectural objective: turn acquired source data into trustworthy, reusable analytical datasets without hiding grain changes, introducing duplicate rows, destroying delta semantics, or creating unnecessary processing cost.
Phase 04 answered:
How do we acquire the data?
Phase 05 answers:
How do we make the acquired data analytically usable?
This distinction is fundamental.
Acquisition is primarily about bringing data across a boundary.
Preparation and transformation are about changing the shape, quality, meaning, and usability of that data.
1. Why Data Preparation Is an Architecture Discipline
A transformation that returns the correct rows today can still be architecturally wrong.
Examples:
- A join duplicates every purchase order item because the lookup table is not unique.
- A filter removes cancelled transactions without documenting the business rule.
- An aggregation destroys the transaction grain before downstream consumers need it.
- A calculated amount uses the wrong currency.
- A transformation converts a reliable delta pipeline into a full reload pattern.
- A Python step is introduced where a pushdown SQL operation would be simpler and faster.
- A view is persisted unnecessarily, creating storage and refresh cost.
- A view is kept virtual even though repeated complex execution causes unacceptable runtime.
The architect therefore asks five questions for every transformation:
- What is the input grain?
- What is the output grain?
- What business rule is being applied?
- Where should the computation execute?
- What operational consequence does the transformation create?
2. The Preparation Mental Model
A robust preparation architecture normally separates:
Raw / Acquired Data
|
v
Technical Standardization
|
v
Cleansing + Validation
|
v
Harmonization
|
v
Business Transformation
|
v
Curated Analytical Dataset
|
v
Semantic Modeling
These are logical layers, not mandatory physical tables.
Do not create five persisted tables simply because the diagram has five boxes.
The correct physical design depends on:
- reuse;
- data volume;
- transformation complexity;
- freshness;
- performance;
- lineage;
- recovery;
- cost;
- source-system protection;
- downstream workload.
3. Transformation vs Modeling
This distinction prevents many Datasphere designs from becoming confusing.
Transformation
Transformation changes the dataset.
Examples:
- filtering rows;
- joining datasets;
- unioning datasets;
- deriving columns;
- standardizing values;
- aggregating;
- cleansing;
- mapping codes;
- enriching records.
Modeling
Modeling describes how consumers should understand and query the data.
Examples:
- dimensions;
- measures;
- associations;
- semantic relationships;
- hierarchies;
- analytical models.
A graphical view can participate in both transformation and modeling, but the architectural intent must remain explicit.
A useful rule is:
Transform to make data correct and reusable. Model to make data understandable and consumable.
4. Datasphere Transformation Options
SAP Datasphere provides several mechanisms for preparing and transforming data.
| Mechanism | Primary role | Typical output |
|---|---|---|
| Data Flow | Move and transform data graphically | Local table |
| Transformation Flow | Load and transform data into a local target table | Local table |
| Graphical View | Virtual graphical transformation/modeling | View |
| SQL View | SQL-based virtual transformation/modeling | View |
| SQL / SQLScript-capable mechanisms | Advanced SQL logic where supported | Depends on object |
| Python operator in supported Data Flow scenarios | Specialized procedural transformation | Data Flow output |
Current SAP documentation describes Data Flows as graphical pipelines supporting operations such as joins, unions, projections, aggregations, and Python scripting. Transformation Flows can use graphical or SQL view transforms and load the result into a local table.
Do not interpret this as “always use Data Flow.”
The correct choice depends on whether the transformation should be:
- virtual or persisted;
- batch or incremental;
- SQL-oriented or graphical;
- reusable as a view;
- compatible with delta processing;
- optimized for a particular runtime.
5. Data Flow vs Transformation Flow
This distinction is especially important because SAP's current architecture has evolved.
Data Flow
A Data Flow is a graphical mechanism for moving and transforming data into a target table.
SAP documents operators including:
- Join;
- Union;
- Projection;
- Aggregation;
- Script.
Transformation Flow
A Transformation Flow loads data from one or more sources, applies transformations, and writes the result to a local table.
It can use:
- a graphical view transform;
- an SQL view transform;
- a target table.
SAP also documents support for loading delta changes from one source table in applicable scenarios.
Architect rule
Do not build a new architecture around historical assumptions about Data Flow being the universal Datasphere ETL mechanism.
For eligible loading scenarios, SAP currently positions Replication Flows and Transformation Flows as preferred mechanisms, while Data Flows remain relevant for supported transformation scenarios.
Phase 11 will go deep into the operational differences.
6. Grain — The Most Important Transformation Concept
Before touching a join, define the grain.
Suppose:
EKKO = 1 row per Purchase Order
EKPO = 1 row per Purchase Order Item
EKBE = potentially multiple history rows per PO item
Then:
EKKO
1
|
| 1:N
v
EKPO
|
| 1:N
v
EKBE
If you join all three without understanding the grain, one PO item can become multiple rows.
If you then aggregate purchase value:
Original PO value = 100,000
After history join:
Item appears 5 times
Naive SUM = 500,000
The database did exactly what you asked.
Your architecture was wrong.
Golden rule
Never design a transformation before you can state the grain of every input and the grain of the output.
7. Grain Statement Template
For every major transformation, document:
| Question | Example |
|---|---|
| Input A grain | 1 row per PO item |
| Input B grain | 1 row per vendor |
| Join relationship | Many-to-one |
| Expected output grain | 1 row per PO item |
| Measures | Quantity, net value |
| Duplicate risk | Vendor must be unique by supplier key |
| Validation | Count before/after join |
This simple discipline prevents a large percentage of analytical data defects.
8. Join Fundamentals
A join combines datasets based on a relationship.
Common joins:
- Inner;
- Left outer;
- Right outer;
- Full outer;
- Cross;
- Specialized joins where supported by the modeling mechanism.
Inner join
Only matching records survive.
A ∩ B
Use when non-matching records are intentionally excluded.
Left outer join
All rows from the left side survive.
A + matching B
Use when the left dataset is the driving business population.
Example:
All Purchase Orders
+
Vendor enrichment where available
Full outer join
Both sides survive, including unmatched records.
Use carefully. It often indicates a reconciliation or master-data comparison problem rather than a normal analytical enrichment.
Cross join
Every row from A is combined with every row from B.
If:
A = 1,000,000 rows
B = 10,000 rows
then the theoretical result is:
10,000,000,000 rows
Cross joins require an explicit business reason.
9. Join Cardinality
Cardinality describes the expected relationship.
Examples:
- 1:1;
- 1:N;
- N:1;
- N:N.
For analytics, N:N relationships are dangerous because they can multiply measures.
Example
Sales fact
10 rows
Customer table
1 row per customer
Expected:
10 rows
Customer duplicate:
2 rows for one customer
Result:
11 rows
The extra row may silently inflate measures.
Architect rule
Never assume a dimension is unique because its name says “master.” Validate uniqueness at the actual join key.
10. Join Explosion
A join explosion occurs when the output row count grows beyond the intended grain.
Typical causes:
- duplicate dimension keys;
- many-to-many relationships;
- joining two transactional datasets;
- incomplete join conditions;
- missing date validity conditions;
- language-dependent text tables joined without language;
- currency tables joined without currency/date keys.
Diagnostic pattern
Always compare:
Rows before join
Rows after join
Distinct business keys before
Distinct business keys after
Measure totals before
Measure totals after
A larger row count is not automatically wrong.
A larger row count that violates the expected grain is wrong.
11. Join Key Design
A join key must represent the business relationship.
Bad:
PO_ID only
Potentially correct:
PO_ID + PO_ITEM
For time-dependent data:
Material
+
Plant
+
Valid-From / Valid-To
For currency:
Currency
+
Exchange Rate Type
+
Valid Date
For language:
Material
+
Language
The more complex the relationship, the more important it is to explicitly document the key.
12. Filter Transformation
Filtering removes rows that do not satisfy a condition.
Examples:
COMPANY_CODE = '1000'
FISCAL_YEAR >= 2025
IS_DELETED = 0
STATUS = 'OPEN'
Filtering early can reduce downstream processing.
But “filter as early as possible” is not a blind rule.
First verify:
- whether the filter is semantically valid;
- whether downstream consumers require excluded rows;
- whether the filter can be pushed toward the source;
- whether it changes delta semantics;
- whether the filter should be part of acquisition or business transformation.
13. Projection
Projection means controlling columns.
Typical operations:
- select required columns;
- exclude unused columns;
- rename columns;
- reorder columns.
Projection matters because wide datasets increase:
- memory;
- network transfer;
- intermediate result size;
- processing cost;
- cognitive complexity.
Example
Raw source:
120 columns
Analytical transformation needs:
18 columns
Do not carry all 120 columns through every downstream transformation without a reason.
SAP's graphical modeling tools explicitly provide projection operations for adding, removing, renaming, and reordering columns.
14. Calculated Columns
Calculated columns derive new values from existing columns.
Example:
NET_VALUE = QUANTITY * NET_PRICE
Another:
DELIVERY_DAYS = DAYS_BETWEEN(ORDER_DATE, DELIVERY_DATE)
SAP documents calculated columns using SQL expressions and provides validation and function assistance in the graphical editor.
Architect considerations
Ask:
- Is the calculation deterministic?
- Is it reusable?
- Does it belong at acquisition, transformation, or semantic level?
- Does it depend on current date/time?
- Does it need currency conversion?
- Does it create a datatype issue?
- Can it be pushed down?
- Does it change the grain?
15. Null Handling
NULL is not the same as zero.
Examples:
NULL quantity
0 quantity
Unknown quantity
Not applicable quantity
These can have different business meanings.
Common functions include:
COALESCE(value, 0)
NULLIF(value, 0)
CASE WHEN ...
Never apply COALESCE blindly.
If NULL means “unknown,” converting it to zero creates false information.
16. Data Type Standardization
Before harmonizing datasets, standardize:
- numeric types;
- dates;
- timestamps;
- currencies;
- units;
- text lengths;
- identifiers;
- boolean/status representations.
Example:
Source A:
PLANT = INTEGER
Source B:
PLANT = VARCHAR
A semantic key should normally have a deliberate common representation.
Do not rely on implicit conversion.
Implicit conversions can produce:
- runtime errors;
- unexpected comparisons;
- poor performance;
- inconsistent null behavior.
17. Date and Timestamp Transformation
Time is one of the easiest places to introduce subtle defects.
Document:
- source timezone;
- target timezone;
- UTC vs local time;
- date vs timestamp;
- fiscal calendar;
- business-day logic;
- daylight-saving behavior where relevant.
Example:
Source timestamp:
2026-01-31 23:30 UTC
Local business date:
2026-02-01
If you derive fiscal periods before applying the correct timezone, the transaction can land in the wrong reporting period.
18. Currency Transformation
Currency is not just multiplication.
A correct conversion can require:
- source currency;
- target currency;
- exchange-rate type;
- validity date;
- quotation direction;
- decimal conventions.
Do not create:
Amount * CurrentRate
for historical analytics unless the business explicitly requires current-rate valuation.
Historical reporting normally requires the rate applicable to the business event or defined valuation date.
19. Unit-of-Measure Transformation
Quantity transformations require:
- source unit;
- target unit;
- conversion factor;
- validity;
- material-specific rules where applicable.
Example:
10 KG
=
22.0462 LB
Do not aggregate quantities across incompatible units.
This is especially important for procurement and supply-chain analytics.
20. Union
Union combines rows from compatible datasets.
Conceptually:
A
+
B
=
A UNION B
Use union when datasets represent the same business grain and compatible columns.
Example:
Sales_2025
UNION
Sales_2026
Union vs Join
| Operation | Meaning |
|---|---|
| Join | Adds columns based on relationships |
| Union | Adds rows from compatible datasets |
Confusing the two is a common modeling error.
21. UNION vs UNION ALL
Where the transformation mechanism exposes these semantics, understand the distinction.
UNION
Duplicates may be removed.
UNION ALL
Rows are appended without duplicate elimination.
For large analytical datasets, unnecessary duplicate elimination can add cost.
If the business data is already mutually exclusive, preserving rows without an unnecessary distinct operation may be more efficient.
Always verify the exact operator semantics of the Datasphere object being used.
22. Aggregation
Aggregation changes grain.
Example:
Input:
1 row per PO item
Output:
1 row per Vendor + Month
Then:
SUM(Net_Value)
SUM(Quantity)
COUNT(PO_Item)
Golden rule
Aggregation is a grain-changing operation.
Write the grain explicitly before and after.
23. Additive, Semi-Additive, and Non-Additive Measures
Additive
Can be summed across all relevant dimensions.
Examples:
- sales amount;
- order quantity.
Semi-additive
Can be summed across some dimensions but not time.
Examples:
- account balance;
- inventory balance.
Non-additive
Should not be summed.
Examples:
- ratio;
- percentage;
- average;
- unit price.
Critical example
If:
Load Factor = Loaded Capacity / Available Capacity
do not calculate:
AVG(Load Factor)
unless the business definition explicitly requires an unweighted average.
Often the correct calculation is:
SUM(Loaded Capacity)
--------------------
SUM(Available Capacity)
This is a transformation and semantic modeling decision, not merely a SQL syntax choice.
24. Lookup and Enrichment
Enrichment adds attributes from another dataset.
Example:
Purchase Order
|
+--> Vendor Master
|
+--> Material Master
|
+--> Plant Master
Before enriching, validate:
- key uniqueness;
- effective dates;
- language;
- organizational scope;
- deletion status.
Enrichment should not silently change the fact grain.
25. Slowly Changing Master Data
Master data can change over time.
Example:
Vendor 1000
2024 → Region A
2025 → Region B
If a 2024 purchase order is joined to the current vendor master, it may incorrectly show Region B.
A historical model may need a validity-aware lookup:
Vendor
+
Valid From
+
Valid To
This is why temporal transformation belongs in the architecture conversation.
26. Data Cleansing
Typical cleansing operations:
- trim whitespace;
- normalize case;
- standardize codes;
- convert empty strings;
- remove invalid records;
- validate mandatory keys;
- normalize date formats;
- map source-specific statuses.
Example:
' open '
'OPEN'
'Open'
'01'
may represent the same business state.
Create a controlled mapping instead of allowing every source variation to leak into the semantic layer.
27. Standardization vs Harmonization
These are different.
Standardization
Makes technical representation consistent.
Example:
'IN'
'IND'
'India'
mapped to a standard country key.
Harmonization
Makes different business structures comparable.
Example:
SAP Company Code
+
Legacy Business Unit
+
Cloud Entity
mapped into a common enterprise organizational model.
Harmonization is where Datasphere starts becoming an enterprise analytical platform rather than simply a staging area.
28. Business Rules
A business rule should be explicit.
Bad:
WHERE STATUS <> 'C'
with no explanation.
Better:
Exclude cancelled purchase orders from the
active procurement KPI dataset.
Document:
- rule;
- owner;
- effective date;
- source fields;
- exceptions;
- test cases.
A transformation is part of the enterprise data contract.
29. Technical Transformation vs Business Transformation
Technical
Examples:
- datatype conversion;
- trimming;
- renaming;
- null normalization.
Business
Examples:
- active customer definition;
- net sales calculation;
- open PO classification;
- revenue recognition category.
Keep business logic visible and governed.
Do not hide major business rules inside an obscure technical flow.
30. Pushdown
Pushdown means allowing processing to execute closer to the data/runtime rather than pulling data into an external processing layer unnecessarily.
Prefer supported native operations when they provide:
- lower data movement;
- better scalability;
- simpler architecture;
- easier lineage;
- better operational behavior.
SAP's current graphical view tooling can expose generated SQL and runtime metrics, allowing modelers to inspect and optimize execution.
Architect question
Do not ask only:
Can I implement this transformation?
Ask:
Where will this transformation execute, how much data will move, and what happens when volume becomes 100 times larger?
31. Python Transformation
Python can be valuable for specialized logic.
Use it when:
- the transformation genuinely requires procedural Python logic;
- supported Data Flow functionality fits the use case;
- SQL/graphical operators cannot express the requirement cleanly.
Do not use Python simply because it is familiar.
Potential disadvantages:
- additional runtime behavior;
- more complex operations;
- reduced transparency compared with standard operators;
- harder governance;
- different scaling characteristics.
Prefer declarative operations when they express the requirement adequately.
32. SQL Transformation
SQL is ideal when the transformation is naturally relational.
Examples:
- joins;
- filters;
- CASE expressions;
- aggregations;
- date calculations;
- window functions where supported;
- set operations.
Use SQL when it improves:
- precision;
- readability;
- maintainability;
- reuse;
- testability.
But do not assume that writing SQL automatically makes the transformation faster.
Execution architecture matters more than syntax preference.
33. Transformation Layering
A practical architecture can look like:
RAW
|
+-- technical standardization
|
STG
|
+-- cleansing
|
HARM
|
+-- enterprise mappings
|
CURATED
|
+-- business-ready datasets
|
SEMANTIC
|
+-- analytical consumption
These names are examples.
Datasphere does not require these exact layers.
The important thing is that the architecture has a clear purpose for each layer.
34. When to Persist a Transformation
Persist when one or more of these are true:
- transformation is expensive;
- dataset is reused frequently;
- downstream queries need predictable performance;
- source system should not be queried repeatedly;
- recovery requires a stable intermediate state;
- transformation is a reusable enterprise data product;
- volume makes repeated virtual execution expensive.
Do not persist automatically.
Persistence creates:
- storage;
- refresh operations;
- lifecycle management;
- monitoring;
- data freshness decisions.
35. When to Keep a Transformation Virtual
Virtual transformation can be appropriate when:
- logic is lightweight;
- data is already local;
- freshness matters;
- reuse is limited;
- persistence would add unnecessary complexity;
- runtime performance is acceptable.
A virtual view is not “free.”
Its cost is paid at query time.
36. Transformation Cost Model
Think of total cost as:
Acquisition Cost
+
Transformation Cost
+
Storage Cost
+
Query Cost
+
Operational Cost
A persisted transformation may increase storage and refresh cost while reducing repeated query cost.
A virtual transformation may reduce storage while increasing query runtime.
Architectural optimization is therefore a workload decision.
37. Data Quality Gates
Place quality checks at meaningful boundaries.
Example:
Acquisition
|
+-- schema check
+-- key check
+-- volume check
|
Transformation
|
+-- duplicate check
+-- business rule check
+-- reconciliation
|
Curated
|
+-- KPI validation
Do not wait until the dashboard to discover that the data is broken.
38. Reconciliation
Reconciliation compares source and target outcomes.
Useful controls:
- row counts;
- distinct keys;
- amount totals;
- quantity totals;
- min/max dates;
- null counts;
- deleted-record counts.
Example:
S/4HANA PO value = 1,250,000,000
Datasphere PO value = 1,250,000,000
Difference = 0
A zero difference does not prove correctness, but a non-zero difference requires investigation.
39. Transformation Testing
Test at three levels.
Unit level
Test individual expressions.
Example:
Net = Gross - Discount
Dataset level
Test:
- grain;
- duplicates;
- row counts;
- nulls;
- totals.
Business level
Test:
- KPI definitions;
- business scenarios;
- edge cases;
- historical behavior.
40. Golden Test Dataset
Create a small deterministic dataset containing:
- normal rows;
- nulls;
- duplicates;
- missing lookups;
- deleted rows;
- boundary dates;
- multiple currencies;
- multiple units;
- invalid values.
Then execute the transformation against it.
This is far more valuable than testing only with a large production-like dataset.
41. Data Preview
Data preview is a development tool, not a production observability strategy.
Use it to validate:
- joins;
- calculated columns;
- filters;
- aggregations;
- data types;
- output grain.
SAP provides data preview capabilities across several modeling editors, with access depending on privileges and object configuration.
Do not conclude that a preview proves production scalability.
42. Runtime and Explainability
For complex transformations, inspect execution behavior.
SAP's current tooling can provide runtime metrics, generated SQL, Explain Plan, View Analyzer, and transformation-flow simulation in applicable scenarios.
Use these to investigate:
- expensive joins;
- large intermediate datasets;
- poor filtering;
- unnecessary columns;
- repeated computation;
- persistence opportunities;
- source pushdown limitations.
43. Transformation Failure Modes
Common failures:
| Failure | Likely cause |
|---|---|
| Duplicate rows | Wrong join cardinality |
| Missing records | Incorrect inner join/filter |
| Inflated measures | Join multiplication |
| Incorrect historical values | Current master data joined to historical facts |
| Slow execution | Large joins/intermediate data |
| Wrong totals | Incorrect aggregation grain |
| NULL explosion | Datatype or join mismatch |
| Delta failure | Transformation incompatible with change semantics |
| Schema error | Source contract changed |
| Memory pressure | Excessive intermediate result |
44. Delta-Aware Transformation
When transformation consumes change-enabled data, understand that not every transformation is equally compatible with delta semantics.
SAP documents specific limitations for transformation flows with delta capture, including restrictions around aggregation and certain joins involving active-record views.
This leads to an important architect rule:
A transformation that is correct for a full load is not automatically correct for an incremental load.
Always test:
- insert;
- update;
- delete;
- reprocessing;
- late-arriving change.
45. Full Load vs Delta Transformation
Full-load mindset
Read everything
↓
Transform everything
↓
Replace target
Delta mindset
Read changes
↓
Interpret change semantics
↓
Transform changes
↓
Apply correctly to target
The second model is much harder.
That is why delta compatibility must be an explicit architecture requirement.
46. Source-to-Target Mapping
Create a mapping specification.
| Source | Target | Rule |
|---|---|---|
| EKKO-EBELN | PO_ID | Direct |
| EKPO-MENGE | ORDER_QTY | Cast + unit validation |
| EKPO-NETWR | NET_VALUE | Currency-aware |
| LFA1-LIFNR | VENDOR_ID | Direct |
| T001-BUKRS | COMPANY_CODE | Direct |
| Derived | PO_STATUS | Business rule |
This becomes the bridge between requirements, implementation, testing, and support.
47. Naming Standards
Use names that reveal intent.
Good:
TR_PO_ITEM_HARMONIZATION
V_PO_ITEM_ENRICHED
TF_VENDOR_STANDARDIZATION
SQL_PO_OPEN_VALUE
Avoid:
TEST1
FINAL2
NEW_FLOW
COPY_OF_FINAL
TEMP_NEW
Names are part of operational usability.
48. Reusable Transformation Patterns
Pattern A — Source standardization
Raw Source
↓
Rename
↓
Datatype Standardization
↓
Technical Staging
Pattern B — Enrichment
Fact
+
Master
↓
Enriched Fact
Pattern C — Harmonization
SAP Source
+
Legacy Source
+
Cloud Source
↓
Enterprise Mapping
↓
Common Dataset
Pattern D — Aggregation
Transaction Grain
↓
Business Grain
↓
KPI Dataset
Pattern E — Reconciliation
Source
↓
Control Totals
↓
Target
↓
Compare
↓
Exception
49. Procurement Example — End to End
Consider:
EKKO → PO Header
EKPO → PO Item
LFA1 → Vendor
MARA → Material
T001 → Company Code
Desired output:
FACT_PURCHASE_ORDER_ITEM
Target grain:
One row per Purchase Order + Purchase Order Item.
Transformation:
EKKO
|
+---- EKPO
|
+---- Vendor
|
+---- Material
|
+---- Company Code
|
v
Standardize + Validate
|
v
FACT_PURCHASE_ORDER_ITEM
Before aggregation, validate that each enrichment dataset is unique at the required key.
50. Procurement Business Rules
Example rules:
OPEN_PO =
delivery outstanding
AND cancellation flag is not active
NET_VALUE =
ORDER_QTY × NET_PRICE
PO_AGING =
CURRENT_DATE - PO_DATE
These rules should have business owners.
Do not treat transformation logic as merely technical implementation.
51. Finance Example
Suppose the source contains:
BKPF
BSEG
SKA1
CSKS
T001
Target:
FACT_FINANCE_POSTING
Potential transformations:
- account normalization;
- company-code enrichment;
- cost-center enrichment;
- fiscal period derivation;
- sign normalization;
- currency conversion;
- document classification.
Critical warning:
Never aggregate BSEG before you understand the accounting grain and reporting requirement.
52. Aviation Example
For flight analytics:
Flight
+
Aircraft
+
Route
+
Schedule
+
Actual Operations
Target grain might be:
One row per flight leg per operating date
Derived measures:
- scheduled block time;
- actual block time;
- delay minutes;
- available seats;
- passenger count;
- load factor.
Load factor should normally be derived from appropriate aggregated numerator and denominator rather than blindly averaging lower-level ratios.
53. What Not to Transform Too Early
Avoid premature:
- aggregation;
- currency conversion;
- unit conversion;
- status filtering;
- deletion;
- dimension denormalization.
Why?
Because another downstream use case may need the original information.
A good enterprise architecture preserves reusable information until there is a deliberate reason to reduce it.
54. What to Transform Early
Technical standardization is often appropriate early:
- datatype normalization;
- column naming;
- basic validity checks;
- safe trimming;
- technical metadata;
- clearly defined source-system mappings.
The rule is:
Normalize technical inconsistencies early; delay irreversible business reduction until the requirement is clear.
55. Transformation Documentation
Every production transformation should document:
- purpose;
- owner;
- source objects;
- input grain;
- output grain;
- join keys;
- filters;
- calculations;
- aggregation;
- business rules;
- delta behavior;
- expected volume;
- SLA;
- dependencies;
- monitoring;
- recovery.
If another engineer cannot understand the transformation without opening every node, the design is under-documented.
56. Architect Decision: Flow or View?
Use a view when:
- logic can remain virtual;
- freshness is important;
- reuse as a query object is valuable;
- runtime is acceptable.
Use a flow with a target table when:
- persistence is justified;
- transformation is expensive;
- stable intermediate state is needed;
- batch processing is appropriate;
- operational recovery requires a physical target.
The answer is workload-specific.
57. Architect Decision: Graphical or SQL?
Use graphical modeling when:
- visual lineage matters;
- transformations are straightforward;
- teams benefit from visual review;
- standard operators are sufficient.
Use SQL when:
- relational logic is complex;
- SQL is clearer than a large graph;
- advanced expressions are required;
- developers need precise control over the query definition.
Do not choose based on ideology.
58. Architect Decision: Python or SQL?
Prefer SQL/graphical operators when they can express the requirement cleanly.
Consider Python when:
- procedural logic is genuinely needed;
- supported Data Flow scripting is appropriate;
- the transformation cannot be expressed cleanly using relational operators.
The architect should be able to explain why Python exists.
59. Performance Engineering Rules
For large datasets:
- Reduce unnecessary columns.
- Reduce unnecessary rows.
- Validate join keys.
- Avoid accidental many-to-many joins.
- Aggregate only at the correct business grain.
- Avoid repeated expensive calculations.
- Prefer supported pushdown.
- Inspect runtime behavior.
- Persist only when workload evidence supports it.
- Measure before and after optimization.
Performance engineering is evidence-driven.
60. Transformation Anti-Patterns
Anti-pattern 1 — Join everything
More joins do not automatically mean a richer model.
Anti-pattern 2 — Aggregate early
You may destroy reusable detail.
Anti-pattern 3 — Carry every source column
Wide datasets increase processing and cognitive cost.
Anti-pattern 4 — Python for everything
Procedural code can replace simple declarative operations unnecessarily.
Anti-pattern 5 — Hard-code business rules
Business logic becomes opaque and difficult to govern.
Anti-pattern 6 — Ignore grain
The most dangerous transformation error.
Anti-pattern 7 — Treat preview as performance testing
Small preview data does not represent production volume.
Anti-pattern 8 — Assume full-load correctness implies delta correctness
Incremental processing has different semantics.
61. Hands-On Lab 1 — Standardize a Raw Dataset
Create a raw procurement dataset containing:
- PO ID;
- PO item;
- vendor;
- material;
- quantity;
- price;
- currency;
- order date.
Tasks:
- Rename columns using enterprise naming.
- Standardize datatypes.
- Normalize nulls.
- Validate mandatory keys.
- Create a technical staging object.
- Document the target grain.
Deliverable:
Source-to-target mapping plus validation checklist.
62. Hands-On Lab 2 — Enrich Purchase Orders
Use:
- PO items;
- vendor master;
- material master;
- company code master.
Tasks:
- Define the grain.
- Validate master-data uniqueness.
- Join each enrichment dataset.
- Compare row counts.
- Compare distinct PO item keys.
- Compare measure totals.
- Investigate any row multiplication.
Deliverable:
Join-cardinality decision record.
63. Hands-On Lab 3 — Build a KPI Dataset
Starting from PO item grain:
Create:
Vendor + Month
with:
- total PO value;
- total quantity;
- PO count;
- average item value.
Tasks:
- Define additive measures.
- Define non-additive measures.
- Aggregate only after validating the input grain.
- Reconcile totals with the detailed dataset.
Deliverable:
KPI definition + aggregation specification.
64. Hands-On Lab 4 — Historical Master Data
Create vendor history:
Vendor
Region
Valid From
Valid To
Join it to historical purchase orders.
Tasks:
- Define effective-date logic.
- Test boundary dates.
- Test missing history.
- Verify one applicable master record per fact.
- Compare current-master vs historical-master results.
Deliverable:
Temporal enrichment design.
65. Hands-On Lab 5 — Delta-Aware Transformation
Build a transformation using a change-enabled source where the supported scenario allows it.
Test:
- Insert.
- Update.
- Delete.
- Re-run.
- Failure and restart.
- Reconciliation.
Document which transformations remain compatible with the chosen delta architecture.
SAP specifically documents restrictions around some delta-capture transformation patterns, so this lab must be treated as an operational exercise rather than just a modeling exercise.
66. Hands-On Lab 6 — Performance Comparison
Create two implementations of the same business logic:
Version A
Virtual graphical transformation.
Version B
Persisted transformation flow.
Measure:
- execution time;
- source impact;
- target storage;
- repeated query cost;
- operational complexity.
Then decide which architecture is better.
67. Troubleshooting Playbook
Duplicate rows
Check:
- join cardinality;
- duplicate keys;
- missing join conditions;
- language;
- validity dates.
Missing rows
Check:
- inner vs left join;
- filters;
- NULL handling;
- source completeness.
Inflated measures
Check:
- join multiplication;
- aggregation grain;
- duplicate enrichment records.
Incorrect dates
Check:
- timezone;
- datatype;
- fiscal calendar;
- boundary conditions.
Incorrect currency values
Check:
- rate type;
- rate date;
- source/target currency;
- conversion direction.
Slow transformation
Check:
- input volume;
- column count;
- join cardinality;
- filters;
- intermediate result size;
- pushdown;
- unnecessary persistence.
Delta behaves incorrectly
Check:
- source change semantics;
- supported transformation pattern;
- delete handling;
- aggregation;
- joins involving active-record/change-capture data.
68. Architect Exercise — Enterprise Procurement
Design a transformation architecture for:
- 500 million purchase-order items;
- 20 million vendors;
- 10 million materials;
- historical vendor attributes;
- multiple currencies;
- multiple units;
- daily delta ingestion;
- hourly procurement KPI refresh.
Your design must specify:
- Input grain.
- Output grains.
- Join strategy.
- Persistence boundaries.
- Delta compatibility.
- Currency strategy.
- Unit strategy.
- Historical master-data strategy.
- Performance strategy.
- Reconciliation.
- Monitoring.
- Failure recovery.
Then explain which transformations you would deliberately not perform in the raw layer.
69. Architect Exercise — When the Dataset Grows 100x
Assume a transformation currently processes:
5 million rows
It will become:
500 million rows
Ask:
- Does the grain remain the same?
- Does federation remain viable?
- Which joins become dominant?
- Which dimensions require uniqueness guarantees?
- Should intermediate data be persisted?
- Can transformations be pushed down?
- Can processing be partitioned?
- Which columns can be eliminated?
- Which calculations can move downstream?
- What new monitoring is required?
This exercise separates implementation knowledge from architecture thinking.
70. Interview Preparation
Q1. What is the difference between transformation and modeling?
Transformation changes the dataset; modeling defines how the resulting data is represented and consumed semantically.
Q2. Why is grain important?
Because joins and aggregations operate on rows, and incorrect grain can silently duplicate or eliminate business facts.
Q3. Why can a left join still create duplicates?
Because the right-side key may not be unique.
Q4. What is join explosion?
Unexpected multiplication of rows caused by relationship/cardinality problems.
Q5. When should you aggregate?
When the required business output has a defined lower-granularity grain and the measures are mathematically appropriate for aggregation.
Q6. Should every calculation be pushed into the transformation layer?
No. Some calculations belong in semantic/consumption logic, especially when they depend on dimensional context or must remain dynamically reusable.
Q7. Data Flow or Transformation Flow?
Evaluate the exact workload, source/target capabilities, delta requirements, runtime, and current SAP guidance. Do not answer with a universal rule.
Q8. When should you use Python?
When procedural transformation is genuinely required and the supported Data Flow scenario is appropriate.
Q9. How do you prevent inflated measures?
Define grain, validate key uniqueness, inspect row counts, and reconcile measures before and after joins.
Q10. How do you optimize a transformation?
Reduce data early where semantically valid, minimize columns, validate joins, use supported pushdown, inspect runtime behavior, and persist only when evidence supports it.
71. Production Checklist
- Input grain documented.
- Output grain documented.
- Source-to-target mapping documented.
- Join keys documented.
- Cardinality validated.
- Duplicate checks implemented.
- Filter rules documented.
- Business rules have owners.
- Datatypes standardized.
- Currency logic defined.
- Unit logic defined.
- Timezone logic defined.
- NULL semantics defined.
- Aggregation grain documented.
- Additive/non-additive measures identified.
- Delta compatibility tested where required.
- Delete behavior tested.
- Performance measured.
- Persistence decision justified.
- Monitoring defined.
- Reconciliation defined.
- Recovery tested.
- Naming standards applied.
- Lineage reviewed.
- Security reviewed.
72. Architect Takeaway
A strong Datasphere transformation is not the one with the most nodes.
It is the one where you can explain:
What is the input grain?
What changes?
Why does it change?
What is the output grain?
Where does it execute?
What happens at 100 times the volume?
What happens when a record is deleted?
How do we prove the result is correct?
That is the difference between building a transformation and engineering an enterprise data pipeline.
The next phases build on this foundation:
- Phase 06 — Graphical Modeling
- Phase 07 — SQL & SQLScript Modeling
- Phase 08 — Semantic Modeling
- Phase 09 — Analytic Models & Consumption
- Phase 10 — Advanced Semantics & Hierarchies
- Phase 11 — Data Flows, Replication & Task Chains
Keep the boundary clear:
Acquire → Prepare → Model → Semantically expose → Consume.
That sequence is one of the core mental models of enterprise SAP Datasphere architecture.
Official SAP Documentation Anchors
Use these current SAP sources as implementation references:
- SAP Datasphere — Creating a Data Flow
- SAP Datasphere — Creating a Transformation Flow
- SAP Datasphere — Use Replication Flows and Transformation Flows to Load and Write Data
- SAP Datasphere — Creating a Graphical View
- SAP Datasphere — Create a Calculated Column in a Graphical View
- SAP Datasphere — Acquiring, Preparing, and Modeling Data