Ask HN: How do you test SQL?

Article summary

The article asks for resources and best practices on testing SQL pipelines, specifically for a data team using DBT. The author is looking for ways to apply best practices for testing SQL pipelines. The goal is to ensure the quality and reliability of the data pipelines. The article seeks recommendations on patterns, tools, and reference materials for testing SQL pipelines.

Main themes

• SQL pipeline testing
• DBT testing
• ORM limitations
• Raw SQL testing
• Data observability
• Testing frameworks
• Integration testing
• Data pipeline quality assurance

What commenters say

• Testing SQL queries at the application level can be effective, but may not provide the same level of granularity as unit testing.
• Using an ORM can simplify testing, but may not handle complex analytical queries well.
• Raw SQL can be preferable for complex queries, but requires additional effort to generate types and mappings for each query's result set schema.
• Testing SQL pipelines can be done through various methods, including black box testing, performance testing, and data observability tools.
• DBT tests are a good starting point for testing SQL pipelines, but may not cover all use cases.
• Using a testing framework like pgTAP can provide a structured approach to testing SQL queries.
• Testing SQL queries in isolation can be beneficial, but may not capture the complexities of real-world data pipelines.
• Integration testing with a real database instance can provide more comprehensive coverage of SQL queries and data pipelines.