Airflow DAG for BigQuery Table Clones

[hunterpack]

Description

Develop an Airflow DAG to automate the weekly refresh of the staging environment using BigQuery's CREATE TABLE CLONE functionality.

Tasks

• Create dag_stellar_dbt_staging_refresh.py.
• Implement the CREATE TABLE CLONE SQL logic to mirror production datasets into staging.
• Add a schedule for Wednesday mornings (post-release).
• Add workflow_dispatch to allow manual triggers via the Airflow UI.

Acceptance Criteria

• Data in the staging dataset matches production at the time of cloning.
• Storage costs for the staging dataset remain minimal.
• The DAG is visible and triggerable by the data team in Airflow.

[sydneynotthecity]

The list of tables to clone should be dynamic. The DAG should use BigQuery's INFORMATION_SCHEMA to list the contents of key datasets and copy all tables under that dataset.

There are times when there are table copies or backups, if they are cloned to staging, that is ok. Copies should follow naming conventions _*bkp_<YYYYMMDD>* so that regex can drop table copies from the list of tables to clone.

Because schema changes can break table truncation, the DAG should drop all tables in the staging dataset prior to cloning data from prod.