SAP Datasphere - SQL Views as SQL Script – Automatic generation of columns in the left side pane

[anna_sch-25]

Hi everyone,

I am working with SQL Views using SQLScript (Table Function) in SAP Datasphere and noticed that the output columns must always be created manually in the Model Properties → Columns section before the SQLScript can be validated or deployed.

Even when the SQLScript contains a simple RETURN SELECT ... statement, Datasphere does not automatically generate the column definitions based on the SQL result. As far as I can see, this is different from standard SQL views, where column inference works automatically.

My questions:

1. Is there any way to automatically generate the output columns in the left side panel when using SQLScript (Table Function) in SAP Datasphere?
2. If not:
   • Is this a current technical limitation?
   • Is there any roadmap indicating that automatic column generation will be supported in the future?

Context / What I tried:

• SQLScript with RETURN SELECT ...
• SQLScript with table variables
• Validation to trigger auto‑generation
• Switching between SQL Standard and SQLScript

In all cases, Datasphere requires me to manually maintain the business/technical names and data types in the Model Properties panel before validation succeeds.

Example SQLScript:

return

with "cte_customers" as (
    select
        "id",
        "name"
    from "customers"
)

, "cte_customers_ranked" as (
    select
        "id",
        "name",
        1 as "rank_number"
    from "cte_customers"
)

select
    "id",
    "name",
    "rank_number"
from "cte_customers_ranked";

Expected behavior (wish):

Some way to auto‑populate the column list based on the SQLScript output (similar to SQL Standard views).

Actual behavior:

Columns must be fully defined manually, otherwise validation fails.

If anyone has additional insight, workaround ideas, or official SAP statements regarding this behavior, I would really appreciate it.

Thank you.

Kind regards,
Anna

Clicking Validate for the following statement automatically generates the column ID:

tab =
select
    "id" AS ID
from "customers";

return
select ID from :tab;

Clicking Validate for the following statement does not automatically generate the column ID:

tab = with "cte1" (ID) as (
    select
        "id" AS ID
    from "customers"
)
select ID from "cte1";

return
select ID from :tab;

Is there an issue with the WITH clause?

[anna_sch-25]

From a developer’s perspective, SAP Datasphere should explicitly support a Code-First modeling approach — not as a replacement for the UI, but as a first-class, equal alternative.

I have only started working with SAP Datasphere since last week, and this was the very first limitation my colleague and I noticed. The inability to fully define models in code immediately stood out in our daily development work.

For professional developers, it is essential to be able to define models entirely in code. This includes the ability to declare a structured output schema directly in SQLScript, such as a header with column names, data types, and semantics (similar to RETURNS TABLE or declarative column definitions).
Today, this metadata must be maintained manually in the UI, which becomes extremely time-consuming and error-prone for complex or frequently changing queries.

A Code-First approach would provide significant advantages:

• Versioning and Traceability
Fully declarative models can be cleanly versioned in Git, reviewed via diffs, and reproduced reliably. UI-managed metadata cannot offer the same level of transparency.

• Maintainability and Scalability
Large SQL models with many columns are difficult to refactor and maintain through the UI. Code is inherently better suited for restructuring, reuse, and long-term maintenance.

• Reduction of Manual Errors
Maintaining logic in SQL and semantics in the UI creates duplication and inconsistency. A declarative schema in code would establish a single source of truth.

• Developer Adoption and Productivity
Many Datasphere users come from HANA, SQL, and data engineering backgrounds. A UI-only modeling approach creates friction and limits Datasphere’s adoption as a true enterprise-grade platform.

Importantly, Code-First does not exclude UI-First.
Business users can continue to benefit from graphical modeling, while developers gain the flexibility to define everything in code — including structure, semantics, and output schema.

If SAP Datasphere aims to position itself as a modern, developer-friendly data platform, Code-First modeling is not a nice-to-have — it is a necessity.

I strongly encourage SAP to consider this capability and to provide a clear roadmap in this direction.

[CLTGravesen]

Hey Anna,

The short answer is that the SQL editor is very - ehm - simple (but I'm sure it has a great personality).
The Editor will try to derive the columns from whatever it finds immediately after the "Return" statement. 
It will put no effort into it, and any logic between the output and the source will break it. 

An example:

Any kind of obfuscation between the editor and the source tables will result in the editor throwing it's hands in the air. 

Here is a really basic example with a simple temp table between the source and the output:

Even this simple script confuses the poor things. 
It does derive the columns, but everything is cast as a string(100).

Adding a new "Ranking" column, which is clearly an integer, will still result in a string(100).

A workaround (not really).

If you already have an object with the structure you want; one thing that makes it less-awful, it to add a 

return select * from source

And validate. That will generate the structure and derive the schema correctly. 

You can then write your script on top, and replace the "Return" statement later.
This is shaky at best, but it's the least awful way I found. 

[anna_sch-25]

return select * from source is not a workaround for complex sql queries with multiple joins.

[anna_sch-25]

Thank you for your reply. I know the not-really-a-workaround with "return select * from source" for simple queries. It's not working with complex SQL queries with multiple joins and same column names in different tables. Do you know where this feature could be requested?

[CLTGravesen]

I couldn't agree more 🙂