Phase 07 — SQL & SQLScript Modeling

Architectural objective: move from visual relational modeling to deliberate code-based modeling in SAP Datasphere—knowing when SQL is the right abstraction, how SQLScript extends it, how the runtime behaves, and how to design maintainable, performant production models.

Phase 06 taught you to express relational logic graphically. This phase teaches you to express the same class of problems with code while developing the judgment required to decide when code is better than a graphical model.

SAP Datasphere provides an SQL View editor for SQL and SQLScript-based modeling. SAP documents SQL Views as Data Builder modeling objects and notes that Datasphere supports a subset of SAP HANA SQL syntax. SQLScript Table Functions provide a stronger programming abstraction when declarative SQL alone is not sufficient.

1. What This Phase Solves

By the end of this phase, you should be able to answer:

What exactly is an SQL View in SAP Datasphere?
How is it different from a Graphical View?
What is the difference between standard SQL and SQLScript in Datasphere?
When should an architect choose SQL instead of graphical modeling?
When is a SQLScript Table Function justified?
How should joins, unions, filters, aggregations, NULLs, CASE expressions and parameters be designed?
How do you reason about grain before writing SQL?
How do you prevent join multiplication and incorrect measures?
How do you validate SQL before deployment?
How do you investigate a slow SQL model?
When should a view remain virtual and when should it be persisted?
How do input parameters propagate through nested models?
How should SQL models be layered in an enterprise?
How do you avoid turning SQLScript into an unmaintainable procedural program?
How do you distinguish a SQL problem from a source, federation or persistence problem?
How do you defend a SQL-vs-Graphical-vs-SQLScript decision in an architecture review?

The goal is not to memorize syntax.

The goal is to become capable of designing and defending code-based Datasphere models.

2. The Mental Model

Think of Datasphere SQL modeling as a progression:

Graphical View
    |
    | visual relational logic
    v
SQL View
    |
    | declarative SQL
    v
SQLScript Table Function
    |
    | controlled procedural/data-flow logic
    v
HANA Cloud execution engine
    |
    +-- optimization
    +-- data access
    +-- joins
    +-- aggregation
    +-- filtering
    +-- function execution

The architectural principle is:

Use the least complex modeling abstraction that expresses the requirement clearly and performs acceptably.

That normally means:

Graphical View for highly visual relational logic.
SQL View when code expresses the logic more clearly or compactly.
SQLScript Table Function only when SQL itself cannot express the required reusable logic cleanly.

Do not choose SQLScript simply because it looks more advanced.

3. Current Product Boundary

SAP Datasphere is not simply an unrestricted HANA SQL console.

The SQL View editor supports SQL capabilities exposed by Datasphere, and SAP explicitly describes Datasphere as supporting a subset of HANA SQL syntax. Therefore, an architect must distinguish:

SQL supported by HANA Cloud
SQL supported by the Datasphere SQL View editor
SQLScript capabilities available in the Datasphere modeling context
SQL that may work in another HANA environment but is not appropriate for a Datasphere model

This is one of the most important habits in this course:

Never infer Datasphere support merely because a feature exists somewhere in HANA.

Use the current Datasphere documentation for the actual modeling context. SAP's current documentation lists SQL Views as a first-class modeling object and documents SQL and SQLScript editing separately.

4. Where Phase 07 Fits

Sources
   |
   v
Connections / Acquisition
   |
   v
Tables / Remote Tables
   |
   v
Data Preparation
   |
   +-------------------+
   |                   |
   v                   v
Graphical View       SQL View
                       |
                       +-- Standard SQL
                       |
                       +-- SQLScript Table Function
   |                   |
   +---------+---------+
             |
             v
      Semantic Modeling
             |
             v
       Analytic Models
             |
             v
       SAC / Consumption

Phase 06 taught the graphical branch.

Phase 07 teaches the code branch.

Phase 08 will build on these modeling foundations to teach semantic modeling.

5. SQL View vs Graphical View

Both can represent relational transformations, but they encourage different ways of thinking.

Dimension	Graphical View	SQL View
Primary expression	Nodes and relationships	SQL code
Best for	Visual relational logic	Compact/complex relational logic
Readability	Visual	Textual
Code review	Structural	Natural for SQL developers
Complex conditional logic	Can become visually large	Often concise
Business transparency	Often strong	Depends on SQL quality
Developer familiarity	Team dependent	High for SQL teams
Parameter handling	UI plus expressions	Explicit code plus parameters
Procedural logic	Not the primary fit	SQLScript may be appropriate

The correct decision is not:

“SQL is more powerful, therefore SQL is better.”

The correct decision is:

“Which abstraction gives this requirement the clearest, safest and most maintainable implementation?”

6. The Three Modeling Choices

Can the requirement be expressed clearly as relational logic?
                    |
                  Yes
                    |
          +---------+---------+
          |                   |
   Graphical is clear     SQL is clearer
          |                   |
     Graphical View        SQL View
                              |
                              v
             Does SQL remain declarative and maintainable?
                              |
                     +--------+--------+
                    Yes                No
                     |                  |
                  SQL View       Consider SQLScript
                                      |
                                      v
                             Is procedural logic truly
                             required and reusable?
                                      |
                                 Yes / No
                                      |
                                 Table Function

This decision tree should become automatic for an architect.

7. Grain Comes Before SQL

This is the single most important SQL modeling habit in the entire course.

Before writing a SELECT, write down:

What does one output row represent?

Examples:

one sales order item
one flight per day
one purchase order item
one customer per month
one material/plant/day
one invoice item
one employee assignment

If you cannot state the grain, you are not ready to write the join.

8. A SQL Modeling Contract

Before implementing a production SQL View, define:

Contract Item	Example
Input grain	Sales order item
Output grain	Sales order item
Required keys	SalesOrder, SalesOrderItem
Measures	NetAmount
Dimensions	Customer, Material
Join cardinality	Many-to-one
Null behavior	Unknown customer retained
Duplicate behavior	Not allowed
Filter behavior	Fiscal year / company code
Parameter behavior	Optional fiscal year
Persistence	Virtual initially
Consumption	Downstream semantic model

This contract is more valuable than a clever query.

9. Basic SELECT and Projection Discipline

The basic pattern is:

SELECT
    "CUSTOMER_ID",
    "CUSTOMER_NAME",
    "COUNTRY"
FROM "CUSTOMER";

Prefer explicit columns over SELECT *:

SELECT
    "SALES_ORDER",
    "CUSTOMER_ID",
    "NET_AMOUNT"
FROM "SALES";

Why?

reduced unnecessary data movement
clearer dependencies
smaller intermediate results
easier downstream consumption
safer schema evolution
better maintainability

Project only what the model actually needs.

10. Aliases

