Create Synapse Analytics Marts with dbt

Use dbt with Synapse Dedicated SQL Pool to turn Gold payments data into finance, operations, risk, merchant, and executive analytics marts.
Azure • Analytics • Advanced • Payments

Architecture Diagram

Build this project

  • Source code
  • Setup files
  • Architecture walkthrough
  • Implementation videos
  • Execution guidance
  • Support
Get Project Access

Included in Data0to1 Project Library

What this project solves

Gold data is business-ready, but teams usually need focused reporting datasets for specific decisions.

Finance teams may need settlement and revenue views, operations teams may need process-level metrics, risk teams may need monitoring datasets, merchant teams may need merchant-level reporting, and leadership may need executive scorecards.

In this pipeline, you will create the analytics layer on Azure using dbt and Synapse Dedicated SQL Pool. You will read Gold serving tables, build staging and serving models, create business marts for finance, operations, risk, merchant monitoring, and leadership reporting, and track model freshness and row count reconciliation.

What you’ll build

  • Connect dbt to Synapse Dedicated SQL Pool
  • Read Gold serving tables from Synapse
  • Create staging models to clean and standardize source columns
  • Build serving models for core payments entities
  • Create finance, operations, risk, merchant, and executive marts
  • Add model freshness and row count reconciliation checks
  • Prepare reporting-ready datasets for BI tools

How you’ll build it

  • Start from Gold serving tables in Synapse
  • Configure dbt models for staging, serving, and mart layers
  • Transform payments data into reusable analytics models
  • Build business marts for finance, operations, risk, merchant, and executive reporting
  • Validate row counts, freshness, and reconciliation checks before publishing

Tools you’ll use

Azure Synapse Dedicated SQL Pool • dbt Core • SQL • Azure Data Lake Storage Gen2 • Synapse SQL • ODBC Driver for SQL Server

What you’ll walk away with

After completing this pipeline, you will be able to:

  1. Build analytics transformations using dbt on Azure
  2. Organize dbt models into staging, serving, BI, and audit layers
  3. Create reporting-ready datasets for finance, operations, risk, merchant, and executive teams
  4. Build incremental models in Synapse
  5. Track model freshness and row count differences
  6. Create reusable business marts from Gold data
  7. Understand how lakehouse data is served to analytics teams

Ready to build this project?

Get access to the project code, setup files, architecture walkthrough, implementation videos, and support.

Get Project Access