search

astronomer / astro-sdk

Astro SDK allows rapid and clean development of {Extract, Load, Transform} workflows using Python and SQL, powered by Apache Airflow.
https://astro-sdk-python.rtfd.io/
Apache License 2.0
349 stars 43 forks source link

load_file replacing existing tables fails if there are views on the tables #1929

Open mpgreg opened 1 year ago

mpgreg commented 1 year ago

load_file(if_exists='replace') works the first time a DAG is run. Upstream tasks subsequently create views on that DAG. Rerunning the DAG fails as it cannot drop the existing tables because of the dependent views.

[2023-04-30, 09:32:12 UTC] {taskinstance.py:1776} ERROR - Task failed with exception Traceback (most recent call last): File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context self.dialect.do_execute( File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute cursor.execute(statement, parameters) psycopg2.errors.DependentObjectsStillExist: cannot drop table tmp_astro.stg_customers because other objects depend on it DETAIL: view tmp_astro.customers depends on table tmp_astro.stg_customers HINT: Use DROP ... CASCADE to drop the dependent objects too.

Version

To Reproduce

Example DAG is at: https://github.com/astronomer/airflow-ml-demo/blob/c2e150649301458ef4fe26d8b6bd6c3f30836137/dags/customer_analytics.py#L197

Upstream tasks that create views are at: https://github.com/astronomer/airflow-ml-demo/blob/c2e150649301458ef4fe26d8b6bd6c3f30836137/dags/customer_analytics.py#L239

Expected behavior if_exists='replace' should drop the table even if views exist

tatiana commented 1 year ago

Thanks for reporting the issue, @mpgreg ! I added it to the 1.7 milestone. cc @jlaneve