Cloud concept · Updated

Open Database Connectivity (ODBC) - Open Database Connectivity (ODBC) is a standard programming interface that lets applications access data sources through compatible drivers.

In short

ODBC separates application database-access calls from the driver that communicates with a particular database or query engine. It enables reporting tools, ingestion processes, and other applications to reuse a common connectivity interface, while still requiring source-specific drivers, authentication, networking, and SQL compatibility.

Cloud Concepts Hub →

6 key aspects of Open Database Connectivity (ODBC)

ODBC standardizes application access to data sources while leaving driver compatibility, connection security, SQL behavior, and extraction reliability as explicit engineering responsibilities.

01
Standard interface with source-specific drivers

Applications use common connection, query, and result-retrieval operations, while a compatible driver handles communication with the target. Install the driver for the actual database or query service rather than assuming any ODBC driver will work.

02
DSN and DSN-less configuration

A DSN references stored connection configuration, while a DSN-less connection supplies properties directly. Choose an approach that fits deployment automation, and keep secrets out of source code and diagnostic logs.

03
Runtime and architecture compatibility

The application, driver manager, and driver must be compatible with the operating system and process architecture. Package and test native dependencies in the environment that will actually execute the workload.

04
Independent security and network requirements

ODBC does not bypass network restrictions or database authorization. Establish endpoint reachability, configure TLS certificate validation, and use an authentication method supported by the driver and target.

05
Controlled extraction performance

Source-side filters, column selection, batch fetching, and carefully bounded concurrency reduce unnecessary data transfer and memory usage. Test the impact on the source database before increasing parallel extraction.

06
Portability with functional limits

A standard API does not eliminate SQL-dialect, data-type, or transaction differences. Validate driver capabilities and implement incremental state management separately when building ingestion pipelines.

Cloud services for Open Database Connectivity (ODBC)

An ODBC-capable application can extract operational records from Amazon RDS, query analytical tables in Amazon Redshift, or submit queries through Amazon Athena by selecting the appropriate driver and connection configuration. These services expose different authentication models, permissions, SQL capabilities, and performance characteristics, so a shared API does not make their connections interchangeable. AWS Glue can participate in the surrounding transformation pipeline, but its standard Spark database connections use JDBC rather than ODBC. Select the connector supported by each runtime, and validate networking, credentials, data-type mappings, and source load across the complete pipeline.

Amazon RedshiftAmazon Redshift

Amazon Redshift provides an ODBC driver for applications that query warehouse data through the ODBC interface. Configure the cluster or workgroup endpoint, supported authentication, and network access, then tune queries and result fetching for the workload.

Amazon AthenaAmazon Athena

Amazon Athena provides an ODBC driver that lets compatible applications submit SQL queries and retrieve results. Configure the AWS Region, workgroup, authentication, and applicable query-result settings; permissions must cover Athena and the data, catalog, and result resources used by the queries.

Amazon RDSAmazon RDS

Applications connect to RDS databases using an ODBC driver compatible with the selected database engine, such as SQL Server or PostgreSQL. RDS does not provide one universal driver across engines, and successful access still depends on database credentials, TLS configuration, routing, and security-group rules.

AWS GlueAWS Glue

AWS Glue illustrates the important distinction between ODBC and JDBC when selecting data-integration connectors: standard Glue Spark database connections use JDBC. An ODBC connection string or driver cannot simply replace the JDBC configuration expected by those connections.

Open Database Connectivity (ODBC) exam questions

Real practice exam questions on Open Database Connectivity (ODBC), with the correct answer and its explanation. Answer them with every option in the exam simulator.

DEA-C01

A company has a reporting application that uses ODBC to query Parquet datasets in Amazon S3 through a serverless SQL query engine. A new report must join these datasets with current customer records in an Amazon RDS for PostgreSQL database in private subnets. The join must run as a single SQL statement in the existing query engine. The company must not replicate the customer table to Amazon S3 or introduce a data warehouse. Which solution meets these requirements?

