How would you schedule daily refresh jobs for an arbitrary number of tables when processing data from Oracle to Snowflake?
💡 Model Answer
For an arbitrary number of tables, dynamic task generation is key. Use an orchestrator like Airflow to build a DAG that reads a metadata table listing all source tables, their load type (full or incremental), and any transformation rules. For each table, create a sub‑task that runs a parameterized SQL script or a Python operator. The script pulls data from Oracle using a connection pool, applies any necessary transformations, and loads into Snowflake via the Snowflake Connector. The DAG is scheduled to run daily (e.g., at 5 pm UTC). Airflow’s XComs can pass the last successful run timestamp for incremental tables. Use a templated SQL file with Jinja variables for table names and load logic. This approach scales automatically: adding a new table to the metadata table automatically creates a new sub‑task without code changes. Monitor task status via Airflow’s UI and set alerts for failures.
Sign in to unlock the rest of this answer
This answer was generated by AI for study purposes. Use it as a starting point — personalize it with your own experience.
🎤 Get questions like this answered in real-time
Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers on a discreet on-screen overlay.
Get Assisting AI — Starts at ₹500