says that \u201cany sufficiently superior expertise is indistinguishable from magic<\/em>\u201d. That\u2019s precisely how lots of right now\u2019s AI frameworks really feel. Instruments like GitHub Copilot, Claude Desktop, OpenAI Operator, and Perplexity Comet are automating on a regular basis duties that will\u2019ve appeared not possible to automate simply 5 years in the past. What\u2019s much more outstanding is that with just some traces of code, we are able to construct our personal subtle AI instruments: ones that search via information, browse the online, click on hyperlinks, and even make purchases. It actually does really feel like magic.<\/p>\n Although I genuinely imagine in knowledge wizards<\/a>, I don\u2019t imagine in magic. I discover it thrilling (and sometimes useful) to know how issues are literally constructed and what\u2019s taking place below the hood. That\u2019s why I\u2019ve determined to share a collection of posts on agentic AI design ideas that\u2019ll make it easier to perceive how all these magical instruments truly work.<\/p>\n To achieve a deep understanding, we\u2019ll construct a multi-AI agent system from scratch. We\u2019ll keep away from utilizing frameworks like CrewAI or smolagents and as a substitute work immediately with the muse mannequin API. Alongside the best way, we\u2019ll discover the elemental agentic design patterns: reflection, device use, planning, and multi-agent setups. Then, we\u2019ll mix all this information to construct a multi-AI agent system that may reply advanced data-related questions.<\/p>\n As Richard Feynman put it, \u201cWhat I can’t create, I don’t perceive<\/em>.\u201d So let\u2019s begin constructing! On this article, we\u2019ll deal with the reflection design sample. However first, let\u2019s determine what precisely reflection is.<\/p>\n Let\u2019s mirror on how we (people) often work on duties. Think about I must share the outcomes of a current characteristic launch with my PM. I\u2019ll possible put collectively a fast draft after which learn it a few times from starting to finish, guaranteeing that each one elements are constant, there\u2019s sufficient info, and there are not any typos.<\/p>\n Or let\u2019s take one other instance: writing a SQL question. I\u2019ll both write it step-by-step, checking the intermediate outcomes alongside the best way, or (if it\u2019s easy sufficient) I\u2019ll draft it suddenly, execute it, have a look at the outcome (checking for errors or whether or not the outcome matches my expectations), after which tweak the question based mostly on that suggestions. I’d rerun it, test the outcome, and iterate till it\u2019s proper.<\/p>\n So we hardly ever write lengthy texts from prime to backside in a single go. We often circle again, evaluate, and tweak as we go. These suggestions loops are what assist us enhance the standard of our work.<\/p>\n LLMs use a special strategy. When you ask an LLM a query, by default, it’ll generate a solution token by token, and the LLM gained\u2019t be capable to evaluate its outcome and repair any points. However in an agentic AI setup, we are able to create suggestions loops for LLMs too, both by asking the LLM to evaluate and enhance its personal reply or by sharing exterior suggestions with it (just like the outcomes of a SQL execution). And that\u2019s the entire level of reflection. It sounds fairly simple, however it could yield considerably higher outcomes.<\/p>\n There\u2019s a considerable physique of analysis displaying the advantages of reflection:<\/p>\n Reflection is very impactful in agentic techniques as a result of it may be used to course-correct at many steps of the method:<\/p>\n It\u2019s clear that reflection can considerably enhance accuracy. Nonetheless, there are trade-offs value discussing. Reflection may require a number of further calls to the LLM and probably different techniques, which might result in elevated latency and prices. So in enterprise circumstances, it\u2019s value contemplating whether or not the standard enhancements justify the bills and delays within the consumer circulation.<\/p>\n Since there\u2019s little doubt that reflection brings worth to AI brokers, it\u2019s broadly utilized in fashionable frameworks. Let\u2019s have a look at some examples.<\/p>\n The concept of reflection was first proposed within the paper \u201cReAct: Synergizing Reasoning and Appearing in Language Fashions\u201d<\/a> by Yao et al. (2022). ReAct is a framework that mixes interleaving levels of Reasoning (reflection via specific thought traces) and Appearing (task-relevant actions in an surroundings). On this framework, reasoning guides the selection of actions, and actions produce new observations that inform additional reasoning. The reasoning stage itself is a mixture of reflection and planning.<\/p>\n This framework grew to become fairly fashionable, so there at the moment are a number of off-the-shelf implementations, reminiscent of:<\/p>\n Now that we\u2019ve realized the speculation and explored current implementations, it\u2019s time to get our fingers soiled and construct one thing ourselves. Within the ReAct strategy, brokers use reflection at every step, combining planning with reflection. Nonetheless, to know the affect of reflection extra clearly, we\u2019ll have a look at it in isolation.<\/p>\n For instance, we\u2019ll use text-to-SQL: we\u2019ll give an LLM a query and count on it to return a sound SQL question. We\u2019ll be working with a flight delay dataset<\/a> and the ClickHouse SQL dialect.<\/p>\n We\u2019ll begin through the use of direct era with none reflection as our baseline. Then, we\u2019ll attempt utilizing reflection by asking the mannequin to critique and enhance the SQL, or by offering it with further suggestions. After that, we\u2019ll measure the standard of our solutions to see whether or not reflection truly results in higher outcomes.<\/p>\n We\u2019ll start with probably the most simple strategy, direct era, the place we ask the LLM to generate SQL that solutions a consumer question.<\/p>\n We have to specify the API Key for the Anthropic API.<\/p>\n The subsequent step is to initialise the shopper, and we\u2019re all set.<\/p>\n Now we are able to use this shopper to ship messages to the LLM. Let\u2019s put collectively a perform to generate SQL based mostly on a consumer question. I\u2019ve specified the system immediate with primary directions and detailed details about the info schema. I\u2019ve additionally created a perform to ship the system immediate and consumer question to the LLM.<\/p>\n That\u2019s it. Now let\u2019s take a look at our text-to-SQL answer. I\u2019ve created a small analysis set<\/a> of 20 question-and-answer pairs that we are able to use to test whether or not our system is working nicely. Right here\u2019s one instance:<\/p>\n Let\u2019s use our text-to-SQL perform to generate SQL for all consumer queries within the take a look at set.<\/p>\n Now we’ve got our solutions, and the subsequent step is to measure the standard.<\/p>\n Sadly, there\u2019s no single appropriate reply on this state of affairs, so we are able to\u2019t simply evaluate the SQL generated by the LLM to a reference reply. We have to provide you with a approach to measure high quality.<\/p>\n There are some features of high quality that we are able to test with goal standards, however to test whether or not the LLM returned the fitting reply, we\u2019ll want to make use of an LLM. So I\u2019ll use a mixture of approaches:<\/p>\n Let\u2019s begin by executing the SQL. It\u2019s value noting that our The subsequent step is to create an LLM decide. For this, I\u2019m utilizing a series\u2011of\u2011thought strategy that prompts the LLM to offer its reasoning earlier than giving the ultimate reply. This provides the mannequin time to assume via the issue, which improves response high quality.<\/p>\n Now, let\u2019s run the LLM decide to get the outcomes.<\/p>\n Let\u2019s have a look at one instance to see how the LLM decide works.\u00a0<\/p>\n The reasoning is sensible, so we are able to belief our decide. Now, let\u2019s test all LLM-generated queries.<\/p>\n The LLM returned the proper reply in 70% of circumstances, which isn’t dangerous. However there\u2019s undoubtedly room for enchancment, because it usually both offers the mistaken reply or fails to specify the format accurately (typically inflicting SQL execution errors).<\/p>\n To enhance the standard of our answer, let\u2019s attempt including a mirrored image step the place we ask the mannequin to evaluate and refine its reply.\u00a0<\/p>\n For a mirrored image name, I\u2019ll maintain the identical system immediate because it accommodates all the mandatory details about SQL and the info schema. However I\u2019ll tweak the consumer message to share the preliminary consumer question and the generated SQL, asking the LLM to critique and enhance it.<\/p>\n Let\u2019s refine the queries with reflection and measure the accuracy. We don\u2019t see a lot enchancment within the last high quality. We\u2019re nonetheless at 70% appropriate solutions.<\/p>\n Let\u2019s have a look at particular examples to know what occurred. First, there are a few circumstances the place the LLM managed to repair the issue, both by correcting the format or by including lacking logic to deal with zero values.<\/p>\n Nonetheless, there are additionally circumstances the place the LLM overcomplicated the reply. The preliminary SQL was appropriate (matching the golden set reply), however then the LLM determined to \u2018enhance\u2019 it. A few of these enhancements are affordable (e.g., accounting for nulls or excluding cancelled flights). Nonetheless, for some cause, it determined to make use of ClickHouse sampling, though we don\u2019t have a lot knowledge and our desk doesn\u2019t help sampling. Consequently, the refined question returned an execution error: Reflection didn\u2019t enhance accuracy a lot. That is possible as a result of we didn\u2019t present any further info that will assist the mannequin generate a greater outcome. Let\u2019s attempt sharing exterior suggestions with the mannequin:<\/p>\n The results of our test on whether or not the format is specified accurately After working our accuracy measurements, we are able to see that accuracy has improved considerably: 17 appropriate solutions (85% accuracy) in comparison with 14 (70% accuracy).<\/p>\n If we test the circumstances the place the LLM mounted the problems, we are able to see that it was in a position to appropriate the format, tackle SQL execution errors, and even revise the enterprise logic (e.g., utilizing air time for calculating velocity).<\/p>\n Let\u2019s additionally do some error evaluation to look at the circumstances the place the LLM made errors. Within the desk under, we are able to see that the LLM struggled with defining sure timestamps, incorrectly calculating complete time, or utilizing complete time as a substitute of air time for velocity calculations. Nonetheless, a number of the discrepancies are a bit difficult:<\/p>\n General, I feel we achieved a fairly good outcome. Our last 85% accuracy represents a big 15% level enchancment. You would probably transcend one iteration and run 2\u20133 rounds of reflection, however it\u2019s value assessing once you hit diminishing returns in your particular case, since every iteration goes with elevated price and latency.<\/p>\nWhat reflection is<\/h2>\n
![\"\"](\"https:\/\/contributor.insightmediagroup.io\/wp-content\/uploads\/2025\/10\/Screenshot-2025-10-23-at-08.57.53-1024x789.png\")
![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/1600\/1*fZCBw-xDGLRiQaOSGKznQw.png\")
\n
![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/1600\/1*UbEzF5n1PZbD8oRLJW3mMw.png\")
\n
Reflection in frameworks<\/h2>\n
\n
ReAct<\/code> class,<\/li>\ncreate_react_agent<\/code> perform,<\/li>\nReflection from\u00a0scratch<\/h2>\n
Direct era<\/h3>\n
pip set up anthropic<\/code><\/pre>\nimport os\nos.environ['ANTHROPIC_API_KEY'] = config['ANTHROPIC_API_KEY']<\/code><\/pre>\nimport anthropic\nshopper = anthropic.Anthropic()<\/code><\/pre>\nbase_sql_system_prompt = '''\nYou're a senior SQL developer and your activity is to assist generate a SQL question based mostly on consumer necessities. \nYou might be working with ClickHouse database. Specify the format (Tab Separated With Names) within the SQL question output to make sure that column names are included within the output.\nDon't use rely(*) in your queries since it is a dangerous observe with columnar databases, favor utilizing rely().\nBe certain that the question is syntactically appropriate and optimized for efficiency, considering ClickHouse particular options (i.e. that ClickHouse is a columnar database and helps capabilities like ARRAY JOIN, SAMPLE, and so on.).\nReturn solely the SQL question with none further explanations or feedback.\n\nYou'll be working with flight_data desk which has the next schema:\n\nColumn Identify | Information Kind | Null % | Instance Worth | Description\n--- | --- | --- | --- | ---\nyr | Int64 | 0.0 | 2024 | Yr of flight\nmonth | Int64 | 0.0 | 1 | Month of flight (1\u201312)\nday_of_month | Int64 | 0.0 | 1 | Day of the month\nday_of_week | Int64 | 0.0 | 1 | Day of week (1=Monday \u2026 7=Sunday)\nfl_date | datetime64[ns] | 0.0 | 2024-01-01 00:00:00 | Flight date (YYYY-MM-DD)\nop_unique_carrier | object | 0.0 | 9E | Distinctive provider code\nop_carrier_fl_num | float64 | 0.0 | 4814.0 | Flight quantity for reporting airline\norigin | object | 0.0 | JFK | Origin airport code\norigin_city_name | object | 0.0 | \"New York, NY\" | Origin metropolis identify\norigin_state_nm | object | 0.0 | New York | Origin state identify\ndest | object | 0.0 | DTW | Vacation spot airport code\ndest_city_name | object | 0.0 | \"Detroit, MI\" | Vacation spot metropolis identify\ndest_state_nm | object | 0.0 | Michigan | Vacation spot state identify\ncrs_dep_time | Int64 | 0.0 | 1252 | Scheduled departure time (native, hhmm)\ndep_time | float64 | 1.31 | 1247.0 | Precise departure time (native, hhmm)\ndep_delay | float64 | 1.31 | -5.0 | Departure delay in minutes (unfavourable if early)\ntaxi_out | float64 | 1.35 | 31.0 | Taxi out time in minutes\nwheels_off | float64 | 1.35 | 1318.0 | Wheels-off time (native, hhmm)\nwheels_on | float64 | 1.38 | 1442.0 | Wheels-on time (native, hhmm)\ntaxi_in | float64 | 1.38 | 7.0 | Taxi in time in minutes\ncrs_arr_time | Int64 | 0.0 | 1508 | Scheduled arrival time (native, hhmm)\narr_time | float64 | 1.38 | 1449.0 | Precise arrival time (native, hhmm)\narr_delay | float64 | 1.61 | -19.0 | Arrival delay in minutes (unfavourable if early)\ncancelled | int64 | 0.0 | 0 | Cancelled flight indicator (0=No, 1=Sure)\ncancellation_code | object | 98.64 | B | Purpose for cancellation (if cancelled)\ndiverted | int64 | 0.0 | 0 | Diverted flight indicator (0=No, 1=Sure)\ncrs_elapsed_time | float64 | 0.0 | 136.0 | Scheduled elapsed time in minutes\nactual_elapsed_time | float64 | 1.61 | 122.0 | Precise elapsed time in minutes\nair_time | float64 | 1.61 | 84.0 | Flight time in minutes\ndistance | float64 | 0.0 | 509.0 | Distance between origin and vacation spot (miles)\ncarrier_delay | int64 | 0.0 | 0 | Service-related delay in minutes\nweather_delay | int64 | 0.0 | 0 | Climate-related delay in minutes\nnas_delay | int64 | 0.0 | 0 | Nationwide Air System delay in minutes\nsecurity_delay | int64 | 0.0 | 0 | Safety delay in minutes\nlate_aircraft_delay | int64 | 0.0 | 0 | Late plane delay in minutes\n'''\n\ndef generate_direct_sql(rec):\n # making an LLM name\n message = shopper.messages.create(\n mannequin = \"claude-3-5-haiku-latest\",\n # I selected smaller mannequin in order that it is simpler for us to see the affect \n max_tokens = 8192,\n system=base_sql_system_prompt,\n messages = [\n {'role': 'user', 'content': rec['question']}\n ]\n )\n\n sql = message.content material[0].textual content\n \n # cleansing the output\n if sql.endswith('```'):\n sql = sql[:-3]\n if sql.startswith('```sql'):\n sql = sql[6:]\n return sql<\/code><\/pre>\n{\n'query': 'What was the very best velocity in mph?',\n'reply': '''\n choose max(distance \/ (air_time \/ 60)) as max_speed \n from flight_data \n the place air_time > 0 \n format TabSeparatedWithNames'''\n}<\/code><\/pre>\n# load analysis set\nwith open('.\/knowledge\/flight_data_qa_pairs.json', 'r') as f:\n qa_pairs = json.load(f)\nqa_pairs_df = pd.DataFrame(qa_pairs)\n\ntmp = []\n# executing LLM for every query in our eval set\nfor rec in tqdm.tqdm(qa_pairs_df.to_dict('data')):\n llm_sql = generate_direct_sql(rec)\n tmp.append(\n {\n 'id': rec['id'],\n 'llm_direct_sql': llm_sql\n }\n )\n\nllm_direct_df = pd.DataFrame(tmp)\ndirect_result_df = qa_pairs_df.merge(llm_direct_df, on = 'id')<\/code><\/pre>\nMeasuring high quality<\/h3>\n
\n
TabSeparatedWithNames<\/code>).<\/li>\nget_clickhouse_data<\/code> perform doesn\u2019t throw an exception. As a substitute, it returns textual content explaining the error, which could be dealt with by the LLM later.<\/p>\nCH_HOST = 'http:\/\/localhost:8123' # default tackle \nimport requests\nimport pandas as pd\nimport tqdm\n\n# perform to execute SQL question\ndef get_clickhouse_data(question, host = CH_HOST, connection_timeout = 1500):\n r = requests.publish(host, params = {'question': question}, \n timeout = connection_timeout)\n if r.status_code == 200:\n return r.textual content\n else: \n return 'Database returned the next error:n' + r.textual content\n\n# getting the outcomes of SQL execution\ndirect_result_df['llm_direct_output'] = direct_result_df['llm_direct_sql'].apply(get_clickhouse_data)\ndirect_result_df['answer_output'] = direct_result_df['answer'].apply(get_clickhouse_data)<\/code><\/pre>\nllm_judge_system_prompt = '''\nYou're a senior analyst and your activity is to match two SQL question outcomes and decide if they're equal. \nFocus solely on the info returned by the queries, ignoring any formatting variations. \nTake note of the preliminary consumer question and data wanted to reply it. For instance, if consumer requested for the common distance, and each queries return the identical common worth however in certainly one of them there's additionally a rely of data, it is best to contemplate them equal, since each present the identical requested info.\n\nReply with a JSON of the next construction:\n{\n 'reasoning': 'tmp = []\n\nfor rec in tqdm.tqdm(direct_result_df.to_dict('data')):\n attempt:\n judgment = llm_judge(rec, 'llm_direct')\n besides Exception as e:\n print(f\"Error processing report {rec['id']}: {e}\")\n proceed\n tmp.append(\n {\n 'id': rec['id'],\n 'llm_judge_reasoning': judgment['reasoning'],\n 'llm_judge_equivalence': judgment['equivalence']\n }\n )\n\njudge_df = pd.DataFrame(tmp)\ndirect_result_df = direct_result_df.merge(judge_df, on = 'id')<\/code><\/pre>\n# consumer question \nIn 2024, what share of time all airplanes spent within the air?\n\n# appropriate reply \nchoose (sum(air_time) \/ sum(actual_elapsed_time)) * 100 as percentage_in_air \nthe place yr = 2024\nfrom flight_data \nformat TabSeparatedWithNames\n\npercentage_in_air\n81.43582596894757\n\n# generated by LLM reply \nSELECT \n spherical(sum(air_time) \/ (sum(air_time) + sum(taxi_out) + sum(taxi_in)) * 100, 2) as air_time_percentage\nFROM flight_data\nWHERE yr = 2024\nFORMAT TabSeparatedWithNames\n\nair_time_percentage\n81.39\n\n# LLM decide response\n{\n 'reasoning': 'Each queries calculate the proportion of time airplanes \n spent within the air, however use completely different denominators. The primary question \n makes use of actual_elapsed_time (which incorporates air_time + taxi_out + taxi_in \n + any floor delays), whereas the second makes use of solely (air_time + taxi_out \n + taxi_in). The second question is strategy is extra correct for answering \n \"time airplanes spent within the air\" because it excludes floor delays. \n Nonetheless, the outcomes are very shut (81.44% vs 81.39%), suggesting minimal \n affect. These are materially completely different approaches that occur to yield \n comparable outcomes',\n 'equivalence': FALSE\n}<\/code><\/pre>\ndef get_llm_accuracy(sql, output, equivalence): \n issues = []\n if 'format tabseparatedwithnames' not in sql.decrease():\n issues.append('No format laid out in SQL')\n if 'Database returned the next error' in output:\n issues.append('SQL execution error')\n if not equivalence and ('SQL execution error' not in issues):\n issues.append('Fallacious reply supplied')\n if len(issues) == 0:\n return 'No issues detected'\n else:\n return ' + '.be part of(issues)\n\ndirect_result_df['llm_direct_sql_quality_heuristics'] = direct_result_df.apply(\n lambda row: get_llm_accuracy(row['llm_direct_sql'], row['llm_direct_output'], row['llm_judge_equivalence']), axis=1)<\/code><\/pre>\n![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/1600\/1*oUityyRHgCEMldnKQAFF0Q.png\")
Including a mirrored image step<\/h3>\n
simple_reflection_user_prompt_template = '''\nYour activity is to evaluate the SQL question generated by one other analyst and suggest enhancements if vital.\nVerify whether or not the question is syntactically appropriate and optimized for efficiency. \nTake note of nuances in knowledge (particularly time stamps varieties, whether or not to make use of complete elapsed time or time within the air, and so on).\nBe certain that the question solutions the preliminary consumer query precisely. \nBecause the outcome return the next JSON: \n{{\n 'reasoning': '![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/1600\/1*fBn9HoJJHtWnZE7l2MqdFg.png\")
![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/2400\/1*vsoqKVhiOEx9VhEbEle1lg.png\")
Database returned the next error: Code: 141. DB::Exception: Storage default.flight_data does not help sampling. (SAMPLING_NOT_SUPPORTED)<\/code>.<\/p>\n![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/1600\/1*JFsKJgR3O7tnrszxQrOk7Q.png\")
Reflection with exterior\u00a0suggestions <\/h3>\n
The output from the database (both knowledge or an error message)
Let\u2019s put collectively a immediate for this and generate a brand new model of the SQL.<\/p>\nfeedback_reflection_user_prompt_template = '''\nYour activity is to evaluate the SQL question generated by one other analyst and suggest enhancements if vital.\nVerify whether or not the question is syntactically appropriate and optimized for efficiency. \nTake note of nuances in knowledge (particularly time stamps varieties, whether or not to make use of complete elapsed time or time within the air, and so on).\nBe certain that the question solutions the preliminary consumer query precisely. \n\nBecause the outcome return the next JSON: \n{{\n 'reasoning': '![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/1600\/1*XlUBC2ByIuDBQa2phJNNWQ.png\")
![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/2400\/1*6_2AfwAxxEBZIkmih0UBxg.png\")
\n
avg(distance\/time)<\/code> or sum(distance)\/sum(time)<\/code>. Each choices are legitimate since nothing was specified within the consumer question or system immediate (assuming we don\u2019t have a predefined calculation technique).<\/li>\n<\/ul>\n![\"\"\/](\"https:\/\/cdn-images-1.medium.com\/max\/2400\/1*wCjbvibivJgbBrdAxRI1zw.png\")
\n