Agate Data Helper

Relevant source files

The following files were used as context for generating this wiki page:

• dbt_common/clients/agate_helper.py
• tests/unit/test_agate_helper.py
• dbt_common/dataclass_schema.py
• dbt_common/exceptions/base.py
• pyproject.toml
• CONTRIBUTING.md

The Agate Data Helper is a utility module within dbt-common designed to facilitate the manipulation, conversion, and merging of data using the Agate library. It provides specialized data types and helper functions to ensure consistent type inference and handling across dbt-core and its various database adapters.

This module addresses specific data handling requirements, such as preserving integer types during table merges, handling ISO8601 datetime strings without aggressive auto-conversion, and preventing the unintended casting of boolean values to integers.

Sources: dbt_common/clients/agate_helper.py:1-15, README.md:1-5

Core Data Types and Custom Casting

The helper extends Agate's base data types to implement dbt-specific logic for type casting and serialization. These custom types ensure that data integrity is maintained when moving between raw Python objects, CSVs, and internal Agate tables.

Specialized Types

• Integer: A custom DataType that explicitly handles int and None. It prevents None values from being defaulted to Agate's generic Number type, which is critical for maintaining consistency during table unions.
• Number: Overrides the default Agate behavior to prevent boolean values (True/False) from being cast to numeric 1 or 0.
• ISODateTime: A specialized datetime handler that focuses strictly on ISO8601 parsing via the isodate library, removing Agate's default "clever" parsing that might lead to incorrect inferences.

Sources: dbt_common/clients/agate_helper.py:18-62, tests/unit/test_agate_helper.py:192-212

Type Tester Configuration

The build_type_tester function constructs an agate.TypeTester with a specific hierarchy of types. This ensures that columns are evaluated in a predictable order, with Integer and Number taking precedence over broader types like Text.

Type	Null Values Handled	Notes
Integer	("null", "")	Custom dbt implementation
Number	("null", "")	Prevents bool-to-int casting
Date	("null", "")	Format: %Y-%m-%d
DateTime	("null", "")	Format: %Y-%m-%d %H:%M:%S
ISODateTime	("null", "")	ISO8601 strict parsing
Boolean	("null", "")	True: ("true",), False: ("false",)
Text	Configurable	Final fallback type

Sources: dbt_common/clients/agate_helper.py:65-80

Table Creation and Conversion

Agate Data Helper provides multiple entry points for creating tables from different data structures, including raw rows, lists of dictionaries, and CSV files.

Data Flow for Table Construction

The following diagram illustrates how different input formats are processed into Agate tables using the helper functions.

This diagram shows the relationship between raw input formats and the specific helper functions used to generate standardized Agate tables.

Sources: dbt_common/clients/agate_helper.py:86-150

Flat Data Conversion

The table_from_data_flat function is particularly important for scenarios where data should not be coerced. For example, it prevents strings like '005' from being turned into integers and ensures that container types (lists, dicts) are serialized into JSON strings within the table cells.

Sources: dbt_common/clients/agate_helper.py:117-141, tests/unit/test_agate_helper.py:171-190

Table Merging Logic

Merging multiple Agate tables requires careful handling of column types, especially when some columns contain all null values in a subset of tables.

Column Type Builder

The ColumnTypeBuilder is an internal utility that manages type reconciliation during merges. It uses a hierarchy to decide the final type of a merged column:

1. If a column is _NullMarker (all nulls), it is treated as a placeholder.
2. If one table has Integer and another has Number, the resulting column is promoted to Number.
3. If there is a fundamental type mismatch (e.g., Date vs Boolean), a DbtRuntimeError is raised.

Sources: dbt_common/clients/agate_helper.py:165-200

Merging Sequence

The sequence diagram demonstrates the internal reconciliation process that prevents type loss when merging tables with disparate null distributions.

Sources: dbt_common/clients/agate_helper.py:203-233, tests/unit/test_agate_helper.py:115-169

Error Handling

The module utilizes standard dbt exceptions to signal failures in data processing.

Exception Class	Trigger Condition
DbtRuntimeError	Occurs during table merges when columns with the same name have incompatible types.
agate.exceptions.CastError	Raised by custom types (Integer, Number, ISODateTime) when input data does not conform to expected formats.

Sources: dbt_common/clients/agate_helper.py:25-58, dbt_common/exceptions/base.py:73-81

Summary

The Agate Data Helper provides a robust layer on top of the Agate library, tailored for the specific needs of dbt's data transformation workflows. By implementing custom type testing and merge logic, it ensures that data types remain consistent across diverse database platforms and data ingestion methods. Its primary significance lies in preventing common data corruption issues such as silent type coercion of strings, incorrect boolean-to-integer casting, and type degradation during table joins and unions.

Sources: dbt_common/clients/agate_helper.py:1-20, CONTRIBUTING.md:3-10