Use aliases when they improve clarity.

SELECT
    S."SALES_ORDER",
    S."CUSTOMER_ID",
    S."NET_AMOUNT"
FROM "SALES" AS S;

For multi-source models, aliases make join logic easier to review:

SELECT
    S."SALES_ORDER",
    C."CUSTOMER_NAME",
    S."NET_AMOUNT"
FROM "SALES" AS S
LEFT JOIN "CUSTOMER" AS C
    ON S."CUSTOMER_ID" = C."CUSTOMER_ID";

11. WHERE and Predicate Design

Filtering belongs in the relational logic where it is semantically valid.

SELECT
    "SALES_ORDER",
    "CUSTOMER_ID",
    "NET_AMOUNT"
FROM "SALES"
WHERE "COMPANY_CODE" = '1000';

The architect question is not:

“Can I add a WHERE?”

It is:

“At what stage should this filter logically and physically be applied?”

Early reduction can reduce downstream processing, but never push a filter below a transformation if doing so changes the required semantics.

Make AND/OR intent explicit with parentheses:

WHERE (
        "COUNTRY" = 'IN'
        OR "COUNTRY" = 'US'
      )
  AND "STATUS" = 'ACTIVE'

12. NULL Semantics

NULL represents absence of a value. It is not zero, empty string, false or ordinary text.

Do not write:

WHERE "CUSTOMER_ID" = NULL

Use:

WHERE "CUSTOMER_ID" IS NULL

or:

WHERE "CUSTOMER_ID" IS NOT NULL

This matters directly for joins, CASE expressions, filters and aggregations.

13. COALESCE

A common pattern is:

COALESCE(
    "L1_REV_ENPL",
    "L2_REV_ENPL",
    "L3_REV_ENPL"
) AS "FINAL_REV_ENPL"

COALESCE returns the first non-NULL value.

Architectural warning:

COALESCE is not automatically a business rule.

If L1, L2 and L3 represent mutually exclusive sources, document the precedence. If multiple values can legitimately coexist, COALESCE may hide a data-quality problem.

14. CASE Expressions

CASE is one of the most important SQL modeling tools.

CASE
    WHEN "NET_AMOUNT" < 0 THEN 'RETURN'
    WHEN "NET_AMOUNT" = 0 THEN 'ZERO'
    ELSE 'SALE'
END AS "TRANSACTION_TYPE"

Use CASE for:

business classifications
flags
derived categories
controlled fallback logic
semantic normalization

Avoid enormous nested CASE expressions. If business logic becomes a policy engine, consider reference data or a separate model.

15. JOINs

SQL joins combine relational sets.

Common types include:

INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN where supported and appropriate
CROSS JOIN

For analytical modeling, INNER and LEFT joins are the most common.

16. INNER JOIN

SELECT
    S."SALES_ORDER",
    C."CUSTOMER_NAME",
    S."NET_AMOUNT"
FROM "SALES" AS S
INNER JOIN "CUSTOMER" AS C
    ON S."CUSTOMER_ID" = C."CUSTOMER_ID";

Meaning:

Keep rows where a matching customer exists.

Use this only when unmatched source rows should genuinely disappear.

17. LEFT JOIN

SELECT
    S."SALES_ORDER",
    C."CUSTOMER_NAME",
    S."NET_AMOUNT"
FROM "SALES" AS S
LEFT JOIN "CUSTOMER" AS C
    ON S."CUSTOMER_ID" = C."CUSTOMER_ID";

Meaning:

Keep all sales rows; enrich them when a customer match exists.

This is often the safer enrichment pattern when the fact transaction must not disappear because of missing master data.

18. The Most Dangerous SQL Mistake: Join Multiplication

Suppose:

SALES
1 row per Order Item

CUSTOMER
1 row per Customer

Correct:
Order Item × Customer -> still 1 row per Order Item

Now suppose CUSTOMER_ASSIGNMENT contains multiple rows per customer:

Order Item × Customer Assignment
           |
           +-- Assignment 1
           +-- Assignment 2

The output grain changes.

A SUM performed after that join may be inflated.

This is one of the most important production defects in analytical SQL.

19. Join Cardinality Must Be Explicit

Before every important join, write:

Left side:
1 row = ______

Right side:
1 row = ______

Expected relationship:
1:1 / 1:N / N:1 / N:N

Expected output grain:
1 row = ______

If you cannot answer those questions, stop coding.

20. N:N Joins Are an Architecture Warning

Many-to-many joins are not always wrong.

But they demand explicit reasoning.

Ask:

Is multiplication intentional?
What does one output row represent?
Should the relationship be resolved before joining?
Should a bridge table be used?
Should the data be aggregated first?
Is the relationship temporal?
Is there a valid business key?

Never accept an N:N join merely because the query runs.

21. ON vs WHERE for Outer Joins

Compare:

FROM "SALES" AS S
LEFT JOIN "CUSTOMER" AS C
    ON S."CUSTOMER_ID" = C."CUSTOMER_ID"
   AND C."STATUS" = 'ACTIVE'

with:

FROM "SALES" AS S
LEFT JOIN "CUSTOMER" AS C
    ON S."CUSTOMER_ID" = C."CUSTOMER_ID"
WHERE C."STATUS" = 'ACTIVE'

These can have different semantics.

The second condition can eliminate rows where C is NULL and effectively change the intended outer-join behavior.

Architect rule:

Know whether the predicate defines the relationship or filters the final result.

22. UNION and UNION ALL

UNION combines result sets with duplicate-elimination semantics.

UNION ALL combines result sets without duplicate elimination.

When duplicate elimination is not required, UNION ALL is generally the more explicit choice.

SELECT "ID", "AMOUNT"
FROM "SALES_2025"

UNION ALL

SELECT "ID", "AMOUNT"
FROM "SALES_2026";

But do not use UNION ALL blindly. If the datasets overlap, duplicate rows may be technically valid but business-invalid.

23. Union Contract

For every union, define:

column order
data types
semantic meaning
NULL/default behavior
source provenance
duplicate expectations

A strong enterprise model preserves source provenance when reconciliation requires it.

24. Aggregation Changes Grain

SELECT
    "CUSTOMER_ID",
    SUM("NET_AMOUNT") AS "TOTAL_NET_AMOUNT"
FROM "SALES"
GROUP BY "CUSTOMER_ID";

If the input is one row per order item and the output is one row per customer, the SQL has changed the grain.

That is not merely a calculation.

It is a grain transformation.

25. Additivity

Before aggregating a measure, determine whether it is:

Additive

Can be summed across the relevant dimensions.

Examples may include sales amount and quantity.

Semi-additive

Can be summed across some dimensions but not others.

Examples may include inventory balance and account balance.

Non-additive

Should not be summed.

