Solveddbt core Record failing rows for tests into an auditable table


Feature description

dbt should record rows that fail schema or data tests into tables, to be used for auditing. There are two general ways this could work:

  1. insert into an errors table with a schema like:
  • run started at
  • invocation id
  • test name
  • failing row primary key / json
  1. Create a table for each test failure in a scratch schema, matching the schema of the test query that failed

The baked-in schema tests will need to be rewritten to provide debuggable information in these tables.

This should probably be an opt-in feature for tests. It could be configured either in schema.yml, though it would also be good to configure whole swaths of tests at once.

Who will this benefit?

dbt users could use this table to quickly determine why their tests failed. At present, it's surprisingly difficult to debug a failed test.

18 Answers

βœ”οΈAccepted Answer

I support and would use the use case of reporting on failed tests. I am more interested in using them for auditing than for debugging β€”Β I would like a nice easy table to query via Looker for a conditional "Go Fix ETL" alert.

When I went looking to do this feature I naturally reached for hooks, as I'm using them already for audit purposes much as described in the documentation.

I'm logging the individual model run:

    - "create table if not exists {{ target.schema }}.audit_dbt_runs (model text, state text, time timestamp)"

    - "insert into {{ target.schema }}.audit_dbt_runs (model, state, time) values ('{{}}', 'start', getdate())"
    - "insert into {{ target.schema }}.audit_dbt_runs (model, state, time) values ('{{}}', 'end', getdate())"

As well as the overall results:

    - "create table if not exists {{ target.schema }}.audit_dbt_results (node text, status text, execution_time decimal(10,2), time timestamp)"
    - "insert into {{ target.schema }}.audit_dbt_results (node, status, execution_time, time) values {{ results_values(results) }}"

(I have a simple macro results_values to turn the results into insertable values.)

In this case I would generally like the exact same thing for tests. Something exactly analogous to the on-run-start/on-run-end with the end run context results would suit my needs:

    - "create table if not exists {{ target.schema }}.audit_dbt_test_results (node text, status text, execution_time decimal(10,2), time timestamp)"
    - "insert into {{ target.schema }}.audit_dbt_test_results (node, status, execution_time, time) values {{ results_values(results) }}"

Other Answers:

Hi @Aylr and @gordonhwong!

In case it's still useful for anyone, the following macro is what I use:

{% macro results_values(results) %}
  {% for res in results -%}
    {% if loop.index > 1 %},{% endif %}
    ('{{ res.node.alias }}', '{{ res.status }}', {{ res.execution_time }}, getdate())
  {% endfor %}
{% endmacro %}

This pairs with:

    - "create table if not exists {{ target.schema }}.audit_dbt_results (node text, status text, execution_time decimal(10,2), time timestamp)"
    - "insert into {{ target.schema }}.audit_dbt_results (node, status, execution_time, time) values {{ results_values(results) }}"

Hey @bashyroger, thanks for bringing this topic back to the fore. I just opened a new issue to sketch out our latest thinking about how dbt should store tests in the database: #2593

Related Issues:

