4 ms·

I see a lot of replies saying something to the effect that "SQL is actually really testable, people just don't do it" which I'm sure is true, but probably less

I see a lot of replies saying something to the effect that "SQL is actually really testable, people just don't do it" which I'm sure is true, but probably less helpful if you're having to work on a tech stack that doesn't offer unit testing (I'm thinking something like Azure Data Factory or even some setups/configurations of airflow). Would love to hear from anyone out there who has a good test cycle for predominantly SQL based code? What tools are you using? How does the process work out for you? What are some pitfalls you found along the way?

code_biologist 3y ago
I gave a positive review of dbt's testing capabilities in this other comment: https://news.ycombinator.com/item?id=36720013 https://news.ycombinator.com/item?id=36720013 This is what the actual generic tests look like. There's also support for one-off SQL-based tests: https://docs.getdbt.com/reference/resource-properties/tests#out-of-the-box-tests https://docs.getdbt.com/reference/resource-properties/tests#... This article covers a lot of ground as to how to actually use dbt tests in practice. I'm particularly glad they mentioned the dbt-utils and dbt-expectations libraries: https://www.datafold.com/blog/7-dbt-testing-best-practices https://www.datafold.com/blog/7-dbt-testing-best-practices The dbt ecosystem is also a strength. This article has good examples of the type of tooling and libraries in the ecosystem: https://meltano.com/blog/top-5-of-dbt-packages-and-tools-in-2022/ https://meltano.com/blog/top-5-of-dbt-packages-and-tools-in-... I have no affiliation with any of the above linked companies, just a happy dbt user! I'll also add that I don't use the dbt cloud, just invoke the dbt cli in a backend task runner.
waffletower 3y ago
We use dbt. It definitely does improve the testability of SQL and provides a nexus for engineers and analysts to collaborate. I still heavily advocate keeping its utilization surface as low as possible, and maintaining efficient, compact, and incremental data modeling.
rawgabbit 3y ago
What I tell people about SQL is that there are two different domains: (a) operational data ingestion which is usually for the current month and (b) analytics historical data which is for all time. Think of how a business runs its finances and accounting. The analysts run daily reports to ensure the data ingested yesterday ran correctly. They enter adjusting entries as needed and call Data Engineering to fix any integration issues. At the end of the month, they "close" the month and that data is now historical. For the first scenario, data ingestion... I typically have seen the business analysts and engineering analysts run daily reports to ensure all the integrations and systems were successful. When data engineers deploy new code, yes there is also limited testing. The most frequent problem I see is when the business analysts merge duplicate records; this throws everything off. Engineering will have to work with the business to correct a slew of systems and records to adjust for the fact where there was once two John Doe records and hundreds of children records in multiple systems... there is now one John Doe record, hundreds of children records, and new adjusting records. You can argue, there shouldn't have been two John Doe records in the first place. That is true if you only manage only one system. In many companies there are subsidiaries which have their own data entry points. e.g., When a big corp acquires smaller companies, they will want to suck up the subsidiary data and reconcile all the duplicates. It takes years to sort out all the system integrations. At the end of the month, when the data becomes historical, the company may decide to change its business rules going forward. So the historical data violates the new business rules. e.g., During a teacher shortage, the local school district says we will hire military veterans without college degrees; this year we will go back to the old policy. e.g., This year the state decides to start daylight savings time earlier than the rest of the country. That is it is very time consuming to write test cases for historical data especially if no one documents the historical business rules which happens more often than you think.
- iamcreasy 3y ago
  > So the historical data violates the new business rules. How did you solve this? Is there a set of standard practices to address situation like this?
  - rawgabbit 3y ago
    There is no tech best practice I know of. Even the business finance folks have no solution. This is where idealism hits reality. Yeah it would nice to say that GitHub and source control can give you state. But as we all know code is not reality. It is an idealized representation of reality where we would like to believe business rules don’t change and things never fail.

code_biologist 3y ago

I gave a positive review of dbt's testing capabilities in this other comment: https://news.ycombinator.com/item?id=36720013 https://news.ycombinator.com/item?id=36720013 This is what the actual generic tests look like. There's also support for one-off SQL-based tests: https://docs.getdbt.com/reference/resource-properties/tests#out-of-the-box-tests https://docs.getdbt.com/reference/resource-properties/tests#... This article covers a lot of ground as to how to actually use dbt tests in practice. I'm particularly glad they mentioned the dbt-utils and dbt-expectations libraries: https://www.datafold.com/blog/7-dbt-testing-best-practices https://www.datafold.com/blog/7-dbt-testing-best-practices The dbt ecosystem is also a strength. This article has good examples of the type of tooling and libraries in the ecosystem: https://meltano.com/blog/top-5-of-dbt-packages-and-tools-in-2022/ https://meltano.com/blog/top-5-of-dbt-packages-and-tools-in-... I have no affiliation with any of the above linked companies, just a happy dbt user! I'll also add that I don't use the dbt cloud, just invoke the dbt cli in a backend task runner.

waffletower 3y ago

We use dbt. It definitely does improve the testability of SQL and provides a nexus for engineers and analysts to collaborate. I still heavily advocate keeping its utilization surface as low as possible, and maintaining efficient, compact, and incremental data modeling.

rawgabbit 3y ago

What I tell people about SQL is that there are two different domains: (a) operational data ingestion which is usually for the current month and (b) analytics historical data which is for all time. Think of how a business runs its finances and accounting. The analysts run daily reports to ensure the data ingested yesterday ran correctly. They enter adjusting entries as needed and call Data Engineering to fix any integration issues. At the end of the month, they "close" the month and that data is now historical. For the first scenario, data ingestion... I typically have seen the business analysts and engineering analysts run daily reports to ensure all the integrations and systems were successful. When data engineers deploy new code, yes there is also limited testing. The most frequent problem I see is when the business analysts merge duplicate records; this throws everything off. Engineering will have to work with the business to correct a slew of systems and records to adjust for the fact where there was once two John Doe records and hundreds of children records in multiple systems... there is now one John Doe record, hundreds of children records, and new adjusting records. You can argue, there shouldn't have been two John Doe records in the first place. That is true if you only manage only one system. In many companies there are subsidiaries which have their own data entry points. e.g., When a big corp acquires smaller companies, they will want to suck up the subsidiary data and reconcile all the duplicates. It takes years to sort out all the system integrations. At the end of the month, when the data becomes historical, the company may decide to change its business rules going forward. So the historical data violates the new business rules. e.g., During a teacher shortage, the local school district says we will hire military veterans without college degrees; this year we will go back to the old policy. e.g., This year the state decides to start daylight savings time earlier than the rest of the country. That is it is very time consuming to write test cases for historical data especially if no one documents the historical business rules which happens more often than you think.

iamcreasy 3y ago
> So the historical data violates the new business rules. How did you solve this? Is there a set of standard practices to address situation like this?
- rawgabbit 3y ago
  There is no tech best practice I know of. Even the business finance folks have no solution. This is where idealism hits reality. Yeah it would nice to say that GitHub and source control can give you state. But as we all know code is not reality. It is an idealized representation of reality where we would like to believe business rules don’t change and things never fail.