As part of a long-standing client engagement, we were tasked with expanding their analytics platform to include an agentic AI chatbot. The goal was to let business users ask questions about sales in plain language and get answers consistent with the reports they already relied on.
After an architectural review, we chose Snowflake as the AI platform for the chatbot. The client’s ingestion and transformation pipelines already ran in Snowflake, which made it the practical choice. The chatbot would use a semantic view, a native Snowflake object that defines business metrics and the dimensions and table relationships used to analyze them. It can also contain verified queries: reviewed SQL associated with business questions that helps guide an agent’s answers. The central challenge was that the business measure definitions were implemented as DAX measures in Power BI, while the agent needed SQL equivalents in Snowflake.
Net Sales showed where translating DAX to SQL became difficult. The definition included discounts, returns and reward adjustments. Those adjustments translated readily. Growth comparisons did not: they followed the retailer’s trading calendar, which groups complete Sunday-through-Saturday weeks into months, quarters and years. A valid year-over-year comparison needed the corresponding trading weeks from the prior year and the same number of complete weeks.
In early testing, the agent understood that trade dates mattered, but its attempts to shift dates back a calendar year produced the wrong window. It recognized the mismatch and tried other approaches, sometimes repeating the error before finding the right dates. Across those early tests, the agent took as long as two and a half minutes and five attempts to reach the correct comparison.
We couldn’t expect business users to inspect the SQL or work through several attempts to get the correct comparison. We needed the answer to follow the organization’s trading calendar consistently.
Recovering the logic from an imported Power BI model
We built the client’s data platform in Snowflake using dbt, but the reporting definitions lived in Power BI as DAX measures. An early idea was to inspect the queries Power BI sent to Snowflake. The Power BI model used Import mode for performance, so those queries showed how Power BI loaded data during scheduled refreshes. Power BI evaluated the DAX measures against the imported model afterward, and Snowflake did not receive all the business calculations we needed to recover. Microsoft describes this distinction in its overview of semantic-model modes.
That left us with a practical question: how could we reproduce the DAX results in SQL? We needed to work through the Power BI project itself, including the Power Query transformations that prepared the imported data, the DAX expressions and the model context that affected their results. The DAX measures carried reporting decisions accumulated over years of business use. They gave us something more useful than a fresh guess at what a sales metric ought to mean.
Tooling note, September 2026: Snowflake made Power BI ingestion through Semantic View Autopilot generally available on August 18, 2026. It can import supported measures and relationships into semantic views, though support for report-level measures and time-intelligence functions remains incomplete.
Using dependencies and report activity to prioritize the work
We started by extracting the DAX measure definitions and organizing their dependencies into a dictionary, a process we call measure harvesting. That let us trace each headline metric back to the calculations producing the number. We used DAX Studio to inspect and query the model as we followed those dependencies.
Following the references changed how we ordered the work. A short measure could combine calculations from different source systems and fact tables at different levels of detail. The dependency map showed us which underlying calculations had to be in place before we could reproduce the headline measure.
Dependencies told us how the measures fit together, but not which ones mattered most to users. Usage reports in the Power BI service showed which reports people opened, while Measure Killer identified which reports referenced each KPI measure. Together, those signals helped us prioritize the logic behind active reports and the measures shared across them.
We still needed stakeholder judgment. A report opened only at quarter-end could be critical despite modest usage. The activity data gave us a starting point for that conversation instead of asking stakeholders to rank an undifferentiated list of measures.
As we recovered the definitions, the dependency dictionary became the foundation for a long-overdue KPI catalog. We connected the metric names people used in reports to the calculations behind them and recorded ownership, approval status and intended uses. The catalog gave the next team a place to find the approved version of a metric.
Translating and testing the shared definition
Once we understood the dependencies, we translated Net Sales into SQL. The new calculation had to preserve the business adjustments, filters and trade-date logic embedded in the DAX measure.
We also tried a DirectQuery version of the Power BI model. Some of the more complex DAX calculations, including the trade-date logic, did not work in that version. Time-intelligence calculations can be finicky to translate between DAX and SQL because their results depend on the calendar and filter context. The queries Power BI sent to Snowflake therefore did not give us a complete representation of the metric.
We translated the behavior into SQL and made the assumptions explicit. We then compared the results with Power BI under the conditions we had defined. Once the results agreed, we added the validated SQL to the semantic view as verified queries and kept the comparisons repeatable. That allowed us to check later changes without starting the investigation over.
After further tuning of the agent’s instructions, the sales comparisons in our tests used the correct trade-date window on the first attempt. The agent could apply a reviewed query pattern instead of reconstructing the calculation for each question.
Those verified queries became the starting point for the verified-query flywheel. When the agent needed to generate SQL beyond those reviewed patterns, the interface identified the answer as unverified. We treated those questions as candidates for business review, helping us identify which definitions the semantic view needed next.
Carrying approved definitions into the semantic view
Matching the Power BI results did not make every recovered measure ready for the agent. Some definitions already had business approval and supported reports people relied on. For those measures, our task was to preserve the approved rule in Snowflake and document the conditions under which the SQL and DAX results agreed.
The KPI catalog gave us a practical reference when configuring the semantic view. It connected each metric name with its approved business definition, owner, intended uses and validated SQL implementation. When similar names or variations existed, we used that context to select the version intended for the user’s question.
We treated recovery, translation and approval as separate states. Reproducing an inherited DAX measure in SQL established technical parity. It did not resolve an undocumented workaround or a conflict with another definition. We flagged those cases for business review before making them available as governed definitions.
When an approved rule changed, we recorded the revised definition and its effective date in the catalog. We then updated the implementation and repeatable comparisons. This kept the business decision separate from the technical translation. Our validation approach assigns responsibility for those decisions alongside the technical checks.
Keeping implementations aligned across systems
Recovering Net Sales required us to trace a reported number back through its dependencies. Documenting the definition and repeatable comparisons gave later consumers a starting point without requiring them to repeat the same reverse-engineering work. The Power BI model remained available when a deeper investigation was needed.
We did not assume every definition belonged in Snowflake. Power BI can itself serve as a governed shared model, including for Excel through supported connections. In this engagement, the AI interface needed the definitions in Snowflake. The semantic view therefore became another implementation of the same approved rule.
The same maintenance problem appears when business logic begins in another BI tool, a notebook or a spreadsheet. The next consumer still needs an approved definition and a tested implementation.
The KPI catalog became our starting point for updates to shared measures. We connected each approved definition to its implementations in Power BI, SQL and the Snowflake semantic view, then recorded who depended on it. That inventory showed us which implementations and comparison tests a change affected.
When someone changed a measure directly in one system, we brought the change back to the catalog, reviewed it against the approved definition and reconciled the affected implementations. This kept a system-specific edit from silently redefining the metric.
The Snowflake query path also required its own access review. Power BI’s security behavior did not transfer with the reporting logic, so we treated tests and access controls as part of the new implementation.
Governing business definitions across reporting and AI
Adding the AI chatbot extended the business definitions beyond the reports where they had been established. Net Sales needed to mean the same thing whether a user viewed a Power BI report or asked the chatbot. The KPI catalog and repeatable comparisons gave us a way to keep those paths aligned.
We treat business-definition governance as part of data modeling and semantic-view development. Each implementation needs an approved definition to follow, an owner to decide changes and a repeatable way to verify the results. Capturing the reasoning behind a measure makes that agreement maintainable as the platform grows.
In this engagement, the catalog shows us where a definition is implemented, while the comparisons show whether the results still agree. When a business rule changes, the inventory identifies the affected implementations and tests. When an agent’s answer falls short, the documented rule and tested calculation give us a specific starting point for the investigation.
If a new analytics initiative is exposing similar gaps in your existing reports, we can discuss the definitions and validation work involved.