dbt tests are a great CI gate, but they run after the model builds and only detect by the time one fails, the bad data is already in your table. For "keep the good rows, isolate the bad ones, keep going" pattern, you need row-level DQ that runs in-transit, and I adopted DQX as I use it with other Spark workloads too.

The thing I had to unlearn is that you do not rewrite your dbt SQL gold model as Python. The transformation logic stays in a normal `.sql` model, you just add a thin Python model beside it that dbt.ref()s it and applies DQX. The Python model (orders_gold_dq) becomes the published Gold table; the SQL model (orders_gold) becomes an internal intermediate. Your downstream consumers point to orders_gold_dq, not orders_gold.

-- models/orders_gold.sql
select order_id, customer_id, amount, status from {{ ref('orders_silver') }}

Thin DQ Layer:

# models/orders_gold_dq.py  
from databricks.labs.dqx.engine import DQEngine
from databricks.sdk import WorkspaceClient

def model(dbt, session):
  df = dbt.ref("orders_gold")
  checks = [
    {"criticality": "error", "check": {"function": "is_not_null",
       "arguments": {"column": "order_id"}}},
    {"criticality": "warn",  "check": {"function": "is_in_list",
       "arguments": {"column": "status", "allowed": ["new", "paid", "shipped"]}}},
    {"criticality": "error", "check": {"function": "is_unique",
       "arguments": {"columns": ["order_id"]}}},
 ]
  dq = DQEngine(WorkspaceClient())
  valid_df, quarantine_df = dq.apply_checks_by_metadata_and_split(df, checks)
  return valid_df  # clean rows become the published table

If your transformation is already a Python model (e.g. complex PySpark logic), you don't need the extra _dq.py layer at all, just embed DQX directly inside that model before the return:.

error rows → quarantine only, never written to the clean table; warn rows → stay in clean output with _warnings metadata, not quarantined. Each quarantined row carries _errors/_warnings with the rule + a readable message.

Wire DQX in as a serverless dep in dbt_project.yml (+submission_method: serverless_cluster,
+environment_dependencies: [databricks-labs-dqx]), then just dbt run or use your preferred scheduling pattern. And I scheduled this on Databricks Lakeflow with a dbt task, as seen in the picture above.

Entra PIM (Privileged Identity Management) and Databricks AIM (Automatic Identity Management) work well together to eliminate standing privilege in Azure Databricks.

Instead of permanently assigning privileged access, you put Databricks permissions on Entra security groups and let PIM control who can activate membership in those groups, and for how long. AIM then picks up that group membership from Entra and applies the Databricks permissions tied to it.

Note: AIM refreshes group membership on auth/authorization events, so browser-based access usually updates faster than token- or job-based access.

That makes this pattern especially useful in a few scenarios:

1) Break-glass workspace admin access

Example:

• Group: databricks-prod-breakglass-admin
• Databricks permission: workspace admin on the production workspace

Normal state: nobody has standing admin access.

Incident state: an on-call engineer activates the PIM assignment, gets temporary admin access to the workspace, does the required troubleshooting, and then loses that access automatically when the activation expires.

That is much cleaner than leaving a broad admin group permanently active.

2) Temporary production support access

A lot of teams need occasional elevated access for prod debugging, pipeline validation, or incident triage, but not full-time access.

Example:

• Group: databricks-prod-support
• Databricks permission: access to the prod workspace with scoped access to relevant jobs and compute

This works well for support engineers, platform teams, or on-call rotations that need short-lived access during a support window.

3) Time-bound access to sensitive Unity Catalog data

This is another very strong use case, especially for regulated or high-sensitivity datasets.

Example:

• Group: databricks-sensitive-data-readers
• Databricks permission: USE CATALOG, USE SCHEMA, SELECT on sensitive tables or schemas
• PIM policy: approval, justification, and limited activation duration

Instead of giving analysts or engineers long-lived access to sensitive data, they activate it only when needed. When the PIM window ends, Entra removes the group membership, and Databricks reflects that change on a subsequent auth or authorization-triggering event.

Why AIM makes this easier

AIM uses Entra as the source of truth and refreshes group memberships during authentication and authorization events such as browser logins, token authentication, and job runs. So the operating model becomes:

