• 8 min read •By Tiago Marques

Finishing What I Started: Kimball on PySpark in a dbt World

Back in December, I wrote about building a Kimball-style modeling framework for Databricks. At the time, it was a modest v0.1.1 beta: fourteen Python modules that parsed YAML configurations and generated basic Delta Lake MERGE statements for SCD Type 1 and Type 2 tables.

Almost immediately, colleagues and data engineers asked the obvious question:

“The entire data engineering world has settled on dbt for modeling. Why spend your nights building a custom PySpark framework for the same thing?”

It was a fair question. dbt is the industry standard for analytics engineering. It has an immense ecosystem, great documentation, and millions of users. Writing a bespoke dimensional framework in Python can easily look like classic NIH (Not Invented Here) syndrome.

Yet over the past nine months, I kept going. The project evolved from a proof-of-concept script into a hardened Gold-layer loading engine, now at v0.3.0. Along the way, I also built a private dbt-native experiment (databricks_kimball_framework_dbt) to test parity, and started tinkering locally with automated linting (kimball_lint) and visual authoring (SDL).

Here is why I chose to finish the PySpark framework, what changed between the early prototype and the current codebase, and what working across both stacks taught me about where dimensional complexity actually lives.


1. What the PySpark Framework Actually Grew Into

The original December version was essentially a boilerplate generator. If you had a clean source table and wanted to MERGE it into an SCD2 dimension without writing 40 lines of repetitive SQL, it worked.

Production pipelines, however, do not stay in happy-path territory for long. Real lakehouse workloads bring out-of-order records, late-arriving dimensions, schema drifts, conflicting writers, and long cluster start-up times. As I ran into these walls, the framework evolved significantly:

A Strictly Spark-Free Control Plane

In early iterations, even basic configuration validation required spinning up a local PySpark session. That meant waiting 10–15 seconds for a JVM to initialize just to find out someone made a typo in a YAML field.

In v0.3.0, the entire control plane is decoupled from Spark:

  • kimball validate: Validates YAML configs against strict Pydantic schemas instantly on a laptop.
  • kimball compile: Resolves dependencies, builds a directed acyclic graph (DAG), and detects circular references or duplicate target writers before touching any data.
  • kimball plan: Compares your local configuration against a deployed manifest.json. It classifies changes (metadata update, validation rule, backfill required, or breaking change) and reports every transitively affected downstream table.

Because this layer has zero Spark dependencies, it runs in CI in milliseconds.

YAML Configs ──► kimball compile ──► DAG Validation ──► manifest.json
                                        │
                                        └──► Databricks Asset Bundle (DAB)

Databricks Asset Bundles (DABs) for Parallel Execution

A recurring challenge with PySpark is orchestration. Running multiple threads in a single Python process often leads to SparkSession contention and subtle race conditions on target tables.

Instead of writing custom thread pools, the compiler now emits native Databricks Asset Bundle job definitions (kimball compile --bundle-output generated-job.yml). The DAG levels are compiled directly into parallel Databricks Workflow tasks. Independent dimensions load concurrently across the cluster, while target tables retain strict single-writer isolation and centralized checkpointing.

Real Key Brokering & Type 7 Semantics

Basic SCD2 tracks historical row changes, but analytical users frequently need both point-in-time (“as-was”) reporting and current-state (“as-is”) aggregation. That requires Kimball Type 7 dual-key modeling:

  1. A Durable Key (_dk): A deterministic hash identifying the entity across its entire lifetime.
  2. A Version Surrogate Key (_sk): A hash derived from the natural key plus the business-effective timestamp, identifying that specific revision.

The framework manages this key brokering automatically. It seeds standardized non-null reserved members (-1 for Unknown, -2 for Not Applicable, -3 for Invalid, and -4 for Pending/Late-Arriving). Furthermore, it enforces strict half-open validity intervals (valid_from to valid_to with explicit high-dates like 9999-12-31) instead of NULL endpoints, eliminating tricky join bugs in downstream BI tools.

Operational State & Unresolved Keys

When a fact table arrives with a foreign key referencing a customer that hasn’t landed in the dimension table yet, most naive pipelines either fail or drop the row.

The framework writes operational state into two metadata tables:

  • etl_control: Records source watermarks, batch IDs, commit timestamps, and row counts for every run.
  • etl_unresolved_dimension_keys: Captures orphaned fact relationships. When a fact references a missing dimension key, the framework inserts an inferred placeholder member into the dimension with a -4 status, writes the relationship to the unresolved keys table, and automatically hydrates the full row once the dimension data arrives.

2. The dbt Experiment: What Happened When I Built the Sibling

After getting the PySpark engine stable, I wanted to test my own assumptions. Could this entire architecture be expressed as pure dbt macros?

In July, I set up a private sibling experiment—databricks_kimball_framework_dbt—to see if I could implement the exact same observable behaviors using dbt-core and Jinja macros on Databricks SQL and Delta Lake.

