Store SQLMesh Audit Failures for Later Consumption

Summary

Add an option to sqlmesh audit to store failed audit results in a table for later analysis and reporting.

Background

Similar features exist in other data tools:

• dbt: store_failures
• Dataform: Assertions

Use Case

Storing failed audit results enables:

• Historical tracking of audit performance
• Integration with data catalogs and quality dashboards
• Detailed analysis of failure patterns

Example: Generating a data catalog with a "Quality" tab showing all audits and their results, similar to Great Expectations Cloud.

Proposed Solution

1. Create a new table for failed audits

SELECT * FROM audits.failed

Example output:

2. Store detailed failure information

For each failed audit, create a separate table with failure details:

SELECT * FROM audits.audit_1

Alternative: JSON Export

Consider exporting failed values as a JSON object for flexibility.

Implementation Details

• Add a new command-line option to sqlmesh audit (e.g., --store-failures)
• Implement logic to create and populate the audits.failed table
• Create individual tables for each failed audit
• (Optional) Implement JSON export functionality

Questions

• How long should failed audit data be retained?
• Is there a way to store any kind of audit failure in the same assertion_table independent of the model and the audit query?