Oracle to Snowflake transpile - KEEP keyword support

[prabhur]

It seems like sqlgot retains the same syntax in both Oracle and Snowflake when a query has a KEEP keyword in an aggregate function.

import sqlglot
oracle_query_text = """
 SELECT
     department_id,
     department_name,
     MAX(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) AS most_recent_hire_salary,
     MAX(employee_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) AS most_recent_hire_name,
     MAX(hire_date) AS latest_hire_date
 FROM employees e
 JOIN departments d ON e.department_id = d.department_id
 GROUP BY department_id, department_name
 ORDER BY department_id;
 """

print(sqlglot.transpile(oracle_query_text, read="oracle", write="snowflake", pretty=True)[0])

SELECT
  department_id,
  department_name,
  MAX(salary) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) AS most_recent_hire_salary,
  MAX(employee_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) AS most_recent_hire_name,
  MAX(hire_date) AS latest_hire_date
FROM employees AS e
JOIN departments AS d
  ON e.department_id = d.department_id
GROUP BY
  department_id,
  department_name
ORDER BY
  department_id

Would you be able to add support for this, given Snowflake doesn't have an equivalent and an alternative will be to perhaps wrap the ranking logic in a CTE and use it.

Thanks for the awesome library.

[georgesittas]

Appreciate the kinds words :)

This seems like a complicated transformation, so it's not in scope for SQLGlot. It gives you the tools to achieve it though if you want.