Data Source Mapping: How to Map Fields to Dashboards

Data source mapping is the work of defining how fields in a source system (a database, a spreadsheet, an analytics account, an API) relate to the fields, metrics and filters that a dashboard shows. Do it deliberately and your charts are accurate, explainable and cheap to maintain. Skip it and you get duplicate counts, mismatched totals and customers who stop trusting the numbers.

This guide is written for developers, product managers and SaaS teams who need to put analytics in front of customers or colleagues. It covers what a mapping should record, how to work from the question backwards, how mapping shapes dashboard layout, and how to monitor freshness so that stale data never reaches a screen.

DashrendrBuild Customer-Facing Dashboards FasterDashrendr helps SaaS teams connect data sources and create customizable, branded dashboards without the burden of building a BI platform.Explore Dashrendr

TL;DR

• A data source mapping links each source field to a target field and records the type, the transformation and the grain, so anyone can explain a number without reverse-engineering it.

• Start with the questions the dashboard must answer, then map back to the source. Do not pull every column “just in case”.

• Document the grain (one row per what?) before anything else. A wrong grain causes duplicate metrics that no design polish can fix.

• Treat the mapping as a living document with validation checks and a freshness threshold, because sources drift.

• Mapping decides your dashboard’s information architecture: group metrics by decision, not by source table.

Table of Contents

• What data source mapping actually is

• What a useful mapping records

• Design the target first, then map back

• Get the grain right before anything else

• Mapping by type of source

• How mapping shapes dashboard information architecture

• Data freshness monitoring: the part mapping forgets

• Treat the mapping as living, not finished

• Our perspective: map less, ship sooner

• A step-by-step walkthrough

• Where Dashrendr fits

• Common mistakes to avoid

• FAQ

• Sources

What data source mapping actually is

In plain terms, mapping is a translation table. Data source mapping defines the relationship and transformation rules between fields in a source system and those in a target, and it acts as the blueprint for integration, dashboard creation and analytics pipelines. The same page notes that it involves spotting semantic similarity between data models, cutting redundancy and checking accuracy before data is visualised.

For a dashboard, the “target” is usually not a warehouse table. It is a metric, a dimension or a filter: monthly active users, revenue by plan, tickets by priority. The source might be a PostgreSQL table, a Google Analytics property, a Google Sheet or a REST endpoint. Mapping answers the question: which source fields, combined how, produce this number?

Three things get confused with it, so it helps to separate them:

• Data integration moves the data. Mapping describes how the pieces line up while it moves.

• Data modelling designs the shape of the target. Mapping connects that shape to where the data came from.

• Dashboard design decides how it looks. Mapping decides whether what it shows is right.

What a useful mapping records

A spreadsheet with two columns, “source” and “target”, is not a mapping. It is a wish list. A reasonable minimum, based on interface mapping guidance, covers four things:

1. Identity: which version of the source and target the mapping applies to.

2. Structural path: the specific object, table or endpoint the field comes from.

3. Contract: data type, nullability and precision.

4. Transformation rules: expressions, filters and aggregations applied on the way.

The same guidance stresses documenting transformation logic and flagging personal information, since the mapping becomes the single source of truth for downstream data quality. That second point matters more when the dashboard is customer-facing, because a mislabelled personal field can leak across tenants.

A practitioner write-up on source-to-target mapping adds a point most templates miss: record sample values. Theory dies fast when you see real data. A column called status that you assumed held three values might hold eleven, including blanks and a legacy code from 2019.

Here is a compact template you can copy into a doc or a repo:

Field	What to write	Example
Metric name	The label the viewer sees	Active accounts
Question answered	The decision it supports	Are customers adopting the new feature?
Source	System, table or endpoint	PostgreSQL, events
Source fields	Columns used, with types	account_id (uuid), created_at (timestamptz)
Grain	One row per what	One row per event
Transformation	Filters, joins, aggregation	Count distinct account_id, last 30 days
Sample values	Real rows, including awkward ones	Nulls in account_id for anonymous events
Owner	Who answers questions about it	Product analytics lead
Freshness target	Maximum acceptable age	60 minutes

Design the target first, then map back

The most common mistake is starting from the source and exposing everything. Practitioners recommend the opposite: define the analytical grain and KPIs first, then map backwards. This avoids the “data dump” and keeps unneeded source fields out of the architecture.

A practical order of work:

1. Write down five to ten questions the dashboard must answer. “How many seats are in use?” is a question. “Show usage stats” is not.

2. For each question, name the metric and the dimensions it needs (by plan, by week, by customer).

3. Find the minimum source fields that produce them.

4. Map only those fields. Everything else stays unmapped, on purpose.

This keeps your queries lighter and your documentation shorter. It also gives you a clear answer when a stakeholder asks for “just one more column”: does it serve a listed question, or is it a new question?

Get the grain right before anything else

