原文towardsdatascience.com/transforming-data-quality-automating-sql-testing-for-faster-smarter-analytics-6da431493570?sourcecollection_archive---------0-----------------------#2024-10-26如何测试 SQL 和结果数据集的质量以回答业务问题并增加客户信任https://medium.com/hello.akashm?sourcepost_page---byline--6da431493570--------------------------------https://towardsdatascience.com/?sourcepost_page---byline--6da431493570-------------------------------- Akash Mukherjee·发表于Towards Data Science ·阅读时长 11 分钟·2024 年 10 月 26 日–https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/4436d79ec654db529fca075e12919af5.png图片由Caspar Camille Rubin提供来自Unsplash在软件开发中有许多自动化测试工具和框架可以依赖。但是对于分析团队来说手动测试和数据质量保证QA仍然是常态。许多时候是客户或业务团队首先发现数据质量或完整性的问题而不是分析团队。这就是自动化能够产生巨大影响的地方。通过建立一个自动化系统使用脚本在大规模上运行数据质量测试你可以保持工作高效而不牺牲数据的准确性或完整性。当然当业务问题模糊或开放性时情况会变得更加复杂。在这种情况下规则基础逻辑与大语言模型LLM的结合可以提供极大帮助——让你生成场景并运行自动化检查。在本教程中我们将展示如何构建一个自动化测试系统用来评估和评分数据和 SQL 查询的质量即使业务问题是以简单的英文表达的。在我们开始之前你需要具备的知识要跟随本教程请确保你具备以下内容对数据库和 SQL 有扎实的理解有使用 Python 进行 API 调用和数据处理的经验访问 GPT-4 API 令牌用于测试的业务问题数据集设计系统架构为了构建一个自动化 QA 系统来评估 SQL 查询架构必须集成基于规则的逻辑、LLM 验证和自动评分。这个设置非常适合处理那些开放式的业务问题帮助您将测试从手动过程扩展到自动化。关键组件包括查询引擎接收并执行 SQL 查询的地方。评估模块结合静态规则与基于 LLM 的验证结果。评分系统根据不同用户角色如数据科学家、商业领袖和最终用户对结果进行评分。该架构包括一个反馈回路记录问题类型——例如缺失数据、错误粒度或性能缓慢等。这些信息将存储在一个集中式数据库中以便您可以随时间持续优化系统。我们将使用 Python 进行脚本编写使用 SQL 跟踪后端问题使用 OpenAI 的 LLM 解读自然语言输入。通过定期安排这些测试的运行您将保持一致的数据质量和可扩展性同时微调查询性能以与商业目标对齐。下图展示了数据如何流经系统——从 SQL 摄取到自动化测试、评分和问题跟踪——以便在大规模下保持高数据质量。最终这个系统不仅仅是发现错误——它推动持续改进并使您的技术执行与业务目标保持一致。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/64922a96612de5060deab240c1c68a64.png作者提供的图像用来说明技术架构的图示教程第 1 步准备测试问题与答案数据集要开始收集内部团队或客户经常向分析团队提出的真实业务问题。许多问题可能是临时的数据请求因此通过准备多种问题您可以确保测试具有相关性。以下是一些例子帮助您入手问题 #1“我们的 Pro Plan 用户中有多少是从试用版转化的”问题 #2“2024 年 6 月我们吸引了多少新用户”问题 #3“目前哪些产品正在流行”问题 #4“我们最畅销的产品目前的销售量是多少”第 2 步构建您的评估与评分标准2a定义您的评分员为了进行彻底的测试从不同角度设置评分员以确保覆盖所有方面最终用户关注可用性和清晰度。结果是否易于解读是否直接解决了原始的商业问题数据科学家评估技术准确性和完整性。所有必要的数据集是否已包含分析是否详细且可重复商业领袖关注与战略目标的对齐。输出是否支持与业务目标一致的决策2b定义评分标准每个评分员应根据特定因素来评估查询准确性查询是否提供正确的答案是否有数据点缺失或误解相关性输出是否包含所有必要的数据并排除了无关信息逻辑查询是否结构良好连接、过滤和聚合是否正确应用效率查询是否在没有额外复杂性或延迟的情况下进行了性能优化2c跟踪和记录问题类型为了涵盖所有方面记录查询执行过程中常见的问题非常重要。这样可以更容易地标记并运行自动化评估。错误粒度数据返回的细节级别不正确。过多列结果中包含不必要的字段造成杂乱。缺失数据输出中缺少关键数据。错误值计算或数值错误。性能问题查询运行效率低执行时间过长。importopenaiimportjson# Set your OpenAI API key hereopenai.api_keyyour-openai-api-keydefevaluate_sql_query(question,query,results):# Define the prompt with placeholders for question, query, and resultspromptf As an external observer, evaluate the SQL query and results against the clients question. Provide an assessment from three perspectives: 1\. End User 2\. Data Scientist 3\. Business Leader For each role, provide: 1\. **Overall Score** (0-10) 2\. **Criteria Scores** (0-10): - Accuracy: How well does it meet the question? - Relevance: Is all needed data included, and is irrelevant data excluded? - Logic: Does the query make sense? - Efficiency: Is it concise and free of unnecessary complexity? 3\. **Issue Tags** (2D array: [tag, details]): - Examples: Wrong Granularity, Excessive Columns, Missing Data, Incorrect Values, Wrong Filters, Performance Issues. 4\. **Other Observations** (2D array: [tag, details]) Client Question:{question}SQL Query:{query}SQL Results:{results}Respond ONLY in this format: json {{ endUser: {{overallScore: , criteriaScores: {{accuracy: , relevance: , logic: , efficiency: }}, issueTags: [], otherObservations: []}}, dataScientist: {{overallScore: , criteriaScores: {{accuracy: , relevance: , logic: , efficiency: }}, issueTags: [], otherObservations: []}}, businessLeader: {{overallScore: , criteriaScores: {{accuracy: , relevance: , logic: , efficiency: }}, issueTags: [], otherObservations: []}} }} py # Call the OpenAI API with the promptresponseopenai.Completion.create(enginegpt-4,# or whichever model youre usingpromptprompt,max_tokens500,# Adjust token size based on expected response lengthtemperature0# Set temperature to 0 for more deterministic results)# Parse and return the resultreturnjson.loads(response[choices][0][text])# Example usagequestionHow many Pro Plan users converted from trial?querySELECT COUNT(*) FROM users WHERE plan Pro AND status Converted AND source Trial;results250evaluationevaluate_sql_query(question,query,results)print(json.dumps(evaluation,indent4))第 3 步自动化测试3a遍历问题一旦收集了业务问题设置一个循环将每个问题、相关的 SQL 查询以及结果传递到评估函数中。这让你能够自动化整个评估过程确保每个查询都得到一致的评分。3b安排定期运行通过定期调度脚本运行来自动化测试过程——理想情况下在每次数据刷新或查询更新后运行。这保持测试与数据同步能够在问题出现时及时捕捉。3c在数据库中记录得分、标签和观察结果对于每次测试运行在结构化数据库中记录所有得分、问题标签和观察结果。使用 Python 脚本将相关数据填充到表格例如issue_catalog中。这为你提供了评估历史记录以便跟踪趋势、找出常见问题并优化未来的测试。第 4 步报告测试结果4a按得分进行透视和分组利用 SQL 查询或 BI 工具创建透视表根据总体得分和具体标准如准确性、相关性、逻辑性和效率对结果进行分组。这有助于你发现性能趋势找出哪些方面需要更多关注。为了计算每个查询在所有评分员中的总体得分可以使用加权公式总得分 w1​×准确性 w2​×相关性 w3​×逻辑性 w4​×效率其中 w1​、w2​、w3​、w4​ 是分配给每个评分标准的权重。这些权重的总和应为 1以便进行归一化。例如你可以根据数据科学家的优先级赋予准确性更高的权重而根据业务领导的优先级赋予相关性更高的权重。4b: 突出显示主要问题确定最频繁和最关键的问题——比如缺失数据、错误的粒度或性能低效。提供一份详细报告列出这些问题发生的频率以及哪些类型的查询最受影响。专注于如果不加以解决可能会导致更大错误的模式。例如突出显示可能因为数据质量问题而扭曲决策或减缓业务流程的情况。优先处理需要立即采取行动的问题例如那些影响查询性能或关键数据集准确性的问题并列出明确的后续步骤以解决它们。https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/b2484b2780ed989ca27648b77d518b00.png作者提供的图像使用示例测试数据创建的图表4c: 分析评分者的方差仔细查看不同评分者终端用户、数据科学家、业务领导评分之间的差异。较大的差异可能揭示出技术执行和业务目标之间的潜在错位。例如如果某个查询在技术准确性上得分较高但在与业务问题的相关性上得分较低这表明在将数据洞察转化为可操作结果时存在差距。类似地如果终端用户认为结果难以理解而数据科学家认为它们在技术上是正确的这可能指向沟通或呈现方面的问题。通过追踪这些差异你可以更好地将分析过程与技术精度和商业价值对齐从而保持所有利益相关者的满意。为了量化这种差异你可以计算评分者评分的方差。首先定义各个评分为S-EndUser​:终端用户的总体评分。S-DataScientist​:数据科学家的总体评分。S-BusinessLeader​来自业务领导的总体评分。三个评分者的平均分μ可以按如下公式计算μ (S-EndUser​ S-DataScientist​ S-BusinessLeader​​) / 3接下来计算方差σ²这是每个评分者的评分与平均评分之间的平方差的平均值。方差的公式是σ² (S-EndUser − μ)² (S-DataScientist − μ)**² (S-BusinessLeader − μ)**² / 3通过计算这种方差你可以客观地衡量评分者的评分差异。大的差异表明一个或多个评分者对查询质量或相关性的看法不同这可能意味着技术输出与业务需求之间需要更好的对齐。步骤 5创建反馈循环5a: 确定关键问题在整个测试过程中你很可能会发现一些问题反复出现。它们可能包括缺失的数据、错误的值、错误的粒度或性能低效等问题。重要的是不仅要记录这些问题还要对它们进行分类和优先级排序。例如如果缺少关键数据应该立即解决而性能优化可以视为长期的优化任务。通过专注于最有影响力和重复出现的问题你将能够有效改善数据质量并更好地解决根本原因。5b优化你的 SQL 查询现在你已经识别出重复出现的问题是时候更新 SQL 查询来解决它们了。这涉及到优化查询逻辑以实现准确的连接、筛选和聚合。例如如果遇到错误的粒度调整查询以适当的级别聚合数据。对于缺失的数据确保所有相关的表正确连接。如果出现性能问题简化查询、添加索引或使用更高效的 SQL 函数。目标是在此过程中将你记录的反馈转化为 SQL 代码的实际改进使你未来的查询更加精准、相关且高效。5c重新测试以验证结果一旦你的查询得到优化重新运行测试以验证改进效果。自动化这个步骤确保你更新后的查询始终与新的数据或业务问题进行评估。再次运行测试可以确认你的修改已解决问题并提高了整体数据质量。它还可以帮助确认你的 SQL 查询完全符合业务需求从而能够更快速和准确地获得洞察。如果出现新的问题只需将其反馈到循环中持续改进。自动化反馈循环的示例代码为了自动化这个反馈循环这里有一个 Python 脚本可以处理多个测试用例包括业务问题、SQL 查询和结果通过 OpenAI 的 API 评估这些用例并将结果存储到数据库中forquestion,query,resultsintest_cases:# Call the OpenAI API to evaluate the SQL query and resultsresponseopenai.Completion.create(enginetext-davinci-003,# Replace with GPT-4 or relevant enginepromptprompt.format(questionquestion,queryquery,resultsresults),max_tokens1000)# Process and store the responseprocess_response(response)defstore_results_in_db(test_run_id,question,role,scores,issue_tags,observations):# SQL insert query to store evaluation results in the issue cataloginsert_query INSERT INTO issue_catalog (test_run_id, question, role, overall_score, accuracy_score, relevance_score, logic_score, efficiency_score, issue_tags, other_observations) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s); db_cursor.execute(insert_query,(test_run_id,question,role,scores[overall],scores[accuracy],scores[relevance],scores[logic],scores[efficiency],json.dumps(issue_tags),json.dumps(observations)))db_conn.commit()设置问题目录表issue_catalog表作为存储详细测试结果的主要库帮助你清晰地跟踪查询性能并标记长期存在的问题。通过使用 JSONB 格式存储问题标签和观察结果你可以获得更大的灵活性允许你记录复杂信息而无需频繁更新数据库架构。以下是设置的 SQL 代码CREATE TABLE issue_catalog(idSERIAL PRIMARY KEY,test_run_id INT NOT NULL,question TEXT NOT NULL,role TEXT NOT NULL,--e.g.,endUser,dataScientist,businessLeader overall_score INT NOT NULL,accuracy_score INT NOT NULL,relevance_score INT NOT NULL,logic_score INT NOT NULL,efficiency_score INT NOT NULL,issue_tags JSONB,--Storing issue tagsasJSONforflexibility other_observations JSONB,--Storing other observationsasJSON created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);这个反馈循环的目的持续改进通过跟踪问题随着时间的推移你将能够细化 SQL 查询并稳步提高其质量。每次测试运行都会提供可操作的洞察通过针对最常见的问题你的系统会随着每次运行变得更高效和更具弹性。数据质量保证定期对更新的 SQL 查询进行测试帮助你验证它们是否正确处理新数据和测试用例。这个持续的过程展示了你的调整是否真正提高了数据质量并保持与业务需求的一致性从而降低未来问题的风险。与业务需求的一致性根据提出问题的人进行排序——无论是终端用户、数据科学家还是业务领导者——都能让你专注于对技术准确性和业务相关性都有重要意义的改进。随着时间的推移这将建立一个技术努力直接支持有意义的业务洞察的系统。可扩展的测试和优化这种方法在增加更多测试用例时能够平滑扩展。随着问题目录的扩展模式逐渐显现使得微调影响广泛业务问题的查询变得更加容易。随着每次迭代你的测试框架变得更强大推动数据质量在大规模上的持续改善。总结自动化 SQL 测试是分析团队的游戏规则改变者它帮助他们早期发现数据问题并精确地解决它们。通过建立一个结合规则逻辑和大语言模型LLM的结构化反馈循环你可以扩展测试处理即使是最复杂的业务问题。这种方法不仅提高了数据的准确性而且使你的洞察与业务目标保持一致。分析的未来依赖于自动化与洞察之间的平衡——你准备好迈出这一步吗