data-engineering-mcp

Generic, local, and offline MCP server that transforms XLSX file headers into a searchable catalog, infers candidate relationships, and generates only Oracle SQL SELECT/WITH. It does not access databases, does not execute SQL, and has no Power BI integration or dependency.

Requirements and installation

• Python 3.12

• MCP Python SDK 2.x (MCPServer, the current public API of the installed version)

python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -e ".[dev]"

Runtime dependencies: mcp, pandas, openpyxl, and pydantic; tests use pytest. The catalog uses openpyxl directly to open the workbooks in read-only mode and reads only the first required row.

Related MCP server: io.github.Optisol-Business/db-metadata-extractor-mcp

Data and architecture

Datasets are provided locally by the user and are not part of the repository. Place .xlsx files in data/; they remain available to the MCP but are ignored by Git. Each file is a table. The logical name strips .xlsx, the rawzn. prefix, and the _SINTETICO suffix, using a case-insensitive comparison. The first sheet not named SQL that has headers is used; records do not participate in discovery.

data/*.xlsx -> Catalog -> RelationshipEngine -> SQLGenerator
                                      \-> explicação conservadora de SQL

New files are discovered by atualizar_catalogo, with no code changes. The update also detects removals and changes in the ordered header list, updates the timestamp, and recalculates candidates. The local schemas/datasets directory is configurable via the DATA_ENGINEERING_MCP_DATA_DIR variable; the default is data/.

Execution and testing

.\.venv\Scripts\data-engineering-mcp.exe
# ou
.\.venv\Scripts\python.exe -m data_engineering_mcp.server

.\.venv\Scripts\python.exe -m pytest
.\.venv\Scripts\python.exe scripts\smoke_test.py

The default transport is stdio. Logs go to stderr to avoid corrupting the protocol. Set DATA_ENGINEERING_MCP_DATA_DIR to use a different local folder.

Tools

• listar_tabelas, descrever_tabela, buscar_coluna, buscar_tabelas

• inferir_relacionamentos, encontrar_caminho, gerar_join

• atualizar_catalogo, status_catalogo

• gerar_sql, gerar_select, explicar_sql

gerar_sql receives tabelas, colunas, and typed filters {table?, column, operator, value}. Operators: =, <>, >, >=, <, <=, IN, IS NULL, IS NOT NULL, LIKE. Values become bind variables (:p1), never concatenated text. Ambiguous columns require TABELA.COLUNA. The default JOIN is LEFT JOIN, a conservative choice that preserves rows from the first table; the tool reports MEDIUM candidates and rejects LOW by default.

Example arguments:

{
  "tabelas": ["RAW_HAP_TB_USUARIO", "RAW_HAP_TB_PESSOA"],
  "colunas": ["CD_USUARIO", "NM_PESSOA_RAZAO_SOCIAL"],
  "filtros": [{"column": "FL_STATUS_USUARIO", "operator": "=", "value": 2}]
}

Relationship confidence

Every result is a nominal candidate, never a confirmed PK/FK. The score starts at 20 for identical column names; adds 30 for CD_, ID_, or NU_ prefixes; adds 20 when the column entity appears in a table name, plus 10 more if it appears in both. Occurrence in more than two tables subtracts 5 per extra occurrence (maximum 25); generic columns without an identifier prefix subtract 25. HIGH >= 75, MEDIUM >= 50, LOW < 50. No data values are read and no cardinality is computed, and this version has no explicit PK/FK metadata.

Security and limitations

Only structured Oracle SELECT/WITH SQL is generated. There is no surface for DDL/DML, arbitrary filter SQL, credentials, network, database, or external APIs.

Known limitations:

• relationships are inferred nominally and may produce false positives or semantic negatives;

• this version has no explicit PK/FK metadata;

• generic or shared columns may lead to inappropriate paths;

• LOW candidates must not be used automatically;

• MEDIUM candidates are inferences, not confirmations;

• explicar_sql performs conservative syntactic analysis and does not semantically validate the query against Oracle;

• some accent-containing logs may display incorrectly on a Windows console configured as CP1252.

XLSX files are always treated as read-only. Local datasets (data/*, *.xlsx, *.xls, *.csv, and *.parquet) are ignored and are not part of the repository.