Add option to store audit failures in database

[fredguth]

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:

project	model	audit_id	audit_description	query	failed	audited_at
Project A	Model X	audit_1	Initial audit	SELECT * ...	true	2024-08-28 07:56:36.366-03
Project B	Model Y	audit_2	...	...	false	2024-08-28 07:56:36.366-03
Project C	Model Y	audit_3	...	...	false	2024-08-28 07:56:36.366-03

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?