cduarte-ta opened a new issue, #74477:
URL: https://github.com/apache/airflow/issues/74477
**TL;DR:** After every Snowflake task, OpenLineage runs an extra
`INFORMATION_SCHEMA.QUERY_HISTORY()` query to fetch query metadata. In our
account it costs about 1.8 s per task on a user warehouse, it can return no row
because of the 100-row default `RESULT_LIMIT`, and there's no way to turn it
off without disabling lineage entirely. Switching to `QUERY_HISTORY_BY_USER()`
with a time window was about 2.7× faster and always found the row in our tests.
Making the lookup configurable would also help.
### Versions
apache-airflow-providers-snowflake 6.17.0 (code unchanged on `main`),
Airflow 3.x on Astronomer.
### What happened
`SnowflakeHook.get_openlineage_database_specific_lineage` hardcodes
`query_for_extra_metadata=True`, so `_get_queries_details_from_snowflake` runs
this after every task:
```sql
SELECT QUERY_ID, EXECUTION_STATUS, START_TIME, END_TIME, QUERY_TEXT,
ERROR_CODE, ERROR_MESSAGE
FROM table(snowflake.information_schema.query_history())
WHERE QUERY_ID = '<id>';
```
1. **Cost:** one Airflow service user ran 9,103 of these in 7 days (about 1
per 9 real queries), 4.6 h in total: avg 1.83 s, p90 about 2.6 s, max 18 s.
Most of it is warehouse execution time.
2. **Missed lookups:** `QUERY_HISTORY()` returns at most 100 rows (the
default `RESULT_LIMIT`) of *all* queries visible to the role, and the `WHERE`
filter is applied afterwards. For a role with wide visibility, those 100 rows
covered about 2 seconds, so a query that had just run was not found:
```sql
select 1; set qid = last_query_id();
select count(*) from table(snowflake.information_schema.query_history())
where query_id = $qid; -- 0
select count(*) from
table(snowflake.information_schema.query_history_by_user()) where query_id =
$qid; -- 1
```
When the lookup misses, no error is raised. The events quietly fall back
to `now()` and the task state.
3. **No opt-out:** `[openlineage] disabled_for_operators` turns off all
lineage for the operator, which breaks downstream consumers (Datadog, in our
case).
### Benchmark
One session, 6 interleaved runs each, result cache off, same warehouse,
filtered on a query that had just run:
| variant | p50 | found |
|---|---|---|
| `query_history()` (current) | 853 ms | not reliably |
| `query_history(end_time_range_start => -10 min)` | 723 ms | 0/6 |
| `query_history_by_user()` | 443 ms | yes |
| `query_history_by_user(end_time_range_start => -10 min)` | 308 ms | yes |
### Suggested fix
1. Use `QUERY_HISTORY_BY_USER()`. The code comment already says the intent
is the current user's queries.
2. Pass `END_TIME_RANGE_START` (e.g. the task start time) and a
`RESULT_LIMIT` of at least `len(query_ids)`.
3. Make `query_for_extra_metadata` configurable instead of hardcoded.
Code: `providers/snowflake/.../utils/openlineage.py`
(`_get_queries_details_from_snowflake`) and `hooks/snowflake.py`
(`get_openlineage_database_specific_lineage`).
### Are you willing to submit PR?
- [ ] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's Code of Conduct
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
