4

We are embarking in a project using both Matillion and Snowflake and want to put in place some Unit/Integration/Regression testing.

Automated would be brilliant but manual would be good too.

We could invent something (simple) ourselves... but it would be better to benefit from other people experience.

Eric Mamet
  • 2,681
  • 2
  • 13
  • 43
  • Hi @eric it is likely you may be looking for a code reference, however it will widely depend on the specifics of the unit testing you are trying to do. With that in mind, I would recommend adding more details to your question. Can you share one of the workloads you are looking to tackle first? – Rachel McGuigan Feb 11 '20 at 20:42
  • We do not have any precise code in mind yet. Just wondering whether there are tools / best practices that could give us a head start. For tools examples, with SQL Server I really liked tSQLt while with Postgres we used pgtap. But the availability/ease of Cloning databases might have implied some Snowflake specific practices?... – Eric Mamet Feb 12 '20 at 22:31
  • 3
    This is a great question because Matillion documentation doesn't provide any guidance on how to do automated testing. Our team has started using Python's [unittest](https://docs.python.org/2/library/unittest.html) module, to run queries and check the results. It's good for basic testing but hard to organize test modules in Python script components. We're also looking at a neat data testing package called [Great Expectations](https://docs.greatexpectations.io/en/latest/), but we're not sure how many of the features can be used within Matillion's ecosystem. – nofinator Feb 17 '20 at 14:48
  • Have you got any feedback on Great Expectations? I am interested by this tool... – Eric Mamet Mar 06 '22 at 21:19
  • HI @nofinator we are now working without Matillion. Just AWS S3 files loaded by Snowflake itself directly. Do you have conclusions of your look into https://docs.greatexpectations.io/en/latest/ ? – Eric Mamet Sep 22 '22 at 15:59
  • @EricMamet I'm no longer using Matillion either (thankfully). We had a meeting with Great Expectations and concluded it's next to impossible to use with Matillion anyway. However, I still haven't really tried Great Expectations. Instead, I've tried the test feature that's part of dbt (good, but not worth adopting dbt just for that) and Monte Carlo's monitors (great but costs $$). – nofinator Sep 23 '22 at 15:23

2 Answers2

3

Indeed there is a lack of possibility to unit/integration testing your models within Matillion. You need some external tools to implement these - we were using a Spring Boot Microservice with the following steps:

  1. Setup the Testdata with some Plain SQL scripts via JDBC connection to the underlying Database
  2. Run the correspondig job via the Matillion REST API
  3. Using JUnit to make assertion and verify the outcome
sudo
  • 747
  • 6
  • 19
  • Where do you orchestrate this from, Jenkins? – nofinator Feb 19 '20 at 17:05
  • 1
    You can build it with Maven or Gradle and orchestrate it with any CI/CD-tool like Jenkins or Gradle – sudo Feb 20 '20 at 12:54
  • Thanks for your answer but we have no java knowledge and currently learning python so it may not be for us... However, maybe we can do something similar in the Python world... – Eric Mamet Feb 29 '20 at 21:35
  • For sure - you could to it with JayDeBeApi to connecting to Snowflake, requests and unittest. Unfortunately there are no easy to use client APIs for Matillion – sudo Mar 06 '20 at 10:50
0

Looks like Matillion just released Object Validation in version 1.46. I haven't tried it yet but I imagine this would allow users to set up unit tests, which could then be scheduled or run after the orchestration job it's testing.

nofinator
  • 2,906
  • 21
  • 25