Lessons From The Night I Met Dbt on Databricks

It was late he kind of quiet evening when every human questions the meaning of life and their pipelines of course :).I’d been debugging a transformation script that broke for the fifth time that week. The problem wasn’t the data; it was the process. process That’s when I stumbled upon dbt (Data Build Tool), not a new ETL tool, but a framework that thinks like a software engineer. SQL models, modularity, testing, and lineage suddenly, transformation felt… elegant. dbt (Data Build Tool), thinks like a software engineer This article tells the story of how I implemented the Medallion Architecture using dbt + Databricks, transforming messy e-commerce data into business-ready insights. This article tells the story of how I implemented the Medallion Architecture using dbt + Databricks, transforming messy e-commerce data into business-ready insights. This article tells the story of how I implemented the Medallion Architecture using dbt + Databricks, transforming messy e-commerce data into business-ready insights. Medallion Architecture dbt + Databricks Introduction — What is dbt, Really? If you’ve ever written SQL transformations in a warehouse and wondered why everything feels disconnected — dbt is the missing link. dbt is a data transformation framework that turns your warehouse into a software development environment. You define models as .sql files, manage dependencies with ref(), run automated tests, and version your transformations — all in Git. data transformation framework software development environment Think of dbt as: SQL + Jinja + CI/CD = Reproducible Data Models SQL + Jinja + CI/CD = Reproducible Data Models SQL + Jinja + CI/CD = Reproducible Data Models SQL + Jinja + CI/CD = Reproducible Data Models In essence, Bronze, Silver and Gold aren’t just a naming convention — it’s a design philosophy. dbt lets you build, test, and document each layer with confidence. Bronze, Silver and Gold build, test, and document Where Everything Begins Where Everything Begins Like every good story, this one starts with chaos. chaos The Bronze layer is where raw data lands — unfiltered, imperfect, and full of potential. In my project, the Bronze models ingested four datasets: orders, products, users, and reviews. orders products users reviews -- models/bronze/bronze_orders.sql {{ config(materialized='table') }} -- models/bronze/bronze_orders.sql {{ config(materialized='table') }} SELECT order_id, user_id, product_id, quantity, unit_price, order_date, current_timestamp() AS ingestion_timestamp FROM {{ source('raw', 'orders') }} SELECT order_id, user_id, product_id, quantity, unit_price, order_date, current_timestamp() AS ingestion_timestamp FROM {{ source('raw', 'orders') }} This stage is all about fidelity over perfection — preserving every record from the source system. If data were gold ore, the Bronze layer is the mining site. fidelity over perfection Refinery Refinery The Silver layer is where the alchemy begins. Here, the goal is to clean, standardize, and enrich — turning raw ore into polished data assets. alchemy -- models/silver/silver_orders.sql {{ config(materialized='table') }} -- models/silver/silver_orders.sql {{ config(materialized='table') }} SELECT o.order_id, o.user_id, o.product_id, o.quantity, o.unit_price, (o.quantity * o.unit_price) AS order_amount, u.country, p.category, p.brand FROM {{ ref('bronze_orders') }} o JOIN {{ ref('bronze_users') }} u ON o.user_id = u.user_id JOIN {{ ref('bronze_products') }} p ON o.product_id = p.product_id SELECT o.order_id, o.user_id, o.product_id, o.quantity, o.unit_price, (o.quantity * o.unit_price) AS order_amount, u.country, p.category, p.brand FROM {{ ref('bronze_orders') }} o JOIN {{ ref('bronze_users') }} u ON o.user_id = u.user_id JOIN {{ ref('bronze_products') }} p ON o.product_id = p.product_id This layer introduces business logic — standardizing currency, validating foreign keys, and computing new metrics. business logic I even used a simple unit test in dbt to validate calculations: unit test # models/silver/_silver.yml unit_tests: - name: test_order_amount_calc model: silver_orders given: - input: ref('bronze_orders') format: csv rows: | id,quantity,unit_price 1,10,100.00 2,2,5.00 expect: format: csv rows: | id,order_amount 1,1000.00 2,10.00 # models/silver/_silver.yml unit_tests: - name: test_order_amount_calc model: silver_orders given: - input: ref('bronze_orders') format: csv rows: | id,quantity,unit_price 1,10,100.00 2,2,5.00 expect: format: csv rows: | id,order_amount 1,1000.00 2,10.00 Testing is where dbt shines — imagine asserting your data logic before it breaks production. before Decisions, Decisions, and More Decision Making By the time data reaches the Gold layer, it has survived the turbulence of the Bronze lake and the refinements of the Silver refinery. Gold layer Now, it’s ready to speak business. speak business In the Medallion Architecture, the Gold layer is the semantic and analytics layer where data is no longer raw or transactional but aggregated, curated, and meaningful. It’s here that analysts, decision-makers, and BI tools finally meet the story your data has been trying to tell. semantic and analytics layer aggregated, curated, and meaningful The Purpose of the Gold Layer While the Silver layer focuses on conformance and cleansing, the Gold layer is where business value is defined. business value is defined This layer answers questions like: “How much revenue did we generate yesterday?” “Which products or categories are trending?” “What’s the overall customer satisfaction score this week?” “How much revenue did we generate yesterday?” “Which products or categories are trending?” “What’s the overall customer satisfaction score this week?” These are not raw metrics — they are insights, built from Silver tables that have already handled all the data quality, relationships, and enrichments. not raw metrics insights Building the Gold Sales Model My first Gold model, gold_sales__daily.sql, takes transactional-level data from silver_orders and aggregates it into daily revenue facts. -- models/gold/gold_sales__daily.sql {{ config(materialized='view') }} -- models/gold/gold_sales__daily.sql {{ config(materialized='view') }} SELECT order_date, SUM(order_amount) AS total_sales, COUNT(DISTINCT order_id) AS total_orders FROM {{ ref('silver_orders') }} GROUP BY order_date SELECT order_date, SUM(order_amount) AS total_sales, COUNT(DISTINCT order_id) AS total_orders FROM {{ ref('silver_orders') }} GROUP BY order_date Each record in this table represents the business pulse for a single day: total revenue, number of orders, and sales volume. This model is perfect for trend analysis, daily dashboards, and KPI tracking in tools like Power BI, Preset, or Tableau. business pulse for a single day trend analysis daily dashboards KPI tracking Under the hood, dbt automatically understands dependencies via the ref(‘silver_orders’) function — ensuring this model only runs after silver_orders has successfully built. after Measuring Customer Sentiment The second Gold model, gold_avg_rating__daily.sql, captures how customers feel about their purchases — a perfect complement to sales metrics. -- models/gold/gold_avg_rating__daily.sql {{ config(materialized='view') }} -- models/gold/gold_avg_rating__daily.sql {{ config(materialized='view') }} SELECT review_date, AVG(rating) AS avg_rating, COUNT(review_id) AS total_reviews FROM {{ ref('silver_reviews') }} GROUP BY review_date SELECT review_date, AVG(rating) AS avg_rating, COUNT(review_id) AS total_reviews FROM {{ ref('silver_reviews') }} GROUP BY review_date Here, I’m transforming individual review events into a daily sentiment summary, providing an at-a-glance view of customer satisfaction trends. individual review events daily sentiment summary Testing the Gold Layer Like the Silver layer, the Gold layer includes unit tests to verify that business logic holds even as upstream data changes. unit tests # models/gold/_gold.yml version: 2 # models/gold/_gold.yml version: 2 unit_tests: - name: test_gold_avg_rating model: gold_avg_rating__daily given: - input: ref('bronze_reviews') format: csv rows: | id,created_at,product_id,rating 1,2018-01-04T00:57:04.845+00:00,1,5 2,2018-01-04T00:00:00.140+00:00,1,4 3,2020-01-04T00:00:10.040+00:00,2,3 - input: ref('silver_products') format: csv rows: | id,product_name 1,'product1' 2,'product2' expect: format: csv rows: | review_date,product_id,avg_rating 2018-01-04,1,4.5 2020-01-04,2,3 unit_tests: - name: test_gold_avg_rating model: gold_avg_rating__daily given: - input: ref('bronze_reviews') format: csv rows: | id,created_at,product_id,rating 1,2018-01-04T00:57:04.845+00:00,1,5 2,2018-01-04T00:00:00.140+00:00,1,4 3,2020-01-04T00:00:10.040+00:00,2,3 - input: ref('silver_products') format: csv rows: | id,product_name 1,'product1' 2,'product2' expect: format: csv rows: | review_date,product_id,avg_rating 2018-01-04,1,4.5 2020-01-04,2,3 This test asserts that when reviews are averaged by date and product, the resulting daily ratings are correct — proof that the business logic in the model works as intended. From Pipelines to Insights At this point, the Gold layer feeds directly into dashboards and data applications. The same datasets that began as chaotic CSVs in Bronze are now refined insights driving executive reports, automated KPIs, and machine learning pipelines. dashboards and data applications For example: The Sales model powers revenue and growth dashboards. The Average Rating model informs product feedback analysis and NPS tracking. The Sales model powers revenue and growth dashboards. Sales model The Average Rating model informs product feedback analysis and NPS tracking. Average Rating model With dbt, the entire journey from ingestion to insight is transparent, testable, and reproducible — the holy trinity of modern data engineering. The Gold layer is where data storytelling truly happens. transparent, testable, and reproducible data storytelling Your transformations are no longer technical — they are narrative. Each SQL statement contributes to a chapter in the business story, one that explains not just what happened, but why it matters. narrative what happened why it matters How dbt Keeps It All Together How dbt Keeps It All Together By the time I reached this stage, I realized that dbt wasn’t just transforming data. Under the hood, dbt runs on an elegant philosophy: if you can describe your logic clearly, dbt can orchestrate it reliably. This chapter is about the machinery — the quiet genius that holds the Medallion Architecture together. if you can describe your logic clearly, dbt can orchestrate it reliably The Blueprint: dbt_project.yml Every dbt project starts with a heart — a file called dbt_project.yml. It’s the project’s DNA, describing where models live, how they’re built, and which schema or materialization rules to apply. project’s DNA Here’s what mine looks like: # dbt_project.yml name: 'dbt_databricks_project' version: '1.0.0' profile: 'default' # dbt_project.yml name: 'dbt_databricks_project' version: '1.0.0' profile: 'default' model-paths: ["models"] macro-paths: ["macros"] test-paths: ["tests"] snapshot-paths: ["snapshots"]models: dbt_databricks_project: bronze: +schema: bronze +materialized: table silver: +schema: silver +materialized: table gold: +schema: gold +materialized: view model-paths: ["models"] macro-paths: ["macros"] test-paths: ["tests"] snapshot-paths: ["snapshots"]models: dbt_databricks_project: bronze: +schema: bronze +materialized: table silver: +schema: silver +materialized: table gold: +schema: gold +materialized: view This single file tells dbt: “All my Bronze models should be built as tables, all Silver models as tables, and all Gold models as views (or tables for perormance) in their respective schemas.” “All my Bronze models should be built as tables, “All my Bronze models should be built as tables, Bronze tables all Silver models as tables, all Silver models as tables, Silver tables and all Gold models as views (or tables for perormance) in their respective schemas.” and all Gold models as views (or tables for perormance) in their respective schemas.” Gold views (or tables for perormance) Simple, declarative, and consistent. That’s one of DBT’s core superpowers — configuration over chaos. configuration over chaos Dependencies That Build Themselves In a traditional pipeline, you’d write scripts to manually define job dependencies. In dbt, this logic is handled elegantly with one word: ref(). ref() SELECT * FROM {{ ref('silver_orders') }} SELECT * FROM {{ ref('silver_orders') }} That single Jinja function call tells dbt: “Hey, this model depends on silver_orders. Build that first.” “Hey, this model depends on silver_orders. Build that first.” “Hey, this model depends on silver_orders. Build that first.” From there, dbt automatically constructs a directed acyclic graph (DAG) — the same kind of dependency tree you’d see in tools like Airflow or Dagster. directed acyclic graph (DAG) When you run: dbt run --select gold dbt run --select gold dbt doesn’t just execute a SQL script — it resolves the full dependency chain: resolves the full dependency chain First, it builds Bronze (raw tables) Builds Silver (cleansed tables) Then, finally, Gold (aggregated insights) All in the correct order, automatically. First, it builds Bronze (raw tables) Bronze Builds Silver (cleansed tables) Silver Then, finally, Gold (aggregated insights) Gold All in the correct order, automatically. Packages and Modularity dbt encourages modularity through packages, just like Python or Node. packages # packages.yml packages: - package: dbt-labs/dbt_utils version: 1.3.1 # packages.yml packages: - package: dbt-labs/dbt_utils version: 1.3.1 The dbt_utils package brings in a toolbox of reusable macros — functions that simplify things like testing, joins, and common data patterns. This modular design means you never reinvent the wheel. Need to check if a column is unique or not null? One line of YAML does it. dbt treats reusability as a design principle, not an afterthought. reusability as a design principle Testing Assumptions, Not Just Data Tests in dbt are not unit tests in code — they’re trust checks for your data. trust checks They assert truths like: Every order_id should be unique. No user_id should be null. Ratings should only fall between 1 and 5. Every order_id should be unique. No user_id should be null. Ratings should only fall between 1 and 5. These can be written inline in your YAML schema files or as custom tests using macros. custom tests When you run: dbt test dbt test dbt runs every test in parallel, ensuring your pipelines are safe, validated, and production-ready. safe, validated, and production-ready Documentation and Lineage Then comes my favourite feature — dbt Docs. dbt Docs With a single command: dbt docs generate dbt docs serve dbt docs generate dbt docs serve dbt builds a self-documenting data catalogue with Column descriptions, Data types, and source links. And a beautiful lineage graph showing how Bronze flows into Silver, and Silver into Gold. self-documenting data catalogue beautiful lineage graph It’s not just documentation; it’s transparency — the kind that builds confidence between engineers, analysts, and executives. transparency When all these pieces come together — configurations, references, packages, tests, and docs — dbt orchestrates a seamless symphony. You’re not writing isolated SQL scripts anymore; You’re building a data ecosystem where every model knows its place and purpose. data ecosystem So when you run: dbt run --select silver dbt run --select silver dbt knows exactly what to do. exactly It compiles, orders, and executes every dependent model in sequence — no Airflow DAG required. And the best part? You can version it, test it, and redeploy it — just like code. And the best part? And the best part? You can version it, test it, and redeploy it — just like code. You can version it, test it, and redeploy it — just like code. In Essence dbt keeps the Medallion Architecture alive by automating discipline: automating discipline Every model has a schema, purpose, and test. Every dependency is explicit. Every layer — Bronze, Silver, and Gold — is reproducible from scratch. It’s the invisible scaffolding that ensures your Lakehouse doesn’t collapse under complexity. In a world where pipelines often break in silence, dbt whispers clarity. Core Lessons from the Lakehouse Core Lessons from the Lakehouse Building this project offered several practical insights into implementing dbt and the Medallion Architecture effectively within a modern data platform. 1. Start with Clarity, Not Code Successful data projects begin with a clear architectural vision, not SQL syntax. Defining data flow from raw ingestion to curated output ensures every model has a specific purpose within the pipeline. Planning dependencies, naming conventions, and transformation logic upfront reduces complexity and rework later. 2. Data Quality Is Non-Negotiable Reliable analytics depend on trusted data. Integrating dbt tests into each layer (Bronze, Silver, Gold) enforces data validation and prevents downstream issues. Tests for nulls, duplicates, and calculation accuracy (e.g., order amount and average rating) help maintain integrity as data scales. In dbt, testing becomes part of the engineering process rather than an afterthought. dbt tests 3. Documentation Drives Alignment Documentation is as critical as modelling. dbt automatically generates lineage, descriptions, and schema visibility through dbt docs generate, enabling transparency across teams. Clear documentation accelerates onboarding, simplifies debugging, and improves collaboration between engineering and analytics functions. dbt docs generate 4. Apply Software Engineering Principles dbt encourages data teams to think like software engineers: modular design, version control, and CI/CD integration. Breaking logic into reusable components, leveraging packages such as dbt_utils, and maintaining models in Git repositories results in scalable, maintainable pipelines. Consistency and automation improve reliability and simplify operational governance. 5. Maintain Simplicity Effective data architecture prioritizes readability and structure over complexity.DBT’s declarative configuration, ref() dependencies, and schema-based organization make pipelines self-describing and predictable. Simplicity ensures long-term maintainability and reduces friction when adapting to business changes. 6. Adopt a Layered Mindset The Medallion Architecture emphasizes progression and control: Bronze preserves raw data. Silver cleanses, standardizes, and enriches. Gold aggregates for analytics and reporting. Bronze preserves raw data. Bronze Silver cleanses, standardizes, and enriches. Silver Gold aggregates for analytics and reporting. Gold Viewing the pipeline as an evolving system, not isolated scripts, enforces discipline and traceability throughout the data lifecycle. 7. Build for Trust Ultimately, modern data engineering is about trust in process, models, and outcomes. dbt and the Lakehouse pattern provide the structure to make that trust measurable through testing, documentation, and transparency. Every model should answer a clear business question and pass objective validation. In Summary This project reinforced that scalable data platforms depend on design, discipline, and documentation. By combining dbt’s engineering-driven approach with the Medallion Architecture, teams can build reproducible, testable, and insight-ready data systems that evolve reliably over time. Why This Matters to You as a Developer In the end, dbt isn’t just another data tool; it’s a movement. movement It bridges the gap between analytics and engineering, empowering teams to build data like software, tested, versioned, and documented.