Building Transformation Flows and Persisted Views in Data Builder

Explanation

Once basic graphical views and table imports are in place, most real projects need repeatable transformation logic that goes beyond a single view: cleansing raw replicated data, applying business rules, deduplicating records, and persisting results so downstream consumers do not repeatedly recompute expensive joins or aggregations. This is where Data Flows and view persistence in Data Builder become central. A Data Flow is a dedicated object type in Data Builder distinct from a graphical view. While a graphical view is typically used for virtual, on-the-fly transformation logic (joins, unions, calculated columns, aggregations) that executes at query time, a Data Flow is designed to move data through a sequence of transformation steps and write the result into a target table, usually a local table in the space. This makes Data Flows the right tool when you need to materialize a curated dataset once, on a schedule, rather than recompute it every time a report opens. Design pattern: source objects (remote tables, local tables, or views) are pulled into the Data Flow canvas, then chained through transformation nodes such as join, union, projection (column selection/renaming), aggregation, and script-based Python transformations for more complex row-level logic where supported. Each node should be validated incrementally rather than building the entire chain before running it, because tracing an error across many chained transformations is much harder than isolating it early. A closely related capability is view persistence: instead of building a separate Data Flow, you can mark a graphical view as persisted, which tells the platform to run and store its result as a snapshot rather than recalculate it live. This is valuable when the underlying view has many joins, deep unions, or currency/unit conversions that are computationally heavy and when consumers only need periodically refreshed data rather than always-live numbers. The decision between virtual views, persisted views, and Data Flows is a core intermediate-level design skill. Virtual views suit lightweight, always-current data where source systems can handle query pushdown efficiently. Persisted views suit heavier transformation logic on data that tolerates a refresh interval. Data Flows suit multi-step, more procedural transformation logic, especially when combining several sources into a reusable curated table that many downstream views or SAC models will consume. Runtime behavior matters for troubleshooting: a persisted view or Data Flow output only reflects data as of its last successful run. If consumers report stale numbers, the first troubleshooting step is checking the last run timestamp and status of the persistence or Data Flow task, not the view logic itself. Failures during a run can result from source system unavailability, schema drift (a column renamed or removed upstream), data type mismatches introduced by transformation nodes, or resource limits when volumes grow far beyond initial testing volumes. Scheduling and monitoring these objects is a production concern: Data Flow runs and persistence refreshes should be scheduled with realistic windows that respect source system load and downstream consumption deadlines (for example, before business users start their morning reporting cycle). Dependencies between multiple Data Flows and persisted views should be sequenced so that a downstream object is not refreshed before its upstream source has completed successfully, since Datasphere does not automatically infer complex multi-object dependency chains beyond what is explicitly modeled. From an integration perspective, the choice here directly affects SAP Analytics Cloud performance: models built on live connections to virtual views inherit all runtime cost from every underlying join and transformation, while models pointing to persisted views or Data Flow output tables generally get more predictable, faster query response because the heavy lifting already happened during the scheduled run.

Code example

-- Example: SQL view used as a source step inside a Data Flow chain-- (illustrative logic only, not a specific product API) -- Step 1: Cleansed sales source (graphical/SQL view)SELECT    SalesOrderID,    CustomerID,    TRIM(UPPER(Region)) AS Region_Clean,    CASE        WHEN NetAmount < 0 THEN 0        ELSE NetAmount    END AS NetAmount_Adjusted,    OrderDateFROM RAW_SALES_ORDERSWHERE OrderDate IS NOT NULL; -- Step 2 (conceptual Data Flow node logic):-- Join cleansed sales with a currency conversion reference table-- then aggregate by Region_Clean and month, writing the result-- into a local persisted target table, e.g. AGG_SALES_BY_REGION_MONTH -- Step 3: SAC model or downstream view then reads only-- AGG_SALES_BY_REGION_MONTH instead of recomputing joins each time.

Real project scenario

A retail analytics team ingested daily sales transactions via a remote table connection into Datasphere. Initial reports were built directly on a graphical view joining sales, product, and currency conversion tables, but as data volume grew to several years of history, dashboard load times in SAP Analytics Cloud became unacceptable during peak morning usage. The team redesigned the pipeline: a Data Flow now cleanses and joins the raw sources nightly, writing aggregated results into a local table, and the SAC model was repointed to a lightweight view on top of that persisted table. Query times dropped significantly, and the team added a monitoring routine that alerts on Data Flow run failures before business hours.

Common mistakes

• Building deep chains of virtual (non-persisted) views on top of each other and expecting live performance at scale, without ever testing with production-like volumes. • Repointing SAC models to a persisted view or Data Flow output without validating that the row-level grain matches what reports expect (aggregation mismatches). • Not sequencing dependent Data Flow and persistence runs, causing downstream objects to refresh against incomplete upstream data. • Ignoring schema drift in source tables, leading to silent transformation node failures or truncated columns after a source system change. • Treating persistence as a one-time setup instead of an operational asset requiring scheduling, monitoring, and failure alerting.

Best practices

• Start with a virtual view for prototyping; convert to a persisted view or Data Flow only once real volume or complexity justifies materialization. • Validate each transformation node incrementally in a Data Flow rather than running the full chain blind. • Document and monitor refresh schedules, including explicit sequencing for objects that depend on one another. • Track the last successful run timestamp as the first diagnostic step when consumers report outdated numbers. • Align persistence refresh windows with source system load patterns and business reporting deadlines. • Re-validate row-level grain whenever repointing downstream models to a newly persisted or aggregated dataset.

Interview angle

Interviewers commonly probe whether a candidate understands the practical trade-off between virtual views, persisted views, and Data Flows in Datasphere, and can justify a choice based on data volume, refresh tolerance, and downstream consumption patterns. Be ready to explain how you would troubleshoot stale data in a consumption tool, how you sequence dependent transformation jobs, and how you would decide when a graphical view's complexity justifies converting it into a dedicated Data Flow.