Examples include ratios, percentages and many averages.

This becomes critical when the SQL View feeds later analytical models.

26. The Ratio Trap

Never blindly aggregate a ratio.

Suppose:

Region A: 90%
Region B: 10%

A simple average gives 50%.

But if Region A has 10,000 transactions and Region B has 10, the correct weighted result is nowhere near 50%.

Architect rule:

Store or derive the numerator and denominator whenever the final ratio must aggregate correctly.

For example:

Load Factor = Passengers / Available Seats

is often safer to aggregate from its components than by averaging precomputed percentages.

27. HAVING

HAVING filters aggregated groups.

SELECT
    "CUSTOMER_ID",
    SUM("NET_AMOUNT") AS "TOTAL_NET_AMOUNT"
FROM "SALES"
GROUP BY "CUSTOMER_ID"
HAVING SUM("NET_AMOUNT") > 100000;

Conceptually:

WHERE
  |
  v
filters rows

GROUP BY
  |
  v
creates groups

HAVING
  |
  v
filters groups

28. Subqueries and CTEs

Subqueries can express relational conditions inside a query.

SELECT
    S."SALES_ORDER",
    S."NET_AMOUNT"
FROM "SALES" AS S
WHERE S."CUSTOMER_ID" IN (
    SELECT "CUSTOMER_ID"
    FROM "VIP_CUSTOMER"
);

A CTE can make a complex relational query easier to reason about in SQL dialects that support the syntax:

WITH CUSTOMER_TOTAL AS (
    SELECT
        "CUSTOMER_ID",
        SUM("NET_AMOUNT") AS "TOTAL_AMOUNT"
    FROM "SALES"
    GROUP BY "CUSTOMER_ID"
)
SELECT *
FROM CUSTOMER_TOTAL
WHERE "TOTAL_AMOUNT" > 100000;

Important: support is dialect/context-specific in Datasphere. Do not assume every HANA SQL feature is accepted by the Datasphere SQL View editor merely because HANA Cloud supports it. Validate the exact construct in the current Datasphere editor and documentation before standardizing it.

29. Window Functions

Window functions allow calculations across related rows without collapsing the result to one row per group.

Typical analytical patterns include:

ranking
running totals
previous/next row comparisons
partition-level calculations

Conceptually:

SUM("NET_AMOUNT")
OVER (
    PARTITION BY "CUSTOMER_ID"
    ORDER BY "POSTING_DATE"
)

HANA SQL documentation includes window functions as a major SQL capability. However, the Datasphere SQL View editor has its own supported SQL subset, so use the Datasphere validator and current Datasphere SQL documentation as the final authority for a particular function or syntax.

30. GROUP BY vs Window Functions

GROUP BY:

Input rows
   |
   v
groups
   |
   v
one output row per group

Window function:

Input rows
   |
   v
partition/order context
   |
   v
original rows remain
   +
calculated analytical value

This distinction is fundamental.

31. Date, Time, String and Numeric Logic

Enterprise SQL commonly requires:

year/month/quarter
fiscal periods
date differences
timestamp comparisons
timezone handling
string normalization
numeric calculations
safe division
type conversion

Do not build date logic without defining whether the business uses calendar or fiscal time, what timezone applies, and whether the source contains dates or timestamps.

Do not treat currency or units as ordinary numbers. Currency conversion requires currency, rate date/type and target currency semantics. Unit conversion requires unit semantics and often reference data.

32. Data Types Matter

Do not casually combine values with different semantics.

Potential problems include:

numeric text vs numeric data
date vs timestamp
decimal precision differences
incompatible types
implicit conversion
rounding
truncation

A query that executes successfully can still produce incorrect analytical results.

33. Creating an SQL View

The normal workflow is:

Select the appropriate space.
Open Data Builder.
Create a new SQL View.
Add or reference source objects.
Write SQL.
Validate SQL.
Preview the result.
Review warnings.
Save.
Deploy when ready.
Test the deployed object.
Review lineage and runtime behavior.

SAP documents validation and source-change handling in the SQL View editor. When source objects change, the editor can surface validation messages, and SAP recommends validating SQL and reviewing warnings before redeployment.

34. Input Parameters

SAP Datasphere SQL and graphical views can work with input parameters. When a source view has input parameters, Datasphere provides syntax for mapping those source parameters to parameters of the consuming SQL view or resolving them with explicit values.

Conceptually:

Consumer SQL View
      |
      | parameter
      v
Source View
      |
      v
Filtered / parameterized result

Parameters should have an explicit business purpose.

35. Parameter Propagation

Suppose:

V_SALES_BASE
      |
      v
V_SALES_ENRICHED
      |
      v
V_SALES_ANALYTICAL
      |
      v
Consumer

If V_SALES_BASE has a parameter, decide deliberately whether the parameter should:

be exposed by the next layer
be fixed internally
be mapped to another parameter
be eliminated by redesign

Blindly propagating parameters through every layer makes models difficult to consume.

36. Parameter and Persistence Interaction

SAP documents that persistence of a parameterized view has restrictions: a view can be persisted with a parameter when it has a single input parameter with a maintained default value, and the persisted result corresponds to that default value.

Therefore:

A parameterized virtual model and a parameterized persisted model are not necessarily equivalent operationally.

Design persistence only after understanding parameter semantics.

37. SQL View and Persistence

By default, a view can be accessed virtually. SAP documents persistence as an option for improving performance when complex or large views are repeatedly accessed. Persisted data can be refreshed and monitored, while structural redeployment can remove persistence in relevant cases.

Think:

Virtual
  |
  +-- freshness priority
  +-- acceptable runtime
  +-- lower storage footprint

Persisted
  |
  +-- expensive repeated computation
  +-- repeated consumption
  +-- source latency problem
  +-- acceptable refresh latency

Persistence is a workload decision, not a badge of maturity.

38. SQLScript: Why It Exists

SQL is declarative.

SQLScript extends SQL with data, functional and procedural constructs for more complex data-intensive logic. SAP describes SQLScript as a way to combine declarative SQL with imperative logic when required.

The architecture principle is:

Stay declarative as long as possible.

Move into SQLScript only when the requirement benefits from it.

39. SQL vs SQLScript

Requirement	Preferred abstraction
Projection	SQL
Filter	SQL
Join	SQL
Union	SQL
Aggregation	SQL
CASE	SQL
Straightforward derived columns	SQL
Complex reusable data logic	SQLScript may be appropriate
Procedural branching	SQLScript
Complex intermediate table variables	SQLScript
Specialized reusable table-returning logic	Table Function
Simple relational transformation	Do not use SQLScript

The most common SQLScript anti-pattern is using it for logic that SQL already expresses cleanly.

40. Table Functions

