aidp-federate

# `aidp-federate` — cross-source federation in one Spark session Blend multiple sources by reading each into one Spark session and joining them — a signature differentiator (competitor agents degrade on foreign tables or need an external orchestrator + manual key-joins). Execution is via the bundled `$HOME/.aidp/aidp_sql.py` helper (no MCP, no AIDP_SESSION required). ## When to use - "Join data from <source A> and <source B>", "combine Fusion + the lakehouse", "blend Snowflake with store_sales", any cross-source analysis. - **Also the entry point for a SINGLE external source** ("a notebook that connects to Fusion / EPM / ADB / …"): the connection recipe still comes from the **AIDP Spark Connectors** bundle's `aidp-<source>` skill (step 1) — this skill just adds the join. **Never hand-roll the source connection.** (Its `oracle_ai_data_platform_connectors` helper package installs once to `/Workspace/Shared` via that bundle's **`aidp-connectors-bootstrap`** skill.) ## How it works (composition, not new surface) 1. **Source reads → spark-connectors plugin.** For each external source, use the matching connector skill from `oracle-ai-data-platform-workbench-spark-connectors` (e.g. `aidp-alh`/`aidp-oracle-db`/`aidp-exacs`, `aidp-fusion-bicc`/`aidp-fusion-rest`, `aidp-snowflake`, `aidp-aws-s3`, `aidp-object-storage`, …) to get the `spark.read.format(...).option(...).load()` recipe for each source. This skill does **not** re-implement connectors. 2. **Join in one session.** Run a single cell that reads each source into a DataFrame, registers each as a temp view, and `spark.sql(...)` the join — all in the **same** Spark session created by the helper. Use join keys from `.aidp/semantic.md` / `.aidp/catalog.md` (don't guess). The helper mints a UPST from the api_key DEFAULT profile and auto-creates a scratch notebook on the target cluster: ```bash python "$HOME/.aidp/aidp_sql.py" \ --region <region> --datalake <DATALAKE_OCID> --workspace <ws> --cluster <cluster-key> \ --code " df_lake = spark.table('default.default.customer') df_ext = spark.read.format('...').option('...', '...').load() # recipe from the connector skill df_lake.createOrReplaceTempView('lake') df_ext.createOrReplaceTempView('ext') spark.sql('''SELECT ... FROM lake l JOIN ext e ON l.key = e.key ...''').show(50, truncate=False) " ``` Returns JSON `{status, execution_count, outputs, spark_job_ids, error}`. Read the SQL/Spark error from the `error` field and fix grounded in the catalog — don't guess repeatedly. Smoke-test connectivity first with `--code "spark.sql('SELECT 1').show()"`. 3. **Present** the blended result; optionally persist as a table (`aidp-ingest-file-to-table` / `manage-tables`) or save the join via `aidp-verified-queries`. ## Honesty / framing (no-fabrication) - Describe this as **"federate in one Spark session"** (read each source into Spark, then join) — that is literally what the helper does: every source read and the join run in the single Spark session of one `aidp_sql.py` invocation. Do **not** claim *single-query pushdown federation across heterogeneous sources* unless verified live on the target environment — that capability is an open question, so don't overstate it. - Keep all reads in **one** invocation/cell so they share the session; a second `aidp_sql.py` call is a fresh session and the earlier temp views are gone. - Mind volume: large external reads should be filtered/pushed down at the source where the connector supports it; otherwise sample. Ensure the cluster is RUNNING before executing (see `aidp-cluster-ops`). ## References - spark-connectors plugin (source reads) · [$HOME/.aidp/aidp_sql.py]($HOME/.aidp/aidp_sql.py) — bundled Spark-SQL executor - [references/semantic-model.md]($HOME/.aidp/references/semantic-model.md) · [references/no-mcp-rest-map.md]($HOME/.aidp/references/no-mcp-rest-map.md) · pairs with `aidp-analyzing-data`