How Airflow runs SQL against PostgreSQL, MySQL and SQL Server
SQLExecuteQueryOperator from the common SQL provider is the supported way to run scheduled SQL from self-hosted Airflow, and the database-specific provider package supplies the connection type that the operator uses. A team that needs recurring SQL against an operational database or a warehouse should plan around that pairing: the common SQL provider owns the operator, the statement handling and the result handling, while a separate package owns the driver and connection details for each engine.
The division of labor is straightforward. The common SQL provider, documented in the common SQL operators reference, provides the operators and the @task.sql decorator that every SQL-capable provider shares. The PostgreSQL, MySQL and Microsoft SQL Server packages each register a connection type in Airflow, so a connection in the metadata store or a secrets backend can point at the right engine. The Airflow providers packages reference also lists separate packages for Oracle, ODBC and JDBC connections. Oracle, ODBC and JDBC are not covered by the same connection types, so a team targeting one of those engines should confirm the package and driver it needs before writing DAGs against it.
Before building, confirm three things in your environment: which provider versions your Airflow 3 image installs, which database versions your team must reach (the PostgreSQL and MySQL support windows differ, and both are covered in the database policy notes in your own platform standards), and whether the database driver required by each package is allowed in your container build. Those checks determine whether the connection setup described below can be reproduced in staging before a DAG reaches production.
What you need before the first SQL task runs
Before a DAG can run SQL, three versions have to line up: the Airflow release, the provider packages, and the target database. Network paths, service accounts and TLS then decide whether the first connection works and stays secure.
Airflow 3.3.2 and Python version
Airflow 3.3.2 lists Python 3.10 to 3.14 as supported prerequisites, so the worker image has to match one of those interpreters. Pin the Airflow release and install the SQL providers with the matching constraints file for that release, so the common SQL package and the database provider resolve to versions tested together. Confirm the Python and Airflow versions your platform team has standardized on before writing any connection code.
Provider packages and constraints
Install the common SQL provider with the PostgreSQL, MySQL or Microsoft SQL Server provider that matches your target database. Use the Airflow constraints file for your Airflow and Python versions so transitive driver packages do not drift between environments. Record the installed provider versions in your image build, since upgrades of the provider can change operator behavior and defaults.
Metadata database versus target databases
Airflow's own metadata database is separate from the operational databases your DAGs query. For the metadata database, Airflow 3.3.2 supports PostgreSQL 14 to 18 and MySQL 8.0, 8.4 and Innovation releases, and MariaDB is not supported. Do not confuse these with the PostgreSQL, MySQL or SQL Server servers your tasks connect to, which have their own version support that you need to confirm separately.
Network paths and firewall rules
Workers, and the triggerer when deferrable operators are used, must reach each database host and port directly. Open only the listener ports for the specific source and destination pairs, and confirm whether a connection pooler such as PgBouncer sits in between. Test the path from the actual worker subnet or Kubernetes namespace, because a path that works from a jump host often fails from the pods that run tasks.
Least-privilege service accounts
Create one database login per pipeline domain, not a shared administrator account. Grant only the schema, table and statement rights that the DAG needs, such as SELECT for reads and specific INSERT or UPDATE rights for writes, and withhold DDL unless a migration task requires it. Ask the database team to confirm how role membership, password rotation and account expiry will be handled, since those rules affect every DAG that uses the login.
TLS certificates and encrypted connections
Decide before the first run whether each connection must use TLS, and which certificate authority the workers trust. Check that the database server certificate matches the host name in the Airflow connection, and plan certificate renewal, since an expired certificate stops every DAG that depends on it. Confirm the TLS options each provider and driver accepts for your database version, because parameter names differ between PostgreSQL, MySQL and SQL Server.
SQL Server version and driver checks
No version figures for Microsoft SQL Server were supplied for this page, so confirm supported SQL Server versions, the required ODBC driver and any Microsoft driver version with the Microsoft SQL Server provider documentation and your own database team. Install the driver in the worker image rather than on a single host, and test the connection from each worker type, since missing drivers usually surface only at task runtime.
Configuring connections and the first SQL task
This sequence takes a team from installed provider packages to a tested database connection and a first SQL task that runs safely in a non-production DAG. Each step ends with a check the team can repeat before any production DAG depends on the connection.
Install the provider packages on workers
The platform team installs the common SQL provider and the provider package for each database you need, such as PostgreSQL, MySQL or Microsoft SQL Server, into the same environment the scheduler and workers use. For container deployments, the packages go into the image the workers pull, so the build is the place to make the change. Confirm the exact package names and versions against the Airflow providers packages reference and your Airflow 3 release. The step is done when the Airflow environment lists each provider and the DAG parser imports the SQL operator without errors.
Create a dedicated database role
The database administrator creates one role per database and per purpose, such as a read-only role for reporting queries and a separate write role for loads, and grants only the schemas and tables those DAGs need. Avoid reusing a personal account or an administrator login, because audit logs then cannot separate pipeline activity from people. Ask the database team which authentication method the server enforces, since PostgreSQL, for example, documents several options and recommends SCRAM over MD5 password authentication. The step is done when a login with the new role can read the intended tables and is refused on everything else.
Define an Airflow connection for each database
An Airflow administrator creates one connection per database and role in the Airflow UI, the CLI or your secrets backend, using a connection ID that names the environment and purpose, for example a staging reporting connection. Store the password in the connection or in a secrets backend so it never appears in DAG files, and restrict who can view or edit connections through Airflow's access controls. Keep host, port, database name and schema in the connection definition so DAG code stays portable between environments. The step is done when each connection ID appears in the list with the expected host and database, and the password field is masked.
Test each connection from a worker
Because the workers, not the web interface, open the database connection at run time, the test should run from a worker host or worker container that sits on the same network path as production tasks. Run a trivial query such as selecting the server version or the current database user through a short test DAG or an interactive session on a worker. Compare the returned user and database with the role you created, which confirms the connection uses the intended login. If the connection fails, check firewall rules, TLS requirements and the server's allowed host list before changing DAG code. The step is done when the test query returns the expected role and database name from each worker pool.
Write the SQLExecuteQueryOperator task
A data engineer writes a DAG that calls SQLExecuteQueryOperator with the connection ID, a SQL statement that reads a small, known table and a task ID that describes the job. The common SQL provider documentation sets autocommit to off by default, so the operator commits only through the transaction handling it provides, and the fetch-all handler can be set to None when the result is not needed. Keep the statement parameterized where values come from upstream tasks, rather than concatenating strings. The step is done when the DAG parses in the Airflow UI and the task reaches the success state on a manual trigger.
Enable split_statements or return_last where needed
Set split_statements when one task holds several statements that must run separately, and set return_last when a downstream task needs only the final result set, so the output stays small enough for XCom. Test both settings in the non-production DAG with a script that has a known number of statements and a final select with a known row count. Confirm in the task logs that each statement ran in order and that the returned value matches what the final query should produce. Before enabling either option, ask the team that owns the database whether any statements depend on session state or transaction boundaries, because splitting changes how those behave. The step is done when the logged statement count and the returned rows match the expected values.
Run a rollback and row count test
Create a non-production test table, then run a DAG that inserts a known number of rows inside a task that deliberately fails after the insert. Check the target table afterward: if the failure leaves no new rows, the transaction handling behaves as the team expects, and if rows remain, the connection or statement structure needs review before any production load. Then run a successful version and count the rows against the expected total. This test is the evidence the team should keep for the change record. The step is done when the failed run leaves the table unchanged and the successful run produces the expected row count.
Run the DAG in a non-production environment
The Airflow administrator enables the test DAG in a staging or development Airflow environment that points to a non-production database, then lets it run on its schedule for a few cycles and reviews the logs and task durations. Confirm that the connection ID resolves to the staging database in each run, that no credentials appear in task logs, and that retries do not repeat inserts. Record the connection IDs, role names and DAG version so the same configuration can be promoted through your normal change process. The step is done when several scheduled runs succeed with the expected results and the logs show no credential values.
How autocommit, split statements and XCom results change task design
SQLExecuteQueryOperator runs with autocommit turned off by default, so a write is not visible to other sessions until the transaction is committed. For a single INSERT, UPDATE or MERGE, you can enable autocommit on the task, or keep the default and make the commit explicit at the end of the statement list. Explicit commits are easier to reason about when a task contains several dependent writes, because a failure before the commit leaves nothing half applied. Confirm in the provider documentation how your connection type handles commit and rollback on failure before you rely on it for financial or regulated data.
- Autocommit off by default: Writes stay in an open transaction until the task commits, so a failed run should roll back rather than leave partial changes.
- Explicit commit for multi-step writes: Put the statements that must succeed together in one transaction and commit only after the last one.
- Autocommit for single idempotent statements: A lone statement that is safe to repeat can run with autocommit enabled, which avoids holding a transaction open while the task waits.
- Idempotent write patterns: Use MERGE or delete-then-insert within a transaction so a retry does not duplicate rows.
- Connection and session settings: Check that the isolation level and session options on the Airflow connection match what the write expects.
Before release, test a forced failure midway through a write task in a non-production database and confirm that no partial rows remain.
A script with several statements can be sent as one block or split so that each statement runs separately. Splitting is the better choice when statements depend on each other's output, when a driver rejects multi-statement input, or when you want per-statement logging in the task output. Keeping the script whole can be simpler for DDL-heavy setup where the database accepts the block as a unit. The choice affects error messages, transaction boundaries and how easily a failing statement can be identified during an incident.
- Split statements: Setting split_statements tells the operator to run each statement separately, which makes failures easier to attribute to a specific line.
- Transaction boundaries: Confirm whether the whole list runs in one transaction after splitting, since a split script can still commit as a unit.
- Semicolons inside literals: Stored procedure bodies and string literals containing semicolons can break naive splitting, so test those scripts before relying on the split.
- Vendor differences: PostgreSQL, MySQL and SQL Server handle batches and DDL differently, so verify each dialect with a representative script.
- Return values: Combine splitting with return_last when only the final statement's output matters to downstream tasks.
Review the split output in the task log for one full run to confirm that every statement executed in the order you intended.
By default the operator fetches all rows from a query and passes the result on. That is convenient for small lookups, such as a list of active partitions, but it can exhaust worker memory on a large result. Setting the fetch-all handler to None avoids loading every row at once, and return_last limits the value returned to XCom to the final statement's result. Neither setting moves data out of the database, so for large volumes the usual pattern is to write results to a staging table and pass only the table name or a row count between tasks.
- Fetch-all handler set to None: Use this for large queries whose results the task does not need in memory, such as statements that write data.
- return_last: When a script has several statements, this limits XCom to the final result instead of every statement's output.
- XCom lives in the metadata database: XCom values are stored in the Airflow metadata database, so large payloads increase database size, slow the scheduler and make cleanup harder.
- Staging tables: Write large intermediate results to a staging table and pass a reference, such as a table name, partition key or row count, through XCom.
- Size limits and cleanup: Ask your team to set a maximum XCom size policy and a cleanup schedule for the metadata database.
Test the task with production-sized data in a staging environment and watch worker memory and the metadata database size during the run.
The @task.sql decorator offers an alternative form for tasks where the SQL is generated by Python code. Instead of declaring a separate SQLExecuteQueryOperator, you write a Python function that returns the SQL string and let the decorator execute it against the configured connection. This keeps the query close to the logic that builds it, which helps when statements depend on runtime parameters. The trade-off is that the SQL is less visible in the DAG structure, so reviewers need to read the function body to understand what runs against the database.
- Generated SQL: Use the decorator when the statement depends on Python logic, such as building a partition filter from a run date.
- Operator visibility: A classic operator makes the SQL and connection explicit in the DAG graph, which some reviewers prefer for audit purposes.
- Parameter handling: Pass values as bound parameters where the provider supports them, and avoid string formatting that could allow SQL injection from upstream data.
- Testing: Unit test the function that builds the SQL, since the decorator form makes the SQL harder to inspect in the UI.
- Consistency: Pick one style per team and document it, so that transaction and XCom settings are reviewed the same way across DAGs.
Before adopting the decorator, confirm in the common SQL provider documentation which options it supports, and check that your code review process covers the generated SQL.
Mapping Airflow connection fields to database settings
An Airflow connection is the single place where a database's address and identity live, and the operator only refers to it by its connection ID, such as postgres_ops or mssql_warehouse. In the Airflow connection form, host, port, schema (which the database driver usually reads as the database name), login and password map to the standard driver parameters, while extras carries driver-specific options as JSON. The PostgreSQL, MySQL and Microsoft SQL Server providers each read these fields a little differently, so the reader should confirm the exact extra keys for SSL mode, certificate paths, driver names and timeouts in the provider documentation for their installed version, and test each connection before any DAG depends on it. TLS settings belong in extras, not in the DAG file, and certificate files should be mounted into the worker or scheduler image rather than pasted into the connection.
Values that change per run, such as a date window, a table partition or a batch identifier, should reach SQL through parameters, not through Python string formatting. SQLExecuteQueryOperator accepts a parameters argument that the database driver binds safely, and the sql field is templated by Jinja, so a run date can be written as a Jinja expression while the values themselves stay in the bound parameters. Templating is useful for rendering identifiers and file paths, but it does not escape values, so the team should reserve it for trusted, code-defined names and never feed user-supplied text into a templated statement. A practical review step is to search DAG code for f-strings or % formatting near SQL and treat any match as a defect.
Mapping database types to Airflow connection and task settings
This table maps each target database to its Airflow provider package, the connection type to select, the driver to confirm, and the settings that differ between engines. Copy connection type identifiers from the provider documentation for your installed provider version instead of typing them from memory. Use the table as a checklist before you create connections, because each engine's driver and TLS settings are where first runs most often fail.
| Database | Provider package | Connection type and driver to confirm | Main settings to check |
|---|---|---|---|
| PostgreSQL | The PostgreSQL provider package for Airflow | Select the PostgreSQL connection type listed in the provider docs. Confirm the Python driver that version requires and that it is in the Airflow image. | SSL mode, default schema, and any driver options in connection extras. Confirm certificate verification is set as your security team expects. |
| MySQL | The MySQL provider package for Airflow | Select the MySQL connection type listed in the provider docs. Confirm the client library the provider needs and that it supports your server's authentication plugin. | Character set, SSL options, and the database name in the connection. Test how the provider handles autocommit before relying on multi-statement transactions. |
| Microsoft SQL Server | The Microsoft SQL Server provider package for Airflow | Select the Microsoft SQL Server connection type listed in the provider docs. Confirm whether an ODBC driver must be installed on every worker and which driver name to reference. | Encryption and server certificate trust settings, authentication mode, and the driver name passed in extras. Check these on each worker, since they can differ from the API server. |
| Other engines via ODBC or JDBC | The ODBC or JDBC provider packages for Airflow | Select the ODBC or JDBC connection type that matches the target engine. Confirm the driver manager, driver version, and library paths in each worker image. | Driver class or data source name, connection string options, and how credentials are supplied. Keep secrets in a secrets backend, not in the connection string. |
Keeping credentials out of DAG code
A SQL operator is only as safe as the credentials and statements behind it. The cards below cover where database passwords live, which database roles DAG authors and workers should hold, how connection values stay out of logs, how SQL is built, how each connection is encrypted and who may change connections.
Store passwords in a secrets backend
Keep each database password in an Airflow connection that is resolved from a secrets backend, so DAG files only reference a connection ID such as postgres_ops. Confirm which backend your team will use and who can write to it, because a connection stored in the metadata database is readable by anyone with access to that database. Ask your DBA to confirm that each connection points at a dedicated account, not a shared administrator login.
Split DAG author and worker roles
DAG authors should write pipelines against connection IDs, while the database role used by tasks should hold only the grants that the pipeline needs. Separate read-only roles for reporting DAGs from write roles for loads, and avoid granting CREATE or DROP privileges to the account that runs routine statements. Review the role matrix each time a new DAG reuses an existing connection.
Verify connection masking in logs
Airflow masks values of sensitive connection fields in task logs, but masking depends on the names and values it recognizes, so a password echoed by a custom query, a debug print or an error message can still leak. Test a failing task on a non-production connection and search its logs for the password string. Do not place credentials in SQL text, operator parameters or Jinja templates that render into logs.
Parameterize SQL and validate inputs
Pass user-supplied or upstream values as bound parameters through the operator's parameters argument instead of formatting them into the statement with f-strings. Table and schema names cannot be bound as parameters, so validate them against an allow-list before they reach the SQL text. Treat values pulled from XCom or external files as untrusted input, since any upstream task that writes them can change what your DAG executes.
Require TLS and strong authentication
Enable TLS on every connection to PostgreSQL, MySQL and SQL Server, and set the connection's SSL options so the client verifies the server certificate rather than accepting any certificate. For PostgreSQL, SCRAM-SHA-256 is the password method to use, and certificate authentication suits service accounts that a secrets backend can rotate. PostgreSQL 18 deprecated MD5 password authentication, so remove MD5 entries from pg_hba.conf during the build. Confirm exact option names with the provider documentation for your version.
Review who can edit connections
Anyone who can edit a connection can redirect a DAG to a different host or swap in credentials for a more privileged account, so connection editing should be limited to a small group. Set Airflow access rules so that DAG authors can read connection IDs without viewing secret values, and log every connection change in your audit trail. Review the list of editors on a fixed schedule and after staff changes.
Common failures when Airflow SQL tasks misbehave
This table lists the failures teams most often hit with SQL tasks in Airflow, the usual cause of each, and the fix to apply first. Read each row as a symptom to check against your logs, then confirm the fix in a staging run before changing production DAGs.
| Failure | Likely cause | Fix |
|---|---|---|
| Connection refused or timeouts from workers | Workers cannot reach the database host or port because of network policy, DNS, or a firewall rule that allows the scheduler but not the worker subnet or pod network. | Test the connection from a worker container or pod with the same network path. Confirm the host, port and TLS settings in the Airflow connection, and ask the network team to allow the worker address ranges. |
| Authentication failures after password rotation | The password changed in the database but the Airflow connection still holds the old value, or a secrets backend caches the previous secret. | Update the connection in the secrets backend or Airflow connection store, then clear any cached values and retry. Use a rotation runbook that changes the secret and the connection in one change window. |
| Writes silently not committed | SQLExecuteQueryOperator runs with autocommit off by default, so DML and DDL statements are rolled back when the session closes if no commit happens. | Set autocommit explicitly in the operator when the statement must persist, or commit inside the same transaction. Verify the row count in a follow-up check task so a missing commit fails the run. |
| Duplicate rows after retries | A non-idempotent statement such as a plain INSERT re-runs on retry after a partial success, and the earlier write was already committed. | Write load steps as idempotent operations, for example delete-and-insert by partition, MERGE or upsert keyed on a natural key. Keep each retryable step in its own transaction and test a forced retry in staging. |
| XCom size errors from large results | The operator returns full result sets to XCom, which stores the data in the metadata database and fails or slows the scheduler when results are large. | Return only summary values such as row counts or a max key, or write large results to object storage and pass the path. Set the handler or return options so only what the downstream task needs is kept. |
| MySQL driver mismatches | The provider expects a specific Python driver or client library, and the installed version does not match the MySQL server version or authentication plugin in use. | Install the MySQL provider and driver versions that match your Airflow and Python versions from the constraints file. Confirm the server's default authentication method is supported by the driver before upgrading either side. |
| SQL Server TLS or driver errors | The ODBC driver version is missing on the worker image, or the connection does not match the server certificate trust chain or encryption settings required by the server. | Install the required ODBC driver on every worker image, including Kubernetes images, and set encryption and certificate trust options to match your server policy. Validate with a test query from the same image before enabling the DAG. |
Questions teams ask before running SQL from Airflow
Is SQLExecuteQueryOperator enough, or do we need a dedicated operator?
For most PostgreSQL, MySQL and SQL Server work, SQLExecuteQueryOperator is enough. It comes from the common SQL provider, so the same DAG pattern works across databases once the right connection and provider package are installed. A dedicated operator is worth considering when a database offers features the generic operator cannot express cleanly, such as vendor-specific bulk loads or native procedures with special handling. Also check the transaction behavior: autocommit is off by default, so each DAG must commit deliberately. The @task.sql decorator suits short queries that feed downstream Python tasks. Confirm the operator options against the provider version you plan to pin, since behavior evolves between releases. The common SQL operators documentation lists the current options.
What drives the effort of a SQL-from-Airflow integration?
Effort grows with four factors. The first is the number of databases, since each one needs its own connection, provider package and test coverage. The second is statement complexity: multi-statement scripts, stored procedures and transactions that span several steps take more design and testing than single queries. The third is the credential model, meaning whether secrets live in Airflow connections, an external secrets backend, or both, and how rotation is handled. The fourth is retry safety. A task that inserts rows will duplicate data on retry unless it is written to be idempotent, and that design work is often the largest part of the effort. Ask any provider to break its quote down along these four factors, and to state which items are one-time and which recur.
Can our own team set this up without a partner?
Yes, many teams do. A capable data platform team can install the providers, create connections, and write DAGs that run SQL, especially for a single database and a few straightforward pipelines. The harder parts tend to be the ones that are easy to underestimate: transaction boundaries, idempotent loads, secrets handling across environments, and testing DAG code before it touches production tables. Consider whether your team has time to build those patterns and keep them consistent as more pipelines arrive. An outside partner can help define the patterns first, then hand over a runbook and tests your team maintains. Whichever route you choose, assign an owner for each database connection before the first DAG is written.
What do we need to provide for the work?
The work needs three things from your side. Database owners must confirm schemas, the accounts that Airflow will use, and which operations those accounts may perform. Network access must allow the Airflow workers, or the component that runs tasks, to reach each database host and port, and firewall or routing changes often need lead time through your change process. A test environment is essential: a non-production copy of each database, ideally with representative data volumes, lets you verify transactions, result sizes and failure behavior before production. Also provide the naming conventions, the secrets store your organization already uses, and the people who approve DAG changes.
How does DeployOpen scope this work and price it?
DeployOpen works on fixed-scope engagements. Before work starts, the scope, deliverables and timeline are agreed in writing, and a quote follows from that written scope. DeployOpen does not publish a price list. A typical scope lists the databases in use, the number of DAGs or pipeline patterns, the credential and secrets approach, the testing and promotion path, and the handover material. Ask for each of these to appear as a separate line in the quote, along with the assumptions behind it, such as the number of environments and which items the client must provide. Ongoing support, upgrades and continuous testing are available as retainers and are quoted separately from the initial build.
How do PostgreSQL and MySQL upgrade timing affect our DAGs?
Database upgrades can change connection behavior, supported features and authentication, so DAGs should be tested against the target version before the upgrade. For PostgreSQL, minor releases do not change the storage format and are applied by replacing executables and restarting, while major upgrades use dump and restore, pg_upgrade, or logical replication. Logical replication can keep downtime to a few seconds, which affects how long DAG windows must be paused. PostgreSQL 18 also deprecates MD5 password authentication in favor of SCRAM, so confirm that connection credentials use a supported method. For MySQL, check the release track you run, since Innovation releases are supported only until the next Innovation release, while LTS series have longer support. Plan each upgrade with a window that pauses affected schedules and reruns a smoke test on each connection.
Where should database credentials live for these DAGs?
Credentials should live outside DAG code, in Airflow connections or in an external secrets backend that your organization already operates. DAG files are often copied to several places, stored in version control and read by many people, so embedded passwords spread quickly. Use a dedicated database account per connection with the smallest set of privileges the pipeline needs, and rotate those passwords on a schedule. Confirm that Airflow masks secret values in logs and that your audit approach covers connection changes. The exact secrets backend options depend on your Airflow deployment, so confirm them against your environment before you choose a design.
What should we check before a SQL DAG reaches production?
Check four areas before production. Confirm transaction behavior by running each DAG against a test copy and verifying that partial failures leave the target tables in a known state. Confirm retry safety by rerunning a failed task and comparing row counts and checksums. Review result handling, since returning large result sets to XCom can strain the metadata database, so return only small summaries or write bulk data to storage. Finally, confirm that the connection, provider versions and database version match the test environment. Record these checks as a repeatable promotion step so each new DAG passes the same gate.
Keeping SQL DAGs working through database upgrades
Database upgrades are where SQL DAGs most often break, so the plan for them should be written before the first DAG is promoted. The distinction that matters is between minor and major PostgreSQL releases. Minor releases never change the internal storage format, so updating means replacing executables and restarting the server, and the Airflow connections usually survive unchanged. Major upgrades are different: they use pg_dumpall and restore, pg_upgrade, or logical replication. Logical replication works between different major versions and, according to the PostgreSQL upgrading documentation, allows a major upgrade with only several seconds of downtime. Whichever method is chosen, the team should confirm with its DBA which method fits the database size and maintenance window, and whether the target major version is still in support. The PostgreSQL versioning policy lists the end dates for each supported major version.
MySQL upgrades carry a different planning question because of its release tracks. The LTS track receives long-term support under the Oracle Lifetime Support Policy, while Innovation releases are supported only until the next Innovation release. A DAG estate pinned to an Innovation release therefore needs an upgrade cadence that tracks that shorter window. Features are added only in the first release of an LTS series, so later point releases should be treated as fix-only changes. Whatever the track, the team should re-run every SQL operator, check and stored procedure call against the target version in a staging environment that mirrors the production Airflow connections, before cutover. Watch for changed default modes, deprecated syntax and driver differences in the ODBC or JDBC connectors. Also plan connection pooling with PgBouncer and confirm pool sizes against the number of concurrent tasks, since pool exhaustion during a cutover can look like a DAG failure.
Finally, plan failover as a separate decision from upgrades. PostgreSQL supports hot standby servers and both synchronous and asynchronous replication, but according to the PostgreSQL failover documentation it does not detect a primary failure or notify the standby. Automatic failover therefore needs external tools, and each one changes how the sql_alchemy_conn value or the Airflow connection host should be pointed at a virtual endpoint. Ask the database team which tool will perform failover, how Airflow connections will reach the new primary, and whether DAG retries are long enough to cover a switchover. Keep a rehearsal record showing that a failed upgrade step can be rolled back and that the pipeline reruns cleanly afterward.
How DeployOpen scopes and delivers SQL integrations
DeployOpen plans, builds, documents and supports Airflow SQL operators inside infrastructure you control. Each engagement starts with a written scope that names the databases, connections, DAGs and controls involved, so your team knows what is delivered before work begins.
How we scope
- Inventory every PostgreSQL, MySQL and SQL Server target, its owner, network path and credential model before any DAG is written.
- Agree which SQL steps run in Airflow and which stay inside the database, with transaction boundaries documented for each task.
- Engagements are fixed-scope, with deliverables and timelines agreed in writing before work starts. Ongoing support is available as a retainer.
How we build
- Create connections that read credentials from your secrets backend, not from DAG code or plain environment variables.
- Build and test SQLExecuteQueryOperator and @task.sql steps, including autocommit, split_statements and return_last behavior for each statement set.
- Validate results passed to XCom, keeping result sets small and moving large data through staging tables or external storage.
How we hand over
- Deliver DAG code, connection definitions and configuration with version control history your team can review and reproduce.
- Document each connection, its privileges, retry behavior and the failure modes found during testing and how to recover from them.
- Walk your operators through monitoring, rotating credentials and changing a connection without editing DAG source.
How we support
- Plan driver and provider package upgrades alongside your Airflow and database version lifecycles, confirming supported versions before each change.
- Review database major upgrades, such as PostgreSQL version moves, for effects on DAG SQL, statistics and connection authentication.
- Offer ongoing support, upgrades and continuous testing under a retainer, scoped in writing to the platforms you run.