A SQLScript Table Function is a reusable function that returns a table-shaped result.

Conceptually:

Inputs
  |
  v
SQLScript logic
  |
  v
Table result

This makes it possible to encapsulate more complex logic behind a reusable tabular interface.

SAP's HANA Cloud SQLScript documentation describes table user-defined functions and the broader SQLScript function model.

41. Table Function Decision Gate

Before creating a Table Function, ask:

Can standard SQL express the logic?
Can a Graphical View express it clearly?
Would a reference table solve the problem more transparently?
Is procedural logic genuinely required?
Does the logic need reuse?
Is the function output contract stable?
Can the team support SQLScript operationally?
Can the model be tested independently?
What is the performance profile?
What is the migration impact?

If the first four answers point to SQL, stop.

Do not create a Table Function.

42. SQLScript Is Not Automatically Faster

A SQLScript implementation can be faster or slower than SQL depending on what it does.

Performance comes from:

data reduction
efficient relational operations
correct join strategy
pushdown
avoiding unnecessary materialization
efficient execution plans
appropriate persistence
source-system behavior
workload shape

Not from the word "SQLScript".

43. Declarative First

Prefer:

SELECT
    "CUSTOMER_ID",
    SUM("NET_AMOUNT") AS "TOTAL"
FROM "SALES"
GROUP BY "CUSTOMER_ID";

over procedural row-by-row processing.

Set-oriented SQL gives the database optimizer more opportunity to reason about the operation.

SAP's SQLScript documentation explicitly emphasizes set-oriented processing and explains the role of imperative constructs when required.

44. Cursors and Row-by-Row Thinking

If you are thinking:

for every row:
    do X

ask first:

Can X be expressed as a relational operation?

This is a major transition from traditional application-code thinking to database-centric engineering.

45. Native HANA Bridge

If you already know Native HANA, many concepts will look familiar:

Datasphere SQL View
        |
        v
Datasphere modeling context
        |
        v
HANA Cloud execution capabilities

But this does not mean:

"Anything I can do in HANA must be valid in Datasphere SQL View."

The SQL View editor has its own product boundary.

This distinction should remain permanent in your architecture thinking.

46. Execution Mental Model

When a consumer accesses an SQL View, think:

Consumer
   |
   v
SQL View
   |
   +-- nested models
   +-- local tables
   +-- remote tables
   +-- other sources
   |
   v
Execution / optimization
   |
   +-- filters
   +-- joins
   +-- aggregation
   +-- calculations
   |
   v
Result

The important question is:

Where is the expensive work actually occurring?

47. Federation Changes the Problem

A SQL View over local data is one performance problem.

A SQL View over remote data can be another.

Remote access introduces:

network latency
source-system workload
source capability differences
data-volume concerns
pushdown considerations
availability dependencies

SAP's Datasphere documentation explicitly warns that remote-table scenarios have additional considerations when views are persisted.

Therefore:

SQL optimization cannot be separated from data-access architecture.

48. Virtual Does Not Mean Free

Virtual
= less persisted storage
!= automatically faster

And:

Persisted
= storage + refresh work
!= automatically better

Architectural decisions require workload measurement.

49. Projection Pruning

A downstream consumer may require only a subset of columns.

Good model design allows unnecessary columns to be eliminated as early as possible.

Think:

100 columns
   |
   v
required 12 columns
   |
   v
join / aggregation

rather than carrying 100 columns through every transformation.

50. Predicate Reduction

Similarly:

1 billion source rows
        |
        v
filter to relevant period
        |
        v
10 million rows
        |
        v
join

is generally preferable to joining the full billion rows first, provided the filter can legally be applied earlier without changing semantics.

51. Join Before Aggregation vs Aggregation Before Join

Suppose a fact table has 500 million rows and you only need monthly totals.

Option A:

500M fact rows
      |
      v
join dimension
      |
      v
aggregate

Option B:

500M fact rows
      |
      v
aggregate to month/customer
      |
      v
join dimension

Option B may dramatically reduce data volume.

But it is only correct if required dimensions and semantics are preserved.

The architect question is:

At what grain can I safely reduce the dataset without losing required semantics?

52. View Analyzer and Runtime Analysis

Performance engineering must move beyond intuition.

SAP Datasphere provides View Analyzer to inspect the entities composing a view and support optimization and persistence decisions. SAP specifically documents it as useful for understanding complex view dependencies and identifying where persistence may help with performance or technical problems such as out-of-memory conditions.

Use analysis tools to answer:

Which object dominates?
Which source is expensive?
Where does row volume increase?
Which join causes the explosion?
Is a remote source involved?
Is persistence appropriate?
Is the model structurally too complex?

53. Validation vs Preview vs Runtime

These are different stages.

Validation

Is the SQL structurally valid?

Preview

Does the output look correct for tested data?

Runtime analysis

Does it behave acceptably at realistic scale?

Production monitoring

Does it remain healthy under real workload?

A successful validation is not a performance sign-off.

54. Golden Dataset Testing

For every important SQL model, create a small deterministic dataset containing:

normal rows
NULL values
duplicate keys
missing dimension records
boundary dates
zero amounts
negative amounts
duplicate source records
expected aggregation results

Then verify the SQL against known outcomes.

This catches semantic defects much earlier than production testing.

55. Join Test Matrix

Scenario	Expected
Exact match	Enriched
No match	Depends on join type
Multiple matches	Explicitly handled
NULL key	Explicitly understood
Duplicate key	Detected
Invalid key	Reconciled
Unexpected N:N	Alert

A join should have a test strategy, not just a join condition.

56. Debugging an Incorrect Total

Suppose expected revenue is 10,000 but the model returns 18,000.

Debug in this order:

Confirm source total.
Confirm source grain.
Confirm row count before join.
Confirm row count after join.
Identify duplicated business keys.
Inspect join cardinality.
Confirm filters.
Confirm aggregation grain.
Confirm currency/unit semantics.
Only then change the SQL.

57. Debugging a Missing Row

If a row disappears:

Check the source.
Check the source filter.
Check each join.
Identify INNER JOINs.
Check NULL behavior.
Check WHERE predicates on outer-joined tables.
Check parameter values.
Check source replication/federation state.
Check whether the target model intentionally filters the row.

Never assume the final SQL is the only possible cause.

58. Debugging a Slow SQL View

Use this sequence:

Slow result
   |
   +-- Is source remote?
   |
   +-- How many rows enter?
   |
   +-- How many columns enter?
   |
   +-- Which join increases rows?
   |
   +-- Is aggregation happening too late?
   |
   +-- Are filters selective?
   |
   +-- Is a nested model expensive?
   |
   +-- Would persistence help?
   |
   +-- Does the model need redesign?

Do not start by randomly rewriting SQL.

