Leveraging your DuckDB estate¶

DuckDB is different from the other relational databases Egeria connects to: it is an embedded, in-process OLAP engine, not a client/server product. A "DuckDB database" is either a single .duckdb file on disk, or an in-memory session - there is no server process to install, start or connect a network client to. DuckDB is increasingly used as a lightweight analytical engine sitting in front of other data - it can query Parquet, CSV and JSON files directly, and its ATTACH capability lets it federate live with other databases (including PostgreSQL, SQLite and other DuckDB files) without copying data.

Egeria's DuckDB connector gives you oversight of a DuckDB database's own contents and the other data it reaches out to, without needing to open the file yourself:

• Survey a DuckDB database to build a picture of what it contains - how many schemas, tables, views and columns it has - and, distinctively, what other data sources it federates with, captured as a survey report.
• Catalog the database, turning it into an open metadata asset that can be searched, classified, linked to glossary terms and governed alongside the rest of your data landscape - with its schemas and tables catalogued automatically, and its federation relationships (attached databases and external file scans) catalogued as linked assets in their own right.
• Do both of these using standard governance action processes, so the same repeatable, automatable pattern is used as for Egeria's other database connectors.

This solution follows the same broad pattern as Leveraging your PostgreSQL estate, Leveraging your Microsoft SQL Server estate, Leveraging your Oracle Database estate and Leveraging your Db2 for Linux, UNIX and Windows estate, but DuckDB's embedded architecture and federation capability make it different enough from all four that it is worth reading this page in full rather than skimming for differences.

DuckDB Technology Type Names¶

Because DuckDB has no server tier, Egeria's reference data for it is deliberately smaller than for the other relational databases - there is no DuckDB Server or DuckDB database manager (RDBMS) entry, and no tabular data set types:

• DuckDB Relational Database - An embedded, file-based (or in-memory) database managed by the DuckDB engine and accessed directly through its JDBC driver.
• DuckDB Relational Database Schema - A database schema hosted on a relational DuckDB database capable of being called through a JDBC Driver.

A DuckDB resource has up to a three-level name: databaseName.schemaName.tableName.columnName - there is no server name, since the database is the top-level resource.

Connectors to DuckDB¶

The DuckDB connectors extend the generic JDBC connectors, and reuse the same generic JDBC Database Cataloguer used by PostgreSQL, Microsoft SQL Server, Oracle and Db2 LUW to catalogue the database's own native schemas and tables:

There is no server-level connector or survey service (DuckDB has no server to survey or catalogue), and no DuckDB-specific tabular data set connector. These connectors, their template and the associated reference data are delivered together in the DuckDBContentPack.

Surveying a DuckDB database¶

A survey is the recommended first step when you encounter a DuckDB database you don't already have documented in Egeria. The DuckDB Database Survey service connects directly to the database (no server-level survey exists, since there is no server) and produces two kinds of finding: a structural profile of the database itself, and a report on what it federates with.

The structural annotations¶

These reuse the same vendor-neutral annotation types as PostgreSQL, Microsoft SQL Server, Oracle and Db2 LUW, sourced from DuckDB's own catalog metadata table functions (duckdb_schemas(), duckdb_tables(), duckdb_views(), duckdb_columns(), pragma_database_size()) rather than a pg_catalog/sys.*/SYSCAT equivalent - but because DuckDB is embedded, it has no persistent workload-statistics infrastructure to draw on, so several fields that the other four connectors populate are simply absent here:

{
  "class": "ResourceMeasureAnnotation",
  "annotationType": "Capture Database Table Measurements",
  "summary": "Capture summary statistics about a database table.",
  "explanation": "This annotation retrieves statistics about individual columns and aggregates them into a summary for the table.",
  "analysisStep": "Profiling Associated Resources",
  "resourceProperties": {
    "Table Qualified Name": "analytics.main.EMPLOYEE",
    "Table Name": "EMPLOYEE",
    "Table Type": "Table",
    "Number of columns": "8",
    "Has Indexes": "false"
  }
}

The federation annotations¶

DuckDB's ATTACH statement can federate the database with another DuckDB file, a PostgreSQL server, a SQLite file, or (via extensions) object storage; and a view can be defined directly over an external file or object-store resource using table functions like read_parquet(), read_csv(), read_json(), iceberg_scan() or delta_scan(). None of this is captured by the vendor-neutral annotation types above, so the survey adds two DuckDB-specific annotation types of its own:

{
  "class": "ResourceMeasureAnnotation",
  "annotationType": "Capture Attached Data Source",
  "summary": "Capture details of a data source that has been attached to the DuckDB database using its ATTACH federation capability.",
  "explanation": "This annotation is created once for each non-internal entry found in DuckDB's duckdb_databases() table function.",
  "analysisStep": "Profiling Associated Resources",
  "resourceProperties": {
    "Alias": "mypg",
    "Source Type": "postgres",
    "Location": "host=myhost dbname=mydb user=myuser password=***",
    "Read Only": "true"
  }
}

