I have created a test to check if the aggregation of datasets is going well. When I preview the developed test in the IDE, there are no results returned (as desired) and when I run the test in the IDE it passes and gets a green checkmark. The test:Used test query and the result in IDE: Passed in IDE

To check the tests weekly, I have created a job for my tests. However, I get a error and fail on the same test/query.The error: Error in test job

Does anyone have any idea where this fail is coming from? And how I can solve this?

CodePudding user response：

It's likely your tests are running against different targets -- in the Cloud IDE, you may have configured dbt to build models into a development schema, and then it will test those models in the development schema. In your weekly job, you're likely running against the production target, which would build models into another schema.

There are some likely causes for development and production schemas to contain different data (which would cause the test to pass against one environment and fail in the other):

• Code in your development branch may not match that in the main/production branch. Be sure to pull main and then merge main into dev or rebase your dev branch onto the head of main.
• The code may match, but the state of the models themselves may vary. I recommend using dbt build -s orders_items_rev_influencer to ensure all upstream models have been built in the dev target.
• There may be additional configuration that makes dev and prod intentionally different, which may be causing your tests to pass in dev and fail in prod. One common strategy is to subset large datasets in development, so they build faster (e.g., by filtering out data older than one week/month/year). If the old data is causing the test failure, your tests could pass in dev and fail in prod.

A good place to start with failing tests is to view the test failure itself. To do this, I would get the compiled test code from the target directory (e.g., target/compiled/test/scheduled_test_good/orders_items_revenue_influencer.sql) and run that in a SQL client against dev and prod and view the record that is returned. You may then want to use that data to write a new query on the source table that will return more detail. Once you know what record is failing, you can start to debug the model.

Good luck!