Grain is the level of detail of a row: one row per transaction, one row per user per day, one row per account per month. Documenting the grain is the most critical and most overlooked step, and a mismatch leads to duplicate metrics and broken dashboards that visual polish cannot repair.

A worked example. Suppose you want revenue per customer from two sources: an orders table (one row per order) and a customers table (one row per customer). Join them and sum order value by customer and you are fine. Now add a support_tickets table at one row per ticket and join it to the same result before summing. A customer with three orders and four tickets now yields twelve rows, and revenue is counted four times over. The fix is not a prettier chart. It is aggregating each source to the customer grain first, then joining.

A habit worth adopting: every mapping line states its grain in a single sentence, and every join states how the grains reconcile. If you cannot write that sentence, the metric is not ready.

Mapping by type of source

Different sources fail in different ways. Dashrendr connects to Google Analytics, BigQuery, PostgreSQL, MySQL, MongoDB, Google Sheets and REST APIs, and each type deserves a slightly different mapping habit.

Source type	Typical grain	Common mapping trap	What to record
SQL databases (PostgreSQL, MySQL)	Row per entity or event	Joins that multiply rows; soft-deleted records	Keys, join logic, filters for deleted rows
Warehouses (BigQuery)	Often pre-aggregated	Partitioned tables, sampled or late-arriving data	Partition field, refresh schedule
Analytics platforms (Google Analytics)	Aggregated by dimension and date	Metric and dimension combinations that do not mix; reporting lag	Exact metric and dimension names, date range logic
Document stores (MongoDB)	Nested documents	Inconsistent fields across documents	Optional fields, array handling, sample documents
Spreadsheets (Google Sheets)	Whatever the author chose	Manual edits, renamed columns, mixed types	Named ranges, header row, owner of the sheet
REST APIs	Per resource, often paginated	Rate limits, pagination, changing response shape	Endpoint, auth, response path, pagination rule

Spreadsheets and APIs deserve extra suspicion. Both can change shape without anyone telling you, which is why the contract line (type, nullability) and a named owner are worth the effort.

If your data lives in several operational databases and you are wondering whether to consolidate first, our short explainer on what a data warehouse is and when you need one covers the trade-off.

How mapping shapes dashboard information architecture

Mapping and layout are the same decision viewed from two sides. Practitioners of dashboard design make the point that visual design is secondary to information architecture: a dashboard with the right metrics but poor organisation fails, while a plain one that presents correct, fresh metrics succeeds.

What that means in practice:

• Group by decision, not by source. A customer does not care that revenue comes from your billing database and usage from your event table. Put “Am I getting value?” metrics together even if they come from three sources.

• Lead with the headline metric. The top row holds the numbers that answer the primary question. Detail and breakdowns sit below.

• Keep one definition per metric. If “active user” appears twice with different logic, one of them is wrong. Your mapping document is where the single definition lives.

• Show filters that match mapped dimensions. Offering a filter that only half your sources support produces confusing partial results.

• Label units and time ranges. A number without its period and grain is an argument waiting to happen.

A tidy way to plan this is a three-level outline: pages (one per audience question), sections (one per decision) and widgets (one per mapped metric). If a widget has no row in your mapping template, it should not be on the page. For layout tactics beyond the data, see our guide to dashboard design best practices for SaaS, and for the basics of what makes a dashboard, our overview of data dashboards.

Data freshness monitoring: the part mapping forgets

A correct mapping of stale data is still wrong on screen. Freshness monitoring measures the time since the last successful data delivery against a defined service level objective, and alerts you when data exceeds its expected update cadence. The point is to catch silent failures, where a pipeline reports success but delivers nothing new. The same documentation gives an example: a violation when data age passes 60 minutes for an hourly pipeline.

For a dashboard team, a workable approach looks like this:

1. Set a freshness target per source, not per dashboard. Your billing table might need hourly data. A Google Sheet updated by hand weekly might need seven days of tolerance.

2. Find a trustworthy timestamp. A column such as updated_at or ingested_at is best. Be wary of timestamps that record when a row was created but never change on update.

3. Compare against the cadence, with a small buffer. If you load hourly, alert at 90 minutes, not 61.

4. Decide what the viewer sees. Options include a “last updated” label on the dashboard, a warning banner, or hiding the widget. Customers forgive a delay they can see. They do not forgive a wrong number presented as current.

5. Route alerts to the mapping owner. Your template already names one.

Real-time auto-refresh on the dashboard side helps only when the source is itself current. Refreshing a stale table every ten seconds just displays the same old data faster.

Treat the mapping as living, not finished

Mapping is not a one-time task. Teams that cope well design for drift by building validation into the mapping itself: reconciliations, key uniqueness checks and aggregation tolerance tests, so errors are caught before they reach a customer-facing dashboard. Another point from that guide is that a mapping has to reflect reality, not just original intent.

Three checks cover most failures:

• Row count reconciliation: the number of source rows in scope matches the number feeding the metric, within a stated tolerance.