{
  "class": "ResourceMeasureAnnotation",
  "annotationType": "Capture External File Data Source",
  "summary": "Capture details of an external file, or object-store, resource that is scanned by a view defined in the DuckDB database using functions such as read_parquet(), read_csv(), read_json(), iceberg_scan() or delta_scan().",
  "explanation": "This annotation is created once for each external-scan view found in DuckDB's duckdb_views() table function.",
  "analysisStep": "Profiling Associated Resources",
  "resourceProperties": {
    "View Name": "monthly_sales",
    "Scan Function": "read_parquet",
    "Location": "s3://my-bucket/sales/2026/*.parquet",
    "Format": "parquet"
  }
}

(Screenshot to follow: the survey report and its annotations, as displayed against the surveyed asset in the Egeria UI.)

How the survey avoids touching your business data¶

The survey only ever queries DuckDB's own catalog metadata table functions and pragma_database_size() - it never runs a SELECT against your business tables, and the Location value recorded for an attached data source has any password stripped out of the connection string before it is written to open metadata.

DuckDB has no controllable "which databases" list¶

The include/exclude configuration properties that let you narrow a PostgreSQL, Microsoft SQL Server, Oracle or Db2 LUW server survey/cataloguer down to particular databases do not apply here - a DuckDB catalog target is already a single database (there is nothing above it to narrow from). Once inside that database, the JDBC Database Cataloguer integration connector still offers the usual includeSchemaNames/excludeSchemaNames, includeTableNames/excludeTableNames, includeViewNames/excludeViewNames and includeColumnNames/excludeColumnNames properties, exactly as it does for the other four relational technologies.

Cataloguing a DuckDB database¶

Cataloguing a DuckDB database starts the DuckDB Database Cataloguer against the DuckDB Relational Database asset (created from its template), which does three things on every refresh:

1. Identifies the asset. For a file-based database, it uses the same FileClassifier logic that the Files connectors use, so the database's catalogued identity is tied to its canonical (masked) file path. This step is skipped for an in-memory (:memory:) database.
2. Hands off native cataloguing. It attaches the JDBC Database Cataloguer to the database via the same "friendship" pattern used by the PostgreSQL, Microsoft SQL Server, Oracle and Db2 LUW connectors, so the database's own schemas, tables, columns, primary keys and foreign keys are catalogued the standard way.
3. Catalogues federation relationships. This is DuckDB-specific. It re-issues any configured attachStatements (needed because DuckDB does not persist ATTACH-ed data sources in the database file between sessions - every new connection has to ATTACH them again itself), then:
   • For each attached database, it resolves or creates an asset for the attached source - reusing the same file-matching logic as step 1 for a file-backed source (another DuckDB file, or SQLite), or treating it as a network-backed resource otherwise - and links it to the DuckDB database with a "Database attached to the DuckDB database via ATTACH" resource-list relationship. Because there is no friendship-connector-catalogued representation to link to for a foreign network source, its tables are catalogued directly as TabularDataSet assets.
   • For each external file scan, it resolves the scanned file (or object-store location) as a file asset using the same FileClassifier logic, and links the DuckDB view's columns to the file's columns as DerivedSchemaTypeQueryTarget lineage - so a query like SELECT column FROM monthly_sales shows up as column-level lineage back to the Parquet file it actually reads from.

Each federation finding is handled defensively - a problem resolving or cataloguing one attached source or external file does not stop the rest of the pass.

(Screenshot to follow: the catalogued database, its native tables, and its federation relationships to attached sources and external files, shown in the Egeria UI.)

Running this through the Automated Curation API¶

All of the capabilities above are packaged as ready-to-run governance action processes, delivered by the DuckDBContentPack. You initiate them through the Automated Curation API of a view server - either directly over REST, or through pyegeria. Because DuckDB has no server tier, there is only one level of process - there is no server-level equivalent the way there is for PostgreSQL, Microsoft SQL Server, Oracle and Db2 LUW:

Each CreateAndSurvey process runs the same three steps as its PostgreSQL, Microsoft SQL Server, Oracle and Db2 LUW equivalents: create the asset, run the survey, then print the resulting report; the CreateAsCatalogTarget process runs two steps: create the asset, then attach it as a catalog target to the DuckDB Database Cataloguer.

Using the REST API¶

The REST calls below assume you have a running view server (view-server in these examples) and have already obtained a bearer token.

@baseURL=https://localhost:9443
@viewServer=view-server

### Get a bearer token
# @name Token
POST {{baseURL}}/api/token
Content-Type: application/json

{
  "userId" : "peterprofile",
  "password" : "secret"
}

> {% client.global.set("token", response.body); %}

### Survey a DuckDB database
POST {{baseURL}}/servers/{{viewServer}}/api/open-metadata/automated-curation/governance-action-processes/initiate
Authorization: Bearer {{token}}
Content-Type: application/json

