How to Create a Reconciliation Report in Looker

Finding that your sales numbers in Salesforce don't quite match the revenue data in your production database is a frustratingly common problem. Manually exporting CSVs from both systems to find the differences is a time-consuming chore that kills productivity. This article will show you how to build an automated reconciliation report directly in Looker, giving you a powerful way to compare any two data sources and quickly pinpoint discrepancies.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is a Reconciliation Report?

At its core, a reconciliation report is a side-by-side comparison of two different data sets that are supposed to contain the same information. The goal is simple: find out where they don't match. This isn't just about satisfying a curiosity, it's a critical process for maintaining data integrity and building trust in your analytics.

You might use a reconciliation report to compare:

• Salesforce Opportunities vs. Your Application's Orders Table: Check if every "Closed Won" opportunity has a corresponding order record in your database.
• Stripe Transactions vs. QuickBooks Entries: Ensure every payment record from your payment processor made it into your accounting software.
• HubSpot Form Submissions vs. Your CRM's Lead List: Verify that your marketing automation and sales platforms are perfectly in sync.
• Google Analytics Sessions vs. Server-Side Logs: A more technical comparison to understand differences in how traffic is measured.

Building these reports helps you catch sync errors between systems, identify bugs in your data pipelines, and spot human data entry mistakes before they lead to bad business decisions.

The Foundation: Merging Data with a Derived Table

Looker excels at joining related data, but for reconciliation, we need a different approach. We don't want to join records, we want to "stack" them on top of each other to create a single, unified list. The key to this in Looker is using a derived_table in your LookML view.

The concept is straightforward:

1. Write a SQL query to select the primary identifier (like an order_id) and any key metrics (like amount) from your first data source. In this query, we'll also create a new column to label where this data came from, for example, 'salesforce'.
2. Write a second SQL query to select the exact same fields from your second data source and add its corresponding label, like 'production_db'.
3. Use a UNION ALL statement to stack the results of the second query directly below the first one.

This creates a temporary, virtual table inside Looker containing all the records from both systems. From there, we can analyze the combined data to see which records are missing from one source or the other.

Step-by-Step Guide to Building a Reconciliation View in LookML

Let's walk through building a view to reconcile Salesforce opportunities against an internal database of orders. This requires access to edit your project's LookML.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Create a New LookML View File

First, in your Looker project, create a new view file. A good name would be order_reconciliation.view.lkml. This keeps your reconciliation logic neatly organized.

Step 2: Define the Derived Table with UNION ALL

Inside your new file, define your view and its derived table. The SQL inside is the most important part. You need to select the common columns from both tables, give them identical aliases, and add a data_source column to track the origin of each row.

view: order_reconciliation {
  derived_table: {
    sql:
      -- Query 1: Get data from Salesforce
      SELECT
        id              AS order_id,       -- Unique identifier for the transaction
        amount          AS order_amount,   -- The value we want to reconcile
        close_date      AS created_date,   -- The date of the transaction
        'Salesforce'    AS data_source     -- The label for our first source
      FROM salesforce.opportunity
      WHERE is_won = TRUE

      UNION ALL

      -- Query 2: Get data from the production database
      SELECT
        order_key       AS order_id,       -- The same unique identifier
        order_total     AS order_amount,   -- Giving it the same alias: order_amount
        created_at      AS created_date,   -- Giving it the same alias: created_date
        'Production DB' AS data_source     -- The label for our second source
      FROM production.orders
    ,,
  }
  # ... dimensions and measures will go here
}

Notice how id from Salesforce becomes order_id and order_key from the production database also becomes order_id. This consistency is essential for the comparison to work.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 3: Define Dimensions for Analysis

Next, you need to tell Looker about the columns in your new derived table by defining them as dimensions. These are the fields you'll use to build your reports in the Looker Explore UI.

dimension: order_id {
  primary_key: yes
  type: string
  sql: ${TABLE}.order_id ,,
}

dimension: data_source {
  type: string
  sql: ${TABLE}.data_source ,,
}

dimension: order_amount {
  type: number
  value_format_name: usd_0
  sql: ${TABLE}.order_amount ,,
}

dimension_group: created {
  type: time
  timeframes: [raw, date, week, month, year]
  sql: ${TABLE}.created_date ,,
}

Step 4: Create Measures to Count and Compare

Dimensions let us slice and dice the data, but measures let us aggregate and quantify the differences. We’ll need a few key measures:

• A basic count: This will tell us the total record count. When grouped by order_id, it should always be one or higher.
• A filtered count for each source: These allow you to see the count from Salesforce right next to the count from the database.

measure: count {
  type: count
}

measure: count_salesforce {
  type: count
  filters: [data_source: "Salesforce"]
}

measure: count_production {
  type: count
  filters: [data_source: "Production DB"]
}

Now you have everything you need to build your reconciliation reports. Save your LookML and add this view file to a model so it appears in the Looker Explore interface.

Finding Discrepancies with Your Reconciliation Tool

Once the view is added, using the Explore interface to find mismatches becomes straightforward. Here are two simple techniques you can use:

Technique 1: Highlight Total Mismatches

This is a quick high-level check. In your Explore, do the following:

• Pivot on measures: Count Salesforce and Count Production DB
• Select dimensions: Order ID and Created Date

This will give you a side-by-side comparison of totals across data sources. You might see that Salesforce has, say, 374 records while the database shows 372. That's an indication of missing records somewhere.

Technique 2: Identifying Missing Records

Another method is finding orders where data is only present in one of the two sources. Here is how you do that:

• Select the dimensions: Order ID and Data Source
• Select the measure: Count

Run the report. The results will show any order ID that only exists in one data source. This helps you isolate errors to investigate further.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Best Practices for Reconciliation

When building your reconciliation reports, there are some best practices to keep in mind to maximize their accuracy and efficiency:

Choose a Reliable Key

The most important part of any reconciliation is having a reliable, unique identifier that exists in both systems. This could be an order_id, transaction_id, or customer_email. If you don't have one, your entire comparison will be flawed. Spend time ensuring the key you choose is truly consistent across both platforms.

Be Mindful of Time Zones

Data and time stamps can be a common source of mismatches. In any derived table SQL, use functions like CONVERT_TZ to ensure the dates are in the same time zone, or standardize your data collection to operate in the same timezone to avoid discrepancies due to time differences. Be sure to validate this step.

Once you have built a dashboard that you're happy with, save it to a Dashboard and schedule it for delivery. This ensures you don't need to check reports continuously and can promptly address discrepancies as they arise.

Keep It Simple

Don't try to reconcile everything in a single place. Start with a few key items: the unique identifier, order dates, and amounts. Once these match consistently, you can expand your report to include other fields if needed. Start simple to avoid creating overly complex scenarios right out the gate.

Final Thoughts

Creating a reconciliation report in Looker using Derived Tables and SQL with UNION ALL is a powerful technique to ensure trust in your analytics by matching data across sources and pinpointing where discrepancies could be skewing your numbers.

While Looker is powerful for tasks like these, it does require a significant investment in learning LookML, querying SQL, and testing data compilation and analysis. But this will greatly increase the rigor and reliability of your data processes, preventing manual reporting mistakes.