Build the Engine, Not the Pipeline: Lessons from a Configuration-Driven Data Platform

Build the Engine, Not the Pipeline

AI reduces the effort required to implement a solution. It doesn't automatically establish what the right solution is. I've been thinking about that since leaving a project where we built a reusable data processing framework, one that agents like Claude Code and Codex could probably accelerate a great deal.

On a previous project, I helped build that framework using Java, Spring Boot, and SQL. The goal was to let different teams create data processing workloads without building a new application for each one. Data arrived from different sources, in different formats, with different schemas and business rules, and came from databases, files, APIs, or remote storage systems. But much of the underlying processing was repetitive, so instead of rebuilding that logic for every workload, we built a shared engine whose behavior was largely defined through JSON configurations.

The experience taught me a lot about ETL, data modeling, software architecture, and the challenges of creating a platform that other developers could use. Here is what stayed with me, and how I'd approach the same problem today.

1. Build reusable capabilities, not individual pipelines

The main architectural decision was to separate the engine's implementation from the requirements of each data workload.

Java provided the capabilities. JSON defined how they were used.

The framework had several core concepts:

Metadata and models described the structure of data, including field names, identifiers, data types, and default transformations.

Mappers defined how fields were interpreted and converted between formats such as JSON, CSV, Excel, SQL, and Parquet. They controlled mappings, type casting, merging, and transformations.

Configurations described environment settings, connections, database drivers, and secrets.

The engine organized processing into three responsibilities:

  • Consumers ingested data from different sources.
  • Transformers processed and reshaped the data.
  • Publishers wrote the results to their destinations.

These components contained pipelines composed of smaller units called pipes. We also had JSON-configurable REST APIs and a scheduler capable of triggering engines or individual pipelines. The scheduler maintained execution state, while a logging service captured information at pipeline and pipe level.

This design meant different teams could reuse the same processing framework rather than maintain separate applications with overlapping functionality.

Lesson: The important abstraction wasn't JSON itself. It was defining reusable contracts between processing components.

However, configuration introduces a trade-off. As more behavior becomes configurable, the configuration model becomes more complex. Add conditionals, loops, and variable substitution, and at some point you risk creating another programming language, with none of the tooling that real languages have: debuggers, type checks, and tests. A good framework needs a clear boundary between common behavior that can be configured and specialized behavior that belongs in code.

2. ETL is about preserving data correctness

One thing I learned through ETL development is that moving data is often easier than preserving its meaning. Different systems represent information differently. A date might arrive as a string. A decimal might lose precision during conversion. Identifiers might not match between systems. Records can be incomplete, duplicated, or inconsistent.

A typical case is decimal precision. A job can finish successfully with matching row counts and still produce wrong totals, because an amount was read as a floating-point value somewhere between the source and the target. We caught this kind of problem by reconciling aggregates, comparing the sum of a column in the source against the sum in the target, not just the number of rows. The fix was to declare the column explicitly as a decimal with a defined precision and scale in the mapping, and to add the reconciliation as a validation step so the job fails instead of loading quietly.

These problems require explicit decisions about schemas, types, mappings, and transformations, and our metadata and mapping abstractions helped describe how data should be interpreted as it moved through the framework. The broader engineering principle is that schemas are contracts. They describe what downstream systems can expect from a dataset.

But a successful pipeline execution does not necessarily mean the output is correct. Good validation should include technical checks, such as data types and nullability, alongside business rules, uniqueness, relationships, and reconciliation against expected results.

Lesson: Data correctness has to be demonstrated, not assumed.

3. Process large datasets where it makes sense

A significant part of my work involved SQL Server and data-heavy transformations.

We used staging tables, stored procedures, set-based operations, CTEs, and incremental updates to process large datasets.

One important lesson was knowing when to let the database perform the work. Processing records individually through Java isn't always efficient, particularly when the operation involves large joins, filters, aggregations, or updates. For relational workloads, staging data and applying database-side transformations can avoid unnecessary movement between the application and database.

Incremental processing is another important optimization. Rather than rewriting an entire dataset, we can identify new or changed records and process only what's necessary.

This comes with trade-offs. Complex SQL can be harder to maintain and test, while database-specific features increase coupling to a particular engine. The right choice depends on performance requirements, the data volume, and where the data already lives.

I covered some of these techniques in SQL Stored Procedure Best Practices, and the basic extract, transform, load flow in Building a Basic ETL Pipeline in Python with OOP.

Lesson: ETL transforms data before loading it, while ELT loads it first and transforms it inside the destination system. Choosing between them is an architectural decision, not a contest. Processing should happen where it makes the most sense for correctness, performance, and maintainability.

4. Design data for the questions it needs to answer

Data engineering goes beyond ingestion and transformation, because the structure of a dataset determines which questions it can answer. Operational systems often focus on maintaining the current state of a record. Analytical systems need to explore trends, relationships, aggregates, and historical changes. That is why the models our framework produced had to be designed with their consumers in mind, not just their sources.

This is where dimensional modeling becomes important. Star schemas organize information into fact and dimension tables, making analytical queries easier to understand and optimize. A key decision is defining the grain of a fact table: what does one row represent?

Historical information adds another layer. Slowly Changing Dimension Type 2 preserves historical versions of records, for example keeping both of a customer's addresses with the dates each applied. Type 3 retains selected previous values alongside current ones, such as a current and a previous address on the same row. Both help answer how information changed over time, and both are distinct from operational audit logs, which record execution activity or changes made to data.

Start with the business question, define the required historical context, and then design the data model. The best model isn't necessarily the most elaborate one. It's the one that makes the required questions accurate and efficient to answer.

Lesson: Design the data model around the questions it must answer, not around the shape of the source.

5. Production data platforms need visibility