Show answerCORRECT ANSWER

Configure an Athena PostgreSQL federated data source backed by a Lambda connector. Give the connector network access to the private database and the required permissions and credentials. Reference the federated catalog and the S3 catalog in the report query.

Athena federated queries can join S3 datasets with records retrieved from PostgreSQL through a supported connector. The Lambda connector needs database connectivity, credentials, and appropriate permissions. The reporting application can retain its existing Athena ODBC connection without maintaining a replicated customer table or introducing a warehouse.

Practice Open Database Connectivity (ODBC) in a full exam

Questions on Open Database Connectivity (ODBC) in each practice exam, with every answer option and an explanation for each:

Start a free practice exam

What is Open Database Connectivity (ODBC)

3 min read

Open Database Connectivity (ODBC) defines an application programming interface for connecting to data sources, executing SQL statements, retrieving results, and discovering metadata. A typical connection involves an application, an ODBC driver manager, a source-specific ODBC driver, and the target database or query service. The driver implements the ODBC interface and communicates with the target using its supported protocol or API. ODBC is therefore an application-access standard, not a universal database network protocol or a data storage format.

In cloud data engineering, ODBC commonly connects existing reporting tools, desktop applications, and custom ingestion programs to managed databases and analytical query engines. For example, a Python extraction process running on a virtual machine can use pyodbc with an installed SQL Server ODBC driver to read an Amazon RDS for SQL Server database. A reporting application can instead use an Amazon Redshift ODBC driver to query warehouse tables. The application uses the same general interface, but the driver, connection properties, authentication methods, and SQL behavior differ.

Connections can use a data source name, or DSN, that references stored configuration such as the driver, server, and database. A DSN-less connection supplies the necessary properties directly in a connection string. Neither approach requires embedding passwords in application code: retrieve credentials from an appropriate secret store, or use supported identity-based authentication. DSN files and connection strings can still contain sensitive information, so restrict access and avoid logging credentials.

Installing a driver is only one part of establishing connectivity. The application, driver manager, and driver must have compatible operating-system and processor architectures. A 64-bit process generally requires a 64-bit driver. The runtime must also resolve the target hostname and reach the required endpoints. For a private database, this can require private routing or a VPN, appropriate firewall and security-group rules, and correct database permissions. Configure TLS and certificate validation explicitly rather than assuming that ODBC itself guarantees encrypted transport.

For ingestion, execute queries that select only required columns and filter rows at the source when possible. Fetch results in batches rather than loading an entire large table into application memory. Parameterized statements help safely supply values, while fetch sizes, timeouts, connection pooling, and concurrency affect throughput and resource consumption. Multiple parallel readers may improve extraction speed but can overload an operational database. Validate decimal precision, timestamp handling, character encoding, and null values when mapping results into downstream formats.

ODBC improves portability at the API level, not complete portability of application behavior. SQL dialects, supported data types, transaction semantics, metadata behavior, and driver extensions vary. A driver for an analytical query service may expose tabular results without supporting the same update operations or transactions as a relational database. ODBC also does not automatically provide change data capture, incremental extraction, retries, or exactly-once processing. An incremental pipeline must separately manage checkpoints, stable extraction boundaries, and duplicate handling.

For exam scenarios, distinguish ODBC from Java Database Connectivity, or JDBC. Both provide standardized data-access interfaces, but JDBC is designed for Java environments, whereas ODBC is commonly used through native drivers and language bindings. Standard AWS Glue Spark database connections use JDBC; an available ODBC driver is not interchangeable with a required JDBC driver. Choose the interface supported by the execution environment and connector, then verify source compatibility, network access, authentication, permissions, and extraction performance.

Learn Open Database Connectivity (ODBC) in lessons

2 short lessons on Open Database Connectivity (ODBC) in 1 certification course.

Open the lessons