Building an Agentic Data Factory with Parquet, DuckDB, MCP, and Refinement Loops

Giving an AI agent database credentials is easy. Giving it data it can use reliably is much harder.

A typical analytical agent starts by discovering schemas, identifying tables, generating SQL, loading results into context, reconstructing business definitions, and calculating the requested metric. That can work for one question. It does not create a reusable system.

The next agent repeats the discovery process. A slightly different prompt produces a different query. Business logic remains trapped in conversations. Large result sets consume context. Production databases absorb repeated analytical traffic. Answers vary because each workflow reconstructs the company independently.

We have been approaching this as an infrastructure problem: agents need a Data Factory.

An agentic Data Factory applies the same operating principles emerging in software factories to analytical work. It gives agents context, specialized tools, validation, feedback, memory, and repeated refinement loops. Its primary outputs are not generated prose or one-time SQL queries. They are governed metrics, reusable datasets, relationships, and knowledge products.

This article covers the technical architecture behind that model.

1. Treat raw data as source material

Raw tables are optimized for applications and transactions. They are rarely the correct interface for every analytical agent.

The source layer may include:

• Product databases such as Postgres or Supabase
• Billing systems such as Stripe
• Product analytics and event systems
• CRMs and customer platforms
• Internal APIs
• External MCP servers

A Data Factory should access these systems through scoped, read-only connections and move analytical work away from repeated direct production queries.

The sources are inputs. They are not the finished agent interface.

2. Build a refinement pipeline

A conventional data pipeline moves and transforms data. An agentic Data Factory also refines it.

Refinement means converting raw records into analytical products with enough structure and context to be reused safely. A conceptual pipeline looks like this:

Source systems
    ↓
Schema and relationship discovery
    ↓
Cross-source joins and cleaning
    ↓
Business definitions and metric logic
    ↓
Materialized analytical datasets
    ↓
Profiling, quality checks, and anomaly detection
    ↓
Semantic + Knowledge Layer
    ↓
Dashboards, reports, APIs, and agents over MCP

The important boundary is between raw source data and the refined analytical product.

A refined dataset should include more than rows. It should carry:

• A stable name and purpose
• Source and relationship context
• Dimensions and measures
• Metric and KPI definitions
• Refresh state and history
• Annotations and important business events
• Quality and profiling metadata
• Permissions and usage rules
• Analytical lineage

That metadata makes the dataset useful beyond the workflow that created it.

3. Separate temporary work from durable data products

Agents need room to explore without turning every intermediate result into permanent infrastructure.

A useful dataset lifecycle has at least three states:

1. Temporary investigation dataset: Created for an immediate question and allowed to expire.
2. Presentation dataset: Used by dashboards, reports, or another defined artifact.
3. Durable dataset: Promoted for reuse by future agents, metrics, workflows, and interfaces.

Promotion should preserve the query definition, materialized result, metadata, lineage, permissions, and refresh behavior.

This lets agents work quickly while keeping the durable layer intentional. Valuable analytical work survives the conversation. Disposable exploration does not become permanent clutter.

4. Materialize with Parquet and query with DuckDB

For analytical agent workloads, a useful pattern is to materialize refined datasets as Parquet and query them with DuckDB.

Parquet provides a compact columnar format that works well for analytical scans. DuckDB provides an embedded analytical SQL engine capable of joins, aggregations, filters, window functions, and time-series transformations without pushing every request back to the source database.

A simplified dataset definition might look like this:

{
  "name": "customer_revenue_health",
  "sources": ["supabase.accounts", "stripe.charges"],
  "refresh": "0 6 * * *",
  "storage": "parquet",
  "query_engine": "duckdb",
  "purpose": "Revenue, retention, and customer-health analysis"
}

Once materialized, an agent can query the analytical product directly:

SELECT
  customer_id,
  current_mrr,
  active_users_30d,
  support_events_30d,
  churn_risk
FROM customer_revenue_health
WHERE churn_risk = 'high'
ORDER BY current_mrr DESC;

