Principle:Spotify Luigi Database Connection Configuration

Knowledge Sources	Spotify Luigi Repository
Domains	Pipeline_Orchestration, Database, ETL
Last Updated	2026-02-10 00:00 GMT

Overview

Database Connection Configuration is the practice of encapsulating all parameters required to establish a connection to a relational database -- such as host, port, database name, user credentials, and driver options -- into a structured, reusable interface that pipeline tasks consume when loading data.

Description

When a data pipeline needs to write processed results into a relational database, the first requirement is a reliable and well-defined connection configuration. This principle addresses how pipeline tasks declare and organize database connectivity parameters so that the actual data loading logic can obtain a live database connection on demand.

A well-designed connection configuration follows several key guidelines:

• Declarative parameter exposure: Each connection attribute (host, database, user, password, port) is declared as a named property or attribute on the task class, making the configuration visible and overridable by subclasses or external configuration systems.
• Separation of concerns: The connection parameters are defined independently from the data transformation or loading logic. The task knows where to connect but delegates how to connect to a target or connection factory.
• Default and override semantics: Sensible defaults (e.g., standard database ports like 5432 for PostgreSQL or 3306 for MySQL) are provided, while allowing explicit overrides through task parameters or configuration files.
• Connection string abstraction: Some systems collapse individual parameters into a single connection string (e.g., SQLAlchemy connection URIs), which provides a uniform interface across different database backends.
• Connection lifecycle management: The configuration must support creating fresh connections, reusing connections within a transaction boundary, and properly closing connections after use.

The general pattern involves a task class that exposes abstract or overridable properties for each connection parameter. When the tasks execution method runs, it delegates connection creation to a target object that combines these parameters into a live database connection via the appropriate database driver (DBAPI 2.0 connector, SQLAlchemy engine, or similar).

Usage

Use Database Connection Configuration when:

• Your pipeline task needs to write output data into a relational database table (PostgreSQL, MySQL, Redshift, SQLite, or any RDBMS).
• You want to separate connection setup from data loading logic so that the same loading code can target different database instances (development, staging, production) by changing configuration alone.
• You need to support multiple database backends with a consistent interface, allowing tasks to be portable across PostgreSQL, MySQL, or SQLAlchemy-supported databases.
• Your pipeline uses a configuration file or environment variables to manage database credentials, and you want the task framework to integrate cleanly with such external configuration sources.

Theoretical Basis

Database Connection Configuration rests on the Abstract Factory and Template Method design patterns. The abstract base task defines the interface for connection parameters (the factory contract), while concrete subclasses provide the actual values (the factory implementation). The template method pattern governs the execution flow: the base classs run() method calls output().connect() to obtain a connection, and the target object uses the configured parameters to instantiate the appropriate database driver.

The algorithm is straightforward:

1. The task class declares abstract properties: host, database, user, password, table, and optionally port.
2. A concrete subclass provides values for each property, either as hard-coded attributes, Luigi parameters, or values read from a configuration file.
3. When execution begins, the task constructs a target object (e.g., PostgresTarget) by passing these parameters.
4. The target objects connect() method invokes the underlying DBAPI 2.0 driver (e.g., psycopg2.connect() or mysql.connector.connect()) with the configured parameters.
5. The resulting connection object is used for all subsequent operations -- table creation, data copying, and marker table updates -- within a single transaction.
6. After all operations complete and the transaction is committed, the connection is closed.

This approach ensures that connection parameters are validated at task definition time (abstract properties force subclasses to provide values), while the actual connection is created lazily at execution time, minimizing resource usage and allowing for proper transactional semantics.

Related Pages

• Implementation:Spotify_Luigi_CopyToTable_Connection