Our framework included scheduling, job state, and logging capabilities. These allowed developers to understand which jobs ran, their execution status, and where failures occurred. That mattered because troubleshooting a shared platform is different from debugging a standalone application. Failures can originate in source systems, connections, mappings, transformations, database operations, or deployment environments.

The hardest failures to troubleshoot were the quiet ones. A job could be marked as completed while the target held fewer rows than expected, for example when a batch from an API timed out and was skipped. The job state said "success"; only the step-level logs showed what had been dropped. The lesson was to record read and written row counts per step in the job state, and to fail the job, or flag it as completed with warnings, when they diverge. Pairing that with idempotent loads made it safe to simply rerun a job after fixing the cause.

Working with different Java libraries, database drivers, authentication mechanisms, and cloud infrastructure also exposed me to integration and deployment challenges that had little to do with transformation logic itself.

Logging and execution tracking are part of the foundation, but a broader production architecture must also consider safe retries, idempotency, reconciliation, recovery, and data lineage. These serve different purposes. Logs explain execution, lineage explains data dependencies, and recovery mechanisms determine how processing can safely continue after failures.

Lesson: A pipeline that works once is not the same as a system that can be operated reliably.

6. A shared platform changes engineering responsibilities

One of the most valuable parts of this project was working on something intended to serve multiple teams. I contributed to implementing and improving reusable components, solving integration problems, optimizing transformations, supporting production deployment, and helping other developers work with the framework.

That changes how you approach engineering decisions. When building one pipeline, success is delivering that workload. When developing shared infrastructure, you also have to consider how future workloads will be built, how changes affect existing consumers, and how developers will understand and maintain the system.

Documentation, consistent conventions, testing, and the developer experience become important platform capabilities. A framework that supports many features but is difficult to understand can easily lose the productivity benefits it was intended to provide. The goal of platform engineering is to reduce repeated effort while keeping the system understandable and reliable for its consumers.

Lesson: A shared platform is a product, and its developers are its users.

7. What changes with AI coding agents?

We built this framework before AI coding agents were mature enough to use on a project like this. They were only starting to get there as I left the team, so I never used them on it, and everything below is how I imagine it would work, not something I measured.

Even though JSON configuration reduced the need to write new Java applications, creating a workload still involved considerable setup. Developers needed to understand the framework, define models, map fields, configure readers and writers, establish connections, and specify transformations. Much of that work was repetitive.

Today, I imagine tools such as Claude Code or Codex could handle a substantial portion of it. Given the framework's configuration schemas, documentation, and existing examples, an agent could generate much of the initial pipeline configuration.

But I think the more interesting opportunity is combining AI agents with Spec-Driven Development (SDD), which I wrote about in The Shift from Vibe Coding to Spec-Driven Development.

Define the requirements before generating the pipeline

Instead of starting directly with JSON configuration, I would begin with a version-controlled specification.

That specification would describe:

  • The source and destination systems.
  • The expected data model and field mappings.
  • Transformation and data quality rules.
  • Historical data requirements.
  • Scheduling and operational constraints.
  • Expected outputs and acceptance criteria.

An agent could use this specification to generate the metadata, models, mappers, pipelines, SQL transformations, tests, and documentation.

The framework would still execute the workload. The agent would accelerate its construction.

I'd also keep architecture decisions, mapping rules, configuration changes, and validation results in the repository.

This makes the requirements available to both developers and agents while preserving a history of why decisions were made.

Give agents the tools to verify their work

Generating configuration is only one part of the opportunity.

With appropriate access and tooling, agents can inspect existing schemas, propose mappings, execute tests, analyze error logs, and suggest changes.

They can also produce source-to-target mapping documentation and reconciliation queries.

Data platform vendors are already adding agent tooling to their products, and the direction is clear: agents are moving from writing isolated code snippets toward assisting with complete engineering workflows.

But verification remains essential.

Generated SQL can be syntactically correct and still implement the wrong business rule. A pipeline can run successfully while creating duplicate records or losing historical information.

An agent should not be trusted merely because the configuration passes validation.

It should be expected to demonstrate correctness through tests and reconciliation, with engineers reviewing consequential decisions.

Access to production data and infrastructure also needs appropriate permissions, controls, and approvals.

Does this make development ten times faster?

Possibly for certain tasks.

Generating repetitive model definitions, field mappings, and pipeline configurations could become dramatically faster.

Onboarding a developer to an unfamiliar framework could also become easier when an agent can inspect documentation, explain components, and generate working examples.

I don't know how the project's development process has evolved since I left, and I wouldn't claim a specific productivity improvement without measuring it. What seems likely is that the cost of repetitive implementation will continue to decrease.

The more important question is where that leaves engineering judgment.

Agents can help discover technical metadata, but stakeholders still need to establish what the data means.

Agents can propose models, but engineers must decide whether those models support the intended analytical questions.

Agents can generate tests, but somebody needs to define the correct outcomes.

AI reduces the effort required to implement a solution. It doesn't automatically establish what the right solution is.

That makes accurate specifications, domain knowledge, and verification increasingly important.

Lesson: AI agents speed up implementation, but specifications, domain knowledge, and verification still decide whether the result is right.

Final thoughts

Building this framework gave me experience across ETL, architecture, data modeling, SQL performance, and platform design, and it changed how I thought about engineering impact: instead of solving each problem independently, we were building capabilities other teams could use repeatedly.

Today, a well-designed, documented, configuration-driven framework looks like a strong foundation for AI-assisted development. Agents can generate much of the repetitive implementation, while specifications and automated tests provide direction and feedback. But the fundamental responsibilities remain: understand the data, choose the right abstractions, verify correctness, and make systems reliable.

The goal isn't to build pipelines faster for its own sake. It's to deliver trustworthy data faster, with less repeated engineering effort.