Orchestrate

Report an issue

Something wrong, out of date or unclear? Tell us. Corrections are prioritized over new topics. You don't need to sign in.

0 / 2000

Don't include personal or confidential company details. Reports are reviewed with AI assistance.

SAP data on a lakehouse: Business Data Cloud, Iceberg, Dremio and SAP Databricks

What SAP's October 2026 lakehouse announcements change for AI on SAP data, what is available now, and how to query SAP-shaped Iceberg tables with an agent.

Updated Nov 12, 2026Foundational 9 minDeep 40 min
Foundational layer · 9 min read

The 60-second version

A lakehouse keeps data as open files in cheap cloud storage and lets several query engines read the same copy. It replaces the habit of copying data into one warehouse per tool.

At SAP TechEd at the end of October 2026, SAP said SAP Business Data Cloud (BDC) is becoming that kind of lakehouse for SAP and non-SAP data. Three pieces matter:

  • Apache Iceberg, an open table format, so SAP's data products can be read as ordinary tables by many engines.
  • Dremio, which SAP has bought, to query across sources without moving the data first.
  • SAP Databricks, the Databricks platform inside BDC, which is getting more Databricks tools, including the Genie question-answering agent, with a planned link to Joule.

For AI, the point is simple. An agent that answers questions about orders, stock or margins needs governed, current data. A lakehouse can give it one governed copy instead of a pile of extracts. But much of what SAP announced has no availability label yet, so plans should rest on what you can buy and test today.

Why it matters to the business

Take Calder Pumpworks, the course's fictional pump maker. Its US company CP10 runs S/4HANA. Its German company CP20 still runs ECC until July 2027. The group controller wants an assistant that answers "which orders are stuck, and what are they worth?" across both.

Today that usually means extracts: a nightly job copies order data from each system into a reporting platform, and a second job copies it again into whatever the AI team uses. Each copy costs money to run, drifts out of date, and needs its own access rules. When a number in the assistant disagrees with the number in SAP, nobody knows which copy is wrong.

A lakehouse changes three things:

  • One copy, many tools. If SAP publishes order data as a governed table, the reporting tool, a data science notebook and an agent can all read it. Fewer pipelines means lower run cost and fewer reconciliation arguments.
  • SAP and non-SAP data together. Stuck orders matter more next to the customer's complaints in the CRM or the carrier's delay data. Federated queries join them without building another copy.
  • Business meaning travels with the data. SAP data products carry names, keys and personal-data tags, so an agent knows that SoldToParty is the customer and that some fields are personal data.

The risks are just as concrete. Calder's customer number 10000001 is Brennock Water Utility in CP10 and a different German customer in CP20. A lakehouse that mixes both without the source system in the key will add their orders together. Open formats also make it easy to read files directly and skip the catalog's access rules. The technology doesn't remove the need for keys, definitions and permissions; it makes them more important.

How SAP does it

Status as stated on SAP and Databricks pages read on 11 November 2026. "No label" means the page describes the capability without saying GA, beta or planned. Treat those as announcements until a product page or SAP's roadmap gives a date.

