smartnoise-sql support for BigQuery, Redshift and Snowflake engines

[oskarkocol]

What do you think about adding the support for common cloud data warehouse engines to smartnoise-sql? It seems that we're almost there with the Postgres rewriter. But would such move align with the vision for OpenDP?

Cloud providers made it very easy to set up data warehouses for data analysis. Organizations of any size can now separate the low-latency, production-focused data bases from the low-cost, analytics-focused data warehouses. When choosing the provider for a data warehouse, Postgres is often not the first choice. Extending the smartnoise-sql support to the common data warehouse engines, could open the OpenDP project to a new community.

This brings a couple of questions that I'd love to hear your thoughts on: Would DP in a data warehouse be a frequent use case? Is adding such support worth it? Are there any technical limitations preventing smartnoise-sql from working with data warehouses? Would we need to tackle the SQLAlchemy rewriter, before being able to support the warehouse engines?

[joshua-oss]

Yes, data warehouses are a primary scenario that smartnoise-sql was designed for. Some nice properties of data warehouses with differential privacy:

• More data means higher accuracy for a fixed privacy guarantee. Data warehouses often have enough data to ensure the privacy can be provided "for free", with the noise addition being less than the sampling error.
• Data warehouses typically have already handled most of the preprocessing work necessary to ensure row-based user contribution, with fact tables and dimensions clearly designed
• Data warehouse engines usually perform better, since a lot of the aggregation work can be done ahead of time, or in an optimized manner in the backend engine
• Data warehouses often have aggregation/reporting jobs that are scheduled at periodic intervals (e.g. daily, weekly, etc.) which gives a natural way to think about privacy budget spend over repeated queries.

We actually use the SparkSQL connector against data warehouses with petabytes of data. I don't think the SQLAlchemy rewriter would be necessary to support BigQuery or Snowflake. SQLAlchemy rewriter will be useful for engines that don't support SQL-92, and
for adding support for things like quantiles, but SQL-92 engines should be pretty much supported already. For BigQuery, that means using the "standard SQL" dialect, and Snowflake is SQL-92 compatible throughout, AFAIK. Redshift might actually be SQL-92 compatible, I don't know.

For the engines that support SQL-92 and higher, the process is pretty quick:

1. Make a new class derived from SqlReader [1], probably by copying and renaming PostgresReader. Name it something like BigQueryReader.
2. Decide how to pass in an established BigQuery connection to the reader. Basically, this is a specification exercise where you decide what you want the calling code to look like in setting up the connection to the cloud and passing it in to the reader. Implement the constructor that stores the connection.
3. Track down the documentation about how BigQuery handles name comparison for table and column names (e.g. case-sensitive, how to escape names that have special characters, etc.). Verify if the rules in the NameCompare you copied are the same, or if they need to be changed. More details on NameCompare are in the design document.
4. Install some test data in the BigQuery cloud instance and test it with the BigQueryReader. It should "just work".
5. Update the helper methods to allow the system to automatically probe BigQuery connections and transparently instantiate the right reader (should be straightforward).

To include support for these engines in the PyPi package, we would want to make sure the new engine is being tested in all of our automated CI tests. CI tests run as linux docker containers in GitHub Actions, and there are a ton of SQL tests hitting all of the supported functionality against all of the supported engines. More details at [3]. We also support local execution of CI tests via act, so developers can ensure that all supported engines are working prior to pushing a new pull request.

For the cloud data warehouse providers, this would just involve creating a test environment with all of the test tables in [3] installed. If this could be done in a linux docker that can run both in GitHub Actions and locally, that would be ideal. If it requires some sort of subscription and cloud access, it gets more complicated, since we'd need to have GH Actions managing secret keys or whatever, handle billing, etc. All worthwhile tasks, but likely would be the most time-consuming part of supporting these engines.

[1] https://github.com/opendp/smartnoise-sdk/blob/main/sql/snsql/sql/reader/base.py
[2] https://github.com/opendp/smartnoise-sdk/blob/main/sql/docs/papers/SQL%20Design.pdf
[3] https://github.com/opendp/smartnoise-sdk/tree/main/sql/tests

[oskarkocol]

Hey, what a wholesome answer. It's great to hear that the support for cloud warehouses does make sense and is within the reach.

The testing was definitely in my blindspot and from @joshua-oss's answer, it is a requirement for a full support in a PyPi package. As far as I know, BigQuery and Snowflake don't offer any local emulator, so setting up billing and subscription might be inevitable. Since such testing framework is complex and would require a fair bit of DevOps work, I want to ask what you all think about the possible implementation.

My background is not in DevOps, but if I was to implement the testing tonight, I'd consider the following questions.

1. Are there any similar Open Source projects that run CI tests on cloud warehouses? What can be learned from them?

I am not aware of any at the moment.

2. How should the remote and local tests be handled for cloud warehouse engines?

• Option A: Only Remote tests are available. The test warehouse is maintained by OpenDP and it can be interacted with only through Github Actions.
• Option B: Local and Remote tests are available. Remote test setup is the same as above. The local tests require setting up a test warehouse by the contributor.
• Option C: Local and Remote tests are available. The test warehouse is maintained by OpenDP and it is open to public.

A sounds the easiest but with negative impact on developer efficiency. B is probably how things should be done. C would be easiest for contributors but is also open to abuse and would require some sort of request throttling.

3. How should the infrastructure be deployed?

• Option A: Cloud-specific CLI tools. For example, the BigQuery we could use the combination of gcloud to set up access combined with bq to specify tables schema. The data would have to be loaded in a separate step.
• Option B: Terraform. The table, schema and data load can be run together. Example.

Both options can be run via Github Actions. A might be a quick hack for supporting one warehouse and it would save the headache of storing Terraform states. Adding any new provider would require a new set of commands and a new set of dependencies. B could be reused across all cloud providers but requires a backend storage for the state files.

4. If Terraform, then where to store the states?

• Option A: Use Terraform Cloud as backend
• Option B: Use local backend

If tests were to be available locally, then contributors have to be able to run Terraform plans and applies. Backend A seems the most convenient but introduces a dependency on another third-party. The contributor would have to set up a Terraform Cloud account. B sounds reasonable but requires setting cloud credentials in the environment variables.