The source systems remain responsible for operational truth. The refined dataset becomes the reusable analytical interface.

5. Pre-calculate the units the business runs on

Agents should not independently rebuild the same KPI formulas inside every task.

The factory can calculate and store metrics with:

• Their definitions
• Current values
• Historical values
• Day-over-day changes
• Rolling 7-day, 14-day, and 30-day comparisons
• Related business events
• Anomaly state

A conceptual metric object could look like this:

{
  "metric": "net_revenue",
  "definition": "Captured charges minus refunds and fees",
  "value": 2674000,
  "as_of": "2026-09-30",
  "comparisons": {
    "day_over_day": 0.018,
    "rolling_7d": 0.064,
    "rolling_30d": 0.121
  },
  "events": ["pricing_change", "enterprise_plan_launch"],
  "anomaly": null
}

The exact storage model will vary, but the principle is stable: metric logic should become a governed and reusable product rather than prompt-local computation.

6. Build the Semantic and Knowledge Layers together

A semantic layer explains how data should be interpreted. It maps schemas, relationships, dimensions, measures, joins, definitions, and metric logic.

Agents also need organizational context that does not live in the schema:

• Company terminology
• Team preferences
• Historical events
• Rules and permissions
• Prior corrections and decisions
• Usage patterns
• Memories from previous analytical work

Connecting these elements creates a Knowledge Layer. A Knowledge Graph can represent the relationships among source systems, datasets, metrics, business concepts, events, people, and workflows.

Stripe charge ── contributes to ──> Net Revenue
     │                                  │
     └── belongs to ──> Customer ── affects ──> Retention
                                      │
Product usage event ── evidence for ──┘

The graph gives agents a structured way to navigate meaning instead of relying entirely on similarity search or a large context prompt.

7. Deliver refined data through MCP

The Model Context Protocol provides a useful interface between the Data Factory and external agents.

Instead of exposing a generic SQL endpoint, the MCP server can provide higher-level tools such as:

list_datasets()
get_dataset_profile(dataset_id)
query_dataset(dataset_id, sql)
get_metric(metric_id, comparison_window)
get_related_events(metric_id, date_range)
ask_data_analyst(question)

Higher-level tools preserve the factory’s governance and context. The agent discovers approved datasets, sees their purpose and profile, queries them within defined limits, and retrieves metrics through their established definitions.

The same interface can serve Claude, Codex, ChatGPT, Cursor, Gemini, Grok, agents in Slack, and internal systems without giving each one a separate interpretation of the company.

8. Add refinement and quality loops

A Data Factory should not declare an analytical product complete because a query executed.

The output should move through a loop:

Plan
  ↓
Source and join
  ↓
Materialize
  ↓
Profile and validate
  ↓
Review definitions and output
  ↓
Detect quality issues or anomalies
  ├── pass → publish or promote
  └── revise → return to the responsible stage

Quality checks can evaluate:

• Missing or duplicated keys
• Unexpected null rates
• Broken joins
• Grain mismatches
• Out-of-range values
• Metric-definition conflicts
• Stale refreshes
• Schema drift
• Statistical anomalies
• Inconsistent comparison windows

Corrections should become reusable context for future work. Over time, accepted definitions, promoted datasets, annotations, rules, and review findings improve the inputs available to the next agent.

This is how autonomy becomes trustworthy. Humans define goals, constraints, permissions, and review thresholds. Agents handle more of the repeated sourcing, calculation, validation, and refinement work.

From refined data to empowered judgment

Raw data can become information. Refined information connected through definitions, history, events, and relationships becomes knowledge.

Wisdom still belongs to the people responsible for the decision.

The purpose of an agentic Data Factory is not to automate judgment away. It is to give humans and agents a shared analytical foundation so people can spend less time reconstructing data and more time applying judgment to the business.

This is the architecture we are building with the Dreambase Data Factory. For the broader product thesis, read Agents Need a Data Factory.