A Data Model an AI Can Actually Understand
How I use Dataform and GitHub to maintain metadata, trace calculations, and give agents access to the data modelSQLX and policy in GitHub support metadata and lineage that people and agents can query. Image created by the author with AI assistance.Based on my own thoughts, ideas and experience,…
How I use Dataform and GitHub to maintain metadata, trace calculations, and give agents access to the data modelSQLX and policy in GitHub support metadata and lineage that people and agents can query. Image created by the author with AI assistance.Based on my own thoughts, ideas and experience, with AI assistance for editing and illustrative code.We moved our BigQuery data model into Dataform because the old model had become a nightmare. Keeping the SQLX in GitHub didn’t just give us control and visibility; it also gave us a way to generate metadata and lineage from the code.Much of the transformation logic had lived directly in BigQuery. Views depended on views, sometimes many layers deep. Business rules appeared in dashboard calculations or spreadsheets. Someone could usually find the SQL. Finding why a calculation existed, where its inputs came from, or which reports used it took considerably more work.Dataform gave each developer a branch to work in and a repeatable way to compile and run the model. Pull requests made changes visible before release. We could see who changed a business rule, compare versions, and restore an earlier code version when needed.The code, development policies, and automation now live in GitHub. Generated table and column descriptions live in independent metadata tables, alongside separate tables for lineage. An AI application can query those records directly.Two ways to develop the same SQLXA developer can write SQLX in the Dataform web environment in Google Cloud, with access to the editor, compiled SQL, dependency graph, and execution controls. Dataform development workspacesThe same repository can be cloned into an IDE such as Visual Studio Code or PyCharm. The open-source Dataform CLI supports local compilation, testing, and execution against BigQuery. Dataform CLII use both. The browser is convenient for inspecting compiled SQL or running a contained workflow. A local IDE suits changes across several files, normal Git work, and development with a coding agent such as Codex.Shared JavaScript functions and constants sit in the repository too. A change to a common function gets the same version history and review as a change to a calculation. Dataform’s includes support makes that code available across the project. JavaScript in DataformBoth development environments produce changes to the same repository. Our GitHub Actions run the compliance checks and call the services that maintain metadata and lineage.SQLX makes the transformation inspectableConsider a fictional multinational sales company operating across the United States, Canada, and Mexico. It receives orders from regional ERP systems and distributor CSV files, with some reference information maintained in Google Sheets. Sales values are converted to US dollars for reporting in Tableau.A reporting action could look like this. Each row represents an order from a particular source system on a reporting date:config { type: "table", schema: "reporting", name: "north_america_sales", tags: ["north_america_sales", "daily"], dependOnDependencyAssertions: true, assertions: { uniqueKey: [ "source_system", "sales_order_id", "reporting_date" ], nonNull: [ "source_system", "sales_order_id", "reporting_date", "country_code", "net_sales_usd" ], rowConditions: [ "country_code IN ('US', 'CA', 'MX')" ] }, bigquery: { partitionBy: "reporting_date", clusterBy: ["country_code", "sales_region"], labels: { pipeline: "north_america_sales", classification: "internal", data_owner: "sales_operations", data_steward: "sales_data_management" } }}SELECT sales.source_system, sales.sales_order_id, sales.country_code, sales.sales_region, sales.reporting_date, sales.net_sales_usdFROM ${ref("mart_north_america_sales")} AS salesThe net-sales calculation belongs in mart_north_america_sales. The reporting action selects the finished measure. Including source_system in the key allows different regional systems to use the same order number.The config identifies the pipeline, owner, steward, classification, and quality checks. Explicit columns and qualified references make it possible to follow each field through the SQL.Dataform compiles SQLX and its JavaScript into SQL actions and a dependency graph. Our metadata service uses that output to inspect the queries that BigQuery will run. Dataform compilation and executionThe generated descriptions are stored separately. Nucleus and other applications can read or update them without editing a SQLX file.The layers and pipelinesI use five main layers:Source declares the existing data sources the model reads.Staging standardises names and types, applies initial classification and checks incoming data.Transformation reshapes source data, applies mappings and prepares structures within each pipeline.Data mart contains facts, dimensions and business calculations.Reporting joins the required data into tables for dashboards, spreadsheets and applications.The distinction between transformation and business logic is deliberate. Standardising a regional product code belongs in transformation. Defining how discounts and returns affect net sales belongs in the data mart.We previously had dependencies crossing between pipelines throughout the model. Running one workflow could rebuild parts of several others. That made processing costs difficult to control and left developers unsure which tables a run would affect.We separated the pipelines so cross-pipeline joins happen in reporting. Sales, customer, and finance pipelines can each prepare their own data and run on their own schedule. A reporting action combines their completed outputs.A source can supply more than one pipeline. For the sales example, finance may publish an exchange-rate feed. The sales pipeline reads that declared source through its own staging and transformation actions, without depending on the finance pipeline’s intermediate transformations.Dataform tags and execution selection let us choose the actions and dependencies for a run. Separating the reporting workflow lets us refresh a pipeline without automatically rebuilding every report that uses it. Dataform workflow executionWithin the data mart, I favour clearly defined facts and dimensions. Reporting tables are generally wider, joining those structures for reuse. Several sales dashboards can select different columns and filters from one reporting table instead of maintaining slightly different versions of the same joins.Business calculations stay in the data mart. Tableau and Google Sheets read the resulting measures. If the business changes the definition of net sales, a developer changes one SQLX action. The reports pick up the result through their normal refreshes.My early rule was NO VIEWS!Working with a previous team, I inherited a data model built directly in SQL. It had grown organically, let’s say. I found a tangled web of views, with some sitting on top of other views up to 20 layers deep. To understand one reporting field, a developer had to open another view, then another, until they reached the source.That scared me. It was one of the reasons I went looking for a better way to manage the model, which led me to Dataform. It also caused my first overcorrection: NO VIEWS!Sanity now prevails. My reporting layer is mostly tables because dashboards and applications query it directly. The scheduled workflow builds the reporting results before live queries or extract refreshes read them. A Tableau dashboard using an extract reads that stored extract; its refresh queries BigQuery. Tableau connections and refreshesI assess everything underneath individually, although most transformation and data-mart actions are views. They are internal calculation steps. Dataform compiles their definitions, and BigQuery evaluates them as the reporting tables are built. Analysts and reporting tools use the reporting layer. BigQuery logical viewsMaterialising every intermediate step would create copies of data we rarely query directly. Staging is more dependent on the source and the cost of reading it. I materialise an intermediate result where repeated use or processing time makes that worthwhile.History is always stored in tables or incremental tables. The SQL defines which business changes create a new version and how earlier versions are retained. An ingestion timestamp changing each day does not, by itself, mean the business record has changed.The cost savings come from avoiding unnecessary rebuilds and repeated calculations, as well as limiting the data scanned. Partitioning and clustering help when the queries can use those columns to prune the scan. Partitioned tables, clustered tablesProtecting the reporting dataAn empty source file used to be able to work its way through the model and leave an empty reporting table. By the time someone noticed, the report was already affected.We use pre-operations mainly in staging to check incoming data before rebuilding an action. These checks include expected row counts, file dates, and changes in volume. An unexpected drop in a distributor’s delivery can stop processing while a data engineer checks whether the file is complete.For example, a staging action could check for rows from the batch it is processing. The run supplies batch_date, so the same check works for a delayed delivery or a rerun:pre_operations { ASSERT ( SELECT COUNT(*) > 0 FROM ${ref("raw_canada_distributor_sales")} AS source WHERE source.reporting_date = DATE("${dataform.projectConfig.vars.batch_date}") ) AS "No Canadian distributor rows for the expected batch";}Dataform executes pre_operations before creating or updating the action. BigQuery's ASSERT stops the script when the condition fails. Dataform pre-operations, BigQuery ASSERTAssertions check the data produced by an action. In the sales mart, that includes duplicate orders, missing identifiers, and missing currency conversions. A left join with no matching exchange rate produces a null sales value, which the mart’s non-null assertion catches. The checks allow valid negative sales values from refunds and adjustments. Dataform assertionsTo protect the reporting build, its upstream quality assertions are included in the run and configured as dependencies. The example uses dependOnDependencyAssertions: true to add the direct assertions of its mart dependency. A failed mart assertion prevents that reporting action from replacing its existing table. The reporting action's own assertions check the result after it is built. Assertion dependenciesThe sales manager can keep using the last successful reporting result while the data engineer investigates the failed input.Policy runs with the changeOur development policy started as a ways-of-working document. It described what belonged in each layer and how developers were expected to write the SQL. We converted those rules into a machine-readable policy stored with the model.The rules include:Use explicit columns instead of SELECT *.Give tables aliases and qualify column references.Record the pipeline, source system, owner, steward and classification.Keep business calculations in the data mart.Keep cross-pipeline joins in reporting.Apply the required access controls to classified data.A developer or coding agent can use that policy while changing SQLX. The pull-request action checks the change and reports violations in the PR. Required status checks prevent a merge while compliance issues remain. GitHub protected branchesChecks for missing aliases or SELECT * can run directly against the code. AI helps with rules that depend on meaning, including whether combining fields changes their classification. We assess classification in staging and again in reporting, where combinations can reveal information that individual source columns did not.We also run the policy against the existing model. Nucleus displays the outstanding violations so a data engineer can find the affected SQLX and correct it.Layering helps us control access. An application’s service account gets access to the reporting tables it needs. Report consumers do not need access to staging or transformation. Dataform can apply BigQuery policy tags to columns for column-level access control. Dataform policy tagsSharing sits beyond that reporting boundary. A customer receiving a spreadsheet does not need a direct connection to the warehouse.Generating metadata from the changeWe initially tried maintaining descriptions in SQLX. Shared constants reduced some repetition, particularly for columns appearing in several tables. Keeping thousands of descriptions accurate was still too much manual work.We moved the generated descriptions into independent metadata tables and automated the refresh through GitHub.The web service gathers the change context. A dedicated agent generates metadata and lineage records for Nucleus and other applications. Image created by the author with AI assistance.For a change merged into master:A GitHub Action identifies the affected assets and calls the metadata web service.The service gathers compiled SQL, dependencies, warehouse schemas and existing metadata, including known links to external sources and reports.A dedicated metadata agent uses that context to generate table and column descriptions, classification assessments, calculation explanations and lineage records.The service writes the results to the independent metadata and lineage tables.A column record for the sales example might contain:Table: reporting.north_america_salesColumn: net_sales_usdDescription: Net sales after discounts and returns, converted to US dollars using the rate for the reporting date.Classification: InternalOwner: Sales OperationsSteward: Sales Data ManagementSource systems: Regional ERP systems and distributor feedsRelated lineage records identify the source columns and the calculations applied to them.These tables provide the business context an agent needs: definitions, ownership, classification, and the meaning of each measure. They form a queryable semantic layer alongside the SQLX that implements the calculations.I would also retain the originating commit and metadata-generation time, with the last successful production build recorded separately. That gives an application enough information to identify the code being described and the data currently available.Business SMEs use Nucleus to check the metadataWe built a free application called Nucleus so business SMEs can use the metadata without opening BigQuery, GCP or a SQLX file.A sales operations SME can search for a table or column using familiar terms, read its description and correct the wording. For example, an agent may describe sales_region as the region where the order was placed. The SME may know it means the region credited with the sale, which can differ from the delivery location.Nucleus writes the correction to the metadata table. Other applications and agents using that table can read the corrected business meaning.For regeneration, I would keep the SME’s wording separately from the generated description and include it in the agent’s context. The application can use the corrected wording while retaining the generated explanation for comparison.Nucleus also displays lineage as a navigable graph. A sales manager can start with a Tableau field and follow its inputs through the reporting table, data mart and earlier transformations to the original source. The calculations are available without the manager needing to read SQL.Lineage from source to reportGoogle Cloud provides table and column lineage through Knowledge Catalog, formerly Dataplex Universal Catalog. It also accepts custom lineage through the Data Lineage API and OpenLineage. Google Cloud lineageFor our use, the additional service was more than I wanted to operate and pay for. Automatic lineage parsing is billed through the premium processing SKU, with metadata storage also part of the pricing model. I preferred to keep the lineage records in our own tables and extend the integrations we already used. Knowledge Catalog pricingThe compiled Dataform graph identifies action dependencies. The SQL provides the column references and expressions our service uses to generate column-level lineage. Keeping the calculation at each step lets a business SME inspect how a reported value was produced.For the sales measure, the data-mart calculations are:net_sales_local = gross_sales_local - discount_local - returns_localnet_sales_usd = net_sales_local * exchange_rate_to_usdThe exchange-rate link includes the currency and reporting date used to select the rate. Each input can be followed back through the model to its source field.Net sales and currency conversion are calculated in the data mart. The reporting table exposes the result to Tableau, and Nucleus lets business SMEs follow its lineage. Image created by the author with AI assistance.Our lineage model can include source-system tables, Google Sheets, CSV files, APIs and databases upstream. Downstream, we can add Tableau data sources, extracts, dashboards, spreadsheets and applications.For Tableau, our service retrieves extract queries and data-source information through the available APIs, identifies the reporting tables they use and connects those references to the relevant workbooks and dashboards.A sales SME questioning net_sales_usd can use Nucleus with an analyst to examine the inputs and calculations. If the description is wrong, the SME corrects it in Nucleus. A calculation change belongs in the data-mart SQLX, where the developer makes it once for the reports that consume that measure.The production-code change triggers a lineage refresh. After the data and reports refresh, the SME can check the revised value and follow the calculation that produced it.What an agent can work withAn agent using the metadata and lineage can look up a measure’s definition, find its calculation and identify the reports that use it. Ownership and classification are available from the same interface.A coding agent asked to change net sales can find the relevant data-mart action, read the development policy and inspect the affected reporting fields. The developer can review the proposed SQLX against that same information. The PR checks apply to the change regardless of who wrote the code.I want to be able to ask, “What will this calculation change affect?”, and get back the relevant SQLX, source fields and reports.This story is published under the Generative AI publication. Connect with us on LinkedIn and follow Zeniteq to stay in the loop with the latest AI stories. Let’s shape the future of AI together!A Data Model an AI Can Actually Understand was originally published in Generative AI on Medium, where people are continuing the conversation by highlighting and responding to this story.Source: Generative AI Pub — Published — Category: Image AI