Data Infrastructure Part 2 – Alternative Approach

This is the second post in my Data Infrastructure Series. In Post #1, I walked through a Business Intelligence setup built almost entirely on the Microsoft ecosystem — Azure, Fabric, Power BI, and the tools that orbit that universe. That approach has a lot going for it: it’s well-integrated, relatively straightforward to deploy, and most IT teams already have licenses and familiarity in place. But it comes with a significant trade-off — you’re tying your entire data infrastructure to one vendor, and once you’re deep in, walking away is painful.

This post explores a different path. Not necessarily a better one in every situation — but a more open, more composable approach that gives you more control over your stack, your costs, and your future.

The core idea: use Snowflake as your data hub, bring data in with best-in-class ingestion tools, model it with dbt, and then plug in whatever visualization or AI tools make sense for your team.

The Architecture at a Glance

Before diving into each layer, here’s the 30-second version of how this stack hangs together:

Snowflake serves as your central data lake and warehouse
CData and/or Fivetran pull data from your source systems into Snowflake as the raw “bronze” layer
dbt (either open-source Core or the managed Cloud offering) transforms that raw data into clean, reliable “silver” and “gold” layers
Your visualization tool of choice — Power BI, Tableau, Sigma, Looker, whatever your team prefers — sits on top of the gold layer
AI tools, chatbots, and agents connect to the same well-modeled data with confidence

Start with Snowflake: Your Data Lake and Warehouse

Snowflake is the anchor of this approach. It acts as both your data lake (storing raw, unstructured or semi-structured data) and your warehouse (serving as the query engine for analytics). The platform has matured considerably and is genuinely easy to get up and running — especially with tools like Snowflake Cortex that simplify AI integration.

Within Snowflake, you’ll set up what’s called a medallion architecture — a layered approach to organizing your data:

Bronze layer: Raw, unmodified data exactly as it arrives from source systems. Nothing is changed here — this is your archive.
Silver layer: Cleaned, deduplicated, and lightly transformed data. Relationships are established and basic logic is applied.
Gold layer: Business-ready, aggregated, and purpose-built datasets for reporting, dashboards, and AI applications.

This structure gives you a single source of truth that multiple tools and teams can rely on, and it makes debugging and auditing your data much easier.

Getting Data In: CData and Fivetran

Your data doesn’t magically appear in Snowflake — you need connectors to pull it from your source systems. This is where tools like CData and Fivetran earn their keep.

Fivetran is the more widely known of the two. It offers hundreds of pre-built connectors to SaaS applications, databases, and other sources, and it handles the messy work of keeping those connections reliable and up to date. It’s a managed service, so you pay for the convenience, but the time savings are real — especially if you’re pulling from a dozen different systems.

CData is a strong alternative or complement, particularly if you’re working with less common data sources or need more control over how data is pulled. It supports an enormous range of connectors and can be deployed in different configurations depending on your environment.

Either way, the goal is the same: get your source data flowing into Snowflake’s bronze layer reliably and with minimal manual intervention.

Modeling with dbt: Silver and Gold Layers

Once your raw data is in Snowflake, dbt (data build tool) is how you shape it into something useful. dbt lets you write SQL-based transformations that turn your bronze layer into the clean, structured silver and gold layers your analysts and AI tools will actually work with.

dbt Core is open-source and free. You run it from the command line and manage it yourself. It’s powerful, but it requires some engineering comfort to set up and maintain. dbt Cloud is the managed, UI-driven version — it adds a web interface, scheduling, documentation generation, and easier collaboration. It’s not free, but for most teams the productivity gains justify the cost.

What makes dbt compelling in this context is the talent pool. SQL is one of the most widely known languages in the data world. If you can write SQL, you can write dbt models. That means you’re not dependent on a small group of specialists — a much broader set of analysts, engineers, and data practitioners can contribute to and maintain the models.

dbt also produces documentation and data lineage automatically, so you always know where a number came from and what logic produced it. That’s invaluable when a stakeholder questions a figure in a report.

Visualization: Your Tool, Your Choice

Here’s where this approach really differentiates itself from a Microsoft-first stack. Once your data is modeled in Snowflake, you can point virtually any BI visualization tool at it. Power BI, Tableau, Sigma, Looker — they all connect to Snowflake natively.

That means you can choose based on what your team already knows, what your budget allows, or what your end users actually prefer. And if a vendor raises prices at renewal, you have real leverage — your data and your models stay put. You’re just swapping the front end.

This is a genuinely meaningful benefit. Vendor lock-in at the visualization layer is uncomfortable but manageable. Vendor lock-in at the data layer is a much bigger problem. This architecture keeps your options open where it matters most.

The AI Advantage: Chatbots and Agents on Clean Data

One of the most underappreciated benefits of this approach is what it does for AI and chatbot integrations. When you’re building AI tools that answer questions about your business — revenue trends, inventory levels, customer churn — the quality of your underlying data is everything. Garbage in, garbage out.

Because dbt has enforced structure, naming conventions, and business logic across your gold layer, you can point AI agents at that data with genuine confidence. You know what the numbers mean. You know how they were calculated. And when the AI surfaces an insight, it’s grounded in data that’s been deliberately modeled for accuracy.

Snowflake itself has powerful AI capabilities through Cortex, but you’re not required to use them exclusively. You can integrate Claude, OpenAI, Microsoft Copilot, or any other AI platform — all pointing at the same well-structured data. That flexibility is increasingly valuable as the AI tooling landscape continues to evolve rapidly.

The Benefits

Less vendor lock-in: If a vendor raises prices, you can swap them out. Your data model is your asset, not the tool.
Deep talent pool: SQL and dbt skills are widely available. You’re not hunting for niche specialists.
Snowflake is approachable: Despite its enterprise pedigree, Snowflake is relatively easy to configure and manage, especially with tools like Cortex.
AI flexibility: Bring your own agents and chatbots — Claude, OpenAI, Copilot, and others all work alongside Snowflake’s native AI tools.
Modular and future-proof: As better tools emerge, you can adopt them without rebuilding your foundation.

The Drawbacks

More fragmentation: You’re managing relationships with multiple vendors — Snowflake, Fivetran or CData, dbt, and your visualization tool. That’s more contracts, more support relationships, more moving parts.
Integration overhead: Tools in a multi-vendor stack don’t always play as seamlessly together as a tightly integrated suite. You’ll spend more time on plumbing.
Requires more technical ownership: This approach rewards teams with solid data engineering capability. If you don’t have that in-house, the Microsoft approach may be more practical to start.

Wrapping Up

The Microsoft-first approach I outlined in my first post is a perfectly valid choice — especially for teams that are already in that ecosystem and want to move fast with minimal friction. But it’s not the only path, and for teams that prioritize flexibility, cost control, and long-term optionality, the Snowflake + dbt + best-of-breed approach deserves serious consideration.

The key insight is this: the value in your BI infrastructure isn’t in the tools — it’s in the data models and the business logic you’ve encoded. An architecture that protects and preserves that value, regardless of which vendors you’re working with, gives you durable competitive advantage.

In a future post, I’ll dig deeper into some of the specific implementation decisions — how to structure your dbt projects, what to look for when evaluating ingestion tools, and how to think about the transition if you’re starting from an existing stack.

#DataInfrastructure #BusinessIntelligence #Snowflake #dbt #DataEngineering #BI