[Feature] Leverage other python lib to generate sql code instead of hand-crafting all platform-specific code

[chulucninh09]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing metricflow functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Hi, currently we have to implement SQL code render for every platform. Can we use other python lib such as sqlglot to have generic code that can generate code more programtically? Another perk is we can immediately have 20+ dialec supported.

Describe alternatives you've considered

Implement per-platform code, which is labor-costing and easy to break, hard to maintain when interfaces change

Who will this benefit?

Data Engineer, Data Analyst who just want to deliver their application and quickly adapt to other platform

Are you interested in contributing this feature?

Yes

Anything else?

No response

[Jstein77]

Hey @chulucninh09 thanks for opening this issue! I wanted to share a bit more context about our decision making with respect to adding something like sqlglot.

There are a few main things to consider here:

• We don't do any transpiling. We do explicit rendering based on structured inputs. There are advantages to this, like it allows us to do a lot of customization on how we handle inputs or target rendering for outputs, although I don't think we actually take advantage of any of them today.
• The rendering isn't the hard part, so it's not clear to me that taking a dependency on SqlGlot is worth the overhead of dealing with their transpilation and having to gate features on whether or not SqlGlot supports the thing we need to produce.
• For adding new dialects it's super easy for someone to chunk our output SQL into SqlGlot and translate it.

Also worth noting, the idea that we "support 20+ dialects immediately" is incorrect. We might support 20+ dialects immediately, but then we need to spin up the test suites and make sure the rendered + transpiled queries actually work so there's definitely overhead here.

Since we don't have any work planned to address this i'm gonna transfer this to a discussion so we can keep the conversation open.

[Jstein77]

I created a discussion for this here: #1219