59. Common Performance Anti-Patterns

SELECT *

Avoid unnecessary columns.

Repeated logic

Avoid independently implementing the same business rule in many views.

Giant SQL View

Avoid one enormous statement containing every transformation.

Over-decomposition

Avoid dozens of tiny views with no semantic boundary.

Late reduction

Avoid carrying huge row/column volumes into joins when safe early reduction is possible.

Do not persist merely because a view is large.

Procedural overuse

Do not use SQLScript for simple relational logic.

60. The Three-Layer SQL Pattern

A practical enterprise pattern is:

L0 — Source / acquisition
        |
        v
L1 — Reusable relational transformation
        |
        v
L2 — Business / semantic preparation
        |
        v
Consumption

The exact number of layers can vary.

The principle is:

Each layer should have a reason to exist.

61. Reusable SQL Views

Good reusable views usually have:

stable grain
stable keys
documented semantics
predictable NULL behavior
controlled naming
limited hidden business rules
clear ownership

A reusable model should be treated as an interface.

Changing its output grain is an interface-breaking change.

62. SQL View as an API Contract

Think of a reusable SQL View as:

                 SQL View Contract
                       |
          +------------+------------+
          v            v            v
        Grain         Keys       Measures
          |            |            |
          +------------+------------+
                       |
                       v
                  Consumers

If you change the contract, downstream models may break or silently change meaning.

63. Naming Standards

A production standard should distinguish:

source/acquisition objects
reusable transformation objects
business models
consumption models

For example:

SRC_*
TRF_*
BUS_*
SEM_*
ANL_*

The exact convention is organizational.

The principle is:

A reader should understand an object's architectural role from its name.

64. SQL Comments

Comment why, not what.

Weak:

-- Join customer
LEFT JOIN "CUSTOMER" AS C

Better:

-- Customer is intentionally left-joined because missing master-data
-- records must not remove valid sales transactions.
LEFT JOIN "CUSTOMER" AS C

The comment preserves architectural intent.

65. Source Changes and Dependencies

A source change may:

remove a column
rename a column
change data type
change semantics
change grain
change key uniqueness

SAP documents that source changes can generate validation messages in SQL View transforms and recommends validating SQL and reviewing warnings before redeployment.

Before changing a shared SQL View, inspect:

downstream views
analytic models
consumption objects
data access controls
data products where applicable
external consumers

Use lineage and impact analysis rather than memory. SAP documents impact and lineage analysis as a standard modeling capability.

66. Security Boundary

A SQL View should not be considered secure merely because the source table is secure.

Ask:

Who can access the space?
Who can read the object?
Is the data shared across spaces?
Are row-level restrictions required?
Does the consumer inherit the expected authorization behavior?
Are sensitive columns exposed unnecessarily?

Phase 14 will provide the full security model, but SQL modelers must design with those boundaries in mind.

67. SQL View vs Transformation Flow

A SQL View is primarily a modeling/query abstraction.

A Transformation Flow is a data transformation execution object.

If you need to materialize a transformation into a target table as part of an operational data pipeline, the Transformation Flow may be more appropriate.

If you need reusable relational logic evaluated as a view, SQL View may be appropriate.

Do not choose based only on whether both can "transform data."

68. SQL View vs Data Flow

A Data Flow is an acquisition/transformation mechanism.

A SQL View is a relational modeling object.

Data Flow
Source -> transform -> target

SQL View
Source(s) -> query logic -> result

Phase 11 will cover Data Flow and Transformation Flow deeply.

69. SQL View vs Replication Flow

Replication Flow is concerned with replication and synchronization patterns.

SQL View is concerned with query and model logic.

Therefore:

Replication answers “How do I keep data synchronized?”

SQL answers “How do I shape and query that data?”

This distinction prevents architectural confusion.

70. SQL View vs Analytic Model

SQL View:

prepares relational data.

Analytic Model:

defines analytical consumption semantics.

Do not overload SQL Views with every consumption requirement.

The semantic and analytical layers deserve explicit modeling.

71. Hybrid Graphical + SQL Architecture

You do not need to choose SQL everywhere.

A strong architecture may look like:

Remote / Local Sources
        |
        v
Graphical Preparation
        |
        +----------------+
        |                |
        v                v
Reusable SQL View   Reusable Graphical View
        |                |
        +--------+-------+
                 |
                 v
          Semantic Model
                 |
                 v
           Analytic Model

Use each abstraction where it provides the best clarity.

72. When Graphical Modeling Wins

Prefer Graphical Views when:

logic is naturally visual
teams need visual lineage
transformations are straightforward
functional teams review the model
the model benefits from node-level inspection
the graphical editor makes logic clearer

73. When SQL Modeling Wins

Prefer SQL when:

logic is naturally expressed as relational code
the model is join/aggregation heavy
developers need concise code
complex expressions would create a giant graphical model
SQL review is easier for the team
parameterized source handling is clearer in code

74. When SQLScript Wins

Use SQLScript only when:

SQL alone is insufficient
the logic is genuinely procedural or algorithmic
reusable table-returning behavior is required
a stable function interface is valuable
the team can operate and troubleshoot SQLScript

If the only reason is "SQLScript is more advanced," that is not a valid architecture decision.

75. Enterprise Decision Matrix

Question	Graphical	SQL	SQLScript
Simple projection	Excellent	Excellent	Poor choice
Simple filter	Excellent	Excellent	Poor choice
Standard joins	Excellent	Excellent	Usually unnecessary
Large CASE logic	Possible	Often better	Sometimes
Complex relational query	Possible	Often excellent	Sometimes
Procedural logic	Poor fit	Poor fit	Strong
Reusable table function	No	No	Strong
Visual review	Strong	Moderate	Moderate
SQL developer workflow	Moderate	Strong	Strong
Maintainability	Depends	Depends	Requires discipline

76. Architect SQL Checklist

Before approving a SQL View:

Semantics

What is the grain?
What are the keys?
Are measures additive?
What happens to NULL?

Joins

What is each side's cardinality?
Can duplicates occur?
Can the join multiply facts?

Filters

Are filters semantically correct?
Are they selective?
Are outer joins preserved?

Performance

Is the source remote?
Can rows be reduced earlier?
Can columns be reduced earlier?
Is aggregation placed appropriately?
Should the view be persisted?

Operations

Can the model be monitored?
Can it be tested?
Are dependencies understood?

Governance

Is naming consistent?
Is ownership clear?
Is the interface documented?

77. Hands-On Lab 1 — First SQL View

Objective

Build your first reusable SQL View.

Source

Create or use a sales table with:

SALES_ORDER
CUSTOMER_ID
POSTING_DATE
NET_AMOUNT
COMPANY_CODE

Requirement

Return the four business fields and keep only COMPANY_CODE = 1000.