Capability What it is Status on the source page
SAP Business Data Cloud SAP's data platform: data products, SAP Datasphere, SAP Analytics Cloud, SAP Databricks Sold today (see the Business Data Cloud topic)
SAP Databricks Databricks embedded in BDC; SAP data products shared into it without copying "Available today" (Databricks, 30 October)
SAP BDC Connect for Databricks Shares data products with a company's own Databricks "Available today" (same page)
Apache Iceberg and a catalog on Apache Polaris Open table format and an open catalog for BDC data BDC "now supports" both, no GA label (SAP News, 29 October); the Architecture Center's catalog page says "Coming soon"
Dremio Lakehouse query engine; federated SQL; query acceleration (Reflections) Acquisition announced 4 May 2026 and completed; capabilities without labels
SAP Application Data Foundation Makes data from SAP applications available to the BDC engine you choose, including SAP Databricks Announced 27 October; no label on SAP's pages
Genie in SAP Databricks Databricks' agent that answers data questions by writing read-only SQL "Coming to SAP Databricks" (Databricks)
Genie and Joule integration Two-way link so Genie answers and actions reach SAP experiences "Planned" (Databricks); "an integration" (SAP, 27 October)
Lakeflow, Agent Bricks, Unity Gateway, Lakebase Pipelines, agent building, AI governance and cost control, a Postgres database for apps SAP: "will help customers"; Databricks: Lakeflow, Unity Gateway and Lakebase "coming" (its post doesn't name Agent Bricks)
SAP Reltio Master data matching into golden records in BDC "Now generally available in SAP BDC" (SAP, 29 October)

SAP's own reference architecture for SAP Lakehouse, updated 10 November 2026, shows the target design: changes from SAP applications flow into managed storage, are published as Iceberg tables, and are governed by one catalog. It notes its own diagram is still "in progress". SAP publishes no separate price for these capabilities on the pages read for this topic, so ask your account team which BDC components and capacity your contract covers.

Genie, Joule or your own agent: a decision guide

The brief question leaders ask after TechEd: when do we use Genie, when Joule, and when do we build? As of November 2026 the honest answer depends on what is available, not only on what is announced.

Your need Start with Why Watch for
Business users ask about SAP processes and act in SAP (approve, change, follow up) Joule and Joule agents Joule lives in SAP applications and calls SAP's own skills and APIs Joule reads data products in BDC per SAP's announcements; confirm what your release supports
Analysts ask open data questions over SAP plus non-SAP tables in a data platform you already run on Databricks Genie on your Databricks, fed by BDC Connect BDC Connect for Databricks is available today; Genie writes read-only SQL with each user's own permissions (check its preview features for your region) Genie in SAP Databricks is "coming"; the Joule link is planned
An AI team needs features or training data from SAP data products SAP Databricks or your own platform via BDC Connect Data products arrive as tables without a custom extract Which data products exist for your modules and edition
A narrow, high-stakes question with fixed rules (stuck orders by owner, credit exposure) Your own agent with a few fixed tools You control the queries, tests and audit log You own maintenance; reuse the same governed data, not a private copy
Data from ECC or on-premise systems Ask first Databricks describes the Application Data Foundation reaching on-premises SAP data; SAP's pages give no scope yet Get the supported sources and releases in writing

Questions to ask

  • To SAP or your partner: Which of the capabilities we are discussing are generally available in our region and BDC edition today, and where is that written?
  • Which data products cover our processes and modules, and do they include our custom fields?
  • How fresh is each data product: minutes, hours or a nightly load? Who is told when a load fails?
  • Does the Application Data Foundation support our ECC or on-premise systems, and from which release?
  • To your data team: Where are access rules enforced: in the catalog, in each engine, or in the agent's code? Can someone read the files and skip them?
  • How do we keep records from two SAP systems apart when they share numbers?
  • To the AI team: Which agent answers which questions, and what is the test set that proves each answer matches SAP?

Common misconceptions

  • "Open format means anyone can read the data." Open describes the file layout. Access still depends on the catalog granting it and handing out credentials. A design where people read files directly has bypassed governance.
  • "A lakehouse replaces S/4HANA reporting." Operational questions about one document, right now, still belong in the SAP application. The lakehouse serves cross-system and historical questions.
  • "Zero-copy means no copy exists." SAP still copies changes from the application into managed storage. Zero-copy means consumers read that copy without making their own.
  • "Announced at TechEd means we can buy it." Several capabilities have no availability label. Plan on what is generally available, and pilot the rest only where SAP offers a program.
  • "Genie and Joule do the same job." Genie answers data questions in a data platform; Joule works inside SAP applications. The planned link connects them; it doesn't merge them.

Key terms

All course terms, A to Z, in the glossary

  • Lakehouse: a data platform where tables live as open files in object storage and several engines query the same copy.
  • Apache Iceberg: an open table format that turns a folder of data files into a table with a schema, snapshots and safe concurrent writes.
  • Catalog: the service that knows which tables exist, where their current version is, and who may read them. Apache Polaris is an open-source Iceberg catalog.
  • Snapshot: a saved version of a table at one point in time; reading an older snapshot is called time travel.
  • Data product: a governed, described dataset that SAP or a team publishes for others to use, such as sales order items.
  • Zero-copy sharing: letting another platform read a dataset where it is, instead of exporting a copy to it.
  • Federated query: one query that reads several sources where they sit, without loading them into one place first.
  • SAP Databricks: the Databricks platform embedded in SAP Business Data Cloud.
  • Genie: Databricks' agent that answers questions about data by generating read-only SQL.

Check yourself

Pick one answer for each question. The explanation appears after you choose.

1Calder's AI team wants an assistant over order data from S/4HANA and ECC. What does a lakehouse mainly change for them?

Answer: D. The lakehouse's value is one governed copy that reporting, data science and agents all read, which cuts pipelines and reconciliation work. Definitions, tests and write paths are still needed.

2A vendor's slide lists Dremio federation, the Polaris catalog and Genie in Joule as "SAP lakehouse features". What should a leader check first?

Answer: C. As of November 2026 several of these have no availability label on SAP's pages, and the Genie and Joule link is planned. Plans should rest on what is generally available and written down.

3Customer number 10000001 is Brennock in CP10 and a German customer in CP20. What goes wrong if both systems' orders land in one table keyed only by customer number?

Answer: B. Without the source system in the key, the same number means two customers, and totals silently merge them. Keeping the source system in every key keeps them apart.

4Which statement about "zero-copy" data products is accurate?

Answer: D. SAP copies application changes into managed storage. Zero-copy means other engines read that copy in place rather than exporting it again.

5Business users want to ask about stuck orders and then release a block in SAP. Where should the team start?

Answer: B. Acting in SAP needs SAP's own skills and APIs, which Joule uses. Genie writes read-only SQL for data questions, and its link to Joule is still planned.

6The data team proposes letting analysts read the lakehouse's Parquet files directly to save time. What is the main risk?

Answer: B. The catalog decides who may read a table and which files make up its current version. Reading files directly bypasses both, so people can see data they shouldn't and old rows that were replaced.
Deep layer · 40 min read

Mental model

A lakehouse splits a database into four layers you can choose separately: storage (files in object storage), a table format (how a folder of files becomes a table), a catalog (which tables exist, where their current version is, who may read them) and engines (anything that runs queries). SAP's reference architecture draws exactly these layers for BDC: Parquet files, published as Apache Iceberg tables, governed by an Enterprise Catalog built on Apache Polaris, read by Spark, Dremio, SAP HANA Cloud, Snowflake, BigQuery and other Iceberg-aware engines.

The idea that makes the rest click: the catalog, not the file, is the table. Files are immutable. Every change writes new files and a new metadata file, and the catalog moves one pointer to the new version. So the catalog is where you get the current version, older versions, and permission to read any of them. An agent that respects the catalog gets governed, consistent data. An agent that reads files gets duplicates and bypasses access rules. You will see both in the lab.

How it works

From SAP application to an Iceberg table

flowchart LR
  S4[S/4HANA and other<br/>SAP applications] -->|change data capture| FS[Foundation Services<br/>bronze, silver, gold]
  FS --> P[(Parquet files<br/>object storage)]
  P --- M[Iceberg metadata<br/>snapshots, schema]
  M --- C[Enterprise Catalog<br/>Apache Polaris]
  C -->|Iceberg REST, credentials| E1[Dremio SQL]
  C --> E2[HANA Cloud<br/>SQL-on-Files]
  C --> E3[Snowflake, BigQuery,<br/>Fabric]
  BDC[BDC data products] -->|Delta Sharing| DBX[SAP Databricks<br/>Unity Catalog]
  E1 --> AG[Agents and BI]
  DBX --> AG

As described on SAP's Architecture Center pages (updated 23 October and 10 November 2026):

  1. Capture. Foundation Services, SAP's managed pipeline, take changes from SAP applications by change data capture, through the Data Subscription API or the ABAP push framework.
  2. Refine. Spark processes the changes through bronze (raw), silver (cleaned) and gold (business-ready) layers.
  3. Publish. The result is stored as Parquet and exposed as Iceberg, using a feature called UniForm so the one physical copy can be read as Iceberg.
  4. Govern. The Enterprise Catalog, powered by Apache Polaris, registers data products, authorizes catalog, namespace and table access, and issues credentials scoped to the table's storage path.
  5. Consume. Engines read through the catalog's Iceberg REST interface without copying. Dremio adds federated SQL across non-SAP databases and precomputed accelerations it calls Reflections.

The route into SAP Databricks that is available today is the one the Databricks documentation describes: request a data product in the BDC Catalog, share it to SAP Databricks, and it appears as a catalog in Unity Catalog. Table and column comments, primary keys and sap.PersonalData.* tags sync with it, read-only.

What makes Iceberg a table

An Iceberg table is a tree of files. Data files hold rows (usually Parquet). Manifest files list data files with column statistics. A manifest list names the manifests of one snapshot (see the Iceberg terms page). A metadata file lists snapshots, schemas and the current snapshot. The Iceberg reliability notes describe a commit as an atomic swap of the pointer to the current metadata file. Writers work optimistically; if another writer committed first, the loser rebuilds its metadata on the new state and retries.

Three consequences matter for AI work on SAP data:

  • Time travel. Every snapshot stays readable until it is expired. "What did the order book look like at the last load?" is a query, not a backup restore.
  • Old files stay behind. An update rewrites affected data into new files. The old files stay on disk for old snapshots. Read the folder directly and you count rows twice.
  • Schema with identity. Columns have IDs, so renaming a column doesn't break readers, and identifier fields describe the business key for upserts.

How an agent should query a lakehouse

An agent doesn't need raw table access. It needs a few tools that each run a governed query:

sequenceDiagram
  participant U as User 00100266
  participant A as Agent
  participant T as Tools
  participant C as Catalog
  U->>A: Which orders are blocked?
  A->>T: read_orders(user, filter)
  T->>C: load table, current snapshot
  C-->>T: metadata, only if user is granted
  T-->>A: rows for sales org CPUS
  A-->>U: answer + snapshot + tool log

The tool adds the user's grant to every scan, records which snapshot it read, and never offers writes. Genie follows the same principle in Databricks: according to its documentation, generated queries are always read-only and data access is checked against each user's own Unity Catalog identity.

What open table formats change for extraction

Before lakehouses, AI on SAP data meant extraction: you read CDS views or tables through a released interface and loaded your own copy. Open formats and shared data products change the default:

Question With your own extraction With shared data products on a lakehouse
Who runs the pipeline? You, per system and per use SAP's Foundation Services, once
How do consumers get data? A copy per platform Read in place through the catalog or Delta Sharing
Who decides the schema? You The data product's published contract
Freshness Your schedule The data product's load cycle; ask for it
Data not offered as a product Possible, through released interfaces Still needs extraction or a custom data product
Access rules Rebuilt in each copy Defined once in the catalog, enforced per engine

Extraction doesn't disappear. You still need it for fields and systems no data product covers, which is the subject of CDS extraction pipelines for AI. What changes is that the first question becomes "does a data product already exist?", not "how do I build a pipeline?"

Build it yourself: a Calder lakehouse queried by an agent

Before you start: complete Set up your computer for this course and Set up for Unit 7. They create the orchestrate-course folder, its .venv and the unit07 folder.

You will build calder_lakehouse.py. It creates real Apache Iceberg tables on your computer with a small catalog, loads SAP-shaped sales orders from Calder's two systems (CP10 on S/4HANA and CP20 on ECC), applies a change load, and lets a small agent answer questions as a named user. You will see time travel, why reading files skips the catalog, and why two systems' customer numbers must stay apart. The data is fictional, Calder Pumpworks course data; it is not from an SAP system or the SAP sandbox.

flowchart LR
  B[build: load 1] --> T[(Iceberg tables<br/>unit07/lakehouse)]
  L[load-changes: load 2] --> T
  T --> H[history: snapshots]
  T --> F[files: raw files vs. catalog]
  T --> A[ask --as USER:<br/>agent with read-only tools]
  A --> G[agent_log.jsonl]

The data (course values):

Source Sales org Orders
S4_CP10 (S/4HANA) CPUS, USD 1000001 (delivered) and the blocked orders 1000002-1000007 from the order-to-cash topic; load 2 adds 1000011 (16 ES-200, confirmed late) and fixes 1000003
ECC_CP20 (ECC) CPDE, EUR and USD 12000417 (intercompany impeller to CP10, USD), 12000423 Brenzhof, 12000426 Ostmark, 12000428 Lindqvar (credit block)

Customer 10000001 is Brennock Water Utility in CP10 and Lindqvar Wasserwerke AG in CP20, as in the data migration topic. The users: 00100266 inside sales rep may read CPUS; 00100280 group controller may read CPUS and CPDE; 00100153 maintenance planner has no sales access.

What you need

  • Your course folder with the Unit 7 setup done. Cost: free.
  • Three libraries, all free and open source: pyiceberg (with its SQLite and PyArrow extras), pyarrow and sqlalchemy.
  • About 40 minutes. No account, no network after installing, no model calls.

Step 1: Open your course folder

  1. Open a terminal (on Windows, PowerShell; on macOS, Terminal) and turn on your environment.

    Windows (PowerShell):

    cd $HOME\orchestrate-course
    .\.venv\Scripts\Activate.ps1

    macOS/Linux:

    cd ~/orchestrate-course
    source .venv/bin/activate
  2. Check that you are in the right place:

    python --version

    You should see Python 3. followed by a version number, and (.venv) at the start of the prompt.

Step 2: Install the libraries

  1. In VS Code, open requirements.txt in your course folder, add these lines at the end and save:

    pyiceberg[sql-sqlite,pyarrow]==0.12.0
    pyarrow==26.0.1
    sqlalchemy==2.1.8
  2. Install them (same command on Windows and macOS/Linux):

    pip install -r requirements.txt
  3. Check them:

    python -c "import pyiceberg, pyarrow; print(pyiceberg.__version__, pyarrow.__version__)"

    You should see 0.12.0 26.0.1.

Step 3: Keep the lakehouse folder out of Git

The script writes the tables into unit07/lakehouse/. You can rebuild them any time, so they don't belong in your repository.

  1. In VS Code, open .gitignore in your course folder.

  2. Add this line at the end and save:

    unit07/lakehouse/

Step 4: Create the script

  1. In VS Code, right-click the unit07 folder, choose New File, name it calder_lakehouse.py, paste the code below and save.
"""Calder Pumpworks on a small local lakehouse (Unit 7).

Builds Apache Iceberg tables in a folder next to this script, loads SAP-shaped
sales orders from two source systems, applies a change load, and lets a small
agent answer questions through read-only tools, as a named user.

All data is fictional (Calder Pumpworks, course values). No account, no network.

Commands:
  build                     create the catalog and load 1 (as of 2026-10-26)
  load-changes              apply load 2 (as of 2026-10-28): one new order, one fix
  history                   list the snapshots of the order table
  files                     read the Parquet files directly and compare with the catalog
  ask --as USER "question"  ask the agent; add --load N to ask about an older load
  selftest                  build a throwaway copy and check every answer
"""
import argparse
import datetime as dt
import json
import re
import shutil
import sys
import tempfile
from decimal import Decimal
from pathlib import Path

try:
    import pyarrow as pa
    import pyarrow.compute as pc
    import pyarrow.parquet as pq
    from pyiceberg.catalog.sql import SqlCatalog
    from pyiceberg.expressions import And, EqualTo, In
    from pyiceberg.schema import Schema
    from pyiceberg.types import DateType, DecimalType, IntegerType, NestedField, StringType
except ImportError:
    sys.exit("pyiceberg or pyarrow is not installed. Run: pip install -r requirements.txt")

HOME = Path(__file__).resolve().parent / "lakehouse"

# Which source system feeds each sales organization (course values).
ORG_SYSTEM = {"CPUS": "S4_CP10", "CPDE": "ECC_CP20"}

# Who may read which sales organizations. In a real lakehouse this lives in the
# catalog's grants, not in the agent's code (course values).
GRANTS = {
    "00100266": {"role": "inside sales rep (Rockford)", "sales_orgs": ["CPUS"]},
    "00100280": {"role": "group controller", "sales_orgs": ["CPUS", "CPDE"]},
    "00100153": {"role": "maintenance planner", "sales_orgs": []},
}

ORDER_SCHEMA = Schema(
    NestedField(1, "source_system", StringType(), required=True, doc="S4_CP10 or ECC_CP20"),
    NestedField(2, "sales_org", StringType(), required=True, doc="Sales organization"),
    NestedField(3, "sales_order", StringType(), required=True, doc="Sales order number in the source"),
    NestedField(4, "item", IntegerType(), required=True, doc="Item number"),
    NestedField(5, "customer", StringType(), required=True, doc="Sold-to party number in the source"),
    NestedField(6, "material", StringType(), required=True, doc="Material number"),
    NestedField(7, "quantity", IntegerType(), required=True, doc="Order quantity, pieces"),
    NestedField(8, "net_value", DecimalType(15, 2), required=False, doc="Net value; empty when no price"),
    NestedField(9, "currency", StringType(), required=True, doc="Document currency"),
    NestedField(10, "requested_date", DateType(), required=True, doc="Requested delivery date"),
    NestedField(11, "confirmed_date", DateType(), required=False, doc="Confirmed date; empty if none"),
    NestedField(12, "problem", StringType(), required=True, doc="Course label for what blocks the order"),
    identifier_field_ids=[1, 3, 4],
)

CUSTOMER_SCHEMA = Schema(
    NestedField(1, "source_system", StringType(), required=True, doc="S4_CP10 or ECC_CP20"),
    NestedField(2, "customer", StringType(), required=True, doc="Customer number in the source"),
    NestedField(3, "name", StringType(), required=True, doc="Customer name"),
    NestedField(4, "country", StringType(), required=True, doc="Country key"),
    identifier_field_ids=[1, 2],
)

D = dt.date.fromisoformat
LOAD_1 = [
    # source, org, order, item, customer, material, qty, net value, currency, requested, confirmed, problem
    ("S4_CP10", "CPUS", "1000001", 10, "10000001", "CP-ES200", 4, "16800.00", "USD", "2026-10-09", "2026-10-09", "none"),
    ("S4_CP10", "CPUS", "1000001", 20, "10000001", "CP-ES300", 2, "12300.00", "USD", "2026-10-09", "2026-10-09", "none"),
    ("S4_CP10", "CPUS", "1000002", 10, "10000002", "CP-ES200", 6, "24288.00", "USD", "2026-10-30", None, "credit block"),
    ("S4_CP10", "CPUS", "1000003", 10, "10000001", "CP-ES300", 2, "12300.00", "USD", "2026-11-02", None, "billing data incomplete"),
    ("S4_CP10", "CPUS", "1000004", 10, "10000001", "CP-ES300", 4, "24600.00", "USD", "2026-10-27", "2026-11-06", "partly confirmed"),
    ("S4_CP10", "CPUS", "1000005", 10, "10000002", "CP-SK200", 40, None, "USD", "2026-10-28", None, "price missing"),
    ("S4_CP10", "CPUS", "1000006", 10, "10000001", "CP-ES200", 1, "4200.00", "USD", "2026-10-20", "2026-10-20", "billing block"),
    ("S4_CP10", "CPUS", "1000007", 10, "10000001", "CP-ES200", 2, "8400.00", "USD", "2026-11-09", None, "delivery block"),
    ("ECC_CP20", "CPDE", "12000417", 10, "10000010", "CP-IMP300", 1, "480.00", "USD", "2026-11-04", "2026-11-04", "none"),
    ("ECC_CP20", "CPDE", "12000423", 10, "10000004", "CP-IMP200", 12, "4920.00", "EUR", "2026-10-22", "2026-10-22", "none"),
    ("ECC_CP20", "CPDE", "12000426", 10, "10000008", "CP-CAST300", 2, "1180.00", "EUR", "2026-10-23", "2026-10-23", "none"),
    ("ECC_CP20", "CPDE", "12000428", 10, "10000001", "CP-IMP200", 6, "2460.00", "EUR", "2026-11-05", None, "credit block"),
]
LOAD_2 = [
    # Brennock's new order, confirmed late; 1000003 fixed in the source.
    ("S4_CP10", "CPUS", "1000011", 10, "10000001", "CP-ES200", 16, "67200.00", "USD", "2026-11-13", "2026-11-30", "confirmed late"),
    ("S4_CP10", "CPUS", "1000003", 10, "10000001", "CP-ES300", 2, "12300.00", "USD", "2026-11-02", "2026-11-02", "none"),
]
CUSTOMERS = [
    ("S4_CP10", "10000001", "Brennock Water Utility", "US"),
    ("S4_CP10", "10000002", "Hallam Fluid Systems", "US"),
    ("ECC_CP20", "10000001", "Lindqvar Wasserwerke AG", "DE"),
    ("ECC_CP20", "10000004", "Brenzhof Pumpentechnik GmbH", "DE"),
    ("ECC_CP20", "10000008", "Ostmark Filtertechnik GmbH", "DE"),
    ("ECC_CP20", "10000010", "Calder Pumpworks Inc. (intercompany)", "US"),
]


def order_table(rows):
    cols = list(zip(*rows))
    data = {
        "source_system": cols[0], "sales_org": cols[1], "sales_order": cols[2], "item": cols[3],
        "customer": cols[4], "material": cols[5], "quantity": cols[6],
        "net_value": [Decimal(v) if v else None for v in cols[7]], "currency": cols[8],
        "requested_date": [D(v) for v in cols[9]],
        "confirmed_date": [D(v) if v else None for v in cols[10]], "problem": cols[11],
    }
    return pa.Table.from_pydict(data, schema=ORDER_SCHEMA.as_arrow())


def customer_table():
    cols = list(zip(*CUSTOMERS))
    names = ["source_system", "customer", "name", "country"]
    return pa.Table.from_pydict(dict(zip(names, cols)), schema=CUSTOMER_SCHEMA.as_arrow())


def catalog(home=None):
    home = home or HOME
    home.mkdir(parents=True, exist_ok=True)
    return SqlCatalog("calder", uri=f"sqlite:///{(home / 'catalog.db').as_posix()}", warehouse=home.as_posix())


def build(home=None):
    home = home or HOME
    if home.exists():
        shutil.rmtree(home)
    cat = catalog(home)
    cat.create_namespace("sales")
    orders = cat.create_table("sales.sales_order_items", schema=ORDER_SCHEMA)
    orders.append(order_table(LOAD_1), snapshot_properties={"calder.load": "1", "calder.as_of": "2026-10-26"})
    customers = cat.create_table("sales.customers", schema=CUSTOMER_SCHEMA)
    customers.append(customer_table(), snapshot_properties={"calder.load": "1", "calder.as_of": "2026-10-26"})
    return cat


def load_changes(home=None):
    cat = catalog(home)
    orders = cat.load_table("sales.sales_order_items")
    if len(orders.snapshots()) > 1:
        return None
    return orders.upsert(order_table(LOAD_2), snapshot_properties={"calder.load": "2", "calder.as_of": "2026-10-28"})


def loads(table):
    """Snapshots that carry a load number, oldest first: [(load, as_of, snapshot_id)]."""
    out = []
    for snap in table.snapshots():
        props = snap.summary.additional_properties if snap.summary else {}
        if "calder.load" in props:
            out.append((int(props["calder.load"]), props["calder.as_of"], snap.snapshot_id))
    # An upsert can write two snapshots (delete, then append); keep the last one per load.
    return sorted({n: (n, a, s) for n, a, s in out}.values())


# ---------------------------------------------------------------- tools
class Tools:
    """The only things the agent can do. Every call is checked and logged."""

    def __init__(self, cat, user, log_path):
        self.cat, self.user, self.log_path = cat, user, log_path
        self.orgs = GRANTS.get(user, {}).get("sales_orgs", [])
        self.calls = []

    def _log(self, tool, **detail):
        entry = {"user": self.user, "tool": tool, **detail}
        self.calls.append(entry)
        with open(self.log_path, "a", encoding="utf-8") as f:
            f.write(json.dumps(entry, default=str) + "\n")

    def list_tables(self):
        self._log("list_tables")
        return [".".join(t) for t in self.cat.list_tables("sales")]

    def list_loads(self):
        self._log("list_loads")
        return loads(self.cat.load_table("sales.sales_order_items"))

    def read_orders(self, load=None, extra=None):
        """Read order items the user may see, optionally as of an earlier load."""
        table = self.cat.load_table("sales.sales_order_items")
        history = loads(table)
        snap = history[-1] if load is None else next((h for h in history if h[0] == load), None)
        if snap is None:
            raise ValueError(f"no load {load}; loads are {[h[0] for h in history]}")
        row_filter = In("sales_org", self.orgs)          # the user's grant, always applied
        if extra is not None:
            row_filter = And(row_filter, extra)
        rows = table.scan(row_filter=row_filter, snapshot_id=snap[2]).to_arrow()
        self._log("read_orders", load=snap[0], snapshot_id=snap[2], filter=str(row_filter), rows=rows.num_rows)
        return rows, snap

    def read_customers(self):
        systems = [ORG_SYSTEM[o] for o in self.orgs]
        rows = self.cat.load_table("sales.customers").scan(row_filter=In("source_system", systems)).to_arrow()
        self._log("read_customers", systems=systems, rows=rows.num_rows)
        return {(r["source_system"], r["customer"]): r["name"] for r in rows.to_pylist()}


# ---------------------------------------------------------------- agent
def plan(question):
    """Rule-based planner standing in for a model: pick one intent from the question."""
    q = question.lower()
    number = re.search(r"\b(\d{8})\b", q)
    if re.search(r"\b(chang\w*|since|new)\b", q):
        return "changes", None
    if number:
        return "customer_orders", number.group(1)
    if re.search(r"\b(block\w*|stuck|problem\w*)\b", q):
        return "blocked", None
    if re.search(r"\b(value|worth|total)\b", q):
        return "value_by_customer", None
    return "unknown", None


def money(value, currency):
    return "no price" if value is None else f"{value:,.2f} {currency}"


def answer(question, user, home=None, load=None):
    home = home or HOME
    if not (home / "catalog.db").exists():
        sys.exit("No lakehouse yet. Run: python unit07/calder_lakehouse.py build")
    if user not in GRANTS:
        return f"Refused: {user} is not a known user."
    tools = Tools(catalog(home), user, home / "agent_log.jsonl")
    if not tools.orgs:
        tools._log("refused", reason="no sales organization granted")
        return f"Refused: {user} ({GRANTS[user]['role']}) has no read access to sales orders."
    intent, arg = plan(question)
    names = tools.read_customers()
    lines = []

    if intent == "unknown":
        lines.append("I can answer: blocked orders, orders for a customer number, value by customer, "
                     "and what changed since the last load. Tables I can read: " + ", ".join(tools.list_tables()))

    elif intent == "blocked":
        rows, snap = tools.read_orders(load, extra=None)
        stuck = [r for r in rows.to_pylist() if r["problem"] != "none"]
        lines.append(f"{len(stuck)} order items with a problem (load {snap[0]}, as of {snap[1]}):")
        for r in sorted(stuck, key=lambda r: (r["source_system"], r["sales_order"])):
            who = names.get((r["source_system"], r["customer"]), "?")
            lines.append(f"  {r['source_system']:8} {r['sales_order']:>8}/{r['item']}  {who:28} "
                         f"{r['problem']:24} {money(r['net_value'], r['currency'])}")

    elif intent == "customer_orders":
        rows, snap = tools.read_orders(load, extra=EqualTo("customer", arg))
        found = rows.to_pylist()
        systems = sorted({r["source_system"] for r in found})
        if not found:
            lines.append(f"No orders for customer {arg} in the sales organizations you can read.")
        if len(systems) > 1:
            lines.append(f"Warning: customer number {arg} exists in {len(systems)} systems and means "
                         "different customers. Answering per system; never add these together.")
        for system in systems:
            mine = [r for r in found if r["source_system"] == system]
            lines.append(f"{system} {arg} {names.get((system, arg), '?')}: {len(mine)} item(s) (load {snap[0]})")
            for r in sorted(mine, key=lambda r: (r["sales_order"], r["item"])):
                lines.append(f"  {r['sales_order']}/{r['item']} {r['quantity']} x {r['material']}  "
                             f"{money(r['net_value'], r['currency'])}  {r['problem']}")

    elif intent == "value_by_customer":
        rows, snap = tools.read_orders(load)
        totals = {}
        for r in rows.to_pylist():
            key = (r["source_system"], r["customer"], r["currency"])
            totals[key] = totals.get(key, Decimal("0")) + (r["net_value"] or Decimal("0"))
        lines.append(f"Order value by customer and currency (load {snap[0]}, as of {snap[1]}); "
                     "currencies are never added together:")
        for (system, cust, cur), value in sorted(totals.items(), key=lambda kv: -kv[1]):
            lines.append(f"  {system:8} {cust} {names.get((system, cust), '?'):36} {value:>12,.2f} {cur}")
        if pc.sum(pc.is_null(rows["net_value"]).cast("int8")).as_py():
            lines.append("  Note: some items have no price and count as 0.00 here.")

    elif intent == "changes":
        history = tools.list_loads()
        if len(history) < 2:
            lines.append("Only one load so far: nothing to compare. Run load-changes first.")
        else:
            newer = history[-1][0] if load is None else load
            older = newer - 1
            if older < 1:
                lines.append("Load 1 is the first load: nothing earlier to compare with.")
            else:
                new_rows, s_new = tools.read_orders(newer)
                old_rows, s_old = tools.read_orders(older)
                key = lambda r: (r["source_system"], r["sales_order"], r["item"])
                old = {key(r): r for r in old_rows.to_pylist()}
                new = {key(r): r for r in new_rows.to_pylist()}
                lines.append(f"Changes from load {s_old[0]} ({s_old[1]}) to load {s_new[0]} ({s_new[1]}):")
                for k in sorted(new.keys() - old.keys()):
                    r = new[k]
                    lines.append(f"  new      {k[0]} {k[1]}/{k[2]}  {r['quantity']} x {r['material']}  "
                                 f"{money(r['net_value'], r['currency'])}  {r['problem']}")
                for k in sorted(old.keys() - new.keys()):
                    lines.append(f"  removed  {k[0]} {k[1]}/{k[2]}")
                for k in sorted(new.keys() & old.keys()):
                    diff = [f"{c}: {old[k][c]} -> {new[k][c]}" for c in new[k] if new[k][c] != old[k][c]]
                    if diff:
                        lines.append(f"  changed  {k[0]} {k[1]}/{k[2]}  " + "; ".join(diff))

    reads = [c for c in tools.calls if c["tool"].startswith("read_")]
    lines.append(f"[{GRANTS[user]['role']}; sales orgs {', '.join(tools.orgs)}; tool calls: "
                 + ", ".join(c["tool"] for c in tools.calls) + f"; {len(reads)} reads logged]")
    return "\n".join(lines)


# ---------------------------------------------------------------- other commands
def history(home=None):
    table = catalog(home).load_table("sales.sales_order_items")
    print("snapshot id           time (UTC)           operation  load  as of")
    for snap in table.snapshots():
        props = snap.summary.additional_properties if snap.summary else {}
        when = dt.datetime.fromtimestamp(snap.timestamp_ms / 1000, dt.timezone.utc).strftime("%Y-%m-%d %H:%M:%S")
        op = snap.summary.operation.value if snap.summary else "?"
        print(f"{snap.snapshot_id:<21} {when}  {op:<10} {props.get('calder.load', '-'):<5} {props.get('calder.as_of', '-')}")


def files(home=None):
    home = home or HOME
    table = catalog(home).load_table("sales.sales_order_items")
    raw = pa.concat_tables(pq.read_table(p) for p in sorted((home / "sales" / "sales_order_items" / "data").rglob("*.parquet")))
    through_catalog = table.scan().to_arrow()
    print(f"Parquet files on disk:           {len(list((home / 'sales' / 'sales_order_items' / 'data').rglob('*.parquet')))}")
    print(f"Rows reading every file directly: {raw.num_rows}")
    print(f"Rows reading through the catalog: {through_catalog.num_rows}")
    keys = [f"{r['source_system']} {r['sales_order']}/{r['item']}" for r in raw.select(["source_system", "sales_order", "item"]).to_pylist()]
    dupes = sorted({k for k in keys if keys.count(k) > 1})
    print(f"Order items found more than once in the raw files: {len(dupes)} of {through_catalog.num_rows}")
    print("Read Iceberg tables through the catalog: only it knows which files are current.")
    return raw.num_rows, through_catalog.num_rows, dupes


def selftest():
    home = Path(tempfile.mkdtemp()) / "lakehouse"
    checks = []
    build(home)
    a = answer("Which orders are blocked?", "00100266", home)
    checks.append(("rep sees 6 CPUS problems in load 1", "6 order items with a problem" in a and "ECC_CP20" not in a))
    a = answer("Which orders are blocked?", "00100280", home)
    checks.append(("controller also sees the CP20 credit block", "7 order items with a problem" in a))
    a = answer("Show orders for customer 10000001", "00100280", home)
    checks.append(("customer number in two systems is flagged", "exists in 2 systems" in a and "Lindqvar" in a))
    a = answer("Which orders are blocked?", "00100153", home)
    checks.append(("planner is refused", a.startswith("Refused")))
    load_changes(home)
    a = answer("What changed since the last load?", "00100266", home)
    checks.append(("change load shows 1000011 new and 1000003 fixed", "new      S4_CP10 1000011/10" in a and "problem: billing data incomplete -> none" in a))
    a = answer("Which orders are blocked?", "00100266", home, load=1)
    checks.append(("time travel to load 1 still shows 6", "6 order items with a problem" in a))
    raw_rows, cat_rows, dupes = files(home)
    checks.append(("raw files hold an old copy of 1000003", "S4_CP10 1000003/10" in dupes and cat_rows == 13))
    print()
    for name, ok in checks:
        print(("PASS " if ok else "FAIL ") + name)
    shutil.rmtree(home.parent, ignore_errors=True)
    if not all(ok for _, ok in checks):
        sys.exit(1)


def main():
    parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    parser.add_argument("command", choices=["build", "load-changes", "history", "files", "ask", "selftest"])
    parser.add_argument("question", nargs="?", help="question for the agent (with ask)")
    parser.add_argument("--as", dest="user", default="00100266", help="personnel number of the asking user")
    parser.add_argument("--load", type=int, help="answer as of an earlier load (time travel)")
    args = parser.parse_intermixed_args()  # allows options before or after the question

    if args.command == "build":
        build()
        print(f"Built {HOME} with load 1 (as of 2026-10-26): 12 order items, 6 customers.")
    elif args.command == "load-changes":
        result = load_changes()
        if result is None:
            print("Load 2 is already in. Run build to start again.")
        else:
            print(f"Load 2 (as of 2026-10-28): {result.rows_inserted} inserted, {result.rows_updated} updated.")
    elif args.command == "history":
        history()
    elif args.command == "files":
        files()
    elif args.command == "ask":
        if not args.question:
            sys.exit('Add a question in quotes, e.g. ask --as 00100266 "Which orders are blocked?"')
        print(answer(args.question, args.user, load=args.load))
    elif args.command == "selftest":
        selftest()


if __name__ == "__main__":
    main()

Step 5: Build the lakehouse and look at its history

  1. Build the tables with load 1, as of 2026-10-26:

    python unit07/calder_lakehouse.py build
    Built C:\Users\you\orchestrate-course\unit07\lakehouse with load 1 (as of 2026-10-26): 12 order items, 6 customers.

    The path shows your own folder.

  2. Look at the snapshots:

    python unit07/calder_lakehouse.py history
    snapshot id           time (UTC)           operation  load  as of
    3916481568835767410   2026-11-11 21:22:22  append     1     2026-10-26

    Your snapshot ID and time differ: Iceberg picks a random ID and records when you ran it. The load number and "as of" date are properties the script writes into the snapshot.

  3. Open the unit07/lakehouse/sales/sales_order_items folder in VS Code's file explorer. You will see a data folder with a Parquet file and a metadata folder with JSON and Avro files. That is the whole table: files, plus a pointer in catalog.db.

Step 6: Ask the agent as different users

  1. Ask as the inside sales rep (the default user):

    python unit07/calder_lakehouse.py ask "Which orders are blocked?"
    6 order items with a problem (load 1, as of 2026-10-26):
      S4_CP10   1000002/10  Hallam Fluid Systems         credit block             24,288.00 USD
      S4_CP10   1000003/10  Brennock Water Utility       billing data incomplete  12,300.00 USD
      S4_CP10   1000004/10  Brennock Water Utility       partly confirmed         24,600.00 USD
      S4_CP10   1000005/10  Hallam Fluid Systems         price missing            no price
      S4_CP10   1000006/10  Brennock Water Utility       billing block            4,200.00 USD
      S4_CP10   1000007/10  Brennock Water Utility       delivery block           8,400.00 USD
    [inside sales rep (Rockford); sales orgs CPUS; tool calls: read_customers, read_orders; 2 reads logged]
  2. Ask the same question as the group controller. You should see 7 items: the six above plus ECC_CP20 12000428/10 for Lindqvar.

    python unit07/calder_lakehouse.py ask --as 00100280 "Which orders are blocked?"
  3. Ask about customer 10000001 as the group controller:

    python unit07/calder_lakehouse.py ask --as 00100280 "Show orders for customer 10000001"
    Warning: customer number 10000001 exists in 2 systems and means different customers. Answering per system; never add these together.
    ECC_CP20 10000001 Lindqvar Wasserwerke AG: 1 item(s) (load 1)
      12000428/10 6 x CP-IMP200  2,460.00 EUR  credit block
    S4_CP10 10000001 Brennock Water Utility: 6 item(s) (load 1)
      1000001/10 4 x CP-ES200  16,800.00 USD  none
      ...
  4. Ask for values by customer as the group controller. Totals are per system, customer and currency; EUR and USD are never added together:

    python unit07/calder_lakehouse.py ask --as 00100280 "What is the total value by customer?"
  5. Ask as the maintenance planner. The agent refuses before it reads anything:

    python unit07/calder_lakehouse.py ask --as 00100153 "Which orders are blocked?"
    Refused: 00100153 (maintenance planner) has no read access to sales orders.
  6. Ask something outside its tools, such as "Delete order 1000002". It lists what it can answer. It has no tool that writes, so no wording can make it change data.

Step 7: Apply a change load and travel in time

  1. Apply load 2, as of 2026-10-28:

    python unit07/calder_lakehouse.py load-changes
    Load 2 (as of 2026-10-28): 1 inserted, 1 updated.
  2. Ask what changed:

    python unit07/calder_lakehouse.py ask "What changed since the last load?"
    Changes from load 1 (2026-10-26) to load 2 (2026-10-28):
      new      S4_CP10 1000011/10  16 x CP-ES200  67,200.00 USD  confirmed late
      changed  S4_CP10 1000003/10  confirmed_date: None -> 2026-11-02; problem: billing data incomplete -> none
    [inside sales rep (Rockford); sales orgs CPUS; tool calls: read_customers, list_loads, read_orders, read_orders; 3 reads logged]
  3. Ask about blocked orders now, then as of load 1:

    python unit07/calder_lakehouse.py ask "Which orders are blocked?"
    python unit07/calder_lakehouse.py ask "Which orders are blocked?" --load 1

    The first shows 6 items (1000003 is gone, 1000011 is new). The second still shows the original 6. Nothing was restored from a backup: the agent read an older snapshot.

  4. Run history again. Load 2 shows up as several snapshots (an overwrite and append steps), because an upsert is written as more than one commit. The script uses the last snapshot of each load.

Step 8: See why the catalog is the table

  1. Read the raw files and compare:

    python unit07/calder_lakehouse.py files
    Parquet files on disk:           4
    Rows reading every file directly: 25
    Rows reading through the catalog: 13
    Order items found more than once in the raw files: 12 of 13
    Read Iceberg tables through the catalog: only it knows which files are current.

    The upsert rewrote the data into new files and kept the old ones for load 1. Anyone who points a tool at the folder instead of the catalog counts most orders twice and also skips the sales organization grants.

  2. Open unit07/lakehouse/agent_log.jsonl. Each line records the user, the tool, the filter and, for order reads, the snapshot ID. This is the audit trail a reviewer will ask for.

Step 9: Run the self-test and save your work

  1. Run the checks. They build a throwaway copy, so your own tables stay as they are:

    python unit07/calder_lakehouse.py selftest

    The last seven lines should all start with PASS.

  2. Save your work:

    git add .gitignore requirements.txt unit07/calder_lakehouse.py
    git commit -m "Unit 7: Calder orders on a local Iceberg lakehouse with an agent"

What the code does

Part of the script What it does
ORDER_SCHEMA, CUSTOMER_SCHEMA Iceberg schemas with documented columns; the identifier fields (source system, order, item) are the business key
LOAD_1, LOAD_2, CUSTOMERS Fictional Calder data from two systems; load 2 adds an order and fixes one
catalog() A PyIceberg SQL catalog stored in SQLite, with a local folder as the warehouse
build(), load_changes() Create the namespace and tables, append load 1, upsert load 2; each commit carries calder.load and calder.as_of properties
loads() Maps load numbers to snapshot IDs, keeping the last snapshot of each load
GRANTS, ORG_SYSTEM Which user may read which sales organizations, and which system feeds each one
Tools The only actions the agent has: list tables, list loads, read orders, read customers. Every read adds the user's grant as a row filter and is logged
plan() Picks one intent from the question with simple rules, standing in for a model
answer() Runs the intent with the tools; keeps systems and currencies apart; compares two snapshots for "what changed"
files() Reads every Parquet file directly to show what skipping the catalog does
selftest() Builds a temporary lakehouse and checks seven answers

If something goes wrong

What you see What it means What to do
python is not recognized, or command not found Python isn't on the path, or the environment is off Repeat Step 1; on macOS/Linux try python3. See Set up your computer
pyiceberg or pyarrow is not installed The libraries are missing in this .venv Check for (.venv) in the prompt, then pip install -r requirements.txt
pip fails with a proxy or SSL error The company network blocks the package index Try another network, or ask IT for the proxy settings for pip
pip can't find a matching version Your Python is older or newer than the pinned wheels support Check python --version; the course uses Python 3.14. Ask in the course Q&A with the full error
No lakehouse yet You ran ask before build Run python unit07/calder_lakehouse.py build
Only one load so far You asked what changed before load 2 Run load-changes
Load 2 is already in You ran load-changes twice Run build to start again
no load 3 --load names a load that doesn't exist Use --load 1 or --load 2
PermissionError or a locked catalog.db on Windows VS Code or another terminal has the file open Close other terminals using the folder, then run build again

The SAP way

SAP Business Data Cloud as a lakehouse

As of 11 November 2026, SAP's 29 October article says BDC "now supports" native Apache Iceberg and a universal catalog powered by Apache Polaris, and that the Dremio acquisition is complete. It gives no GA or beta label for these. SAP's 27 October release describes the same work in progressive and future tense: SAP "is expanding its lakehouse capabilities with Dremio", and the federated engine "will enable" access without copying. On the SAP Architecture Center, the SAP Lakehouse page describes the design, while the Enterprise Catalog page, updated 27 October and marked as an unlisted page, says only "Coming soon". Read these together as direction plus partial availability, and confirm with SAP for your tenant.

When SAP announced the purchase on 4 May 2026, it said the planned catalog would be built on Apache Polaris and the open Iceberg REST Catalog API and would act as BDC's discovery and semantic layer, and that SAP HANA Cloud keeps the real-time transactional role. The Data Products in SAP Lakehouse page lists the implementation sequence: provision BDC through SAP for Me, connect source applications, activate data products, then install Intelligent Content.

Data products

SAP-managed data products are produced automatically from SAP applications and described in Open Resource Discovery, as covered in SAP Knowledge Graph and Business Data Cloud. The lakehouse pages add four tiers: primary SAP products, primary non-SAP products published in place through Dremio, unified "360" products built on Reltio golden records (Reltio is GA in BDC per SAP's 29 October article), and derived products for a use case. AI results can be written back as derived products, for example predictive scores from tabular models run in SAP AI Core.

SAP Databricks, Genie and Joule

SAP Databricks and SAP BDC Connect for Databricks are "available today", per Databricks' 30 October post. The same post lists Genie, Lakeflow, Lakebase and Unity Gateway as coming to SAP Databricks, and calls the two-way Genie and Joule integration planned. A shared data product becomes a Unity Catalog catalog, and SAP's personal-data tags arrive with it, so you can use them in attribute-based access policies (Databricks documentation). Genie agents, formerly Genie spaces, answer with read-only SQL and run each question against the asking user's Unity Catalog permissions (Genie concepts).

SAP Application Data Foundation

SAP's 27 October release names "a new SAP Application Data Foundation" with deeper BDC integration and no detail; the 29 October article adds only that it makes data from SAP applications available for consumption. Databricks describes it as making data from SAP applications available to the BDC engine a customer chooses, including SAP Databricks, and gives an example of a company on SAP ECC on-premises bringing order history into SAP Databricks. No SAP page read for this topic states its status, supported sources or releases. Treat ECC support as a question for SAP, not a plan.

Licensing notes

The pages read for this topic give no separate price for Iceberg access, Dremio or the Application Data Foundation. When your own Databricks, Snowflake or BigQuery reads shared data, the queries run on that platform's compute and its bill. Ask which engines' reads count against which contract before you design for "zero-copy" savings.

Build vs. SAP

Situation Build your own Use SAP's lakehouse
Prototype or teach the pattern Local Iceberg, as in this lab Overkill
SAP data for AI where SAP data products exist Rebuilding a pipeline duplicates SAP's work Data products shared to SAP Databricks or your platform
Fields or systems no data product covers Extraction through released interfaces into your own tables Ask whether a custom data product or the Application Data Foundation covers it
Company already runs Databricks or Snowflake Your platform stays the home BDC Connect shares data products in without a copy
Governance across SAP and non-SAP data Hard: one catalog per platform The Enterprise Catalog is designed for it; confirm availability first
Agent questions inside SAP apps Not worth it Joule, reading governed data

Architecture view

Where it sits. For Calder, CP10's S/4HANA and CP20's ECC feed BDC; data products sit in managed storage under one catalog; Datasphere, SAP Databricks and agents read them. The flow diagram in How it works shows the layers.

Non-functional requirements.

Requirement Typical target What breaks if missed
Access Grants in the catalog match SAP roles by sales organization and company code; no direct file access Users see other companies' orders through a side door
Freshness Stated per data product, for example hourly for order status The agent calls an order blocked hours after it was released
Keys Source system in every business key Two systems' customers merge in totals
Volume and cost Queries scan partitions, not whole tables; reads by external engines budgeted Agent questions trigger full scans billed by the engine
Traceability Each answer records table, snapshot and filter Nobody can reproduce a disputed answer

Integration and identity. Data enters through SAP's Foundation Services, not through your own connections to the application. Engines authenticate to the catalog, which checks grants and issues credentials scoped to the table's storage path, per the SAP Lakehouse page. Agents call tools that run as, or on behalf of, the signed-in user; Genie already evaluates each user's own Unity Catalog identity.

The decision to record. In the short form from Writing a solution design doc:

  • Context: Calder's group controller wants an assistant for stuck orders across CP10 and CP20. The reporting decision puts both into Datasphere in BDC (CP10 by CDS extraction, CP20 by SLT until July 2027). Iceberg access, the Polaris catalog, the Application Data Foundation and Genie in SAP Databricks have no GA label on SAP or Databricks pages as of November 2026.
  • Options: A, the agent reads the same governed data in BDC that reporting uses; B, share data products to SAP Databricks and wait for Genie there; C, wait for the Iceberg catalog and Dremio; D, a private Iceberg copy filled by the AI team.
  • Decision: A, with read-only tools that add the user's grants and log the snapshot. Revisit B and C when SAP publishes GA status and ECC coverage for the Application Data Foundation. D only for prototypes on fictional data.
  • Consequences: one copy of the truth and one place for access rules; the agent is limited to what the data products and models contain; a later move to Genie or Iceberg REST changes the engine, not the data or the grants.

What a design review will ask.

  1. Which of the components you depend on are generally available in our region, and where is that written?
  2. Where are access rules enforced, and can anyone read the storage directly?
  3. How fresh is each data product, and how does the agent say so in its answers?
  4. How are CP10 and CP20 records kept apart, and how is currency handled?
  5. Which engine's compute does each agent question use, and who pays for it?

Production concerns

  • Authorizations. Map SAP authorization concepts (sales organization, company code, plant) to catalog grants and row filters, and test them with the same users. A grant check in the agent's code, as in the lab, is a backstop; the catalog or engine must enforce it too.
  • Personal data. Data products carry personal-data tags into Unity Catalog. Use them in masking policies before an agent sees contact names or bank details.
  • Evaluation. Build a test set of questions with answers checked against SAP's own reports for the same snapshot. Record the snapshot in every answer so failures can be reproduced.
  • Cost. Lakehouse engines bill for scans. Give agents narrow tools with filters and column lists instead of free SQL over whole tables.
  • Operations. Snapshots accumulate; somebody must own expiry, and expiry ends time travel to those versions. Monitor load failures and surface "data as of" in the agent's answer.
  • Clean core. The lakehouse reads data through SAP's managed pipeline, so it adds no code in S/4HANA. Your own extraction for gaps must use released interfaces.

Pitfalls

  • Treating announcements as features. Designing on Genie in Joule or the Polaris catalog before they have a GA label.
  • Reading files instead of tables. Duplicates and bypassed grants, as Step 8 showed.
  • Keys without the source system. Totals that merge two customers, typical during an ECC to S/4HANA migration.
  • Adding currencies. An agent that sums EUR and USD gives a confident, wrong number.
  • Ignoring freshness. Answering "is this order still blocked?" from yesterday's load without saying so.
  • Free SQL for agents. One question can scan everything and cost more than the answer is worth.

Exercise

Extend the lab so the Augsburg team gets its own view.

  1. Add a user 00200045, an Augsburg sales clerk (a course value), to GRANTS with sales organization CPDE only.
  2. Ask "Which orders are blocked?" as 00200045. You should see only 12000428.
  3. Add a third load, as of 2026-10-30, in which Lindqvar's credit block on 12000428 is released (problem none, confirmed date 2026-11-05). Add a load-changes-3 command or make load_changes() take a load number.
  4. Ask "What changed since the last load?" as 00200045 and as 00100266. The clerk should see the change; the inside sales rep should see no changes, because CPDE is outside the rep's grant.
  5. Add a check for both answers to selftest().

Done when selftest prints only PASS lines, including your two new checks, and agent_log.jsonl shows the clerk's reads filtered to CPDE. Keep the script: the grant model and snapshot logging feed the agent permissions work in Unit 11.

Going further

For readers who want the table format and catalog protocols behind SAP's lakehouse.

  • The Iceberg table specification defines snapshots, manifests, schema evolution by column ID and row-level deletes. It explains why an update leaves old files behind and how expiry removes them. Start from the reliability notes, which explain the atomic commit and the retry rules for concurrent writers.
  • PyIceberg's API (py.iceberg.apache.org) shows the same operations you used, plus inspection tables for snapshots and files, partition evolution and REST catalog configuration. Pointing the lab's catalog() at a REST catalog instead of SQLite is a one-function change, which is the practical meaning of an open catalog API.
  • Genie's tuning model (Genie concepts) uses a knowledge store of synonyms, joins and example SQL. Compare it with the semantic layer in Asking SAP data questions in plain language: both work because someone writes down what the business terms mean.

A hard edge case worth thinking through: an upsert keyed on order and item handles changes, but not an item deleted in SAP. A change feed must carry deletions explicitly, or the lakehouse keeps orders that no longer exist. Check how each data product signals deletions before an agent reports on "open" items.

Check yourself

Pick one answer for each question. The explanation appears after you choose.

1In SAP's lakehouse design, what decides which files make up the current version of a table and who may read it?

Answer: D. Iceberg commits move one pointer to a new metadata file, and the catalog keeps that pointer and the access grants. Files are immutable and say nothing about which version is current.

2After load 2, reading every Parquet file in the lab returns 25 rows but the catalog returns 13. Why?

Answer: B. Updates rewrite affected rows into new files while old files stay for older snapshots. Only the catalog's metadata says which files are current, so the raw read counts most items twice.

3How does the lab's agent stop the inside sales rep from seeing CP20 orders?

Answer: A. The read_orders tool always combines the user's granted sales organizations with any other filter, so no question can widen the scan. In production the catalog or engine must enforce the same rule as well.

4The group controller asks for the total value of customer 10000001. What should a correct agent do?

Answer: A. In Calder's data, 10000001 is Brennock in CP10 and Lindqvar in CP20. Keeping the source system in the key and answering per system avoids merging two customers, and currencies stay separate too.

5A user disputes yesterday's answer that order 1000003 was blocked. What lets you reproduce it?

Answer: D. Each read logs the snapshot it used, and Iceberg can scan that snapshot later. Asking again today reads the current version, where 1000003 is already fixed.

6As of November 2026, which statement about Genie and Joule matches the Databricks and SAP pages?

Answer: B. Databricks' 30 October post says SAP Databricks is available today, lists Genie as coming to it and calls the two-way Genie and Joule integration planned. Genie's own documentation says its generated queries are read-only.

7Your project wants AI on ECC order history through the Application Data Foundation. What should you do first?

Answer: A. SAP's pages announce the Application Data Foundation without status or scope; the ECC example comes from Databricks. Written confirmation from SAP turns an announcement into something you can plan on.

8Which change best reduces cost and risk when an agent queries a lakehouse engine?

Answer: B. Narrow tools keep scans small, apply grants on every call and are easy to test. Free SQL can scan whole tables, and copies or file reads lose the catalog's governance.

Sources

Sign in to track your progress

We'll email you a one-time sign-in link. No password needed.

or

Tell us a little about you

Optional, every field. It helps us pitch answers to your questions at the right level and decide which topics to write next. It is never shown publicly, and you can change or clear it anytime from the account menu.

SAP areas you work in