About This Architecture

SQL Server ELT migration using dbt implements a three-layer transformation architecture that separates raw data ingestion from business logic. Source SQL Server databases feed into an Extract and Load layer powered by Python, PyODBC, and SQLAlchemy, which lands raw data into a target SQL Server Raw Landing zone. The dbt Project then orchestrates transformation across Staging (stg_), Intermediate (int_), and Marts (mart_) layers, progressively refining data for BI and analytics consumers. This pattern decouples extraction from transformation, enabling version control, testing, and documentation of all data models. Fork this diagram to customize your own dbt project structure, add additional source systems, or adapt the layer naming conventions to your organization's standards.

People also ask

How do you structure a dbt project for SQL Server ELT migrations with staging, intermediate, and marts layers?

This diagram shows a three-layer dbt architecture where Python scripts using PyODBC and SQLAlchemy extract source SQL Server data into a raw landing zone. dbt then transforms this raw data through Staging (stg_) models for cleansing, Intermediate (int_) models for business logic, and Marts (mart_) models for analytics consumption, enabling version-controlled, testable data pipelines.