test(backends): enable tests on different backends

[jitingxu1]

enable unit tests on different backends

• backends testing env setup
• enable unit tests on different backends
• add this to CI

[deepyaman]

Seems like a good idea. I think it would also be a good idea to publish a step support matrix to the website, as it's hard to work around a step that contains operations that aren't supported on your backend (more so than on the core Ibis repo, where you may be able to drop into raw SQL or find some workaround), because step definitions are broader in scope. As a result, e.g. if somebody wants to use the TargetEncode step, they can first check whether it's supported on their backend.

[deepyaman]

Two more things to think about:

• Can we make the step support matrix without actually having to connect to backends in the CI? E.g. if quantile operation isn't supported on a backend, can we catch the would-be failure in the step definition without actually having to connect to an actual backend?
• Does it make sense to do this as unit tests, or as something higher-level (like BDD)?
• If this is a big task, should we prioritize a few backends first? Which ones?

[deepyaman]

Feedback:

• You can compile expressions without a connection (using ibis.to_sql and specify the dialect), and you should also be able to compile without any dependencies.

If you want to do execution testing (may not be necessary?):

• Start with 2 backends—DuckDB and something else (probably not BigQuery, too slow to iterate rapidly upon in a testing setup). Maybe pick Clickhouse as the second (differs from DuckDB on scale, default nullability behavior, function naming, etc.); something like Postgres is too similar to DuckDB in terms of SQL.