SELECT
    "SALES_ORDER",
    "CUSTOMER_ID",
    "POSTING_DATE",
    "NET_AMOUNT"
FROM "SALES"
WHERE "COMPANY_CODE" = '1000';

Verify:

row count
columns
NULL behavior
date values
filter correctness

78. Hands-On Lab 2 — Customer Enrichment

Build:

SALES
  +
CUSTOMER
  |
  v
SALES_ENRICHED

Requirements:

preserve every sales transaction
enrich customer name
retain unmatched customers

Therefore the intended join is a LEFT JOIN.

79. Hands-On Lab 3 — Detect Join Multiplication

Create a customer assignment table with duplicate customer records.

Join it to sales.

Measure:

source row count
joined row count
distinct sales order count
total sales amount

Then answer:

Why did the total change?

This lab teaches more than syntax.

80. Hands-On Lab 4 — Aggregation

Create a customer sales summary:

Sales order item
       |
       v
Customer
       |
       v
Month

Output:

CUSTOMER_ID
YEAR
MONTH
TOTAL_NET_AMOUNT

Verify the grain explicitly.

81. Hands-On Lab 5 — Parameterized Model

Create a model that accepts a business parameter such as Fiscal Year.

Then:

run with default value
run with another value
consume the parameterized source from another SQL View
map the parameter
validate the dependency

SAP documents source input parameter mapping specifically for SQL and SQLScript views.

82. Hands-On Lab 6 — SQL vs Graphical

Take an existing Graphical View from Phase 06.

Rebuild the same logic in SQL.

Compare:

readability
number of transformations
maintainability
debugging experience
output
runtime behavior

Then write a one-page architecture decision:

Which implementation would you keep in production and why?

There is no automatic correct answer.

83. Hands-On Lab 7 — Performance Investigation

Create an intentionally inefficient model:

unnecessary columns
late filter
duplicate-producing join
unnecessary nested model

Then optimize it.

Measure:

row counts
runtime
model complexity

Use View Analyzer where applicable to inspect the model structure and identify expensive dependencies.

84. Hands-On Lab 8 — SQLScript Table Function

Only after completing the SQL labs:

identify a problem SQL cannot express cleanly
define the required table output
create a minimal SQLScript Table Function
implement the logic
consume it
test edge cases
compare it with a pure SQL alternative

The exercise is intentionally designed to make you earn the right to use SQLScript.

85. Production Scenario — Procurement Analytics

Imagine:

S/4HANA
   |
   v
Purchase Orders
   |
   +-- Header
   +-- Item
   +-- Supplier
   +-- Material
   |
   v
Datasphere
   |
   +-- Acquisition
   +-- Preparation
   +-- SQL / Graphical Modeling
   +-- Semantic Modeling
   +-- Analytics
   |
   v
SAC

Requirement:

Procurement spend by supplier, material group, company code and month.

Architect questions:

What is the fact grain?
Where should header/item joins occur?
Where should supplier enrichment occur?
Where should currency conversion occur?
Which measures are additive?
Should the model be virtual?
Should intermediate data be persisted?
Where should authorization be applied?

86. Production Scenario — Flight Analytics

For a flight model:

Flight
  |
  +-- Flight date
  +-- Route
  +-- Aircraft
  +-- Capacity
  +-- Bookings
  +-- Load Factor

Architect question:

Should load factor be stored directly, averaged, or recalculated from additive components?

A ratio such as:

Load Factor = Passengers / Available Seats

is often safer to aggregate from its components than by averaging precomputed percentages.

87. Production Scenario — Federated Source

Suppose:

Datasphere SQL View
       |
       v
Remote S/4HANA table
       |
       v
Remote database

The SQL may be correct.

But production performance depends on:

source-system capacity
network
source-side execution
filters
data volume
remote capabilities
refresh/persistence strategy

Therefore:

SQL optimization cannot be separated from data-access architecture.

88. Production Scenario — Reconciliation

Suppose Finance reports:

S/4HANA Revenue = 125M
Datasphere Revenue = 121M

Do not immediately modify SQL.

Perform reconciliation:

same reporting period?
same currency?
same company scope?
same document status?
same source extraction completeness?
same duplicate handling?
same cancellation logic?
same aggregation grain?
same authorization scope?
same refresh timestamp?

SQL is only one component of reconciliation.

89. AI-Generated SQL

Current SAP Datasphere documentation includes a Generate SQL capability for SQL Views. SAP states that it can generate or modify SQL code from a natural-language prompt, while generation is not supported for SQLScript Table Functions.

The correct workflow is:

Prompt
  |
  v
Generated SQL
  |
  v
Human review
  |
  v
Validate
  |
  v
Preview
  |
  v
Test
  |
  v
Performance analysis
  |
  v
Deploy

Never go directly from prompt to production.

90. AI SQL Review Checklist

When AI generates SQL, verify:

grain
joins
NULL semantics
filters
aggregation
duplicate handling
parameter handling
source references
security implications
performance
naming
maintainability

AI can generate syntactically valid SQL that is semantically wrong.

91. Dynamic SQL and Security

Dynamic SQL introduces additional security and optimization concerns.

For normal Datasphere SQL View modeling, avoid dynamic SQL unless clearly justified.

HANA Cloud SQLScript documentation discusses SQL injection risks and runtime disadvantages of unnecessary dynamic SQL.

Architect rule:

Prefer static, validated, declarative SQL.

92. Enterprise SQL Coding Standards

A production standard should include:

Naming

Consistent technical and business names.

Formatting

One logical expression per line where practical.

Aliases

Short, meaningful aliases.

Projection

Explicit columns.

Joins

Explicit join conditions.

Comments

Explain business intent.

Grain

Document input and output grain.

Parameters

Document purpose and default behavior.

NULL

Document important fallback rules.

Aggregation

Document measure semantics.

93. Code Review Template

For every important SQL View review:

Purpose
Input sources
Input grain
Output grain
Join map
Filter map
Measure definitions
NULL behavior
Parameter behavior
Performance considerations
Persistence decision
Security considerations
Dependencies
Test evidence
Owner

This should become part of your professional modeling discipline.

94. Architect Exercise — Choose the Abstraction

Requirement:

Join three tables, filter by fiscal year, derive five classifications, aggregate by customer and month, and expose the result to an analytic model.

Choose:

A. Graphical View
B. SQL View
C. SQLScript Table Function

Defend the choice using:

readability
maintainability
team skill
performance
governance
future change
testing

There is no universal answer.

Your defense is the architect skill.

95. Architect Exercise — The Join Explosion

A dashboard reports:

Expected revenue: 50M
Actual model: 73M

The SQL contains:

