Building Data Flows and Task Chains for Scheduled Data Integration

Explanation

Once graphical and SQL views exist in a space, many real projects need repeatable, scheduled data movement and transformation rather than pure on-the-fly virtual access. Data Builder addresses this with two complementary object types: Data Flows and Task Chains. A Data Flow is a persistence-oriented pipeline. Unlike a graphical view, which is typically consumed live or as part of a view stack, a data flow is designed to read from one or more sources (local tables, remote tables, or views), apply transformation operators (joins, unions, aggregation, script-based Python transforms for advanced cases, and field mapping), and write the result into a target local table. This is the mechanism of choice when: the source system cannot sustain repeated federated query load, transformation logic is complex enough that materializing intermediate results improves performance, or downstream consumers need a stable, versioned snapshot rather than a live pass-through. The data flow editor lets you chain operators visually, preview intermediate results at each node, and configure the target table's load type as either full load (truncate and reload) or delta/append depending on how the target table was defined and whether a delta capture mechanism is enabled upstream. A Task Chain is the orchestration layer above data flows, replication flows, and other schedulable tasks. It lets you sequence multiple tasks in a defined order, run tasks in parallel branches, and set the whole chain to trigger on a schedule (hourly, daily, or custom recurrence) or on demand. A common production pattern is: replication flow lands raw data in a local table, a data flow transforms it into a cleansed and conformed structure, and a second data flow or view persistence step builds an aggregate table for reporting - all sequenced inside one task chain so that step two never starts before step one finishes successfully. Task chains also support notifications and status checks, so failures can be surfaced to an integration or Basis-adjacent support team rather than silently leaving stale data in place. From a runtime perspective, when a task chain fires, each task is submitted to the space's compute allocation. Because data flows and replication flows consume the same shared compute and, in many landscapes, contend with ad hoc query workload from live SAC stories, scheduling decisions matter: heavy full-load transformations are usually pushed to off-peak windows, and space administrators are expected to monitor consumption against the space's assigned resource quota. Monitoring is done through the built-in run history for data flows and task chains, which shows start/end time, row counts, and status per task, and is the first place to check when a dashboard shows stale or missing data. A key limitation to be aware of: Data Builder task chains are scoped to orchestration of Datasphere-native tasks; they are not a general-purpose enterprise scheduler and do not replace an external orchestration tool if a project already has one for cross-system dependencies outside Datasphere (for example, waiting on an upstream ERP batch job). In such cases, task chains are usually triggered at the end of that external job via an integration call, or scheduled with a safety buffer, rather than being made directly dependent on external system events.

Code example

-- Data flow target table is a normal Datasphere local table.-- Example: reviewing a data flow's generated SQL logic conceptually-- (Data Builder generates and executes this internally; shown here for understanding, not manual authoring) -- Step 1: source read (remote table via connection, or local table)SELECT    order_id,    customer_id,    order_date,    net_amount,    currencyFROM "RAW_SALES_ORDERS"; -- Step 2: transformation operator equivalent (join + derived column)SELECT    o.order_id,    o.customer_id,    c.customer_name,    o.order_date,    o.net_amount,    o.currency,    CASE WHEN o.net_amount > 10000 THEN 'HIGH_VALUE' ELSE 'STANDARD' END AS order_segmentFROM "RAW_SALES_ORDERS" oLEFT JOIN "CUSTOMER_MASTER" c    ON o.customer_id = c.customer_id; -- Step 3: load into target local table-- Load type = Full: TRUNCATE target, INSERT full result set-- Load type = Delta: INSERT/UPDATE based on delta capture keys, if configured -- Task chain sequencing (conceptual, configured via UI, not script):-- Task 1: Replication Flow 'RF_RAW_SALES_ORDERS' (source -> RAW_SALES_ORDERS)-- Task 2: Data Flow 'DF_SALES_ENRICHED' (depends on Task 1 success)-- Task 3: Data Flow 'DF_SALES_AGGREGATES' (depends on Task 2 success)-- Schedule: daily at 02:00, with failure notification to integration-support distribution list

Real project scenario

A retail analytics project needed daily-refreshed sales dashboards in SAC, but the source ERP's sales order tables were too large and too heavily used during business hours to query live via federation. The team built a replication flow to land raw order data into a local table overnight, a data flow to join it with customer master data and classify orders into value segments, and a second data flow to pre-aggregate by region and month for a summary dashboard. All three were sequenced in a single task chain scheduled for 2 AM, with the aggregate step configured to run only after the enrichment step completed successfully. When the ERP occasionally delayed its own nightly batch, the task chain's run history immediately showed a zero-row extract from the replication flow, which let the team catch and explain stale dashboard data before end users escalated it as a system defect.

Common mistakes

• Building complex transformation logic directly inside graphical views instead of a data flow, causing slow live query performance for consumers. • Scheduling a task chain without dependency ordering, so an aggregate step runs before its source data flow finishes. • Assuming task chains can wait on events in external, non-Datasphere systems without an explicit trigger or buffer. • Choosing full load for very large target tables when a delta-based approach would drastically reduce runtime and resource consumption. • Not setting up failure notifications, so broken nightly loads go unnoticed until business users report incorrect numbers. • Ignoring the run history and row-count trends, missing early warning signs of upstream data volume anomalies.

Best practices

• Use data flows to persist and transform data when source systems cannot sustain repeated federated queries or transformation logic is heavy. • Sequence dependent tasks explicitly within a task chain rather than relying on independent schedules to align by coincidence. • Schedule resource-intensive full loads during off-peak windows and monitor space compute consumption. • Configure delta-based loads where source and target support it, to reduce runtime and resource usage. • Enable and review failure notifications on task chains so issues are caught before they reach dashboards. • Document task chain dependencies and schedules so support teams can quickly diagnose stale-data incidents.

Interview angle

Interviewers often probe whether a candidate understands when to persist data via a data flow versus leaving a view virtual, and how task chains enforce dependency and scheduling. Be ready to explain the difference between full and delta loads, how task chain failure handling works, and why task chains are not a substitute for enterprise-wide job schedulers when dependencies span outside Datasphere.