[Spike] Adding SAT testing to Java connectors

[evantahler]

A Spike to learn more about what it might look like to add SAT testing to our Java / Database connectors.

• Per DRAFT: Demo Python SAT to detect backwards breaking changes on mysql connector #16445, we know that we can add the basic plumbing to all Sources to run a basic SAT test against the connector. Is this true for all sources?
• To test more than just CHECK, we would need to seed data for the source to connect to. What additional features would need to be added to SAT for this to work? (e.g. a "before_test" and "after_test hook for seeding and teardown)? Or, can we use databases we already host for testing - Are the secrets already formatted for java sources in such a way that SAT can use them?

Goal of this story:

• The output of this spike is a Tech Spec (use template) for what the next steps are for this project, and what work we need to do to get there.

[DoNotPanicUA]

Current state of existing tests coverage :

Database source	is relational DB?	Run tests in containers	SAT*	SAT testFullRefreshRead impl	SAT testDiscover impl	DAT*
bigquery	➕	➖	✔️	❌	❌	✔️
clickhouse	➕	➕	✔️	❌	❌	❌
cockroachdb	➕	➕	✔️	❌	❌	✔️
db2	➕	➕	✔️	❌	❌	✔️
mssql	➕	➕	✔️	❌	❌	✔️
mysql	➕	➕	✔️	❌	❌	✔️
oracle	➕	➕	✔️	❌	❌	✔️
postgres	➕	➕	✔️	❌	❌	✔️
redshift	➕	➖	✔️	❌	✔️	❌
scaffold-java-jdbc	➕	❕	❌	❌	❌	❌
snowflake	➕	➖	✔️	❌	❌	✔️
tidb	➕	➕	✔️	❌	❌	❌
elasticsearch	➖	➕	✔️	❌	❌	❌
kafka	➖	➕	✔️	❌	❌	❌
mongodb-v2	➖	➖	✔️	❌	✔️	❌
sftp	➖	➕	✔️	❌	❌	❌

*SAT - source acceptance test SourceAcceptanceTest
*DAT - datatype acceptance test AbstractSourceDatabaseTypeTest

[DoNotPanicUA]

Current coverage of the SAT:

Test name	Description
testIdenticalFullRefreshes	Run full refresh twice and check that results are not empty and equal
testEntrypointEnvVar	Check that AIRBYTE_ENTRYPOINT environment variable is set
testIncrementalSyncWithState	Only if a source supports incremental sync. Run the first sync and check that result is not empty and that the state is set. The second sync should not return any records.
testEmptyStateIncrementalIdenticalToFullRefresh	Only if a source supports incremental sync. Check that full refresh provides the same records as incremental sync without a state message.
testGetSpec	Run SPEC and compare the result with the expected result.
testFullRefreshRead	Checks that full refresh returns some records. Local check implementation is required!
testCheckConnection	Run CHECK and assert that result is successful.
testDiscover	Run discover and check that it returns something. Local check implementation is required!

[DoNotPanicUA]

Link to the draft tech spec

[DoNotPanicUA]

Review Python SAT from the existing SAT/DAT.

Name	Is it already covered by Java SAT/DAT?	Implement source instance is required.	Custom implementation is required	Test description
Test Spec	❌	➖	➖	Checks that spec.json absolutely equals this file from previous version.
Test Connection	✔️	➕	➖	Checks that a source can connect to an instance using credentials
Test Discovery	❌	➕	Create structure equals config catalog file	Compares discover result with prepared config catalog file. Checks that discover the result is equal to the previous version.
Test Basic Read	✔️	➕	Create structure + data population	Read data and compare with expected result
Test Full Refresh sync	✔️	➕	Create structure + data population	Proofs that two syncs return the same records
Test Incremental sync (TestTwoSequentialReads)	✔️	➕	Create structure + data population	Checks that second sync returns no records
Test Incremental sync (TestReadSequentialSlices)	✔️	➕	Create structure + data population	Validated STATE filtering works right
Test Incremental sync (TestStateWithAbnormallyLargeValues)	✔️	➕	Create structure + data population	Checks that no records return if STATE in the future

[evantahler]

Updated Tech Spec - https://docs.google.com/document/d/1551FZmMH5y8Nn1m6wy8_O3-yVNpdADBPAcAuZK1sHSU/edit#