{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "# Build dbt Models on Opteryx.app\n\nRuns [dbt](https://www.getdbt.com) against [opteryx.app](https://opteryx.app), the hosted service, with the `dbt-opteryx` adapter. It builds a small project over two public datasets, the **National Vulnerability Database (NVD)** and CISA's catalog of **known-exploited vulnerabilities**: staging views, two tables, data tests and a unit test. Then it reads the tables it built back into pandas.\n\nThis notebook accompanies [dbt](https://docs.opteryx.app/docs/integrations/dbt) on docs.opteryx.app.\n\n**You need an opteryx.app account.** dbt runs every statement through the Jobs API, which needs a credential. Signing up is free at [opteryx.app](https://opteryx.app). Then create a personal access token in Opteryx Studio under **Settings → API Tokens**. It gives you a **client ID** and a **client secret**, and the secret is shown only once.\n\ndbt writes its models into your personal space, `personal.<your client ID>`. The last cell drops them again.",
   "id": "cell-0"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 1. Install",
   "id": "cell-1"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%pip install -q dbt-opteryx pandas matplotlib",
   "id": "cell-2"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 2. Credentials\n\nKeep the client secret out of the notebook itself, so it isn't saved with it or shared by accident. This cell looks for the client ID and secret in three places, in order:\n\n1. **Colab Secrets.** Click the key icon in Colab's left sidebar, add `OPTERYX_CLIENT_ID` and `OPTERYX_CLIENT_SECRET`, and turn on notebook access for both. Secrets stay with your Colab account, so you set them once and every notebook can use them.\n2. **Environment variables** with the same names, for running the notebook outside Colab.\n3. **A prompt.** If neither is set, the cell asks, and the secret is typed into a hidden field.\n\nThe values are put into environment variables, which is where the dbt profile below reads them from.",
   "id": "cell-3"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "import os\nfrom getpass import getpass\n\n\ndef credential(name, secret=False):\n    try:\n        from google.colab import userdata\n\n        return userdata.get(name)\n    except Exception:\n        pass   # not in Colab, or the secret isn't set or shared with this notebook\n    if os.environ.get(name):\n        return os.environ[name]\n    return getpass(f\"{name}: \") if secret else input(f\"{name}: \")\n\n\nos.environ[\"OPTERYX_CLIENT_ID\"] = credential(\"OPTERYX_CLIENT_ID\")\nos.environ[\"OPTERYX_CLIENT_SECRET\"] = credential(\"OPTERYX_CLIENT_SECRET\", secret=True)\nprint(\"Client ID:\", os.environ[\"OPTERYX_CLIENT_ID\"])",
   "id": "cell-4"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 3. Create the dbt project\n\nA dbt project is a folder of YAML and SQL files. These cells write one into `nvd_dbt/`, one file per cell.\n\n`profiles.yml` holds the connection. dbt's `database` is an Opteryx **workspace** and its `schema` is a **collection**, so models are built in `personal.<your client ID>`. `user` and `token` carry the client ID and secret.",
   "id": "cell-5"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "for folder in (\"nvd_dbt/models\", \"nvd_dbt/macros\"):\n    os.makedirs(folder, exist_ok=True)",
   "id": "cell-6"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/profiles.yml\nnvd_dbt:\n  target: cloud\n  outputs:\n    cloud:\n      type: opteryx\n      host: opteryx.app\n      port: 443\n      ssl: true\n      user: \"{{ env_var('OPTERYX_CLIENT_ID') }}\"\n      token: \"{{ env_var('OPTERYX_CLIENT_SECRET') }}\"\n      database: personal\n      schema: \"{{ env_var('OPTERYX_CLIENT_ID') }}\"\n      threads: 1",
   "id": "cell-7"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "`dbt_project.yml` names the project. `query-comment: append: true` moves the comment dbt adds to every statement to the end of it, because Opteryx doesn't yet accept one in front of `CREATE COLLECTION`. Models are views unless they say otherwise.",
   "id": "cell-8"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/dbt_project.yml\nname: nvd_dbt\nversion: \"1.0\"\nprofile: nvd_dbt\nquery-comment:\n  append: true\nmodels:\n  nvd_dbt:\n    +materialized: view",
   "id": "cell-9"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "`sources.yml` declares the two public datasets as dbt sources, so models refer to them with `source()` rather than a hard-coded name. dbt can test sources too: every CISA entry must have a CVE ID, and no CVE may appear twice.",
   "id": "cell-10"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/models/sources.yml\nversion: 2\nsources:\n  - name: security\n    database: public\n    schema: security\n    tables:\n      - name: nvd_vulnerabilities\n      - name: cisa_kev\n        columns:\n          - name: cve_id\n            data_tests:\n              - not_null\n              - unique",
   "id": "cell-11"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "Two staging views pick the columns the rest of the project uses:",
   "id": "cell-12"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/models/stg_cves.sql\nSELECT cve_id, published_at, severity, cvss_score\n  FROM {{ source('security', 'nvd_vulnerabilities') }}",
   "id": "cell-13"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/models/stg_exploited.sql\nSELECT cve_id, vendor_project, product, date_added, known_ransomware_campaign_use\n  FROM {{ source('security', 'cisa_kev') }}",
   "id": "cell-14"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "`exploited_cves` is a table: each known-exploited CVE with its NVD severity and score. `ref()` points at the staging views, which is how dbt knows to build them first. Each column keeps its own name, so `kev.cve_id` is stored as `cve_id`.",
   "id": "cell-15"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/models/exploited_cves.sql\n{{ config(materialized='table') }}\nSELECT kev.cve_id, kev.vendor_project, kev.product, kev.date_added,\n       kev.known_ransomware_campaign_use, nvd.severity, nvd.cvss_score\n  FROM {{ ref('stg_exploited') }} AS kev\n INNER JOIN {{ ref('stg_cves') }} AS nvd\n    ON kev.cve_id = nvd.cve_id",
   "id": "cell-16"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/models/exploited_per_vendor.sql\n{{ config(materialized='table') }}\nSELECT vendor_project,\n       COUNT(*) AS exploited_cves,\n       SUM(CASE WHEN known_ransomware_campaign_use = 'Known' THEN 1 ELSE 0 END) AS used_by_ransomware\n  FROM {{ ref('exploited_cves') }}\n GROUP BY vendor_project",
   "id": "cell-17"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "`schema.yml` adds data tests to the models, and a **unit test**. Data tests check the built tables. The unit test checks the logic of `exploited_per_vendor` against three made-up rows, so it doesn't depend on what is in the data today.",
   "id": "cell-18"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/models/schema.yml\nversion: 2\nmodels:\n  - name: exploited_cves\n    columns:\n      - name: cve_id\n        data_tests:\n          - not_null\n          - unique\n          - relationships:\n              arguments:\n                to: ref('stg_exploited')\n                field: cve_id\n      - name: severity\n        data_tests:\n          - accepted_values:\n              arguments:\n                values: ['CRITICAL', 'HIGH', 'MEDIUM', 'LOW', 'NONE']\n  - name: exploited_per_vendor\n    columns:\n      - name: vendor_project\n        data_tests:\n          - unique\n\nunit_tests:\n  - name: counts_cves_and_ransomware_per_vendor\n    model: exploited_per_vendor\n    given:\n      - input: ref('exploited_cves')\n        rows:\n          - {cve_id: CVE-1, vendor_project: Acme, known_ransomware_campaign_use: Known}\n          - {cve_id: CVE-2, vendor_project: Acme, known_ransomware_campaign_use: Unknown}\n          - {cve_id: CVE-3, vendor_project: Initech, known_ransomware_campaign_use: Unknown}\n    expect:\n      rows:\n        - {vendor_project: Acme, exploited_cves: 2, used_by_ransomware: 1}\n        - {vendor_project: Initech, exploited_cves: 1, used_by_ransomware: 0}",
   "id": "cell-19"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "A macro for the clean-up cell at the end. It drops every model this project built:",
   "id": "cell-20"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "%%writefile nvd_dbt/macros/drop_models.sql\n{% macro drop_models() %}\n  {% for node in graph.nodes.values() if node.resource_type == 'model' %}\n    {% set relation = adapter.get_relation(database=node.database, schema=node.schema, identifier=node.alias) %}\n    {% if relation %}\n      {% do adapter.drop_relation(relation) %}\n      {{ log('Dropped ' ~ relation, info=True) }}\n    {% endif %}\n  {% endfor %}\n{% endmacro %}",
   "id": "cell-21"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 4. Run dbt\n\n`dbt()` runs a dbt command against the project, the same as typing `dbt ...` in a terminal, and raises if it fails. `dbt debug` checks the profile and the connection. If it fails on the connection, opteryx.app didn't accept the client ID and secret: check both values, and that the token hasn't been revoked.",
   "id": "cell-22"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "from dbt.cli.main import dbtRunner\n\n\ndef dbt(*args):\n    result = dbtRunner().invoke([*args, \"--project-dir\", \"nvd_dbt\", \"--profiles-dir\", \"nvd_dbt\"])\n    if not result.success:\n        raise RuntimeError(f\"dbt {' '.join(args)} failed\")\n\n\ndbt(\"debug\")",
   "id": "cell-23"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "`dbt run` builds the four models in dependency order: the two views, then `exploited_cves`, then `exploited_per_vendor`.",
   "id": "cell-24"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "dbt(\"run\")",
   "id": "cell-25"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "`dbt test` runs the source tests, the data tests and the unit test.",
   "id": "cell-26"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "dbt(\"test\")",
   "id": "cell-27"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "`dbt show` prints rows from a model:",
   "id": "cell-28"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "dbt(\"show\", \"--select\", \"exploited_per_vendor\", \"--limit\", \"5\")",
   "id": "cell-29"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 5. Read the tables back\n\nThe models are ordinary datasets on opteryx.app, so anything that queries opteryx.app can read them. `dbt-opteryx` installed `opteryx-sqlalchemy`, so pandas can read them directly. The vendors with the most known-exploited CVEs:",
   "id": "cell-30"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "import pandas as pd\nfrom sqlalchemy import URL, create_engine\n\nengine = create_engine(\n    URL.create(\n        \"opteryx\",\n        username=os.environ[\"OPTERYX_CLIENT_ID\"],\n        password=os.environ[\"OPTERYX_CLIENT_SECRET\"],\n        host=\"opteryx.app\",\n        port=443,\n        database=\"default\",\n        query={\"ssl\": \"true\"},\n    )\n)\nmodels = f\"personal.{os.environ['OPTERYX_CLIENT_ID']}\"\n\nwith engine.connect() as conn:\n    vendors = pd.read_sql_query(\n        f\"SELECT * FROM {models}.exploited_per_vendor ORDER BY exploited_cves DESC LIMIT 15\", conn\n    )\nvendors.plot.barh(x=\"vendor_project\", y=[\"exploited_cves\", \"used_by_ransomware\"], figsize=(10, 6),\n                  title=\"Known-exploited CVEs per vendor\").invert_yaxis()",
   "id": "cell-31"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "And how severe the known-exploited CVEs are, by NVD severity:",
   "id": "cell-32"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "with engine.connect() as conn:\n    by_severity = pd.read_sql_query(\n        f\"SELECT severity, COUNT(*) AS cves FROM {models}.exploited_cves GROUP BY severity ORDER BY cves DESC\",\n        conn,\n    )\nby_severity",
   "id": "cell-33"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## 6. Clean up\n\nThis drops the four models from your personal space. Skip it if you want to keep them.",
   "id": "cell-34"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": "dbt(\"run-operation\", \"drop_models\")",
   "id": "cell-35"
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": "## Things to know\n\n- **Every statement is a query.** dbt sends several statements per model, including the ones it uses to look up what already exists, and each one counts towards your account's query allowance.\n- **No transactions.** Every statement commits on its own, so a model that fails halfway can leave its earlier statements applied.\n- **Snapshots aren't supported**, and **model contracts** are refused with an error naming the model.\n- The [dbt integration page](https://docs.opteryx.app/docs/integrations/dbt) lists what works and the known limitations.",
   "id": "cell-36"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