FACT_SALES
LEFT JOIN CUSTOMER
LEFT JOIN CUSTOMER_SEGMENT
LEFT JOIN SALES_ASSIGNMENT

Your task:

identify possible N:N relationships
determine the grain after each join
identify the multiplication point
redesign the model
prove the corrected result with a golden dataset

96. Architect Exercise — Virtual or Persisted?

A SQL View:

reads 300M rows
is used by 30 dashboards
changes every 15 minutes
takes 45 seconds virtually
has a partially remote dependency

Should it be persisted?

Your answer must include:

freshness requirement
query concurrency
refresh cost
source-system load
storage
failure behavior
operational monitoring
consumer SLA

There is no "always persist large data" rule.

97. Architect Exercise — SQLScript or Redesign?

A developer proposes a 700-line SQLScript Table Function because:

“The SQL became too complicated.”

Determine whether the correct solution is:

SQL refactoring
decomposition into reusable views
reference/master data
Graphical View
SQLScript
upstream transformation

Complexity alone is not a sufficient justification for procedural code.

98. Troubleshooting Matrix

Symptom	First questions
Missing rows	INNER JOIN? WHERE on outer join? parameter?
Duplicate rows	N:N join? duplicate keys?
Inflated measures	join multiplication? wrong grain?
NULL unexpectedly	source NULL? join mismatch? CASE?
Slow query	remote source? row volume? joins? persistence?
Validation error	source changed? unsupported syntax? missing parameter?
Wrong aggregation	measure additive? grain changed?
Parameter failure	mapping? default? source parameter?
Persistence unexpected	parameter restriction? structural redeployment?
Remote timeout	source latency? volume? filtering?

99. Production Failure Pattern — Silent Semantic Drift

One of the most dangerous failures is not a runtime error.

SQL still runs
       |
       v
Source key uniqueness changes
       |
       v
Join multiplicity changes
       |
       v
Revenue becomes inflated
       |
       v
Dashboard still loads

This is why data-quality monitoring and reconciliation are essential.

100. Production Failure Pattern — Giant SQL View

Symptoms:

developers are afraid to modify it
validation becomes slow
debugging is difficult
ownership is unclear
dependency graph is huge
business logic is repeated

Remediation:

identify semantic boundaries
isolate reusable logic
establish stable grains
remove duplicated calculations
separate acquisition from business modeling
re-measure runtime

101. Production Checklist

SQL

Semantics

Joins

Cardinalities understood
Duplicate keys tested
N:N joins justified
Outer-join predicates reviewed

Parameters

Purpose documented
Defaults defined where needed
Propagation tested
Persistence implications understood

Performance

Remote dependencies reviewed
Filters evaluated
Columns minimized
Aggregation placement reviewed
View Analyzer/runtime analysis performed where appropriate
Persistence decision documented

Operations

102. Interview Preparation — Fundamentals

What is an SQL View in SAP Datasphere?

A code-based modeling object in Data Builder where SQL logic can be used to create a reusable relational result. SAP documents SQL Views as a supported Datasphere modeling object and provides a dedicated SQL View editor.

Is Datasphere SQL identical to HANA SQL?

No. Datasphere documents a supported SQL subset/context. Always validate against the Datasphere SQL View editor and current Datasphere documentation.

SQL View vs Graphical View?

They are two modeling abstractions for expressing relational logic. Choose based on clarity, maintainability, team skills and runtime behavior.

103. Interview Preparation — Architecture

When would you choose SQLScript?

Only when declarative SQL and simpler modeling abstractions cannot express the required reusable logic cleanly and the additional complexity is justified.

Why can a correct SQL query return incorrect business results?

Because syntactic correctness does not guarantee semantic correctness. Incorrect grain, join multiplication, NULL handling, aggregation and business definitions can produce wrong results.

What is the first question before writing a join?

What does one row represent on each side, and what should one output row represent?

104. Interview Preparation — Performance

How would you optimize a slow SQL View?

Start with measurement:

identify source and dependency cost
inspect row/column volume
inspect joins
reduce data early where semantically safe
review aggregation placement
inspect remote access
use View Analyzer/runtime tools
evaluate persistence
redesign if necessary

Does persistence always improve performance?

No. It adds storage and refresh work. It can improve repeated expensive reads but must be evaluated against freshness and operational requirements. SAP documents persistence as a performance option rather than a universal requirement.

105. Interview Preparation — SQLScript

Why not use SQLScript for everything?

Because SQL is simpler, declarative and often sufficient. SQLScript introduces additional complexity and should be justified by the requirement.

What is a Table Function?

A reusable SQLScript function that returns a table-shaped result and can encapsulate logic that is more complex than a straightforward relational SQL model.

106. Architect-Level Interview Question

“Your team has 300 SQL Views. Performance is degrading. What do you do?”

A strong architect answer is not:

“Optimize the SQL.”

It is:

establish workload baseline
classify views by workload
identify hot paths
inspect dependency graphs
identify remote dependencies
detect repeated logic
detect join explosions
classify virtual vs persisted workloads
review semantic layering
consolidate or decompose where justified
introduce standards
establish performance budgets
monitor continuously

That is architecture.

107. Enterprise SQL Architecture Pattern

                  Enterprise Sources
                         |
               +---------+---------+
               |                   |
           SAP Sources         Non-SAP Sources
               |                   |
               +---------+---------+
                         |
                         v
                  Acquisition Layer
                         |
                         v
                Preparation Layer
                         |
                +--------+--------+
                |                 |
                v                 v
          Graphical Views     SQL Views
                |                 |
                +--------+--------+
                         |
                  Business Models
                         |
                  Semantic Models
                         |
                   Analytic Models
                         |
                    Consumption

SQL is one component of the architecture—not the architecture itself.

108. Enterprise Standard: Decision Records

For important SQLScript or persistence decisions, record:

Decision

What was chosen?

Context

Why was it needed?

Alternatives

What else was considered?

Trade-off

What did we gain and lose?

Evidence

What measurements support the decision?

Review Trigger

When should the decision be reconsidered?

This transforms tribal knowledge into architecture knowledge.

109. SQL Modeling Maturity Model

Level 1 — Syntax

Can write SELECT, JOIN and GROUP BY.

Level 2 — Modeling

Understands grain and semantics.

Level 3 — Engineering

Understands performance and testing.

Level 4 — Production

Understands monitoring, persistence, dependencies and change.

Level 5 — Architecture

Can choose between Graphical, SQL and SQLScript and defend the trade-offs.

Your target is Level 5.

110. Final Mental Model

SQL is not just syntax.

SQL
 |
 +-- Grain
 +-- Keys
 +-- Joins
 +-- Filters
 +-- Aggregation
 +-- NULL semantics
 +-- Parameters
 +-- Data types
 +-- Performance
 +-- Security
 +-- Dependencies
 +-- Operational behavior
        |
        v
   Enterprise Model

