Loadable Table Mixins

Infrastructure for staged, file-based ingestion into ORM tables.

Supports: - staged file loading into backend-specific staging tables - bulk staging sinks per backend: PostgreSQL COPY, DuckDB insert from Arrow, SQLite executemany - backend-aware merge strategies - Arrow-based file readers (delimited text and Parquet) - index handling during merge

---

FileLoadableTableInterface

Bases: ORMTableBase

Mixin for ORM tables that load from files through a staging table.

A file is read in batches by a reader, converted under a conversion policy, written to a staging table by the backend's bulk sink, and merged into the target table. It supports: - dialect-aware staging table creation - delimited text and Parquet files, and other formats through readers - configurable merge strategies - explicit staging table lifecycle management

The class is designed for controlled ingestion pipelines and does not attempt to provide concurrency guarantees.

load_file(session, path, *, reader=None, normalise=True, dedupe=False, chunksize=None, merge_strategy='replace', quote_mode=None, index_strategy='auto', merge_batch_size=None, staging_schema_tag=None, validate_deferred_fks=True, resolved=None, physical_schemas=None, policy=None, rejects_dir=None, report=None) classmethod

Load a file into the target table.

The file name without its suffix must equal the table name. This method orchestrates the full staged ingestion lifecycle: - staging table creation - file loading - merge into the target table - staging table cleanup

Parameters:

Name	Type	Description	Default
session	Session	An active SQLAlchemy session.	required
path	Path	Path to the input file.	required
reader	FileReader | None	Reader for the file. open_reader chooses one from the file suffix when omitted, and chunksize and quote_mode then configure it. Passing either together with a reader is an error, because the reader is already configured.	None
normalise	bool	Whether to convert values to the column types under the policy. When false, values are written as read.	True
dedupe	bool	Whether to deduplicate incoming rows.	False
chunksize	int | None	Rows per batch for the default reader of formats that batch by rows.	None
merge_strategy	str	Merge strategy to apply (e.g. replace, upsert, or insert).	'replace'
quote_mode	str | None	How double quotes in delimited text are read: "csv" (field quoting), "literal" (kept as data), "by_delimiter" (literal for tab-delimited files, field quoting otherwise).	None
index_strategy	str	Index handling strategy during merge. Use "auto" to let the backend choose a sensible default.	'auto'
merge_batch_size	int | None	Rows per merge batch, or None to merge in one statement. On PostgreSQL and DuckDB, each merge batch commits the session. A later failure leaves earlier target batches committed, along with any other pending session work. SQLite keeps the batches in the caller's transaction.	None
staging_schema_tag	str | None	schema_translate_map tag the staging table lives in. None means no schema qualification (backend-default behavior). Threaded through every internal step of the load lifecycle so they all resolve the same backend/schema.	None
validate_deferred_fks	bool	After merging, run an anti-join check for any FK this backend's DB can't enforce itself (see DatabaseBackend.validate_deferred_fks()). Also run before every intermediate commit a batched merge makes, so a violation is caught before that batch commits rather than only after the whole load, when an earlier batch's commit can no longer be rolled back. Every other key is verified by validate_foreign_keys.	True
resolved	ResolvedDatabase | None	Topology this table's engine(s) were resolved from. Required on DuckDB whenever table_cls has any foreign key at all, to classify each as enforced or omitted via oa-configurator's unenforced_foreign_keys(); omitting it raises rather than guessing. Ignored by every other backend, and by a DuckDB table with no foreign keys.	None
physical_schemas	Mapping[str, str | None] | None	A schema tag's physical name beyond resolved's own configured ones. Forwarded alongside resolved.	None
policy	ConversionPolicy | None	How values are interpreted and what happens to unreadable ones. The default policy preserves nonempty text and treats only empty values as missing in text columns.	None
rejects_dir	Path | None	Directory that rejected rows are written to when the policy sets on_error="reject_row", as <file name>.rejects.csv. Defaults to a rejects directory inside the source file's directory.	None
report	LoadReport | None	Receives the load's row accounting. A new report is used when omitted.	None

Returns:

Type	Description
int	Number of rows loaded into staging before merge.

manage_indices(session, index_strategy='auto', *, backend) classmethod

Manage non-primary-key indexes around a staged merge.

index_strategy may be "auto", "drop_rebuild", or "keep". The backend decides what "auto" means. At the moment SQLite keeps indexes by default, while PostgreSQL drops and rebuilds them.

Raises:

Type	Description
IngestError	An index could not be rebuilt after a merge that was committed.

merge_from_staging(session, merge_strategy='replace', *, backend, staging, merge_batch_size=None, validate_deferred_fks=True) classmethod

Merge data from the staging table into the target table.

Parameters:

Name	Type	Description	Default
session	Session	An active SQLAlchemy session.	required
merge_strategy	str	Merge strategy to apply (for example replace, upsert, or insert).	'replace'
backend	DatabaseBackend	Backend of the session's database.	required
staging	Table	Staging table the rows are merged from.	required
merge_batch_size	int | None	Rows per merge batch, or None for one statement. On PostgreSQL and DuckDB, each batch commits the session; earlier batches stay committed if a later batch fails. SQLite keeps the batches in the caller's transaction.	None
validate_deferred_fks	bool	Forwarded to the backend's merge methods, which validate deferred FKs before every intermediate commit a batched merge makes. See load_file().	True

validate_foreign_keys(session, *, staging_schema_tag=None, resolved=None, physical_schemas=None) classmethod

Raise if any row references a row that does not exist.

Loads relax foreign keys during the merge on PostgreSQL and SQLite, so a row loaded before the row it references is accepted. Call this once every table is loaded to verify referential integrity, whatever the load order. DuckDB enforces most of its keys itself, so only the keys it cannot enforce are checked.

Parameters:

Name	Type	Description	Default
session	Session	An active SQLAlchemy session.	required
staging_schema_tag	str | None	schema_translate_map tag used to resolve the backend, as in load_file.	None
resolved	ResolvedDatabase | None	Topology this table's engine(s) were resolved from. Required on DuckDB for a table with foreign keys.	None
physical_schemas	Mapping[str, str | None] | None	A schema tag's physical name beyond resolved's own configured ones.	None

Raises:

Type	Description
ForeignKeyViolationError	A non-null reference has no matching row in the referred table.