guides/testing/README.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a9585707",
   "metadata": {
    "papermill": {
     "duration": 0.069377,
     "end_time": "2021-10-28T01:23:32.215730",
     "exception": false,
     "start_time": "2021-10-28T01:23:32.146353",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "To run this locally, [install Ploomber](https://docs.ploomber.io/en/latest/get-started/quick-start.html) and execute: `ploomber examples -n guides/testing`\n",
    "\n",
    "Found an issue? [Let us know.](https://github.com/ploomber/projects/issues/new?title=guides/testing%20issue)\n",
    "\n",
    "Questions? [Ask us on Slack.](https://ploomber.io/community/)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "25b5941a",
   "metadata": {
    "papermill": {
     "duration": 0.05443,
     "end_time": "2021-10-28T01:23:32.328235",
     "exception": false,
     "start_time": "2021-10-28T01:23:32.273805",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Pipeline testing\n",
    "\n",
    "<!-- start description -->\n",
    "Tutorial showing how to use a task's on_finish hook to test data quality.\n",
    "<!-- end description -->\n",
    "\n",
    "Testing your pipeline is critical to ensure your data expectations hold. When you perform a data transformation, you are expecting the output to have certain properties (e.g. no nulls in certain column). Without testing, these expectations won't be verified and will cause errors errors to propagate to all downstream tasks.\n",
    "\n",
    "These are the most common sources of errors when transforming data:\n",
    "\n",
    "1. A join operation generates duplicated entries because a wrong assumption of a one-to-one relationship (which is really a one-to-many) in the source tables\n",
    "2. A function that aggregates data returns `NULL` because at least one of the input data points was `NULL`\n",
    "3. Dirty data points are used in the analysis (e.g. in a column `age`, you forgot to remove corrupted data points with negative values)\n",
    "\n",
    "Some of these errors are easy to spot (2), but it might take you some tome to find out about others (1 and 3), or worst, you will never notice these errors and just use incorrect data in your analysis. And even if your code is correct and all your expectations hold true, it might not hold true in the future if the data changes and it's important for you to know this as soon as it happens.\n",
    "\n",
    "To make testing effective, **your tests should run every time you run your tasks**. Ploomber has a mechanism to automate this.\n",
    "\n",
    "## Sample data\n",
    "\n",
    "This example loads data from a single table called `my_table`, which has two columns:\n",
    "\n",
    "1. age: ranges from 21 to 80 but there are some corrupted records with -42\n",
    "2. score: ranges from 0 to 10 but there are some corrupted records with missing values\n",
    "\n",
    "Let's take a look at our example `pipeline.yaml`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9ec57a81",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:23:32.466198Z",
     "iopub.status.busy": "2021-10-28T01:23:32.464866Z",
     "iopub.status.idle": "2021-10-28T01:23:32.469773Z",
     "shell.execute_reply": "2021-10-28T01:23:32.470413Z"
    },
    "papermill": {
     "duration": 0.088226,
     "end_time": "2021-10-28T01:23:32.471641",
     "exception": false,
     "start_time": "2021-10-28T01:23:32.383415",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5fa73a5b",
   "metadata": {
    "papermill": {
     "duration": 0.051869,
     "end_time": "2021-10-28T01:23:32.597595",
     "exception": false,
     "start_time": "2021-10-28T01:23:32.545726",
     "status": "completed"
    },
    "region_name": "md",
    "tags": []
   },
   "source": [
    "```yaml\n",
    "# Content of pipeline.yaml\n",
    "clients:\n",
    "  SQLScript: db.get_client\n",
    "  SQLDump: db.get_client\n",
    "\n",
    "tasks:\n",
    "  - source: clean.sql\n",
    "    name: clean\n",
    "    product: ['my_clean_table', 'table']\n",
    "    on_finish: integration_tests.test_sql_clean\n",
    "  \n",
    "  - source: dump.sql\n",
    "    name: dump\n",
    "    class: SQLDump\n",
    "    product: output/my_clean_table.csv\n",
    "    chunksize: null\n",
    "\n",
    "  - source: transform.py\n",
    "    product:\n",
    "        nb: output/transformed.html\n",
    "        data: output/transformed.csv\n",
    "    on_finish: integration_tests.test_py_transform\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "529c57bb",
   "metadata": {
    "papermill": {
     "duration": 0.07206,
     "end_time": "2021-10-28T01:23:32.729720",
     "exception": false,
     "start_time": "2021-10-28T01:23:32.657660",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "The pipeline has three tasks, one to clean the raw table, another one to dump the clean data to a CSV file and finally, one Python task to transform the data. We included a SQL and a Python task to show how you can test both types of tasks but we recommend you to do as much analysis as you can using SQL because it scales much better than Python code (you won't have to deal with memory errors).\n",
    "\n",
    "The configuration is straightforward, the only new key is `on_finish` (inside the first and third task). This is known as a *hook*. Task hooks allow you to embed custom logic when certain events happen. `on_finish` is executed after a task successfully executes. The value is a dotted path, which tells Ploomber where to find your testing function. Under the hood, Ploomber will import your function and call it after the task is executed, here's some equivalent code:\n",
    "\n",
    "```python\n",
    "from integration_tests import test_sql_clean\n",
    "\n",
    "# your task is executed...\n",
    "\n",
    "# ploomber calls your testing function...\n",
    "test_sql_clean()\n",
    "```\n",
    "\n",
    "Before diving into the testing source code, let's see the rest of the tasks.\n",
    "\n",
    "`clean.sql` just filters columns we don't want to include in the analysis:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5389f63",
   "metadata": {
    "papermill": {
     "duration": 0.066898,
     "end_time": "2021-10-28T01:23:32.863930",
     "exception": false,
     "start_time": "2021-10-28T01:23:32.797032",
     "status": "completed"
    },
    "region_name": "md",
    "tags": []
   },
   "source": [
    "```sql\n",
    "# Content of clean.sql\n",
    "DROP TABLE IF EXISTS {{product}};\n",
    "\n",
    "CREATE TABLE {{product}} AS\n",
    "SELECT * FROM my_table\n",
    "WHERE score is not null AND age > 0\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97c81d52",
   "metadata": {
    "papermill": {
     "duration": 0.044372,
     "end_time": "2021-10-28T01:23:32.954042",
     "exception": false,
     "start_time": "2021-10-28T01:23:32.909670",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "`dump.sql` just selects all rows from the clean table to dump it to the CSV file:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "232e5035",
   "metadata": {
    "papermill": {
     "duration": 0.049356,
     "end_time": "2021-10-28T01:23:33.048131",
     "exception": false,
     "start_time": "2021-10-28T01:23:32.998775",
     "status": "completed"
    },
    "region_name": "md",
    "tags": []
   },
   "source": [
    "```sql\n",
    "# Content of dump.sql\n",
    "SELECT * FROM {{upstream['clean']}}\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a007f87",
   "metadata": {
    "papermill": {
     "duration": 0.050213,
     "end_time": "2021-10-28T01:23:33.145786",
     "exception": false,
     "start_time": "2021-10-28T01:23:33.095573",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Finally, the `transform.py` script generates a new column using `score`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b52e255",
   "metadata": {
    "papermill": {
     "duration": 0.043543,
     "end_time": "2021-10-28T01:23:33.234832",
     "exception": false,
     "start_time": "2021-10-28T01:23:33.191289",
     "status": "completed"
    },
    "region_name": "md",
    "tags": []
   },
   "source": [
    "```python\n",
    "# Content of transform.py\n",
    "import pandas as pd\n",
    "\n",
    "# + tags=[\"parameters\"]\n",
    "upstream = ['dump']\n",
    "product = None\n",
    "\n",
    "# +\n",
    "df = pd.read_csv(upstream['dump'])\n",
    "df['multiplied_score'] = df.score * 42\n",
    "\n",
    "# +\n",
    "df.to_csv(product['data'])\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2ca6e71",
   "metadata": {
    "papermill": {
     "duration": 0.043516,
     "end_time": "2021-10-28T01:23:33.323092",
     "exception": false,
     "start_time": "2021-10-28T01:23:33.279576",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Let's now take a look at our tests:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e56e05e",
   "metadata": {
    "papermill": {
     "duration": 0.0459,
     "end_time": "2021-10-28T01:23:33.412133",
     "exception": false,
     "start_time": "2021-10-28T01:23:33.366233",
     "status": "completed"
    },
    "region_name": "md",
    "tags": []
   },
   "source": [
    "```python\n",
    "# Content of integration_tests.py\n",
    "import pandas as pd\n",
    "from ploomber.testing.sql import nulls_in_columns, range_in_column\n",
    "\n",
    "\n",
    "def test_sql_clean(client, product):\n",
    "    \"\"\"Tests for clean.sql\n",
    "    \"\"\"\n",
    "    assert not nulls_in_columns(client, ['score', 'age'], product)\n",
    "    min_age, max_age = range_in_column(client, 'age', product)\n",
    "    assert min_age > 0\n",
    "\n",
    "\n",
    "def test_py_transform(product):\n",
    "    \"\"\"Tests for transform.py\n",
    "    \"\"\"\n",
    "    df = pd.read_csv(str(product['data']))\n",
    "    assert not df.multiplied_score.isna().sum()\n",
    "    assert df.multiplied_score.min() >= 0\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5a59c13",
   "metadata": {
    "papermill": {
     "duration": 0.04346,
     "end_time": "2021-10-28T01:23:33.501320",
     "exception": false,
     "start_time": "2021-10-28T01:23:33.457860",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "## Testing Python scripts\n",
    "\n",
    "To test your Python scripts, you have to know which file to look at. You can do so by simply adding `product` as argument to your function. If your Python script generates more than one product (like in our case), `product` will be a dictionary-like object, that's why we are using `product['data']`. This returns a `Product` object, to get the path to the file, simply use the `str` function.\n",
    "\n",
    "```pycon\n",
    ">>> product # dictionary-like object: maps names to Product objects\n",
    ">>> product['data'] # Product object\n",
    ">>> str(product['data']) # path to the data file\n",
    "```\n",
    "\n",
    "## Testing SQL scripts\n",
    "\n",
    "To test SQL scripts, you also need the client to send queries to the appropriate database, to do so, just add `client` to your testing function.\n",
    "\n",
    "The `ploomber.testing.sql` module implements convenient functions to test your tables. They always take `client` as its first argument, just pass the client variable directly. Since our SQL script only generates a product, you can directly pass the product object to the testing function (otherwise pass `product[key]`) with the appropriate key.\n",
    "\n",
    "*Note:* If you're implementing your own SQL testing logic, doing `str(product)` will return a `{schema}.{name}` string, you can also use `product.schema` and `product.name`.\n",
    "\n",
    "## Running the pipeline\n",
    "\n",
    "Before we run the pipeline, we generate a sample database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9135f1a9",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:23:33.595813Z",
     "iopub.status.busy": "2021-10-28T01:23:33.595114Z",
     "iopub.status.idle": "2021-10-28T01:23:34.387794Z",
     "shell.execute_reply": "2021-10-28T01:23:34.388273Z"
    },
    "papermill": {
     "duration": 0.84281,
     "end_time": "2021-10-28T01:23:34.388574",
     "exception": false,
     "start_time": "2021-10-28T01:23:33.545764",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%sh\n",
    "cd setup\n",
    "python script.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf3b93e2",
   "metadata": {
    "papermill": {
     "duration": 0.045285,
     "end_time": "2021-10-28T01:23:34.480401",
     "exception": false,
     "start_time": "2021-10-28T01:23:34.435116",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Let's now run our pipeline:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a6ada25a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:23:34.577669Z",
     "iopub.status.busy": "2021-10-28T01:23:34.576752Z",
     "iopub.status.idle": "2021-10-28T01:23:40.910362Z",
     "shell.execute_reply": "2021-10-28T01:23:40.910916Z"
    },
    "papermill": {
     "duration": 6.385887,
     "end_time": "2021-10-28T01:23:40.911152",
     "exception": false,
     "start_time": "2021-10-28T01:23:34.525265",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name       Ran?      Elapsed (s)    Percentage\n",
      "---------  ------  -------------  ------------\n",
      "clean      True         0.02013      0.78097\n",
      "dump       True         0.002051     0.0795713\n",
      "transform  True         2.55538     99.1395\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building task 'transform':   0%|          | 0/3 [00:00<?, ?it/s]\n",
      "Executing:   0%|          | 0/5 [00:00<?, ?cell/s]\u001b[A\n",
      "Executing:  20%|██        | 1/5 [00:01<00:06,  1.75s/cell]\u001b[A\n",
      "Executing: 100%|██████████| 5/5 [00:02<00:00,  2.39cell/s]\u001b[A\n",
      "Building task 'transform': 100%|██████████| 3/3 [00:02<00:00,  1.16it/s]\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "ploomber build"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d10b90a",
   "metadata": {
    "papermill": {
     "duration": 0.059869,
     "end_time": "2021-10-28T01:23:41.026163",
     "exception": false,
     "start_time": "2021-10-28T01:23:40.966294",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Everything looks good.\n",
    "\n",
    "Let's now imagine a colleague found an error in the cleaning logic and has re-written the script. However, he was unaware that both columns in the raw table had corrupted data and forgot to include the filtering conditions.\n",
    "\n",
    "The script now looks like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c303137e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:23:41.164158Z",
     "iopub.status.busy": "2021-10-28T01:23:41.162961Z",
     "iopub.status.idle": "2021-10-28T01:23:41.183674Z",
     "shell.execute_reply": "2021-10-28T01:23:41.184204Z"
    },
    "papermill": {
     "duration": 0.092665,
     "end_time": "2021-10-28T01:23:41.184510",
     "exception": false,
     "start_time": "2021-10-28T01:23:41.091845",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "86"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = Path('clean.sql')\n",
    "new_code = path.read_text().replace('WHERE score is not null AND age > 0', '')\n",
    "path.write_text(new_code)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be073efd",
   "metadata": {
    "papermill": {
     "duration": 0.051533,
     "end_time": "2021-10-28T01:23:41.293549",
     "exception": false,
     "start_time": "2021-10-28T01:23:41.242016",
     "status": "completed"
    },
    "region_name": "md",
    "tags": []
   },
   "source": [
    "```sql\n",
    "# Content of clean.sql\n",
    "DROP TABLE IF EXISTS {{product}};\n",
    "\n",
    "CREATE TABLE {{product}} AS\n",
    "SELECT * FROM my_table\n",
    "WHERE score is not null AND age > 0\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b10182e6",
   "metadata": {
    "papermill": {
     "duration": 0.055876,
     "end_time": "2021-10-28T01:23:41.402485",
     "exception": false,
     "start_time": "2021-10-28T01:23:41.346609",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Let's see what happens if we run the pipeline:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8c0c0fcd",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:23:41.544797Z",
     "iopub.status.busy": "2021-10-28T01:23:41.543406Z",
     "iopub.status.idle": "2021-10-28T01:23:45.171899Z",
     "shell.execute_reply": "2021-10-28T01:23:45.172419Z"
    },
    "papermill": {
     "duration": 3.703326,
     "end_time": "2021-10-28T01:23:45.172640",
     "exception": false,
     "start_time": "2021-10-28T01:23:41.469314",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%capture captured\n",
    "%%sh --no-raise-error\n",
    "ploomber build"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "627f8955",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:23:45.280529Z",
     "iopub.status.busy": "2021-10-28T01:23:45.279559Z",
     "iopub.status.idle": "2021-10-28T01:23:45.283117Z",
     "shell.execute_reply": "2021-10-28T01:23:45.283792Z"
    },
    "papermill": {
     "duration": 0.061366,
     "end_time": "2021-10-28T01:23:45.284044",
     "exception": false,
     "start_time": "2021-10-28T01:23:45.222678",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Building task 'clean': 100%|██████████| 3/3 [00:00<00:00, 115.03it/s]\n",
      "Traceback (most recent call last):\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/cli/io.py\", line 20, in wrapper\n",
      "    fn(**kwargs)\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/cli/build.py\", line 51, in main\n",
      "    report = dag.build(force=args.force, debug=args.debug)\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/dag/dag.py\", line 482, in build\n",
      "    report = callable_()\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/dag/dag.py\", line 581, in _build\n",
      "    raise build_exception\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/dag/dag.py\", line 513, in _build\n",
      "    task_reports = self._executor(dag=self,\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/executors/serial.py\", line 138, in __call__\n",
      "    raise DAGBuildError(str(exceptions_all))\n",
      "ploomber.exceptions.DAGBuildError: \n",
      "=============================== DAG build failed ===============================\n",
      "--------- SQLScript: clean -> SQLRelation(('my_clean_table', 'table')) ---------\n",
      "---------- /Users/Edu/dev/projects-ploomber/guides/testing/clean.sql -----------\n",
      "Traceback (most recent call last):\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/tasks/abc.py\", line 591, in _build\n",
      "    self._post_run_actions()\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/tasks/abc.py\", line 342, in _post_run_actions\n",
      "    self._run_on_finish()\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/tasks/abc.py\", line 333, in _run_on_finish\n",
      "    self.on_finish(**kwargs)\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/util/dotted_path.py\", line 74, in __call__\n",
      "    out = self._callable(*args, **kwargs_final)\n",
      "  File \"/Users/Edu/dev/projects-ploomber/guides/testing/integration_tests.py\", line 8, in test_sql_clean\n",
      "    assert not nulls_in_columns(client, ['score', 'age'], product)\n",
      "AssertionError\n",
      "\n",
      "The above exception was the direct cause of the following exception:\n",
      "\n",
      "Traceback (most recent call last):\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/executors/serial.py\", line 186, in catch_exceptions\n",
      "    fn()\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/executors/serial.py\", line 159, in __call__\n",
      "    return self.fn(**self.kwargs)\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/executors/serial.py\", line 166, in catch_warnings\n",
      "    result = fn()\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/executors/serial.py\", line 159, in __call__\n",
      "    return self.fn(**self.kwargs)\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/executors/serial.py\", line 235, in build_in_subprocess\n",
      "    report, meta = task._build(**build_kwargs)\n",
      "  File \"/Users/Edu/dev/ploomber/src/ploomber/tasks/abc.py\", line 603, in _build\n",
      "    raise TaskBuildError(msg) from e\n",
      "ploomber.exceptions.TaskBuildError: Exception when running on_finish for task \"clean\": \n",
      "=============================== Summary (1 task) ===============================\n",
      "SQLScript: clean -> SQLRelation(('my_clean_table', 'table'))\n",
      "=============================== DAG build failed ===============================\n",
      "\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(captured.stderr)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "084f802c",
   "metadata": {
    "papermill": {
     "duration": 0.050875,
     "end_time": "2021-10-28T01:23:45.385548",
     "exception": false,
     "start_time": "2021-10-28T01:23:45.334673",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "Ploomber a structured error message to understand why your pipeline failed. The last few lines are a summary:\n",
    "\n",
    "```\n",
    "=============================== Summary (1 task) ===============================\n",
    "SQLScript: clean -> SQLRelation(('my_clean_table', 'table'))\n",
    "=============================== DAG build failed ===============================\n",
    "```\n",
    "\n",
    "By looking at the summary we know our pipeline failed because one task crashed (`clean`). If we\n",
    "scroll up we'll see a header section:\n",
    "\n",
    "```\n",
    "--------- SQLScript: clean -> SQLRelation(('my_clean_table', 'table')) ---------\n",
    "-------------- /Users/Edu/dev/projects-ploomber/testing/clean.sql --------------\n",
    "```\n",
    "\n",
    "Each task displays its traceback on a separate section. Since only one task failed in our example\n",
    "we only see one task traceback.\n",
    "\n",
    "At the end of this task traceback, we see the following line:\n",
    "\n",
    "```\n",
    "Exception when running on_finish for task \"clean\":\n",
    "```\n",
    "\n",
    "Now we know that the `on_finish` hook crashed. If we go up a few lines up:\n",
    "\n",
    "```\n",
    "assert not nulls_in_columns(client, ['score', 'age'], product)\n",
    "AssertionError\n",
    "```\n",
    "\n",
    "That tells me the exact test that failed! Pipelines can get very large; it helps a lot to have a structured error message that tells us what failed and where it happened. Our take away from the error message is: \"the pipeline building process failed because the `on_finish` hook in the `clean` task raised an exception in certain assertion\". That's much better than either \"the pipeline failed\" or \"this line raised an exception\".\n",
    "\n",
    "Let's fix our pipeline and add the `WHERE` clause again:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bb4233bb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:23:45.494426Z",
     "iopub.status.busy": "2021-10-28T01:23:45.493497Z",
     "iopub.status.idle": "2021-10-28T01:23:45.499337Z",
     "shell.execute_reply": "2021-10-28T01:23:45.500250Z"
    },
    "papermill": {
     "duration": 0.061298,
     "end_time": "2021-10-28T01:23:45.500478",
     "exception": false,
     "start_time": "2021-10-28T01:23:45.439180",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DROP TABLE IF EXISTS {{product}};\n",
      "\n",
      "CREATE TABLE {{product}} AS\n",
      "SELECT * FROM my_table\n",
      "WHERE score is not null AND age > 0\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "121"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = Path('clean.sql')\n",
    "new_code = path.read_text() + 'WHERE score is not null AND age > 0'\n",
    "print(new_code)\n",
    "path.write_text(new_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "796b424c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-28T01:23:45.612135Z",
     "iopub.status.busy": "2021-10-28T01:23:45.611326Z",
     "iopub.status.idle": "2021-10-28T01:23:51.870451Z",
     "shell.execute_reply": "2021-10-28T01:23:51.871346Z"
    },
    "papermill": {
     "duration": 6.319249,
     "end_time": "2021-10-28T01:23:51.871566",
     "exception": false,
     "start_time": "2021-10-28T01:23:45.552317",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name       Ran?      Elapsed (s)    Percentage\n",
      "---------  ------  -------------  ------------\n",
      "clean      True         0.017694     0.67259\n",
      "dump       True         0.001669     0.0634426\n",
      "transform  True         2.61136     99.264\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Building task 'transform':   0%|          | 0/3 [00:00<?, ?it/s]\n",
      "Executing:   0%|          | 0/5 [00:00<?, ?cell/s]\u001b[A\n",
      "Executing:  20%|██        | 1/5 [00:01<00:07,  1.78s/cell]\u001b[A\n",
      "Executing: 100%|██████████| 5/5 [00:02<00:00,  2.34cell/s]\u001b[A\n",
      "Building task 'transform': 100%|██████████| 3/3 [00:02<00:00,  1.13it/s]\n"
     ]
    }
   ],
   "source": [
    "%%sh\n",
    "ploomber build"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a08dcab",
   "metadata": {
    "papermill": {
     "duration": 0.057945,
     "end_time": "2021-10-28T01:23:51.983751",
     "exception": false,
     "start_time": "2021-10-28T01:23:51.925806",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "All good! Pipeline is running without issues again!\n",
    "\n",
    "## Test-driven development (TDD)\n",
    "\n",
    "Writing data tests is essential for developing robust pipelines. Coding tests is simple, all we have to do is write in code that we already have in our mind when thinking what the outcome of a script should be.\n",
    "\n",
    "This thought process happens *before* we write the actual code, which means we could easily write tests even before we write the actual code. This approach is called Test-driven development (TDD).\n",
    "\n",
    "Following this framework has an added benefit, since we force ourselves to put in concrete terms our data expectations, it makes easier to think how we want to get there.\n",
    "\n",
    "Furthermore, *tests also serve as documentation* for us (and for others). By looking at our tests, anyone can see what *our intent* is. Then by looking at the code, it will be easier to spot mismatches between our intent and our implementation.\n",
    "\n",
    "\n",
    "## Pro tip: debugging and developing tests interactively\n",
    "\n",
    "Even though tests are usually just a few short statements, writing them in an interactive way can help you quickly prototype your assertions. One simple trick you can use to do this is to start an interactive session and load the `client` and `product` variables.\n",
    "\n",
    "Let's imagine you want to write a test for a new SQL script (but the same applies for other types of scripts). You add a testing function, but it's currently empty:\n",
    "\n",
    "```python\n",
    "def my_sql_testing_function(client, product):\n",
    "    pass\n",
    "```\n",
    "\n",
    "If you run this, Ploomber will still call your function, you can start an interactive session when this happens:\n",
    "\n",
    "```python\n",
    "def my_sql_testing_function(client, product):\n",
    "    from IPython import embed; embed()\n",
    "```\n",
    "\n",
    "Once you call `ploomber build`, wait for the Python prompt to show and verify you have the `client` and `product` variables:\n",
    "\n",
    "```pycon\n",
    ">>> print(client)\n",
    ">>> print(product)\n",
    "```\n",
    "\n",
    "\n",
    "## Where to go next\n",
    "\n",
    "* [Documentation for ploomber.testing - Handy functions for testing pipelines](../api/testing.rst)\n",
    "* [Our blog post on CI for Data Science (which includes a section on testing pipelines)](https://towardsdatascience.com/rethinking-continuous-integration-for-data-science-ebf0dfc61788)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.1"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 21.528732,
   "end_time": "2021-10-28T01:23:52.258950",
   "environment_variables": {},
   "exception": null,
   "input_path": "guides/testing/_build/readme_preprocessed.ipynb",
   "output_path": "guides/testing/README.ipynb",
   "parameters": {},
   "start_time": "2021-10-28T01:23:30.730218",
   "version": "2.3.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}