mech.app

The mech.app newsletter

Agentic AI, minus the noise.

Get practical field notes on AI agents, automation, developer tools and security delivered to your inbox.

No spam. Unsubscribe anytime.

AI Agents

Enterprise Data Pipelines for Agent Consumption: Schema, Access, and Observability

How Financial Independence Group transformed BI warehouses into agent-ready data layers with semantic metadata, access boundaries, and query instrumenta...

Source: aws.amazon.com
Enterprise Data Pipelines for Agent Consumption: Schema, Access, and Observability

Traditional BI pipelines optimize for human-authored SQL and parameterized dashboards. Agent-driven reasoning breaks that contract. Financial Independence Group’s production deployment at the AWS Quick User Group DC meetup exposed the data engineering work required when autonomous agents replace report writers.

The shift from reporting to reasoning changes three layers: schema design, access control primitives, and query observability. Agents generate unpredictable query patterns, require embedded context that BI cubes never needed, and fail silently when they hit stale materialized views or schema boundaries.

Semantic Layers for Agents vs. BI Cubes

Traditional BI cubes store aggregations, dimensions, and measures. Agent-ready semantic layers add lineage, business logic metadata, and natural language descriptions that LLMs can parse.

Key differences:

  • Embedded context: Each table and column includes plain-text descriptions of what the data represents, update cadence, and known limitations. Agents use this to decide whether a query is answerable.
  • Lineage metadata: Agents need to know if a metric is derived, which upstream sources feed it, and when the last refresh occurred. BI tools assume humans already know this.
  • Constraint declarations: Semantic layers expose valid filter ranges, allowed join paths, and cardinality warnings. Agents use these to avoid generating queries that time out or return nonsense.

Financial Independence Group added a metadata service that sits between their Redshift warehouse and agent orchestration layer. Every table exposes a JSON schema with descriptions, update timestamps, and dependency graphs. Agents query the metadata service before generating SQL.

Access Control Primitives for Autonomous Queries

Human-driven BI uses role-based access control with fixed report templates. Agents generate arbitrary queries at runtime, so access control must operate at the column and row level with dynamic policy evaluation.

New primitives required:

Control TypeBI ApproachAgent Approach
Query scopePredefined report templatesDynamic query generation with column-level grants
Row filteringStatic WHERE clauses in viewsPolicy engine evaluates agent context at query time
Audit trailUser ID + report nameAgent ID + reasoning chain + generated SQL
Rate limitingPer-user session limitsPer-agent token budget + query cost estimation

Financial Independence Group implemented a policy engine that intercepts agent-generated SQL. The engine injects row-level filters based on the agent’s assigned role and logs the full reasoning chain. If an agent tries to query customer PII without proper scope, the policy engine rewrites the query to anonymize or reject it.

They also added a cost estimator that predicts query execution time and resource consumption before the query runs. Agents receive a token budget per session. Expensive queries consume more tokens, preventing runaway resource usage.

Query Pattern Instrumentation

BI dashboards hit the same queries repeatedly. Agents explore the schema dynamically, so you need instrumentation that detects when agents hit schema boundaries, stale data, or unsupported join paths.

Instrumentation points:

  • Schema boundary detection: Log when an agent generates a query referencing a table or column that doesn’t exist. This signals missing metadata or incomplete semantic layer coverage.
  • Stale view warnings: Track when agents query materialized views that haven’t refreshed within their declared SLA. Surface this in the agent’s context so it can caveat its answer.
  • Join path failures: Monitor queries that attempt joins across tables without declared relationships. These often time out or return Cartesian products.

Financial Independence Group added a query analyzer that runs before execution. It checks the semantic layer for declared join paths and warns the agent if a query attempts an undocumented join. The agent can then ask for clarification or fall back to a simpler query.

They also instrument refresh lag. If a materialized view is more than four hours stale, the metadata service flags it. Agents receive this signal and include a disclaimer in their response.

Architecture: Metadata Service and Policy Engine

# Simplified metadata service query flow
class AgentDataService:
    def __init__(self, warehouse, metadata_store, policy_engine):
        self.warehouse = warehouse
        self.metadata = metadata_store
        self.policy = policy_engine
    
    def execute_agent_query(self, agent_id, reasoning_chain, sql):
        # Step 1: Validate against semantic layer
        schema_check = self.metadata.validate_query(sql)
        if not schema_check.valid:
            return {"error": "Schema boundary", "details": schema_check.issues}
        
        # Step 2: Apply access policies
        rewritten_sql = self.policy.apply_policies(agent_id, sql)
        
        # Step 3: Estimate cost
        cost = self.warehouse.estimate_cost(rewritten_sql)
        if cost > agent_budget(agent_id):
            return {"error": "Budget exceeded", "estimated_cost": cost}
        
        # Step 4: Check freshness
        freshness = self.metadata.check_freshness(rewritten_sql)
        
        # Step 5: Execute and log
        result = self.warehouse.execute(rewritten_sql)
        self.log_query(agent_id, reasoning_chain, rewritten_sql, cost, freshness)
        
        return {"data": result, "freshness_warning": freshness.stale}

The metadata store holds table schemas, column descriptions, lineage graphs, and refresh timestamps. The policy engine rewrites SQL to enforce row-level security. The warehouse executes the final query and returns results with freshness metadata.

Failure Modes and Mitigations

Common failure modes:

  • Schema drift: Agents cache metadata. If the warehouse schema changes, agents generate invalid SQL until the cache refreshes. Mitigation: version the semantic layer and invalidate agent caches on schema updates.
  • Ambiguous joins: Agents infer join paths from table names. If two tables share a column name but shouldn’t be joined, agents generate slow queries. Mitigation: declare allowed join paths explicitly in the semantic layer.
  • Stale aggregates: Agents query pre-aggregated tables without checking refresh status. Mitigation: embed refresh timestamps in metadata and surface staleness warnings.

Financial Independence Group saw schema drift failures in their first month. They added a schema versioning system that increments a version number on every DDL change. Agents include the schema version in their metadata queries. If the version mismatches, the metadata service forces a cache refresh.

Deployment Shape

Financial Independence Group runs the metadata service as a separate container in ECS. It queries the Redshift system catalog on a schedule and builds a JSON representation of the semantic layer. Agents call the metadata service via HTTP before generating SQL.

The policy engine runs as a Lambda function triggered by API Gateway. It receives agent ID, reasoning chain, and raw SQL. It queries IAM for the agent’s role, applies row-level filters, and returns rewritten SQL.

Query logs go to CloudWatch with structured JSON. Each log entry includes agent ID, reasoning chain, generated SQL, rewritten SQL, execution time, and cost. They use CloudWatch Insights to detect patterns like repeated schema boundary errors or queries that consistently time out.

Technical Verdict

Use this pattern when:

  • You have an existing BI warehouse and want to add agent-driven analytics without rebuilding the data layer.
  • Agents need to generate queries dynamically rather than execute predefined templates.
  • You need fine-grained access control and audit trails for autonomous queries.

Avoid this pattern when:

  • Your data model changes frequently and you can’t maintain a stable semantic layer.
  • Query latency requirements are under 100ms (the metadata service and policy engine add overhead).
  • You don’t have the engineering capacity to instrument query failures and maintain metadata freshness.

The metadata service and policy engine add complexity but make agent queries safer and more observable. If you’re moving from static reports to agent-driven reasoning, expect to spend more time on schema documentation and access control than on the agent orchestration itself.