dbt core [Feature] dbt should know about metrics
I am just loving this thread! Thank you all so much for contributing your thoughts and expertise in ...
dbt core Record failing rows for tests into an auditable table
I support and would use the use case of reporting on failed tests Feature Feature description dbt sh...
dbt core Specify Columns to update on incremental Models
Got it! Sure this makes a lot of sense in merge statements Is this on Snowflake/BigQuery? I think a ...
dbt core Subselectors for state:modified
@jtcohen6 just here to give a big πŸ‘ to the idea of modification subselectors Specifically state:mod...
dbt core support UDFs on BigQuery
PLEASE NOTE THAT THE WORKAROUND IS NO LONGER REQUIRED as per the last comment of this thread @Trista...
amplify js Uncaught ReferenceError: global is not defined in latest Angular 6 RC
Just for reference I have passed through this issue with adding these lines on my index.html head: ...
react native firebase πŸ”₯(Android) Program type already present: io.invertase.firebase.BuildConfig
I think I found my ultimate error here In my package.json file I had at some point earlier ...
amplify cli Many-To-Many
You can implement many to many yourself using two 1-M @connections and a joining @model ...
superset Was unable to import superset Error: cannot import name '_maybe_box_datetimelike'
tx @Uneasy-listening !!! This worked for me: [only necessary if you have already installed pandas (p...
amplify js fetch is not defined
nodejs fix: I'm using amazon-cognito-auth-js with my express app and I'm following the case 1 exampl...
amplify js Error: No credentials, applicationId or region
I had the same issue (running on the latest Amplify v3) and worked around it by changing the followi...
amplify js Is it possible to get cognito user attributes in Lambda/cloud logic ?
I have been looking around for a while I feel the answers here didn't really answer the problem ...
react native firebase πŸ”₯ Version mismatch causing app termination
Looks like GoogleAppMeasurement gets imported as a dependency with version 5.3.0 A simple addition t...
react native firebase onNotificationOpened not working on Android (background/foreground)
Solved the issue only on background using @ZardozSpeaks approach Under my ...
grafana HTTP Error Bad Gateway when using prometheus
I still face this error after explicitly writing the URL (in my case it was http://localhost:9090) ...
amplify cli aws-exports.js is not generated
Even after the third read I find it utterly confusing and I have usability issues too ...
amplify js Auth Error: Amplify has not been configured correctly using Nuxt.js
I 'm having the same issue in aws-amplify: ^3.0.11 I found out Auth module didn't load configs of aw...
react native firebase Firebase dependency updates are required to fix gradle v4+ builds
@DeepaSriramRR as a temporal workaround you can disable version check of the Google plugin At the en...
react native firebase AndroidX support
Play Services just shipped AndroidX breaking changes - if you must upgrade your android Firebase SDK...
react native firebase RNFirebase core module was not found natively on ios
I will just leave it here in case someone comes and it's still struggling with this ...
amplify js How to refresh Cognito tokens
It will refresh if you call the SDK for it e.g. with Auth.currentSession() and it finds an expired t...
react native firebase [πŸ“š] AdMob - use @invertase/react-native-google-ads
Just FYI we're getting close here AdMob documentation availability Hi there! I've noticed that AdMob...
react native firebase [SOLVED with v2.1.1] Undefined symbols for architecture x86_6: _OBJC_CLASS_$_RNFirebaseDatabaseReference
Okay.. if anybody comes accross this magic error try this It solved the problem (for now) Close Xcod...
react native firebase [android] No Firebase app '[DEFAULT]' has been created - call firebase.initializeApp(), js engine: hermes
I spent a lot of hours for found who was the problem most setup issues such as default app has not b...
react native firebase iOS: Firebase.h not found
Can you first close xcode then delete the xcworkspace & Podfile.lock files in the ios directory then...
amplify cli @auth public/private IAM roles and other Providers
ok my bad was actually quite easy just do : and add a auth provider in my case was IAM ...
react native firebase Android: JDK 10 not supported for v4.1.0
+1 4.1.0 broke builds for Java10 As a temporary workaround on MacOS Issue Upon upgrading to v4.1.0 a...
amplify js Sign up multiple different accounts with the same email
The pre-signup trigger can be used to prevent the new signup from being created when there's an exis...
ClickHouse DB::Exception: Too many parts (600). Merges are processing significantly slower than inserts
Each insert create a folder in /var/lib/clickhouse/.../table_name/ Inside that folder there are 2 fi...
amplify js Amplify Console 200 (Rewrite) fails on SPA React (Router) Application
This worked for me source: </^((?!.(css|gif|ico|jpg|js|png|txt|svg|woff|ttf)$).)*$/> target address:...
react native firebase Notification not showing up in foreground after triggering displayNotification() (Android)
It seems as if the requirements for displaying a foreground notification are stricter on Android ...
amplify js aws-amplify 0.3.0: "Uncaught ReferenceError: require is not defined" when packaged with webpack
Ok I found something that helped Graphql-js uses .mjs as file extension which caused issues with the...
react native firebase Unable to instantiate service io.invertase.firebase.messaging.MessagingService: java.lang.ClassNotFoundException
My problem was solved by removing these lines from my AndroidManifest.xml Issue I've updated both RN...
react native firebase pod install fails after npm install @react-native-firebase/firestore
I tried the above solutions and didn't work for me I solved the issue by deleting the ./ios/Podfile....
react native firebase cannot find symbol BuildConfig.APPLICATION_ID
@mikehardy seems like it should be enough to rename APPLICATION_ID to LIBRARY_PACKAGE_NAME in /app/a...
amplify js Identity providers authentication against User Pools WITHOUT hosted UI
@martimarkov we find a solution for you to use the customized button to do that ...
react native firebase Error: You attempted to use a firebase module that's not installed on your Android project by calling firebase.notifications()
The solution for this is to add those to app/build.gradle : implementation '
react native firebase IOS unable to receive notification
It appears as if RNFirebase documentation is missing a few required steps Issue: Not able to receive...
amplify cli jest-haste-map: Haste module naming collision: -> namefunction <-
For React Native 0.6x configure the blacklist in metro.config.js instead of rn-cli.config.js as per ...
amplify js Getting "no current user" after successful login to Cognito UserPool
I had the same problem but for me removing the cookie storage configuration in aws-exports.js solved...
amplify js How to add user to Group
πŸ‘† Also we have achieved this using the Post Confirmation Lambda trigger Very simplified from our im...
react native firebase RNFirebaseNotifications.h file not found
I think in the header search paths it should be set to recursive for $(SRCROOT)/../node_modules/reac...
amplify js RFC: Amplify Library Modularization and Bundle Size Improvement
Modularization is available in Preview Hi all With us launching Modularization in the coming weeks ...
react native firebase Multiple dex files define Lcom/google/firebase/iid/zzb;
I got the same conflict with react-native-device-info and solved it with : After adding this package...
react native firebase Document how to mock react-native-firebase for jest
For now I've got this going on and it works for me (you may need to mock more or fewer modules or mo...
nativescript plugin firebase new iOS error on google auth - presenting view controller must be set
Google Auth was previously working on iOS but now I am receiving this error with no changes to codeb...
react native firebase Support Firebase SDK v5.0.0+
There are a number of breaking changed in v5 of the iOS pods: (
react native firebase πŸ”₯ [πŸ›] Crashlytics could not determine stripped/unstripped native library directories for project ':app'
Not sure if it is suitable for everyone but with my team with spotted that if in this step : https:/...