• assign Databricks permissions to Entra groups
• manage eligibility in PIM
• let users activate access only when needed
• let Databricks pick up the membership change through AIM

Entra PIM controls who can elevate, AIM reflects that group membership into Databricks, and Databricks permissions remain group-based instead of user-based.

Problem: In Lakeflow Spark Declarative Pipelines, the pivot() function is not supported. The pivot operation in Spark requires the eager loading of input data to compute the output schema. This capability is not supported in pipelines.

Source: https://docs.databricks.com/aws/en/ldp/limitations

How can this be mitigated?

Workaround 1: Rewrite PIVOT Using CASE WHEN

This is the most common workaround. You manually expand the pivot into conditional aggregations.

>Original Query:

SELECT *
FROM sales_data
PIVOT (
  SUM(sales) 
  FOR region IN ('North', 'South', 'East', 'West')
)

>Rewritten without PIVOT:

SELECT 
  product,
  SUM(CASE WHEN region = 'North' THEN sales ELSE 0 END) AS North,
  SUM(CASE WHEN region = 'South' THEN sales ELSE 0 END) AS South,
  SUM(CASE WHEN region = 'East'  THEN sales ELSE 0 END) AS East,
  SUM(CASE WHEN region = 'West'  THEN sales ELSE 0 END) AS West
FROM sales_data
GROUP BY product

This works perfectly in Lakeflow Pipelines because the output schema is fully deterministic at parse time, no eager data loading required.

Workaround 2: Rewrite PIVOT Using aggregate FILTER

Databricks SQL supports the FILTER(WHERE ...) clause on aggregates, which is a cleaner alternative to CASE WHEN:

>Original PIVOT query:

SELECT year, region, q1, q2, q3, q4
FROM sales
PIVOT (
  SUM(sales) AS sales
  FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4)
)

>Rewritten with FILTER:

SELECT 
  year, 
  region,
  SUM(sales) FILTER(WHERE quarter = 1) AS q1,
  SUM(sales) FILTER(WHERE quarter = 2) AS q2,
  SUM(sales) FILTER(WHERE quarter = 3) AS q3,
  SUM(sales) FILTER(WHERE quarter = 4) AS q4
FROM sales
GROUP BY year, region

This syntax is often more readable than nested CASE WHEN, especially with multiple aggregations.

Multi-Column PIVOT Rewrite

>For pivoting on multiple columns simultaneously:

SELECT *
FROM sales
PIVOT (
  SUM(sales) AS sales
  FOR (quarter, region) 
  IN ((1, 'east') AS q1_east, (1, 'west') AS q1_west, 
      (2, 'east') AS q2_east, (2, 'west') AS q2_west)
)

>Rewritten:

SELECT 
  year,
  SUM(sales) FILTER(WHERE quarter = 1 AND region = 'east') AS q1_east,
  SUM(sales) FILTER(WHERE quarter = 1 AND region = 'west') AS q1_west,
  SUM(sales) FILTER(WHERE quarter = 2 AND region = 'east') AS q2_east,
  SUM(sales) FILTER(WHERE quarter = 2 AND region = 'west') AS q2_west
FROM sales
GROUP BY year

Multiple Aggregations

You can also rewrite PIVOTs that use multiple aggregate functions.

>Original Query

SELECT *
FROM (SELECT year, quarter, sales FROM sales) AS s
PIVOT (
  SUM(sales) AS total, AVG(sales) AS avg
  FOR quarter IN (1 AS q1, 2 AS q2, 3 AS q3, 4 AS q4)
)

>Rewritten:

SELECT 
  year,
  SUM(sales) FILTER(WHERE quarter = 1) AS q1_total,
  AVG(sales) FILTER(WHERE quarter = 1) AS q1_avg,
  SUM(sales) FILTER(WHERE quarter = 2) AS q2_total,
  AVG(sales) FILTER(WHERE quarter = 2) AS q2_avg,
  SUM(sales) FILTER(WHERE quarter = 3) AS q3_total,
  AVG(sales) FILTER(WHERE quarter = 3) AS q3_avg,
  SUM(sales) FILTER(WHERE quarter = 4) AS q4_total,
  AVG(sales) FILTER(WHERE quarter = 4) AS q4_avg
FROM sales
GROUP BY year

Summary

Both approaches produce identical results and work fully within SDP pipelines with complete lineage tracking.