• Key uniqueness: the field you treat as the identifier really is unique at the declared grain.

• Total comparison: a headline metric matches an independent figure, such as an invoice total or a finance report, within an agreed margin.

Add a review trigger as well: whenever the source schema changes, the mapping owner reviews the affected lines. A short changelog at the bottom of the mapping document costs almost nothing and saves hours of detective work.

Our perspective: map less, ship sooner

We build a visual dashboard builder for SaaS teams, so we see the same pattern repeatedly. Teams spend weeks building a general-purpose data layer before a single customer sees a chart. The ones who move quickly do the reverse: they pick the handful of questions their customers ask most, map exactly those fields, and put a branded dashboard in front of users to learn what is missing.

Our view is that a mapping should be small enough to hold in your head and honest enough to survive a customer question. If you can explain every number on the dashboard in one sentence, including where it came from and how fresh it is, you are in good shape. If you cannot, adding more widgets will not help.

We also think the tooling should not force you to become a BI vendor to get there. Connect the source, map the fields visually, check the numbers against a figure you trust, and embed. Everything else can wait for the second iteration.

— Paloma

A step-by-step walkthrough

Here is the whole process on a realistic case: a project management SaaS wants a customer-facing usage dashboard.

1. List the questions. How many tasks were completed this month? Which team members are active? Is usage rising or falling?

2. Name metrics and grain. Tasks completed (one row per task), active members (one row per member per day), trend (weekly aggregate).

3. Identify sources. Tasks and members live in PostgreSQL. Marketing site traffic is in Google Analytics but is irrelevant here, so it stays unmapped.

4. Fill in the template for each metric, including sample values. Note that archived tasks have a deleted_at value and must be filtered out.

5. Aggregate before you join. Reduce tasks to member-per-day counts first, then join to the members table.

6. Set freshness. Hourly target; alert at 90 minutes; “last updated” label on the dashboard.

7. Validate. Compare the monthly completed-task total with a direct count in the database. Investigate any gap.

8. Build and embed. Lay out one page of three sections: headline numbers, member activity, trend. Apply your branding and embed it.

9. Review after two weeks. Check which questions customers actually asked, and update the mapping.

The same flow applies to internal reporting, such as weekly KPI snapshots sent by email, where the freshness target is simply the delivery schedule. For ideas on other setups, browse our use cases, including internal admin dashboards.

Where Dashrendr fits

Once the mapping is on paper, the build should take hours, not weeks. Dashrendr lets you connect your sources, build dashboards with a drag-and-drop builder and embed them in your app as embedded analytics, with multi-tenant security, brand customisation and scheduled email delivery. Plans on our public pricing page start at $6 per month (Hobby), then $15 (Starter), $39 (Grow) and $99 (Premium), all billed monthly. Extra team members cost $3 per month each. A free trial comes first, so you can test your mapping against real data before paying.

Common mistakes to avoid

• Mapping everything. Unused fields add cost and confusion.

• Skipping sample data. Real values expose surprises before customers do.

• Joining before aggregating. This is the usual source of double-counted metrics.

• Keeping the mapping in a stale spreadsheet. Specs drift from the real logic. Keep the document next to the code or the dashboard configuration, and review it when either changes.

• No owner. An unowned mapping is a mapping nobody updates.

• Ignoring freshness. Correct logic on old data is still a wrong answer.

FAQ

What is the difference between data mapping and data source mapping?

In everyday use, the terms overlap. Data mapping is the general practice of matching fields between two data models. Data source mapping usually stresses the starting point: which system each field comes from and how it is transformed on the way to a report or dashboard.

Do I need a data warehouse before I map sources to a dashboard?

Not always. Small and mid-sized SaaS products often build dashboards directly on a production database replica, an analytics account or an API. A warehouse becomes worthwhile when you need to combine many sources or run heavy queries without slowing your app.

How often should I review a mapping?

Review it whenever a source schema changes, a metric definition changes, or a number looks wrong. Beyond that, a scheduled check every quarter is a sensible habit. Automated reconciliation checks catch most problems between reviews.

How do I know if my dashboard data is stale?

Compare the age of the latest record against the cadence you expect. If an hourly load has delivered nothing new for well over an hour, treat it as a freshness failure. Show a “last updated” time on the dashboard so viewers can judge for themselves.

Sources

• Enterprise Big Data Mapping: Definition, Tools, Techniques, BMC

• Introduction, Interface Mapping, HCL Software

• How To: Source-to-Target Mapping, Good Strategy Rebellion

• Source-to-Target Mapping: Data Quality Examples and Best Practices, Data Ladder

• Freshness monitoring, AnomalyArmor docs

• Information architecture for dashboards, Vizro

• Data mapping, Wikipedia

Recommended

• Data Dashboard: Definition & Overview

• Dashboard Design Best Practices for SaaS

• Data Aggregation for SaaS: A Developer’s Guide