[CT-1227] [Spike] Ibis on Snowflake

[lostmygithubaccount]

Spike to proof-of-concept Ibis on Snowflake (Snowpark?)

The purpose of this spike is to evaluate the work needed to add Snowflake as an additional backend to Ibis.

Why?

See dbt-labs/dbt-core#5904.

Snowflake is the most popular adapter for dbt. Ibis doesn't support Snowflake, but I don't see any technical reason blocking that. We (or Snowflake) should evaluate this as we consider it as a vendor-neutral standard for analytics in Python.

What?

Evaluate the work needed to add Snowflake as a backend for Ibis. See acceptance criteria for details.

Additional reading

• https://github.com/ibis-project/ibis-substrait
• https://github.com/ibis-project/ibis-bigquery
  • possible point of reference?

Acceptance criteria

No working code is required.

We should answer:

• why isn't Snowflake a supported backend already?
• Snowflake or Snowpark?
  • if Snowpark compiles down to SQL, and Ibis compiles down to SQL, is there a need for Snowpark?
• what's the process for adding a new backend in Ibis?
• what kind of backend would Snowflake be? (direct, string, something else?)
• rough outline of technical details
• rough estimate of amount of work needed
• if feasible, proof-of-concept code

[lostmygithubaccount]

they did it first https://ibis-project.org/docs/dev/backends/Snowflake/

[lostmygithubaccount]

We should answer:

• why isn't Snowflake a supported backend already?

It is in ibis>=4.0.0! looks like something they got to. I've played around with it here: https://github.com/dbt-labs/dbt-demo-data/blob/cody/ibis/ibis.ipynb it works pretty well

• Snowflake or Snowpark?
  • if Snowpark compiles down to SQL, and Ibis compiles down to SQL, is there a need for Snowpark?

both could technically work: either compile Ibis down to SnowSQL locally and ship it off a la dbt-sql, or simply let it run in Snowpark. the former is limited in capability, the latter is slower and more flexible

• what's the process for adding a new backend in Ibis?

TBA

• what kind of backend would Snowflake be? (direct, string, something else?)

looks like string? it outputs SnowSQL code

• rough outline of technical details

TBA

• rough estimate of amount of work needed

support via plugin: dbt-labs/dbt-core#6184

• if feasible, proof-of-concept code

TBW -- writing some based off the PRQL PR: dbt-labs/dbt-core#5982

primary challenges:

• authenticating
• getting the ref or source
• how does the output work? need to call something like ibis.snowflake.compile somewhere

[lostmygithubaccount]

implemented in dbt-labs/dbt-core#6296