{
  "processQualifiedName": "DuckDBDatabase:CreateAndSurveyGovernanceActionProcess",
  "requestParameters" : {
    "databasePath" : "/data/databases/analytics.duckdb",
    "databaseName" : "analytics",
    "versionIdentifier" : "1.0",
    "description" : "DuckDB analytical database."
  }
}

> {% client.global.set("surveyInstanceGUID", response.body.guid); %}

### Catalog the same DuckDB database
POST {{baseURL}}/servers/{{viewServer}}/api/open-metadata/automated-curation/governance-action-processes/initiate
Authorization: Bearer {{token}}
Content-Type: application/json

{
  "processQualifiedName": "DuckDBDatabase::CreateAsCatalogTargetGovernanceActionProcess",
  "requestParameters" : {
    "databasePath" : "/data/databases/analytics.duckdb",
    "databaseName" : "analytics",
    "versionIdentifier" : "1.0",
    "description" : "DuckDB analytical database."
  }
}

> {% client.global.set("catalogInstanceGUID", response.body.guid); %}

### Check progress of an engine action started by either process above
GET {{baseURL}}/servers/{{viewServer}}/api/open-metadata/automated-curation/engine-actions/active?startFrom=0&pageSize=5
Authorization: Bearer {{token}}

### Retrieve the flow definition and progress of a specific process instance
POST {{baseURL}}/servers/{{viewServer}}/api/open-metadata/governance-officer/governance-action-processes/{{catalogInstanceGUID}}/graph
Authorization: Bearer {{token}}

Notice there is no hostIdentifier/portNumber - databasePath (or the literal :memory: for an in-memory database) is how a DuckDB database is located, since it is a file rather than a network service. If the database uses ATTACH to federate with other data, add attachStatements to requestParameters so the cataloguer and survey connectors can re-establish those federation relationships on their own connections - for example:

"requestParameters" : {
  "databasePath" : "/data/databases/analytics.duckdb",
  "databaseName" : "analytics",
  "attachStatements" : "INSTALL postgres,LOAD postgres,ATTACH 'host=myhost dbname=mydb user=myuser password=mypassword' AS mypg (TYPE POSTGRES, READ_ONLY)"
}

Using pyegeria¶

This follows the same pattern as the PostgreSQL pyegeria walkthrough - just with the DuckDB process names and databasePath/databaseName substituted for hostIdentifier/portNumber:

from pyegeria import EgeriaTech, load_mermaid, render_mermaid

view_server = "qs-view-server"
url         = "https://host.docker.internal:9443"
user_id     = "erinoverview"
user_pwd    = "secret"

egeria_tech = EgeriaTech(view_server, url, user_id, user_pwd)
token = egeria_tech.create_egeria_bearer_token()

# See what DuckDB governance action processes are available
elements = egeria_tech.find_elements_by_property_value(
    property_value="DuckDB",
    property_names=["qualifiedName"],
    metadata_element_type_name="GovernanceActionProcess",
)
for element in elements:
    properties = element.get("properties")
    print("* " + properties.get("qualifiedName") + " - " + properties.get("description"))

# Survey a DuckDB database
request_parameters = {
    "databasePath" : "/data/databases/analytics.duckdb",
    "databaseName" : "analytics",
    "versionIdentifier" : "1.0",
    "description" : "DuckDB analytical database.",
}

survey_instance_guid = egeria_tech.initiate_gov_action_process(
    "DuckDBDatabase:CreateAndSurveyGovernanceActionProcess",
    None, None, None, request_parameters, None, None,
)

# Watch the process run as a mermaid diagram
load_mermaid()
process_graph = egeria_tech.get_governance_process_graph(survey_instance_guid)
render_mermaid(process_graph.get("governanceActionProcessMermaidGraph"))

# Once you're happy with what the survey found, catalog the database too
catalog_instance_guid = egeria_tech.initiate_gov_action_process(
    "DuckDBDatabase::CreateAsCatalogTargetGovernanceActionProcess",
    None, None, None, request_parameters, None, None,
)

To follow progress of the integration connectors once cataloguing has started, use:

from commands.ops.monitor_daemon_status import display_integration_daemon_status

display_integration_daemon_status(
    ["DuckDBDatabaseCataloguer", "JDBCDatabaseCataloguer"],
    integ_server="qs-integration-daemon",
    paging=True,
    width=150,
)

(Screenshot to follow: display_integration_daemon_status() output for the DuckDB connectors.)

Related information¶

• Leveraging your PostgreSQL estate - the equivalent solution for PostgreSQL, and the source of the JDBC Database Cataloguer that DuckDB's own schemas and tables are catalogued through.
• Leveraging your Db2 for Linux, UNIX and Windows estate - another connector without a server-wide auto-discovery capability, though for a different reason (no cross-database catalog view, rather than no server at all).
• Open Survey Framework - background on survey reports, annotations and analysis steps.
• Automated Curation - the API used to initiate governance action processes.

Raise an issue or comment below