4 Ways to “Love Your End Users” with Matillion Data Productivity Cloud

When building a scalable data architecture, it’s easy to focus on the shiny, cutting-edge tools and techniques. Machine learning pipelines, AI-driven insights, and complex analytics solutions all grab the spotlight. But here’s the catch: none of those deliver value if the foundational data isn’t clear, accessible, and easy to work with.

Organizations often struggle not because they lack intelligence or drive, but because they overlook the blocking and tackling that makes day-to-day data-driven decision-making possible. The smartest data teams can still fall short if their efforts don’t translate into actionable, user-friendly datasets.

In this first installment of our series on scalable data architecture with Matillion Data Productivity Cloud, David Baldwin, Founder of GiddyUp Data will take us through four practical ways to create a data architecture that serves analysts effectively. These techniques may seem basic, but they are the bread-and-butter of empowering teams with data. Let’s not dismiss the basics—mastering them can make all the difference. Prefer to watch instead? Check out this video!

1. Aggregate at Multiple Levels

Let’s consider U.S. domestic flight data from 2020 to August 2024. At the flight level, this weighs in at 28 million rows. Even though such a dataset is rather modestly sized, it’s big enough to be unwieldy for reporting tools. The solution? Create aggregated tables at varying levels of granularity. It’s not just a performance booster—it’s an act of empathy for your analysts!

For example:

1. High-level aggregation: Summarize data at the state level for quick, overarching insights.
2. Mid-level aggregation: Dive deeper into details, such as airport codes.
3. Row-level granularity: Retain tables with individual flight details for the most granular reporting needs.

This isn’t just about better performance; it’s about enabling seamless storytelling. Analysts can start with a high-level visualization, drill down to airport-level details, and ultimately zoom in on individual flights—all without waiting for their tools to churn through millions of rows.

Moreover, modern reporting tools allow you to connect multiple data sources at different levels of aggregation within a single dashboard. In this scenario, state, airport, and flight-level tables work together to create dynamic, fast-performing reports. This design anticipates user needs, making data exploration intuitive and efficient.

2. Use Filtering to Enhance Focus

Data professionals often underestimate how overwhelming unfiltered datasets can be for end users. Enter the Filter component in Matillion – a simple yet powerful tool for refining datasets into manageable, actionable pieces.

Follow these steps to create a refined dataset that highlights the top three airports in each state by popularity:

1. Window Calculation Component:
   Calculate the total number of flights for each destination airport. This step ensures that flight counts are aggregated for meaningful ranking.
2. Rank Component:
   Rank the airports within each state based on their total flight count, assigning a numeric rank to each airport.
3. Filter Component:
   Use a filter to isolate only the top three airports in each state by their rank.

This approach retains row-level detail while narrowing the dataset to focus on the most relevant information.

The result? Small, focused tables that highlight the most popular airports or specific types of flights. These datasets are easier to analyze and deliver insights faster. This isn’t just about filtering; it’s about curating the data experience for end users, and empowering them with clean, actionable information.

3. Reduce Columns for Simplicity

Wide, feature-rich datasets are valuable, but they can overwhelm users—especially those who aren’t full-time data professionals. Simplifying tables by reducing the number of columns for popular reporting scenarios can have a transformative impact on usability.

Imagine a non-technical business leader who needs to quickly analyze trends. If the data table is intuitive and contains only the relevant fields, they’re far more likely to engage with it confidently. This doesn’t mean sacrificing the richness of your datasets; instead, complement them with curated, user-friendly views designed for common questions or quick decision-making.

Matillion's Data Productivity Cloud makes this process simple and effective with the Rename component. This versatile tool enables you to rename, remove, and reorder columns in your tables. By using the Rename component, you can streamline datasets for specific reporting purposes, ensuring that only the most meaningful fields are presented to end users.

In organizations striving to be data-driven, enabling every user—not just analysts—to perform “quick and dirty” research is crucial. By providing clear, well-structured tables, you empower all stakeholders to act on data, not get lost in it

4. Apply Basic SQL for Change Detection

One of the most common questions analysts ask is, “What has changed?” While enterprise-grade solutions like Change Data Capture (CDC) handle complex scenarios, a simple SQL approach can often deliver quick, actionable insights.

Using an SQL component in a transformation pipeline, you can write queries to flag updates or discrepancies in your datasets. For example, the following query identifies records that exist in both tables but differ in specific columns:

This approach can also be extended to create views identifying new and deleted records, enabling analysts to quickly zero in on points of interest. While Matillion DPC supports CDC for more complex scenarios, simple techniques like this empower analysts to gain insights quickly and stay agile.

Why Blocking and Tackling Matter

These four techniques—aggregation, filtering, column reduction, and change detection—might seem elementary. But in reality, they are often what separates a usable data architecture from one that gathers dust.

Too often, organizations assume that making data available is enough. They forget to educate users on where those data sources live, how to use them, and why they matter. Worse still, they might prioritize complex, high-visibility projects over the foundational work that ensures analysts can actually deliver insights.

The truth is, flashy projects don’t drive value if the basics aren’t done right. Blocking and tackling—the aggregation, filtering, simplifying, and flagging of data—lay the groundwork for everything else. And when you take time to get the fundamentals right, you make it easier for everyone in the organization to make better, faster decisions.

Wrapping Up

The basics aren’t boring—they’re indispensable. By focusing on simple, scalable techniques like these, you’ll build a data architecture that empowers your analysts, supports decision-making, and drives organizational success.

In the next post, we’ll explore how to lean on your cloud data warehouse, such as Snowflake, to further enhance scalability and efficiency with Matillion DPC. Stay tuned!