The experience revealed two big takeaways:

Where dbt Shines

dbt’s ecosystem handles the scaffolding you would otherwise have to build yourself:

  • DAG Orchestration: Model dependencies, ref() resolution, and lineage visualization work effortlessly.
  • Community Tooling: Documentation generation, package management, and generic schema testing (unique, not_null, accepted_values) are battle-tested and widely understood.
  • Standardization: Onboarding a typical analytics engineer to a dbt project is vastly easier than teaching them an internal Python DSL.

Where dbt Hits Friction

The catch is that dbt does not eliminate lakehouse state mechanics—it relocates them into Jinja.

Implementing complex dimensional logic in dbt means writing hundreds of lines of Jinja SQL macros:

  • Managing multi-pass MERGE statements with out-of-order Change Data Feed (CDF) events.
  • Handling dual-key generation, deterministic surrogate hashing, and inferred member hydration.
  • Replaying late-arriving dimension changes across historical fact tables.

When that logic lives inside Jinja, debugging becomes significantly harder. Compiled SQL files stretch into thousands of lines of dense MERGE commands. What is naturally expressed as procedural Python logic (reading Delta commit history, orchestrating multi-step reconciliation, handling rollback upon batch failure) feels stretched when forced into a template language designed primarily for SQL SELECT transformations.

In short: dbt is fantastic for transformation logic, but using it as a low-level state-and-storage engine on Delta Lake requires substantial macro scaffolding.


3. The Real Problem: The Authoring and Modeling Gap

Working between PySpark and dbt brought a deeper realization.

As data engineers, we spend an enormous amount of time debating tools: PySpark vs. dbt, SQL vs. Python, Airflow vs. Dagster. But when analytics projects fail or dashboards display conflicting numbers, it is almost never because someone chose the wrong orchestration framework.

The failures come from the modeling layer:

  • Ambiguous Grain: A fact table mixes transaction-level rows with daily aggregated rows.
  • Key Mismatches: Fact tables joining on mutable natural keys instead of durable keys.
  • Dimension Leakage: Slowly changing attributes duplicated across multiple tables without a clear source of truth.
  • Unreachable Dimensions: Foreign keys that can never resolve because of misaligned time granularities.

Whether you write your pipeline in YAML or dbt SQL, if the dimensional design is flawed, all the tooling does is automate bad data faster.

That realization led to two local tools I’ve been prototyping on my machine:

kimball_lint — Catching Modeling Traps in CI

Inspired by compiler checks, kimball_lint is a local, evidence-aware static linter for dimensional models.

Instead of waiting for data discrepancies to surface in production dashboards or review cycles, it inspects manifests and model metadata to verify warehouse design rules before merging code:

  • Verifies that every fact table explicitly declares its measurement grain.
  • Flags dimension attributes denormalized directly into fact tables without a surrogate relationship.
  • Detects mutable keys used where durable entity keys are required.
  • Alerts on missing time-dimension associations for temporal facts.

SDL (semantic_data_loader) — Visual Authoring with Validated Outputs

Most data practitioners know what business process they are trying to model, but translating that mental model into correct surrogate keys, bridge tables, and staging views can be daunting.

SDL is an experimental visual workbench I’ve been prototyping locally. The idea is to let engineers model facts, dimensions, and relationships visually, validate the graph against dimensional modeling rules, and compile the result into clean, standard, human-readable dbt projects. dbt remains the execution boundary, but the authoring happens in an environment that prevents structural mistakes before code is committed.


Summary: What I Learned

Finishing the PySpark framework was never about convincing data teams to abandon dbt. Rather, it was about mastering the mechanics of the lakehouse by building them from the ground up.

A few core takeaways:

  1. Procedural Python and declarative SQL serve different needs: PySpark excels at low-level Delta stream handling, CDF state reconciliation, and granular transaction control. dbt excels at DAG dependencies, modular SQL transforms, and team collaboration. The two complement each other when applied at the right boundaries.
  2. Key contracts matter more than merge syntax: Getting WHEN MATCHED THEN UPDATE right takes an afternoon. Getting Type 7 durable vs. version keys, null semantics, and inferred late-arriving members right takes weeks of edge-case testing.
  3. The star schema remains as relevant as ever: Modern query engines have gotten faster, but business logic hasn’t gotten simpler. Clean, conformed dimensions and explicit fact grains remain the most reliable way to deliver self-serve analytics that business users can actually trust.

The open-source framework is available on GitHub: 👉 github.com/accordionjourneyman/databricks_kimball_framework

(Note: The dbt parity implementation, kimball_lint, and SDL mentioned above are currently local research prototypes and experiments).

Tiago Marques
Written by

Tiago Marques

Principal Data Analytics Engineer specializing in cloud data platforms, Databricks, Kimball dimensional modeling, and reliable BI architectures.