{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sources from connectors\n",
    "\n",
    "From a warehouse table to a causal model without leaving Python: register a\n",
    "connector, author a custom SQL query against the live database, import the\n",
    "result as a source, and model it.\n",
    "\n",
    "This transcript uses a PostgreSQL database; Snowflake, MySQL, ClickHouse,\n",
    "MongoDB, S3, and the other connector types follow the same verbs — swap the\n",
    "`type` and credentials. Credentials are stored encrypted server-side and are\n",
    "never returned by the API."
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "import rootcause as rc\n",
    "\n",
    "rc.login(base_url=os.environ.get(\"ROOTCAUSE_BASE_URL\", \"https://platform.rootcause.ai\"))\n",
    "ws = rc.workspace(\"connector-demo\", create=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Register and test\n",
    "\n",
    "One call to register, one to prove the credentials reach the database:"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "connector = ws.add_connector(\n",
    "    \"demo-warehouse\", \"PostgreSQL\",\n",
    "    host=os.environ.get(\"DEMO_DB_HOST\", \"localhost\"), port=5455,\n",
    "    database=\"warehouse\", username=\"demo\", password=os.environ.get(\"DEMO_DB_PASSWORD\", \"demo\"),\n",
    ")\n",
    "connector.test()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Browse the schema\n",
    "\n",
    "The same hierarchy the UI shows — schemas, then tables, then columns:"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "connector.browse(\"tables\", schema=\"public\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Author custom SQL against the live database\n",
    "\n",
    "`query()` runs your SQL with a row cap and returns sample rows — nothing is\n",
    "stored, and database errors come back verbatim, so the authoring loop stays\n",
    "tight:"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "try:\n",
    "    connector.query(\"SELECT * FROM store_week\")\n",
    "except rc.RootCauseError as error:\n",
    "    print(error)"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "connector.query(\n",
    "    \"SELECT region, week, marketing_spend, conversions, revenue FROM store_weeks ORDER BY week\",\n",
    "    limit=5,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import the query result as a source\n",
    "\n",
    "The same SQL, minus the safety net: `import_query()` materialises the full\n",
    "result set as a source in the workspace and blocks until ingest completes."
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "source = connector.import_query(\n",
    "    \"SELECT region, week, marketing_spend, footfall, conversions, revenue FROM store_weeks\",\n",
    "    name=\"store-weeks\",\n",
    ")\n",
    "source.to_frame().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Straight to a causal model\n",
    "\n",
    "A source-backed twin, discovery + training in one pass, and a question:"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "twin = ws.create_twin(\"Store weeks\", source_id=source.id)\n",
    "twin.run_pipeline()"
   ]
  },
  {
   "cell_type": "code",
   "metadata": {},
   "execution_count": null,
   "outputs": [],
   "source": [
    "result = twin.intervene({\"marketing_spend\": rc.pct(+15)}, outcomes=[\"revenue\"])\n",
    "result.to_frame()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The loop from here is the same as any other source: extend or re-import on a\n",
    "schedule, `twin.update()` to fold new rows in, and the [temporal-panel notebook](temporal-panel.ipynb)\n",
    "for time series and per-environment modelling."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python",
   "version": "3.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}