How to configure a Validation Result Store to PostgreSQL

By default, Validation ResultsGenerated when data is Validated against an Expectation or Expectation Suite. are stored in JSON format in the uncommitted/validations/ subdirectory of your great_expectations/ folder. Validation Results can include sensitive or regulated data that should not be committed to a source control system. Use the information provided here to configure Great Expectations to store Validation Results in a PostgreSQL database.

Prerequisites

Completion of the Quickstart guide.
A working installation of Great Expectations.
A Data Context.
An Expectations Suite.
A Checkpoint.
A PostgreSQL database with appropriate credentials.

1. Configure the `config_variables.yml` file with your database credentials

GX recommends storing database credentials in the config_variables.yml file, which is located in the uncommitted/ folder by default, and not part of source control.

To add database credentials, open config_variables.yml and add the following entry below the db_creds key:
```
db_creds:
  drivername: postgresql
  host: '<your_host_name>'
  port: '<your_port>'
  username: '<your_username>'
  password: '<your_password>'
  database: '<your_database_name>'
```
To configure the config_variables.yml file, or additional environment variables, see How to configure credentials.

Optional. To use a specific schema as the backend, specify schema as an additional keyword argument. For example:

db_creds:
  drivername: postgresql
  host: '<your_host_name>'
  port: '<your_port>'
  username: '<your_username>'
  password: '<your_password>'
  database: '<your_database_name>'
  schema: '<your_schema_name>'

                                  
                                

2. Identify your Data Context Validation Results Store

The configuration for your Validation Results StoreA connector to store and retrieve information about objects generated when data is Validated against an Expectation Suite. is available in your Data ContextThe primary entry point for a Great Expectations deployment, with configurations and methods for all supporting components.. Open great_expectations.ymland find the following entry:

validations_store_name: validations_store

stores:
  validations_store:
      class_name: ValidationsStore
      store_backend:
          class_name: TupleFilesystemStoreBackend
          base_directory: uncommitted/validations/

                              
                            

This configuration tells Great Expectations to look for Validation Results in the validations_store Store. The default base_directory for validations_store is uncommitted/validations/.

3. Update your configuration file to include a new Validation Results Store

Add the following entry to your great_expectations.yml:

validations_store_name: validations_postgres_store

stores:
  validations_postgres_store:
      class_name: ValidationsStore
      store_backend:
          class_name: DatabaseStoreBackend
          credentials: ${db_creds}

                              
                            

In the previous example, validations_store_name is set to validations_postgres_store, but it can be personalized. Also, class_name is set to DatabaseStoreBackend, and credentials is set to ${db_creds}, which references the corresponding key in the config_variables.yml file.

4. Confirm the addition of the new Validation Results Store

In the previous example, a validations_store on the local filesystem and a validations_postgres_store are configured. Great Expectations looks for Validation Results in PostgreSQL when the validations_store_name variable is set to validations_postgres_store. Run the following command to remove validations_store and confirm the validations_postgres_store configuration:

great_expectations store list

- name: validations_store
class_name: ValidationsStore
store_backend:
  class_name: TupleFilesystemStoreBackend
  base_directory: uncommitted/validations/

- name: validations_postgres_store
class_name: ValidationsStore
store_backend:
  class_name: DatabaseStoreBackend
  credentials:
      database: '<your_db_name>'
      drivername: postgresql
      host: '<your_host_name>'
      password: ******
      port: '<your_port>'
      username: '<your_username>'

                              
                            

5. Confirm the Validation Results Store is configured correctly

Run a Checkpoint to store results in the new Validation Results store in PostgreSQL, and then visualize the results by re-building Data Docs.

Great Expectations creates a new table in your database named ge_validations_store, and populates the fields with information from the Validation Results.

Prerequisites​

1. Configure the config_variables.yml file with your database credentials​

2. Identify your Data Context Validation Results Store​

3. Update your configuration file to include a new Validation Results Store​

4. Confirm the addition of the new Validation Results Store​

5. Confirm the Validation Results Store is configured correctly​