From Informatica to dbt: Journey to Modern, Maintainable Data Pipelines

Around eight years ago, while developing Informatica PowerCenter mappings for data transformations, I asked my tech lead a simple question:

Why are we using a visual tool when everyone on the team understands SQL? Why can't we write this in plain SQL?

The answer was pragmatic. We absolutely could write the transformations in SQL, but putting queries into a collection of .sql files would create a different maintenance problem. Changes, dependencies, testing, and documentation would all become harder to manage consistently.

That answer stayed with me because the real issue was never whether SQL could express the transformation. It was everything required to operate that SQL as a reliable system.

SQL was not the missing piece

Data teams already knew how to use SQL. What they lacked was a shared workflow around it.

A directory full of queries does not automatically provide:

• dependency management;
• repeatable execution order;
• automated data-quality checks;
• generated documentation and lineage;
• isolated development environments; or
• a reviewable history of changes.

Traditional ETL tools supplied much of that structure through a graphical interface. The trade-off was that transformation logic became tied to the tool's abstractions and metadata rather than remaining visible as ordinary SQL.

What dbt changed

dbt (data build tool) approached the problem from the other direction: write transformation logic as SELECT statements, then let the framework handle the surrounding engineering workflow.

In practice, I have seen dbt bring several useful capabilities together:

1. SQL-centred transformations — Data professionals can work in the language they already understand.
2. Built-in testing — Teams can define generic and singular data tests alongside their models.
3. Documentation and lineage — Model descriptions and dependencies can be generated from the same project.
4. Software-engineering practices — Version control, modularity, code review, automated checks, and deployment workflows become normal parts of analytics work.

The important innovation is not simply that dbt runs SQL. It makes SQL transformations behave more like a maintainable software project.

One workflow across the analytics warehouse

I have worked with multi-layered analytical platforms where transformations were spread across tools such as Spark, Dataflow, stored procedures, and warehouse-specific jobs. Every additional execution pattern increases the operational knowledge a team must carry.

dbt can provide a common modelling workflow across supported data platforms and adapters, including warehouses such as BigQuery, Redshift, and others. It does not replace every data-processing system, and it should not be forced onto workloads it was not designed for. Its strength is creating a consistent transformation layer inside the analytics platform.

That consistency improves more than developer productivity. It gives reviewers a clearer view of how a model changed, helps new team members understand dependencies, and moves quality checks closer to the transformation logic.

The lesson from that first question

My old question was framed as visual tooling versus SQL. The better question is:

How do we keep transformation logic accessible while still giving teams the structure required to test, document, review, and operate it?

For many analytics teams, dbt is a strong answer. It preserves the directness of SQL while adding the engineering discipline that loose scripts are missing.

If you work with analytical data and have not tried it yet, the free dbt Fundamentals course is a useful place to start.