Following fixes are implemented:

• Temporary table names are now prefixed with '#' as required by MS SQL dialect
• Temporary table names only persist within a connection so engine is overridden with a connection (the same happens for SQLite)
• Create temporary table using "select .. into {table_name} from ..
• head() now uses "top" instead of "limit" for MS SQL
• get_column_quantiles(): add an empty over() clause as required for MS SQL
• get_column_stdev(): use "stdevp" function for MS SQL instead of "stddev_samp"
• Use column introspection fallback in initialization as introspection seems to return an empty list. The fallback for MS SQL is modified to also retrieve the column types.

Open issues that I don't know how to fix:

• ~get_table_columns(): returns an empty list because table introspection yields an empty column list~
• ~get_column_count(): returns 0 (because get_table_columns() is empty)~
• ~expect_column_values_to_be_of_type(): doesn't work because ~get_table_columns() returns an empty list~ introspection fallback does not provide column type information~
• ~expect_column_values_to_be_in_type_list(): idem~
• expect_column_values_to_be_unique(), expect_column_values_to_not_be_null(), expect_column_values_to_be_null() raise errors (such as reported in #932, #1098)

This should close #1110.

This PR replaces CONTRIBUTING.md with a full-fledged community contributions section.

It's still a draft---some sections are still mostly empty.

I'm creating a draft PR (1) so that the rest of the team can start to orient to a working model with more clarity and predictability for community members, and (2) to progressively get feedback on the sections that are ready for it.

I spent my flight to Dublin cranking away at documentation.

It's coming together pretty nicely.

Can you take a look at the first five expectations and see if you like this format? If so, we can roll it out to the rest of them.

Also, please take a look at the diffs in the rest of the docs. I've moved some pieces around and fixed some lingering inaccuracies.

Sample pics for expectation docs are below:

Hey,

I quickly drafted a first version of the overall success field and summary statistics for the validation report. I'll write test and update the docs as soon as we settle on the exact scope of this PR.

In summary: the validation report now contains some infos about the overall success as well as stats about the results:

{
  "results": [...],  # unchanged
   "success": True,
   "stats": {
     "n_total": 10,
     "n_success": 10,
     "n_failed": 0,
   }

What do you think? Do we need the stats? Should the names me different?

TODO:

• [X] intial implementation
• [X] fix old failing tests
• [X] write tests
• [X] update docs
• [x] rebase/clean up commits
• [x] decide on name "details/statistics" field

This PR closes #305.

In pandas, a column containing 1, 2, 3, 4, abcd will be parsed as a series of strings "1", "2", "3", "4", "abcd". That means that if I run expect_column_values_to_be_of_type on this column, I get:

success=False : This is good partial_exception_list = ["1", "2", "3", "4", "abcd"] : This is bad.

Proposal:

1. Drop the column_map_expectation expect_column_values_to_be_of_type
2. Replace it with a column_aggregate_expectation: expect_column_to_be_of_type
3. Add two new column_map_expectations: expect_column_values_to_be_int_parseable, and expect_column_values_to_be_float_parseable

Notes:

• If expect_column_values_to_be_of_type gets a series with mixed types, it always returns false.
• If expect_column_values_to_be_int_parseable gets a series of ints, it returns true. Ditto for floats in expect_column_values_to_be_float_parseable.

This draft PR adds limited support for Google Cloud Platform. It includes:

• A Google Cloud Storage (GCS) store backend
• SQLAlchemy backend support for BigQuery via pybigquery

Note that a GCS generator was out of scope for this PR, but should be implemented in a future release to achieve full parity between AWS and GCP.

Describe the bug I am using the v3 API, SparkDFExecutionEngine with the following classes ExpectationConfiguration RuntimeBatchRequest context.get_validator().validate().

When I include "catch_exception" in the expectation kwargs or in context.get_validator().validate(catch_exceptions=True), an exception is still thrown.

Calculating Metrics:  50%|█████     | 4/8 [00:00<00:00,  5.30it/s]Traceback (most recent call last):
  File "/opt/project/src/main/glue/reproduce.py", line 92, in <module>
    expectation_suite=suite,
  File "/usr/local/lib/python3.6/site-packages/great_expectations/validator/validator.py", line 1209, in validate
    "result_format": result_format,
  File "/usr/local/lib/python3.6/site-packages/great_expectations/validator/validator.py", line 474, in graph_validate
    metrics = self.resolve_validation_graph(graph, metrics, runtime_configuration)
  File "/usr/local/lib/python3.6/site-packages/great_expectations/validator/validator.py", line 519, in resolve_validation_graph
    runtime_configuration=runtime_configuration,
  File "/usr/local/lib/python3.6/site-packages/great_expectations/validator/validator.py", line 560, in _resolve_metrics
    metrics_to_resolve, metrics, runtime_configuration
  File "/usr/local/lib/python3.6/site-packages/great_expectations/execution_engine/execution_engine.py", line 282, in resolve_metrics
    **metric_provider_kwargs
  File "/usr/local/lib/python3.6/site-packages/great_expectations/expectations/metrics/metric_provider.py", line 58, in inner_func
    return metric_fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/great_expectations/expectations/metrics/map_metric_provider.py", line 465, in inner_func
    message=f'Error: The column "{column_name}" in BatchData does not exist.'
great_expectations.exceptions.exceptions.ExecutionEngineError: Error: The column "unknown_column" in BatchData does not exist.
Calculating Metrics:  50%|█████     | 4/8 [00:00<00:00,  4.45it/s]

To Reproduce Code to reproduce the behavior:

from great_expectations.data_context import BaseDataContext
from great_expectations.data_context.types.base import InMemoryStoreBackendDefaults
from great_expectations.data_context.types.base import DataContextConfig
from great_expectations.core import ExpectationSuite, ExpectationConfiguration
from great_expectations.core.batch import RuntimeBatchRequest
from pyspark.sql import SparkSession
import pandas as pd

data_context_config = DataContextConfig(
    datasources={
        "spark_datasource": {
            "execution_engine": {
                "class_name": "SparkDFExecutionEngine",
                "module_name": "great_expectations.execution_engine",
            },
            "class_name": "Datasource",
            "module_name": "great_expectations.datasource",
            "data_connectors": {
                "runtime_data_connector": {
                    "class_name": "RuntimeDataConnector",
                    "batch_identifiers": [
                        "domain_id",
                        "component_name"
                    ]
                }
            }
        }
    },
    validation_operators={
        "action_list_operator": {
            "class_name": "ActionListValidationOperator",
            "action_list": [
                {
                    "name": "store_validation_result",
                    "action": {"class_name": "StoreValidationResultAction"},
                },
                {
                    "name": "store_evaluation_params",
                    "action": {"class_name": "StoreEvaluationParametersAction"},
                },
                {
                    "name": "update_data_docs",
                    "action": {"class_name": "UpdateDataDocsAction"},
                },
            ],
        }
    },
    expectations_store_name="expectations_store",
    validations_store_name="validations_store",
    evaluation_parameter_store_name="evaluation_parameter_store",
    checkpoint_store_name="checkpoint_store",
    store_backend_defaults=InMemoryStoreBackendDefaults(),
)

context = BaseDataContext(project_config=data_context_config)
suite: ExpectationSuite = context.create_expectation_suite("suite", overwrite_existing=True)

expectation_configuration = ExpectationConfiguration(
    expectation_type='expect_column_values_to_not_be_null',
    kwargs={
        'catch_exceptions': True,  # expect exceptions to be caught
        'result_format': 'SUMMARY',
        'include_config': False,
        'column': 'unknown_column'  # intentionally incorrect column to force error
    },
    meta={
        "Notes": "Some notes"
    }
)
suite.add_expectation(expectation_configuration=expectation_configuration)

pandasDF = pd.DataFrame(data=[['Scott'], ['Jeff'], ['Thomas'], ['Ann']], columns=['Name'])

spark = SparkSession.builder.appName("local").getOrCreate()
sparkDF = spark.createDataFrame(pandasDF)
sparkDF.show()

runtime_batch_request = RuntimeBatchRequest(
    datasource_name="spark_datasource",
    data_connector_name="runtime_data_connector",
    data_asset_name="insert_your_data_asset_name_here",
    runtime_parameters={
        "batch_data": sparkDF
    },
    batch_identifiers={
        "domain_id": "ininfsgi283",
        "component_name": "some_component",
    }
)
validator = context.get_validator(
    batch_request=runtime_batch_request,
    expectation_suite=suite,
).validate()
results = validator.results
print(results)

Environment (please complete the following information):

• Operating System: MacOS
• Great Expectations Version: 0.13.26

Thanks for your time.

Is your feature request related to a problem? Please describe. When an expectation is run the output includes a “partial_unexpected_list” property of values that were unexpected. While this is useful, in most cases, it doesn't allow teams to identify, resolve, or divert data with poor quality.

It would be great if a sample list or a complete list (result_format SUMMARY, COMPLETE) of ID's for each row that failed an expectation was included in the validation result.

Describe the solution you'd like One could include a column name as an argument in the expectation that they would like to be included in the "partial_unexpected_list" (or a new property).

Describe alternatives you've considered One could try infer the PK for a table but this is not possible for all engine types E.g. Spark.

Additional context Enabling teams to not only bring light to data quality issues but identify all rows allows them to address poor data quality in real-time instead of requiring manual intervention.

[FEATURE]/ Added pairwise expectation 'expect_column_pair_values_to_be_in_set' sqlalchemy dataset.py file

Changes proposed in this pull request:

• Allows users to make use of the pairwise expectation 'expect_column_pair_values_to_be_in_set' for SQLAlchemy backends in v2 GE -Provides the 'column_pair_map_expectation 'decorator for other pairwise expectations to be developed for SQLAlchemy backends in v2 GE

After submitting your PR, CI checks will run and @cla-bot will check for your CLA signature.

Definition of Done

Please delete options that are not relevant.

• [x] My code follows the Great Expectations style guide
• [x] I have performed a self-review of my own code
• [x] I have commented my code, particularly in hard-to-understand areas
• [x] I have made corresponding changes to the documentation
• [ ] I have added unit tests where applicable and made sure that new and existing tests are passing.
• [ ] I have run any local integration tests and made sure that nothing is broken.

Describe the bug This is probably not a bug and is user error but I didn't see a suitable template.. I am trying to run expectations via code (not the CLI) as a part of my ETL pipeline in order to validate data before it goes to production. I want to save the expectation results json and upload it to S3 and setup a S3-hosted data docs to pull from those results & the expectation suite.

To Reproduce Steps to reproduce the behavior:

1. Run the below attached code
2. Note that when data docs opens, it only contains the expectations, and not the results of those expectations.

Expected behavior Data docs contains the results of my expectations.

Environment (please complete the following information):

• Operating System: Linux & MacOS
• Great Expectations Version: [e.g. 0.14.1]

Additional context If there is a better way to do this that say better leverages existing great_expectations features, do please point me in that direction. Notably, I couldn't make the CLI configuration of my great_expectations.yml work for me, as I need this to run dynamically in a pipeline, uploading to different locations depending on client.

import great_expectations as ge
from great_expectations.data_context.types.base import DataContextConfig, DatasourceConfig, FilesystemStoreBackendDefaults
from great_expectations.data_context import BaseDataContext
import numpy as np
import pandas as pd
import json
import os
from datetime import datetime
from great_expectations.data_context.types.resource_identifiers import (ExpectationSuiteIdentifier,
    ValidationResultIdentifier,
)

df = pd.DataFrame(np.random.randint(0,100,size=(100, 4)), columns=list('ABCD'))

abs_path = os.getcwd() + '/great_expectations'

data_context_config = DataContextConfig(
    datasources={
        "my_pandas_datasource": DatasourceConfig(
            class_name="PandasDatasource",
        )
    },
    store_backend_defaults=FilesystemStoreBackendDefaults(root_directory=abs_path),
)
context = BaseDataContext(project_config=data_context_config)

domain_name = 'test'

suite = context.create_expectation_suite(domain_name, overwrite_existing=True)

batch_kwargs = {
    "datasource": 'my_pandas_datasource',
    "dataset": df,
    "data_asset_name": domain_name,
}

batch = context.get_batch(batch_kwargs, "test")

print(batch.head())

batch.expect_table_row_count_to_be_between(max_value=250, min_value=10)

batch.expect_table_column_count_to_equal(value=4)

batch.expect_table_columns_to_match_ordered_list(
    column_list=[
        "A",
        "B",
        "C",
        "D",
    ]
)

batch.expect_column_values_to_not_be_null(column="A",
    result_format='COMPLETE')

batch.expect_column_values_to_be_null(column="A",
    result_format='COMPLETE')

batch.expect_column_values_to_be_in_set(
    column="A",
    value_set=["A", "B", "C", "D", "E", "F"],
    result_format='COMPLETE'
)

results = batch.validate()


# This step is optional, but useful - evaluate the Expectations against the current batch of data
run_id = {
"run_name": domain_name,
"run_time": datetime.now()
}
results = batch.validate(expectation_suite=None,
                                run_id=None,
                                data_context=context,
                                evaluation_parameters=None,
                                catch_exceptions=True,
                                only_return_failures=False,
                                run_name=domain_name,
                                run_time=datetime.now(),)

# save the Expectation Suite (by default to a JSON file in great_expectations/expectations folder
# batch.save_expectation_suite(suite, domain_name, discard_failed_expectations=False)
batch.save_expectation_suite(discard_failed_expectations=False)

# Neither details nor meta (I inferred this as expected) seem to contain an expectation_suite_identifier
# expectation_suite_identifier = list(results["details"].keys())[0]
# expectation_suite_identifier = list(results["meta"].keys())[0]
# print('expectation_suite_identifier')
# print(expectation_suite_identifier)

validation_result_identifier = ValidationResultIdentifier(
    expectation_suite_identifier=domain_name,
    # expectation_suite_identifier=expectation_suite_identifier,
    batch_identifier=batch.batch_kwargs.to_id(),
    run_id=run_id
)

# This doesn't work
# context.build_data_docs()

# Neither does this
# context.build_data_docs(domain_name, results)
# context.open_data_docs(domain_name)

# Neither does this
# context.build_data_docs(domain_name, suite_identifier)
# context.open_data_docs(suite_identifier)
# context.open_data_docs(validation_result_identifier)

# Neither does this
suite_identifier = ExpectationSuiteIdentifier(expectation_suite_name=domain_name)
context.build_data_docs(domain_name, suite_identifier)
context.open_data_docs()

with open('validation_results.json', 'w') as f:
    f.write(str(results))

Describe the bug print(validator.head()) gives

for (metric_name, metric_configuration) in metrics.items()
KeyError: ('table.head', 'batch_id=7d60dd00fbc6ebeac60da5ea671dccb0', '04166707abe073177c1dd922d3584468')

when connecting to BigQuery instance

To Reproduce I am trying to configure the great_expectations tool

https://greatexpectations.io/

and here is the documentation i followed

https://docs.greatexpectations.io/docs/guides/connecting_to_your_data/database/bigquery

and i was able to create

import os

from ruamel import yaml

import great_expectations as ge
from great_expectations.core.batch import BatchRequest, RuntimeBatchRequest
gcp_project = 'my-gcp-project'
bigquery_dataset = 'my-dataset'

CONNECTION_STRING = f"bigquery://{gcp_project}/{bigquery_dataset}"

context = ge.get_context()
datasource_config = {
    "name": "my_bigquery_datasource",
    "class_name": "Datasource",
    "execution_engine": {
        "class_name": "SqlAlchemyExecutionEngine",
        "connection_string": "bigquery://my-gcp-project/my-dataset",
    },
    "data_connectors": {
        "default_runtime_data_connector_name": {
            "class_name": "RuntimeDataConnector",
            "batch_identifiers": ["default_identifier_name"],
        },
        "default_inferred_data_connector_name": {
            "class_name": "InferredAssetSqlDataConnector",
            "name": "whole_table",
        },
    },
}

datasource_config["execution_engine"]["connection_string"] = CONNECTION_STRING

context.test_yaml_config(yaml.dump(datasource_config))

context.add_datasource(**datasource_config)

batch_request = RuntimeBatchRequest(
    datasource_name="my_bigquery_datasource",
    data_connector_name="default_runtime_data_connector_name",
    data_asset_name="json_data",  # this can be anything that identifies this data
    runtime_parameters={"query": "SELECT * from mytable where colA='someVal' LIMIT 10"},
    batch_identifiers={"default_identifier_name": "default_identifier"},
    batch_spec_passthrough={
        "bigquery_temp_table": "ge_temp"
    },  # this is the name of the table you would like to use a 'temp_table'
)

context.create_expectation_suite(
    expectation_suite_name="test_suite", overwrite_existing=True
)
validator = context.get_validator(
    batch_request=batch_request, expectation_suite_name="test_suite"
)
print(validator.head())

assert isinstance(validator, ge.validator.validator.Validator)

however, when i run this i get

  Traceback (most recent call last):
  File "geBq.py", line 55, in <module>
    print(validator.head())
  File "/some/path/python3.7/site-packages/great_expectations/validator/validator.py", line 1620, in head
    "fetch_all": fetch_all,
  File "/some/path/python3.7/site-packages/great_expectations/validator/validator.py", line 364, in get_metric
    return self.get_metrics({"_": metric})["_"]
  File "/some/path/python3.7/site-packages/great_expectations/validator/validator.py", line 359, in get_metrics
    for (metric_name, metric_configuration) in metrics.items()
  File "/some/path/python3.7/site-packages/great_expectations/validator/validator.py", line 359, in <dictcomp>
    for (metric_name, metric_configuration) in metrics.items()
  KeyError: ('table.head', 'batch_id=7d60dd00fbc6ebeac60da5ea671dccb0', '04166707abe073177c1dd922d3584468')

what does this error mean? how can i resolve it? i just want to connect to my BigQuery table and then run some validations on it. Btw, i am able to query this table on the BigQuery console

Expected behavior I should be able to successfully connect Great Expectations with my data on BigQuery instance

Environment (please complete the following information):

• Operating System: Linux
• Great Expectations Version: 0.13.37

Additional context Please let me know if I can add any other details

Changes proposed in this pull request:

• Tests that were introduced by #6656 were squashed by #6676 because of a bad merge. This PR re-adds them to the tests/checkpoint/test_checkpoint_result_format.py file

• adding query
• first push with test
• setting the terminology a little differently

Please annotate your PR title to describe what the PR does, then give a brief bulleted description of your PR below. PR titles should begin with [BUGFIX], [FEATURE], [DOCS], [MAINTENANCE], or [CONTRIB]. If a new feature introduces breaking changes for the Great Expectations API or configuration files, please also add [BREAKING]. You can read about the tags in our contributor checklist.

Changes proposed in this pull request:

After submitting your PR, CI checks will run and @cla-bot will check for your CLA signature.

For a PR with nontrivial changes, we review with both design-centric and code-centric lenses.

In a design review, we aim to ensure that the PR is consistent with our relationship to the open source community, with our software architecture and abstractions, and with our users' needs and expectations. That review often starts well before a PR, for example in GitHub issues or Slack, so please link to relevant conversations in notes below to help reviewers understand and approve your PR more quickly (e.g. closes #123).

Previous Design Review notes:

Definition of Done

Please delete options that are not relevant.

• [ ] My code follows the Great Expectations style guide
• [ ] I have performed a self-review of my own code
• [ ] I have commented my code, particularly in hard-to-understand areas
• [ ] I have made corresponding changes to the documentation
• [ ] I have added unit tests where applicable and made sure that new and existing tests are passing.
• [ ] I have run any local integration tests and made sure that nothing is broken.

Thank you for submitting!

Changes proposed in this pull request:

• Use get_context within BaseDataContext factory function

Definition of Done

• [x] My code follows the Great Expectations style guide
• [x] I have performed a self-review of my own code
• [x] I have commented my code, particularly in hard-to-understand areas
• [x] I have run any local integration tests and made sure that nothing is broken.

Describe the bug When going through the steps to generate a new expectation suite from a sample batch of data, the process will fail with a Unable to determine reader method from path error if the batch is a csv file with an uppercase extension (i.e., myfile.CSV).

To Reproduce Steps to reproduce the behavior:

1. Follow the tutorial to create a new datasource (steps 1 and 2).
2. Rename one of the files in the data folder to have an uppercase .CSV extension
3. Go through step 3 of the tutorial, selecting the uppercase .CSV file as the source of the batch data
4. It will fail with the above error
5. Do step 3 but for the other, lowercase, .csv file instead, and note that it succeeds

Expected behavior .csv and .CSV should be treated the same way

Environment (please complete the following information):

• Operating System: MacOS
• Great Expectations Version: 0.15.41

Additional context N/A

Please annotate your PR title to describe what the PR does, then give a brief bulleted description of your PR below. PR titles should begin with [BUGFIX], [FEATURE], [DOCS], [MAINTENANCE], or [CONTRIB]. If a new feature introduces breaking changes for the Great Expectations API or configuration files, please also add [BREAKING]. You can read about the tags in our contributor checklist.

Changes proposed in this pull request:

After submitting your PR, CI checks will run and @cla-bot will check for your CLA signature.

For a PR with nontrivial changes, we review with both design-centric and code-centric lenses.

In a design review, we aim to ensure that the PR is consistent with our relationship to the open source community, with our software architecture and abstractions, and with our users' needs and expectations. That review often starts well before a PR, for example in GitHub issues or Slack, so please link to relevant conversations in notes below to help reviewers understand and approve your PR more quickly (e.g. closes #123).

Previous Design Review notes:

Definition of Done

Please delete options that are not relevant.

• [ ] My code follows the Great Expectations style guide
• [ ] I have performed a self-review of my own code
• [ ] I have commented my code, particularly in hard-to-understand areas
• [ ] I have made corresponding changes to the documentation
• [ ] I have added unit tests where applicable and made sure that new and existing tests are passing.
• [ ] I have run any local integration tests and made sure that nothing is broken.

Thank you for submitting!

Implementing deep copy of runtime_configuration variable in graph_validation function

Please annotate your PR title to describe what the PR does, then give a brief bulleted description of your PR below. PR titles should begin with [BUGFIX], [FEATURE], [DOCS], [MAINTENANCE], or [CONTRIB]. If a new feature introduces breaking changes for the Great Expectations API or configuration files, please also add [BREAKING]. You can read about the tags in our contributor checklist.

Changes proposed in this pull request:

• closes #6565

After submitting your PR, CI checks will run and @cla-bot will check for your CLA signature.

For a PR with nontrivial changes, we review with both design-centric and code-centric lenses.

In a design review, we aim to ensure that the PR is consistent with our relationship to the open source community, with our software architecture and abstractions, and with our users' needs and expectations. That review often starts well before a PR, for example in GitHub issues or Slack, so please link to relevant conversations in notes below to help reviewers understand and approve your PR more quickly (e.g. closes #123).

Previous Design Review notes:

Definition of Done

Please delete options that are not relevant.

• [x] My code follows the Great Expectations style guide
• [x] I have performed a self-review of my own code
• [x] I have added unit tests where applicable and made sure that new and existing tests are passing.
• [x] I have run any local integration tests and made sure that nothing is broken.

Thank you for submitting!