The best SQL model is not the shortest query.

It is the model whose:

grain is obvious
semantics are defensible
dependencies are controlled
performance is measurable
behavior is testable
operational characteristics are understood
architectural purpose is clear

111. Phase 07 Exit Criteria

Do not consider this phase complete merely because you can write SQL.

You should be able to:

If you can do all of this, you are no longer merely "writing SQL."

You are engineering Datasphere models.

112. Official Documentation Anchors

Use current SAP documentation as the authority when implementing this phase:

SAP Datasphere current documentation and modeling guide
Creating an SQL View
Processing source input parameters in an SQL View
Persisting data in a Graphical or SQL View
Exploring Views with View Analyzer
SQL and SQLScript modeling documentation
SAP HANA Cloud SQL Reference
SAP HANA Cloud SQLScript Reference

SAP's current Datasphere documentation explicitly lists SQL Views among the supported Data Builder modeling objects, while its current modeling documentation covers SQL Views and SQLScript Table Functions.

For SQLScript language details, use the current SAP HANA Cloud SQLScript reference rather than older HANA Platform examples when a syntax question is version-sensitive.

Official SAP Documentation

Use these current SAP sources as implementation references:

Architect Takeaway

A Datasphere SQL View is not merely a place to type SQL. It is a governed modeling interface with a grain, contract, dependencies, performance profile and consumption purpose.

And SQLScript is not the "advanced SQL button."

It is an additional programming abstraction that should be introduced only when simpler declarative modeling is insufficient.

The architect's job is therefore not:

“Can I write this in SQL?”

It is:

“What is the clearest, safest, most maintainable and operationally appropriate way to model this requirement in Datasphere—and why?”

That question is the bridge from Datasphere developer to Datasphere architect.

1. What This Phase Solves​

2. The Mental Model​

3. Current Product Boundary​

4. Where Phase 07 Fits​

5. SQL View vs Graphical View​

6. The Three Modeling Choices​

7. Grain Comes Before SQL​

8. A SQL Modeling Contract​

9. Basic SELECT and Projection Discipline​

10. Aliases​

11. WHERE and Predicate Design​

12. NULL Semantics​

13. COALESCE​

14. CASE Expressions​

15. JOINs​

16. INNER JOIN​

17. LEFT JOIN​

18. The Most Dangerous SQL Mistake: Join Multiplication​

19. Join Cardinality Must Be Explicit​

20. N:N Joins Are an Architecture Warning​

21. ON vs WHERE for Outer Joins​

22. UNION and UNION ALL​

23. Union Contract​

24. Aggregation Changes Grain​

25. Additivity​

Additive​

Semi-additive​

Non-additive​

26. The Ratio Trap​

27. HAVING​

28. Subqueries and CTEs​

29. Window Functions​

30. GROUP BY vs Window Functions​

31. Date, Time, String and Numeric Logic​

32. Data Types Matter​

33. Creating an SQL View​

34. Input Parameters​

35. Parameter Propagation​

36. Parameter and Persistence Interaction​

37. SQL View and Persistence​

38. SQLScript: Why It Exists​

39. SQL vs SQLScript​

40. Table Functions​

41. Table Function Decision Gate​

42. SQLScript Is Not Automatically Faster​

43. Declarative First​

44. Cursors and Row-by-Row Thinking​

45. Native HANA Bridge​

46. Execution Mental Model​

47. Federation Changes the Problem​

48. Virtual Does Not Mean Free​

49. Projection Pruning​

50. Predicate Reduction​

51. Join Before Aggregation vs Aggregation Before Join​

52. View Analyzer and Runtime Analysis​

53. Validation vs Preview vs Runtime​

Validation​

Preview​

Runtime analysis​

Production monitoring​

54. Golden Dataset Testing​

55. Join Test Matrix​

56. Debugging an Incorrect Total​

57. Debugging a Missing Row​

58. Debugging a Slow SQL View​

59. Common Performance Anti-Patterns​

SELECT *​

Repeated logic​

Giant SQL View​

Over-decomposition​

Late reduction​

Blind persistence​

Procedural overuse​

60. The Three-Layer SQL Pattern​

61. Reusable SQL Views​

62. SQL View as an API Contract​

63. Naming Standards​

64. SQL Comments​

65. Source Changes and Dependencies​

66. Security Boundary​

1. What This Phase Solves

2. The Mental Model

3. Current Product Boundary

4. Where Phase 07 Fits

5. SQL View vs Graphical View

6. The Three Modeling Choices

7. Grain Comes Before SQL

8. A SQL Modeling Contract

9. Basic SELECT and Projection Discipline

10. Aliases

11. WHERE and Predicate Design

12. NULL Semantics

13. COALESCE

14. CASE Expressions

15. JOINs

16. INNER JOIN

17. LEFT JOIN

18. The Most Dangerous SQL Mistake: Join Multiplication

19. Join Cardinality Must Be Explicit

20. N:N Joins Are an Architecture Warning

21. ON vs WHERE for Outer Joins

22. UNION and UNION ALL

23. Union Contract

24. Aggregation Changes Grain

25. Additivity

Additive

Semi-additive

Non-additive

26. The Ratio Trap

27. HAVING

28. Subqueries and CTEs

29. Window Functions

30. GROUP BY vs Window Functions

31. Date, Time, String and Numeric Logic

32. Data Types Matter

33. Creating an SQL View

34. Input Parameters

35. Parameter Propagation

36. Parameter and Persistence Interaction

37. SQL View and Persistence

38. SQLScript: Why It Exists

39. SQL vs SQLScript

40. Table Functions

41. Table Function Decision Gate

42. SQLScript Is Not Automatically Faster

43. Declarative First

44. Cursors and Row-by-Row Thinking

45. Native HANA Bridge

46. Execution Mental Model

47. Federation Changes the Problem

48. Virtual Does Not Mean Free

49. Projection Pruning

50. Predicate Reduction

51. Join Before Aggregation vs Aggregation Before Join

52. View Analyzer and Runtime Analysis

53. Validation vs Preview vs Runtime

Validation

Preview

Runtime analysis

Production monitoring

54. Golden Dataset Testing

55. Join Test Matrix

56. Debugging an Incorrect Total

57. Debugging a Missing Row

58. Debugging a Slow SQL View

59. Common Performance Anti-Patterns

SELECT *

Repeated logic

Giant SQL View

Over-decomposition

Late reduction

Blind persistence

Procedural overuse

60. The Three-Layer SQL Pattern

61. Reusable SQL Views

62. SQL View as an API Contract

63. Naming Standards

64. SQL Comments

65. Source Changes and Dependencies

66. Security Boundary