DEV Community: Aniket Abhishek Soni

dbt snapshots are not an audit log

Aniket Abhishek Soni — Tue, 03 Nov 2026 16:26:58 +0000

The persistent myth that dbt snapshots act as a foolproof audit log is a dangerous misunderstanding of how slowly changing dimensions (SCDs) function in modern data warehouses.

Many engineers treat snapshots as a catch-all solution for "tracking changes." If a table changes, they point a dbt snapshot at it and assume they have a perfect history of every state transition. However, dbt snapshots are designed to track the current state of a dimension over time, not to capture the high-frequency event stream required for an audit.

What happened

In 2022, the engineering team at Monzo shared a postmortem regarding a data integrity incident where a miscalculation in their ledger system led to incorrect balances. While this specific incident involved application-level logic rather than a dbt snapshot, it serves as a critical reminder of the difference between "state" and "event."

When systems rely on snapshots to reconstruct history, they often fail to capture the sequence of events that occurred between snapshot runs. If your snapshot frequency is set to once per day, and a record is updated three times within those 24 hours, the snapshot will only record the state as it existed at the moment of the snapshot run. You have lost the intermediate transitions. If those transitions were critical for compliance or debugging, your "audit log" is fundamentally incomplete.

Photo by camera obscura on Unsplash

The underlying failure pattern

The failure occurs because dbt snapshots (as documented in dbt Labs' official docs) are built to implement SCD Type 2. They compare the current state of a source record against the last recorded state.

Consider this hypothetical configuration:

{% snapshot user_status_snapshot %}

{{
    config(
      target_schema='snapshots',
      unique_key='user_id',
      strategy='check',
      check_cols=['status', 'subscription_level'],
    )
}}

select * from {{ source('raw', 'users') }}

{% endsnapshot %}

When dbt snapshot runs, it performs a comparison. If the status column changed from 'active' to 'pending' and back to 'active' between two runs, the snapshot engine sees no difference. The historical record of that 'pending' state is lost.

Furthermore, snapshots are designed to be idempotent and batch-oriented. They are not designed to handle high-velocity write-ahead logs. Relying on them for an audit trail assumes that the source system provides a clean, consistent view of state at the time of execution. If the source system itself is subject to race conditions or eventual consistency—common in distributed architectures—the snapshot simply inherits that ambiguity.

Photo by Deng Xiang on Unsplash

How to defend against it

To build a reliable audit trail, you must move away from state-based snapshots and toward event-based logging.

First, treat your data sources as event streams. Instead of relying on a users table that represents the current state of a user, ingest the underlying transaction or change logs. If you are using a database like PostgreSQL, tools like Debezium capture these row-level changes (CDC) and stream them into your warehouse. This ensures that every state transition is recorded as an immutable event, regardless of how many times a record changes in a single hour.

Second, separate your analytical snapshots from your audit requirements. It is perfectly acceptable to use dbt snapshots for building SCD Type 2 dimensions for your BI tools. These are designed for "as-of" reporting, where you want to know what a user's subscription level was on a specific date. They are not, however, an acceptable replacement for a granular audit log.

Third, enforce immutability in your storage layer. If you are using technologies like Apache Iceberg, leverage table properties to maintain history via snapshots or versioning. When you need to audit a change, query the table history directly rather than relying on a dbt-managed table that has discarded intermediate states.

If you must use dbt for auditing, you are likely using the wrong tool. An audit log requires:

Completeness: Every state change must be captured.
Ordering: The sequence of changes must be preserved.
Provenance: The origin and timestamp of the event must be immutable.

dbt snapshots provide none of these guarantees by default. They are a convenience for modeling, not a mechanism for compliance.

Conclusion

The distinction between capturing state and capturing events is the difference between an analytical convenience and a system of record. When you configure a dbt snapshot, you are choosing to manage the complexity of SCD Type 2 dimensions. You are not choosing to archive your system's history.

Use dbt snapshots to simplify your JOINs and power your dashboards. Use event-driven architectures and immutable logs when you need to prove exactly what happened, and when. Never confuse the two, or you will eventually find yourself trying to debug a "missing" state transition that your snapshot infrastructure was never designed to preserve in the first place.

Cover photo by Bruno Guerrero on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Trimming Container Bloat with Docker History and Dive

Aniket Abhishek Soni — Mon, 02 Nov 2026 18:09:50 +0000

Why this matters: Large container images increase pull times during CI/CD, consume unnecessary storage in container registries, and expand the attack surface by including extraneous binary dependencies. Managing image size is a fundamental practice for maintaining lean, reproducible data pipelines that scale predictably across environments.

A Docker image is not a single file; it is a stack of filesystem changes. A common misconception is that deleting a file in a later RUN command removes it from the final image. It does not. The file persists in the layer where it was created, and subsequent layers simply mark it as "hidden" or "deleted," meaning your image size remains bloated by the original data.

If you are shipping data processing agents or heavy analytics environments, you are likely carrying "ghost" data—temporary caches, source code, or build artifacts—that provide no runtime value. Finding these hidden bytes requires more than just docker images. You need to peel back the layers.

Prerequisites

To follow this tutorial, you need the following installed:

Docker Engine (version 20.10 or higher).
Dive (a tool for exploring a docker image, layer by layer). See the official repository for installation instructions.
A terminal environment with permissions to build images.

Photo by Sasun Bughdaryan on Unsplash

Step 1: Inspecting layers with docker history

Before using specialized tools, understand what Docker reports natively. The docker history command displays the commands used to create each layer and the resulting size of that layer.

Create a dummy Dockerfile to visualize the problem:

FROM python:3.11-slim
RUN apt-get update && apt-get install -y gcc g++
COPY ./data_processing_script.py /app/script.py
RUN pip install pandas==2.2.0
RUN rm -rf /root/.cache/pip

Build the image:
docker build -t bloat-test:latest .

Now, run the history command:
docker history --no-trunc bloat-test:latest

The output shows every instruction. Look for layers with large sizes. You will notice that the apt-get install layer is significant. Even though you might think you are cleaning up in a later layer, the initial download and extraction remain permanently in the image history.

Step 2: Using Dive to visualize the bloat

docker history is text-based and difficult to parse for large images. Dive provides an interactive TUI (Text User Interface) that allows you to navigate each layer and see exactly which files were added, modified, or removed.

Run Dive on your image:
dive bloat-test:latest

Navigate the interface using the arrow keys:

Left Panel: Lists the Docker layers.
Right Panel: Shows the contents of the filesystem for the selected layer.

Watch for the "Image Efficiency Score" in the top right. If you see a low score, it usually indicates that you are creating files in one layer and deleting them in another. Dive highlights these changes in red or green. If a file appears in a layer and is later deleted, it is still consuming space in your final build.

Step 3: Implementing multi-stage builds

The most effective way to eliminate bloat is by using multi-stage builds. This approach ensures that build-time dependencies (like compilers or header files) never make it into the final runtime image.

Modify your Dockerfile to use a build stage:

# Stage 1: Build dependencies
FROM python:3.11-slim AS builder
RUN apt-get update && apt-get install -y gcc g++
COPY requirements.txt .
RUN pip install --user -r requirements.txt

# Stage 2: Runtime image
FROM python:3.11-slim
WORKDIR /app
# Copy only the installed packages from the builder
COPY --from=builder /root/.local /root/.local
COPY . .
ENV PATH=/root/.local/bin:$PATH
CMD ["python", "script.py"]

Build the new image:
docker build -t lean-image:latest .

Run dive again on lean-image:latest. You will observe a significant reduction in the "Efficiency Score" and total size, as the gcc and g++ compilers were discarded at the end of the builder stage.

Step 4: Minimizing layers with build techniques

Every RUN command in a Dockerfile creates a new layer. You can reduce the number of layers by chaining commands. This is particularly useful for package management.

Instead of:

RUN apt-get update
RUN apt-get install -y git
RUN apt-get clean

Use:

RUN apt-get update && \
    apt-get install -y git && \
    apt-get clean && \
    rm -rf /var/lib/apt/lists/*

Combining these into a single RUN instruction ensures that the temporary files created by apt-get are removed within the same layer, preventing them from being committed to the final image.

Photo by Anne Nygård on Unsplash

Common pitfalls

The .dockerignore oversight: If you don't use a .dockerignore file, you are likely copying local .git directories, virtual environments, or __pycache__ folders into your image. Always add these to a .dockerignore file in your root directory to keep the build context clean.
Over-reliance on apt-get clean: While cleaning package lists is good practice, it is secondary to avoiding the installation of unnecessary tools entirely. Always prefer distroless or alpine base images if your application dependencies allow it.
Ignoring cache mounts: If you are using BuildKit (enabled by default in modern Docker), use --mount=type=cache for your package manager (e.g., pip or apt). This caches files during the build but does not include them in the resulting image layer, significantly speeding up subsequent builds without bloating the output.

Conclusion

Bloated containers are a silent tax on your data engineering velocity. By inspecting layers with dive and enforcing multi-stage builds, you ensure that your production artifacts contain only what is strictly necessary to execute your code. This not only speeds up deployment cycles but also improves the security posture of your infrastructure by reducing the number of binaries and libraries present in your environment.

Try it: Take a current production Dockerfile that you suspect is oversized, run dive on it today, and identify the single largest layer. Then, apply a multi-stage build pattern to isolate that layer's dependencies and compare the final image size.

Cover photo by Rinson Chory on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Photon is not a magic speed button for every Spark query

Aniket Abhishek Soni — Mon, 02 Nov 2026 03:09:41 +0000

Why this matters: Photon is a vectorized query engine that replaces portions of the standard Spark JVM-based execution with C++. Understanding when it triggers and where it fails prevents wasted compute spend on workloads that gain zero performance benefit from the engine.

The most persistent myth in the Databricks ecosystem is that switching to a Photon-enabled runtime automatically makes every Spark job run faster. It is easy to see why: "Photon" sounds like a performance upgrade, and the marketing literature promises significant speedups for ETL and SQL. However, Photon is not a universal accelerator. It is a specialized engine designed for specific types of data processing. When your workload falls outside its supported surface area, your cluster spends time overhead-mapping Spark operations to Photon kernels for no gain, or worse, falling back to the standard JVM execution path mid-query.

Prerequisites

To follow this tutorial, you need:

A Databricks workspace with a cluster running Databricks Runtime (DBR) 11.3 LTS or higher.
The databricks-runtime provides Photon by default on supported instance types.
Access to the Spark UI to inspect query plans.
pyspark installed locally if you wish to mock the data, though the query profile analysis must be performed on a cluster with Photon enabled.

Photo by Lightsaber Collection on Unsplash

Step 1: Generating a workload that Photon loves

Photon shines brightest when processing large volumes of data using SQL-like operations: joins, aggregations, and scanning columnar formats like Parquet or Delta. It is designed to minimize the overhead of the Java object model by processing data in a vectorized, columnar format in native memory.

Consider this illustrative scenario where we join two large tables. Photon will attempt to vectorize the join and the subsequent aggregation.

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum

# Create a local SparkSession for testing
spark = SparkSession.builder.appName("PhotonTest").getOrCreate()

# Create hypothetical data
df_sales = spark.range(1000000).selectExpr("id as sales_id", "cast(rand()*100 as int) as region_id", "cast(rand()*1000 as double) as amount")
df_regions = spark.range(100).selectExpr("id as region_id", "concat('Region_', cast(id as string)) as region_name")

# A classic join and aggregate query
result = df_sales.join(df_regions, "region_id") \
    .groupBy("region_name") \
    .agg(sum("amount").alias("total_sales"))

result.explain()

Step 2: Inspecting the Query Profile

The explain() output in your notebook provides a glimpse, but the Spark UI is where you confirm Photon’s activity. When you run the above code on a Photon-enabled cluster, open the Spark UI and navigate to the SQL / DataFrame tab.

Click on the query description. You are looking for the "Photon" indicators:

Operator Names: Look for operators prefixed with "Photon" (e.g., PhotonScan, PhotonHashAggregate, PhotonShuffleExchange). If you see these, the engine is actively executing that node.
Physical Plan: If you see standard Spark operators (like HashAggregate instead of PhotonHashAggregate), Photon is not handling that specific operation.

If your query is entirely standard Spark, the "Photon" column in the SQL tab will be blank or indicate 0% usage. This is your primary diagnostic tool: if your expensive jobs show 0% Photon usage, you are paying for the Photon-enabled instance type without getting the benefit.

Step 3: Identifying the fallback mechanism

Photon does not support every expression or data type in the Spark ecosystem. When it encounters an unsupported operation (like a custom UDF that requires the JVM), it performs a "fallback."

In the Spark UI, look for the transition points. If a large portion of your DAG is "Photon" but a specific stage reverts to standard JVM operators, you have found a bottleneck. Often, this is caused by:

Python UDFs: Photon cannot run Python code directly.
Complex Types: Certain nested types or specific custom serialization formats.
Unsupported SQL functions: Rare, but specific legacy Spark functions may not have a native C++ implementation yet.

Step 4: Testing for "Photon-hostile" code

To see Photon get bypassed, run a query that relies heavily on a Python UDF.

from pyspark.sql.functions import udf
from pyspark.sql.types import StringType

# A Python UDF forces a transition to the JVM/Python process
@udf(returnType=StringType())
def expensive_py_udf(val):
    return f"processed_{val}"

df = spark.range(100000).select(expensive_py_udf(col("id")).alias("transformed"))
df.collect()

When you view the query profile for this, you will notice the plan lacks the Photon prefix on the projection operator. By introducing the UDF, you effectively "de-vectorized" that part of the pipeline.

Photo by Markus Spiske on Unsplash

Common pitfalls

Instance Type Misalignment: Photon is not available on all instance types. If you select an instance type that is not optimized for it, the cluster will start, but Photon will be disabled. Always check the Databricks documentation for your specific cloud provider's Photon-supported instance types.
Over-reliance on UDFs: If your entire ETL pipeline is wrapped in Python UDFs to perform standard transformations, you have effectively neutered Photon. Where possible, rewrite UDFs using native Spark SQL functions (pyspark.sql.functions). Native functions are heavily optimized for Photon.
Small Data overhead: For very small datasets, the overhead of moving data into the Photon native memory space can sometimes make the query slightly slower than the standard JVM engine. Do not optimize for Photon if your data fits in a single partition and runs in milliseconds.
Assuming 100% coverage: You will rarely see 100% Photon usage in complex pipelines. Aim for "Photon-heavy" segments in your largest data-shuffling stages.

Conclusion

Photon is a powerful tool for large-scale data processing, but it is not a "set it and forget it" feature. It requires you to write "Photon-friendly" code—avoiding custom UDFs in favor of native SQL expressions and ensuring your data joins and aggregations are well-structured.

Use the Spark UI as your source of truth. If you see the "Photon" prefix in your query plan, you are getting the speedup. If you see the standard JVM operators, investigate the node and determine if a UDF or an unsupported function is forcing the engine to fall back.

Try it: Take one of your existing, high-latency SQL pipelines, run it on a Photon-enabled cluster, and inspect the Spark UI. Identify the stages that are not running as Photon, and attempt to replace any UDFs in those stages with native Spark SQL functions. Observe the change in the Spark UI execution plan.

Cover photo by Trevor Kay on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Don't treat Fabric data ingestion as a one-size-fits-all problem

Aniket Abhishek Soni — Sat, 31 Oct 2026 13:07:16 +0000

The persistent myth in modern data architecture is that there is a "gold standard" for ingesting data into a lakehouse. You will often hear that one method—usually the one with the lowest latency—is inherently superior to the others. In reality, choosing between OneLake shortcuts, mirroring, and copy jobs is not a quest for the "best" tool, but an exercise in managing technical trade-offs between governance, compute overhead, and data freshness.

The contenders

When building a data platform in Microsoft Fabric, you have three primary ways to land data in your Lakehouse:

OneLake shortcuts: These are symbolic links that point to data stored in ADLS Gen2, Amazon S3, or other Fabric workspaces. You aren't moving the data; you are creating a transparent reference.
Mirroring: A high-frequency, automated replication process that keeps your operational data (like Azure SQL Database or Cosmos DB) in sync with OneLake in near real-time, specifically optimized for analytical workloads.
Copy jobs (Data Pipelines/Dataflows): The classic ETL/ELT approach. You define an activity to read from a source and write to a destination. The data is physically moved, transformed, and saved as Delta tables.

Photo by Steve A Johnson on Unsplash

Governance and security scope

The most significant differentiator is the "blast radius" of your security model.

Shortcuts are identity-agnostic at the point of ingestion. Because the data stays in its source location, you must manage permissions in two places: the source system and the Fabric workspace. If you use a shortcut, you are essentially extending the governance perimeter of your Fabric workspace to include the source cloud storage.

Mirroring, however, is a first-class citizen within the Fabric governance model. Once mirrored, the data resides within OneLake as Delta tables. You can apply Fabric-native security, such as sensitivity labels and column-level security, directly on the mirrored items.

Copy jobs offer the most granular control. Because you are explicitly moving the data, you can apply data quality checks, PII masking, or schema enforcement during the copy process. If your regulatory environment requires an immutable audit trail of exactly what was moved and when, the explicit nature of a copy job is easier to document for compliance than a background sync process.

Performance and operational overhead

Performance is often conflated with "how fast the data arrives." In reality, we must look at how the data performs under query load.

Shortcuts are essentially free in terms of ingest compute. You incur no data transfer costs within the same region, and there is zero latency. However, if the underlying storage format is not optimized for analytical queries (e.g., a massive collection of small JSON files), your queries will suffer when hitting the shortcut directly via Direct Lake or Spark.

Mirroring is purpose-built for performance. It converts operational data into a Delta format optimized for the engine. The trade-off is the overhead on the source system. While Microsoft designs mirroring to minimize impact, you are still running a replication engine that must track changes in the source.

Copy jobs are the most resource-intensive. They require compute (Spark or Data Integration pipelines) to perform the move. If you have a massive dataset, you are paying for the compute to read, serialize, and write that data. The benefit is that you can optimize the schema (e.g., partitioning, Z-ordering) during the copy, which can significantly improve performance for downstream Power BI reports using Direct Lake.

Failure modes and observability

Engineering resilience requires us to assume that the pipeline will fail.

Copy jobs are the most observable. They provide standard logging in the Fabric pipeline monitoring tab. You can retry individual activities, set up email alerts on failure, and build sophisticated dependency chains. If a copy job fails, you know exactly which batch of data failed to load.

Mirroring is a "black box" by design. It is meant to be set-and-forget. While you can monitor the status of the mirror, you have less control over the granular execution. If the replication encounters a schema conflict or a source-side permission issue, your ability to "patch" the data in transit is limited.

Shortcuts have the fewest failure modes because they aren't "running" anything. If the source data is deleted or the credentials for the shortcut expire, the shortcut breaks. This is a binary failure: the data is either there or it isn't.

Summary of trade-offs

Feature	Shortcuts	Mirroring	Copy Jobs
Data Movement	None (Reference)	Replication	Physical Move
Latency	Near-zero	Near-real-time	Batch/Triggered
Transformability	None	Limited	High
Complexity	Low	Low (Config)	High (Pipeline)
Governance	Dual-system	Fabric-native	Fabric-native

Photo by Brecht Corbeel on Unsplash

How to measure it yourself

Before committing to one approach, test the end-to-end query performance of your target dataset.

Create a sample dataset in your source system.
Ingest it via all three methods into three separate Lakehouses.
Use the following Spark snippet in a Fabric notebook to measure the time it takes to perform a simple aggregation:

# Illustrative benchmark: Measure read time for a table
import time

start = time.time()
df = spark.read.table("lakehouse_name.table_name")
count = df.count()
end = time.time()

print(f"Read and count took: {end - start} seconds")

Repeat this for your most common analytical queries (e.g., joins, filters on high-cardinality columns). If your query performance is identical across all three, prioritize the method that provides the lowest operational burden (usually shortcuts). If your reports are sluggish, you have the evidence needed to justify the overhead of a copy job or mirroring.

What I'd pick, and why

I default to OneLake shortcuts whenever possible. If the data is already in Parquet or Delta format in ADLS Gen2, there is rarely a technical justification for moving it. Why pay for compute to move bytes that are already accessible?

I opt for Mirroring when the source is a transactional database (like Azure SQL) and the business requirement is "near-real-time" analytics. Mirroring removes the complexity of managing custom Change Data Capture (CDC) pipelines.

I reserve Copy jobs for when the data requires "cleaning." If your source system has messy, denormalized, or non-compliant data, use a copy job to transform it into a high-performance, governed structure in your Lakehouse. Never use a copy job simply to "move" data that is already in a compatible format; you are only adding points of failure and unnecessary compute costs to your architecture.

Cover photo by Milad Fakurian on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

How Window Functions Actually Scale (or Don't)

Aniket Abhishek Soni — Fri, 30 Oct 2026 03:34:29 +0000

Most data engineers treat window functions as "black box" syntax that magically computes aggregates without collapsing rows, but they are actually the primary cause of unoptimized shuffles in production pipelines.

The common assumption is that OVER(PARTITION BY ... ORDER BY ...) is just a way to write cleaner SQL. In reality, you are describing a specific physical execution plan that forces your query engine to reorganize data across a cluster. If you don't understand the physical shuffle cost, you are essentially gambling with your compute budget.

How it actually works

When you execute a window function, the engine performs a "window sort" or a "window shuffle." Consider a hypothetical dataset of user transactions distributed across a cluster.

In Apache Spark, for example, the WindowSpec defines how data is grouped and ordered before the function (like SUM or ROW_NUMBER) is applied. If you write:

SELECT 
  user_id, 
  transaction_date, 
  SUM(amount) OVER (PARTITION BY user_id ORDER BY transaction_date) as running_total
FROM transactions

The engine must ensure that all records for a specific user_id land on the same executor node. This is a wide transformation. The data is partitioned by user_id, and then sorted locally within each partition by transaction_date.

According to the Apache Spark SQL documentation, if your partition key has high cardinality (e.g., billions of unique user IDs), the shuffle process will involve moving nearly the entire dataset over the network. If your data is already clustered (e.g., using CLUSTER BY in Snowflake or Z-ORDER in Delta Lake), the engine might skip the shuffle, but the documentation warns that the optimizer's ability to leverage existing distribution depends heavily on the specific join or window conditions matching your physical data layout.

If you are using PostgreSQL, the mechanics differ. PostgreSQL uses a "window aggregate" node in its execution plan. It performs a sort operation if the data isn't already ordered by the partitioning and ordering keys. If the dataset exceeds work_mem, PostgreSQL will spill the sort to disk (the temp_files statistic in pg_stat_database will reveal this).

Photo by Filip Pižl on Unsplash

Design choices and tradeoffs

The most significant tradeoff in window functions is the "All-or-Nothing" memory pressure.

When you use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, the engine must maintain a buffer of all rows in the partition that satisfy the condition. If your window partition is massive—for instance, an entire year of data for a single high-activity account—the executor or the database worker must hold that state in memory.

In distributed systems, this leads to the "Skewed Partition" problem. If 99% of your data belongs to one partition key, one executor handles 99% of the work while the others sit idle. The query finishes as fast as the slowest, most overloaded node.

Another subtle trap is the interaction between window functions and predicate pushdown. Many users assume that putting a WHERE clause after a window function is efficient. However, because window functions operate on the result set of the preceding operations, the engine often cannot push filters through the window node. You are essentially asking the database to perform a window calculation on the entire set before discarding the rows you didn't actually need.

Always check your execution plan (using EXPLAIN ANALYZE in PostgreSQL or the Spark UI's SQL tab) to see if a Filter node appears after the Window node. If it does, you are paying the full price for the calculation of rows that will never reach the final output.

Photo by Conny Schneider on Unsplash

When to reach for it (and when not to)

Use window functions when the logic is inherently relative. Deduplication (using ROW_NUMBER()), identifying gaps in sequences, and calculating running totals are the canonical use cases where the code readability is worth the performance cost.

However, do not use window functions as a replacement for joins when you need to calculate a global constant. A common anti-pattern is:

-- Don't do this
SELECT 
  amount, 
  SUM(amount) OVER () as total_amount
FROM transactions

This forces the engine to calculate a global sum using a window shuffle. It is significantly more performant to calculate the sum in a separate CTE or subquery and join it, or use a window function only if you specifically need the per-row context.

Avoid window functions entirely in high-frequency streaming applications if the state must be maintained over long durations. If your window requires a long "lookback" (like a 30-day running average), the state management cost in systems like Apache Flink or Spark Structured Streaming can lead to checkpointing bottlenecks. In those cases, it is often better to pre-aggregate data in smaller time buckets and join those buckets.

Conclusion

Window functions are not free primitives; they are explicit instructions to re-partition and sort your data. By understanding that PARTITION BY is a physical data movement command, you can spot potential bottlenecks before they manifest as failed jobs or runaway warehouse costs. Always verify your execution plan: if you see a shuffle or a disk-spill warning, your window function is likely doing more work than your business logic requires.

Cover photo by Jandira Sonnendeck on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Signing Container Images With Cosign for Secure Data Pipelines

Aniket Abhishek Soni — Thu, 29 Oct 2026 03:21:02 +0000

Most engineers treat container registries as glorified file servers, assuming that if a tag exists, it is the one they built. This is a dangerous oversight in any environment handling sensitive data. Relying on image tags alone provides no guarantee that the image running in your orchestration layer is the same bits-and-bytes package that passed your CI suite.

If you aren't cryptographically signing your container images, your data pipelines are vulnerable to man-in-the-middle attacks, registry poisoning, and unauthorized image swapping. Signing images with Sigstore Cosign allows you to attach a verifiable digital signature to your image in the container registry, ensuring that only trusted artifacts reach your production environment.

Prerequisites

To follow this tutorial, you need the following:

Docker: Version 20.10 or later.
Cosign: Installed via brew install cosign or the official binary from the Sigstore documentation.
A Container Registry: A registry that supports OCI artifacts (e.g., Docker Hub, GitHub Container Registry, or AWS ECR).
Environment: A local machine with Docker daemon access and an authenticated session to your registry (docker login).

We assume you are using standard Linux-based images. This guide focuses on signing with local key pairs, though Cosign supports Keyless signing using OIDC identities, which is recommended for mature CI/CD pipelines.

Photo by catcher xuefeng on Unsplash

Step 1: Generate a signing key pair

Before you can sign an image, you need a private key to perform the signing and a public key to verify it. Cosign generates these as a pair.

Run the following command to generate a key pair:

cosign generate-key-pair

This will create two files: cosign.key and cosign.pub.

cosign.key: The private key used for signing. Keep this secret. In a real CI environment, you would store this in a secret manager like AWS Secrets Manager, GitHub Secrets, or HashiCorp Vault.
cosign.pub: The public key used for verification. This is what your deployment infrastructure (like a Kubernetes admission controller or a CI gate) will use to check if an image is legitimate.

Step 2: Build and push a hardened image

For this example, imagine a standard Python-based data processing image. Use a multi-stage Dockerfile to keep the footprint minimal and the security posture strong.

Create a Dockerfile:

# Use a pinned version for reproducibility
FROM python:3.11-slim-bookworm AS builder

# Prevent python from writing pyc files and enable unbuffered logging
ENV PYTHONDONTWRITEBYTECODE=1
ENV PYTHONUNBUFFERED=1

WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir --user -r requirements.txt

# Final stage
FROM python:3.11-slim-bookworm
RUN groupadd -r appuser && useradd -r -g appuser appuser
WORKDIR /app
COPY --from=builder /root/.local /home/appuser/.local
COPY --chown=appuser:appuser . .

USER appuser
HEALTHCHECK --interval=30s --timeout=3s \
  CMD curl -f http://localhost:8080/health || exit 1

ENTRYPOINT ["python", "main.py"]

Build and push the image:

docker build -t <your-registry>/<your-repo>:<tag> .
docker push <your-registry>/<your-repo>:<tag>

Step 3: Sign the image

Now that the image is in the registry, you can sign it. Cosign stores the signature as a separate OCI artifact in the same registry, linked to the image digest.

Execute the following command to sign the image:

cosign sign --key cosign.key <your-registry>/<your-repo>@<digest>

Note that we are using the digest (@sha256:...) rather than the tag. Tags are mutable and can be pointed to different images; digests are immutable content hashes. Always sign the digest to ensure you are signing the exact bytes you tested.

You can verify that the signature was pushed by checking your registry's UI or by running:

cosign triangulate <your-registry>/<your-repo>:<tag>

Step 4: Verify the signature before deployment

This is the most critical step for your CI/CD pipeline. Whether you are running a standalone script or a Kubernetes cluster, you must block any execution if the image signature doesn't match your cosign.pub file.

To verify manually:

cosign verify --key cosign.pub <your-registry>/<your-repo>:<tag>

If the signature is valid, Cosign returns the signature metadata and exits with code 0. If someone has replaced the image, or if the image was not signed with the corresponding private key, the command will fail and exit with a non-zero code.

In your CI pipeline (e.g., GitHub Actions), you should include this as a mandatory step:

- name: Verify Image Signature
  run: cosign verify --key cosign.pub <your-registry>/<your-repo>:<tag>

If this step fails, the pipeline stops, and no vulnerable or unauthorized code is deployed.

Photo by Ankit Manoharan on Unsplash

Common pitfalls

Signing tags instead of digests

As mentioned, tags are mutable. If you sign my-image:latest, an attacker can push a new image to the latest tag, and the registry will point to it. While the signature on the previous image remains, the latest tag becomes untrusted. Always use the @sha256: digest in your signatures and your deployment manifests.

Key management

The cosign.key file is your root of trust. If this is leaked, an attacker can sign malicious images that your pipeline will blindly accept. Never commit cosign.key to source control. Use your CI provider’s secret storage. For more advanced setups, look into Cosign’s support for KMS to keep your keys in hardware security modules.

Ignoring verification failures

A signature check is only effective if your deployment process strictly enforces it. If your deployment scripts continue execution even after a verification failure, you have provided a false sense of security. Ensure your automation scripts use set -e or equivalent error handling to halt immediately upon a non-zero exit code from cosign verify.

Conclusion

Cryptographic signing is not an optional feature for regulated environments; it is the baseline for supply chain integrity. By moving from "trusting the registry" to "verifying the signature," you eliminate the risk of image tampering and ensure that your data pipelines run exactly what you intended.

Try it: Generate a local key pair, sign a "Hello World" image, and intentionally change the Dockerfile to break the hash. Run the verification command and observe how the tool correctly identifies the mismatch between the signed digest and the modified image.

Cover photo by Daniel Miksha on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Is Your Databricks Job Still Using Your Personal Access Token?

Aniket Abhishek Soni — Wed, 28 Oct 2026 11:58:05 +0000

Why this matters: Hardcoded personal access tokens are a primary vector for privilege escalation and data exfiltration in managed data environments. Moving to Service Principals decouples authentication from individual identities, ensuring that pipeline access persists beyond the lifecycle of any single user account.

Consider a hypothetical pipeline that syncs data from a third-party API into a Delta Lake table. The job is configured to run on a cluster using a Python script that requires an API key. For months, the job runs perfectly. Then, the engineer who originally set up the job leaves the organization. Three days later, the job fails with a generic 401 Unauthorized or 403 Forbidden error, despite the code remaining unchanged.

This is the "Orphaned Token" anti-pattern. When a user account is deactivated, any Personal Access Token (PAT) associated with that account is immediately revoked, silently breaking every downstream automation tied to that identity.

Symptoms

An engineer investigating this failure often sees the following:

The "It Works on My Machine" trap: The pipeline runs successfully when executed manually by a senior engineer with broad permissions, leading to the false conclusion that the issue is transient or related to "network blips."
Confusing Logs: Spark logs might show java.io.IOException or com.databricks.backend.common.rpc.DatabricksSqlException with cryptic authentication messages.
Silent Failures: If the code doesn't explicitly log the full stack trace of the authentication error, the job might just hang while waiting for an API response that will never arrive.

Photo by Lu on Unsplash

1. Finding the Hardcoded Secret

The most common cause is a string literal hidden in a configuration file or a dbutils call. To confirm, check the job configuration in the Databricks workspace.

How to confirm:
Search the codebase for dbutils.secrets.get calls or, worse, raw strings like api_key = "secret123". If you are using Databricks Asset Bundles (DABs), check your databricks.yml for environment variables that might be pulling from local files rather than the Databricks Secret Manager.

How to fix:
Stop referencing secrets directly in your code. Instead, use a Secret Scope. If you are on Databricks, use the Databricks Secret CLI to create a scope and add the secret:

# Create a scope backed by Databricks
databricks secrets create-scope my-app-scope

# Add the secret (do this interactively to avoid shell history logging)
databricks secrets put-secret my-app-scope my-api-key

2. The Identity Mismatch (Personal vs. Service Principal)

Even if you use secret scopes, the job might still be running under the execution context of a user. If the cluster is configured to use "Single User" mode, it inherits the identity of the user who started the cluster.

How to confirm:
Check the Job Run details. Does it list an individual user as the "Run As" identity? In a secure architecture, this should always be a Service Principal.

How to fix:
Create a Service Principal in your cloud provider (e.g., Entra ID, AWS IAM) and grant it the necessary permissions in your Unity Catalog or Hive Metastore. Then, update your Databricks Job definition to use this Service Principal for the run.

If using Databricks Asset Bundles, configure the run_as block in your bundle.yml:

resources:
  jobs:
    my_sync_job:
      name: sync-api-to-delta
      run_as:
        service_principal_name: <application-id-of-service-principal>
      tasks:
        - task_key: main
          spark_python_task:
            python_file: ./src/sync.py

3. Scope ACLs and Permissions

Sometimes the job fails because the Service Principal lacks the "READ" permission on the secret scope itself.

How to confirm:
Run a test script using the Service Principal’s token (or via a job run as the SP) to access the secret:

# Test script to verify secret access
try:
    secret_value = dbutils.secrets.get(scope="my-app-scope", key="my-api-key")
    print("Access successful")
except Exception as e:
    print(f"Access denied: {e}")

How to fix:
Grant the Service Principal access to the scope using the Databricks CLI:

databricks secrets put-acl my-app-scope <service-principal-application-id> READ

4. Environment Variable Injection

If you are using Docker containers or custom Spark configurations, you might be injecting secrets as environment variables in the cluster config. This is dangerous because these values are often visible in the Spark UI "Environment" tab to anyone with "Can View" access to the cluster.

How to fix:
Instead of setting MY_API_KEY in the Spark Config, keep it in the Secret Scope. Update your Python code to fetch it at runtime:

# Use the Databricks utility to fetch the secret at runtime
# This keeps the secret out of the Spark UI environment variables
api_key = dbutils.secrets.get(scope="my-app-scope", key="my-api-key")

Preventing it

To prevent these issues from recurring, implement these systemic safeguards:

Enforce "Run As" Policies: Configure your CI/CD pipelines to reject any Databricks Job definition that does not specify a run_as block pointing to a Service Principal.
Linting: Use custom scripts in your CI pipeline to scan your Python code for patterns like os.environ.get("SECRET_KEY") or hardcoded strings that look like API keys.
Unity Catalog: Move away from legacy secret scopes where possible and leverage Unity Catalog’s external locations and credentials. By using Unity Catalog, you can grant the Service Principal direct access to cloud storage (S3/ADLS/GCS) without ever needing to manage a secret key for the storage layer itself.
Audit Logs: Regularly review Databricks Audit Logs for jobs running as individual users, and use that list to target your migration efforts.

By removing the reliance on personal tokens, you move your platform from a state of "fragile convenience" to "hardened automation." The goal is for your pipelines to be identity-agnostic, running with the same permission set regardless of who triggered the deployment.

Tags: #databricks #security #dataengineering #cicd

Cover photo by Stephen Hickman on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

How to Integrate SQL Databases with Fabric Lakehouses

Aniket Abhishek Soni — Tue, 27 Oct 2026 11:51:01 +0000

Why this matters: Data platforms often suffer from the "latency-vs-governance" trade-off, where transactional data sits in a silo away from analytical engines. By orchestrating SQL databases alongside Fabric Lakehouses, you can maintain transactional integrity while enabling high-performance analytics without complex ETL re-engineering.

Many engineers treat the Lakehouse as a monolithic destination for all data, leading to complex ingestion pipelines just to view simple operational state. Consider a hypothetical scenario where you have a primary application database (e.g., Azure SQL Database) and an analytical workspace in Fabric.

The "Before" approach involves heavy ELT:

-- The "Before" trap: Staging everything as raw Parquet
COPY INTO 'abfss://<container>@<storage>.dfs.core.windows.net/raw/'
FROM dbo.Transactions
WITH (FILE_TYPE = 'PARQUET');

The "After" approach, leveraging Fabric’s ability to interact with external SQL sources via shortcuts or Mirrored Databases, allows for a more streamlined architecture. You keep your operational schema where it lives, exposing it to the Lakehouse for unified semantic modeling without constant full-load migrations.

Prerequisites

A Microsoft Fabric workspace with a capacity (Fabric trial or SKU) assigned.
An existing Azure SQL Database (or SQL Server 2022 instance) with public or private endpoint connectivity.
Appropriate permissions: "Contributor" or "Administrator" on the Fabric workspace and "db_datareader" on the source SQL database.
Knowledge of the Microsoft Fabric Mirroring feature (currently in public preview for Azure SQL Database).

Photo by GuerrillaBuzz on Unsplash

Step 1: Establishing a Mirrored Database in Fabric

Instead of writing scripts to move data, you can use the Mirroring feature to create a replica of your SQL database directly within your Fabric workspace. This process creates a specialized item that synchronizes data near real-time from your source to the OneLake.

Navigate to your workspace, select New, and choose Mirrored Azure SQL Database. You will need the server name and database name. Once configured, Fabric manages the synchronization process.

-- This is handled by the Fabric Mirroring engine
-- Once linked, you can query via T-SQL in the Fabric SQL Analytics endpoint
SELECT TOP 10 * FROM [dbo].[Transactions]
WHERE [TransactionDate] >= DATEADD(day, -7, GETDATE());

This creates a mirrored representation of your data in Delta format inside OneLake, which is automatically cataloged in the Fabric metastore.

Step 2: Referencing Operational Data in Lakehouse Notebooks

Once the mirrored data is available in OneLake, you can join it with other tables in your Lakehouse using PySpark. This is particularly useful for data quality checks where you need to compare transactional totals against landing zone aggregates.

# Accessing the mirrored table using the SQL Analytics endpoint path
df_mirrored = spark.read.format("delta").table("dbo.Transactions")

# Perform a quick validation check
row_count = df_mirrored.count()
print(f"Total records in transactional mirror: {row_count}")

# Hypothetical quality check: ensure no null IDs
if df_mirrored.filter("TransactionID IS NULL").count() > 0:
    raise ValueError("Data quality check failed: Null IDs detected.")

Step 3: Enabling Direct Lake for Operational Reporting

The power of having this operational data in Fabric is the ability to use Direct Lake mode for your semantic models. Direct Lake allows Power BI to read the Delta files directly from OneLake without importing them into the model or requiring a DirectQuery gateway, providing performance comparable to Import mode with the freshness of DirectQuery.

In your workspace, create a Semantic model.
Select the mirrored table as a source.
Ensure the storage mode is set to Direct Lake.
Define your DAX measures:

-- DAX measure for operational monitoring
Total Sales = SUM('Transactions'[Amount])

-- Daily average with time-intelligence
Daily Avg Sales = 
CALCULATE(
    AVERAGE('Transactions'[Amount]),
    DATESYTD('Date'[Date])
)

Photo by Brecht Corbeel on Unsplash

Common pitfalls

Schema Evolution: When using Mirroring, be aware that changes to the source schema might take a moment to propagate. Always validate your downstream Power BI reports after significant DDL changes on the source SQL database.
Capacity Throttling: Mirroring consumes compute resources within your Fabric capacity. If you have high-frequency transactional data, monitor your "Fabric Capacity Metrics app" to ensure you aren't hitting utilization limits during peak sync periods.
Permission Mismatches: Ensure that the service principal or user identity used to create the connection has consistent access. A common failure pattern is having "Read" access on the SQL Database but failing to configure the Fabric workspace identity for cross-region data access.
Preview Limitations: Features like "Mirroring" are subject to change. Always check the official Microsoft Learn documentation for the most current limitations, such as supported data types or specific SQL features that might not be replicated.

Conclusion

By treating the SQL database as a first-class citizen of the Lakehouse rather than a source to be "dumped," you reduce pipeline fragility and latency. You gain the ability to perform complex analytical validation on transactional data without duplicating the storage footprint unnecessarily.

Try it: Identify one operational table that is currently being extracted via a daily batch job. Attempt to enable the Mirrored Azure SQL Database feature in a sandbox workspace and see if you can replace that batch job with a Direct Lake semantic model.

Cover photo by Marc PEZIN on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

How to Package Shared Python Libraries with pyproject.toml

Aniket Abhishek Soni — Mon, 26 Oct 2026 05:16:11 +0000

You ship the job. It passes CI. Then, a colleague runs it in a notebook environment, and suddenly your shared utility library throws an ImportError because it pulled in a different version of pandas than the one used in your orchestration layer.

Many data teams still manage shared code by appending directory paths to sys.path or bundling messy setup.py scripts that haven't been updated since the mid-2010s. This is a fragile architecture that invites inconsistency. If you want a robust data platform, you should stop treating your internal tooling like an afterthought and start packaging it as a proper Python distribution using pyproject.toml.

Why the common approach falls short

The "copy-paste-folder" or "sys.path.append" strategy is an engineering debt bomb. When you rely on environment-level path hacks, you lose the ability to manage versioning, track dependencies, and enforce consistent execution environments across local development, CI/CD runners, and production compute clusters.

Legacy setup.py files are equally problematic. They rely on arbitrary code execution during the installation process, which is opaque, slow, and increasingly discouraged by the Python Packaging Authority (PyPA). If your build process requires a Python script to resolve its own environment, you’ve already lost control of your supply chain.

The transition to PEP 517 and PEP 621 standards, centered on the pyproject.toml file, moves configuration away from executable code into static metadata. This allows build tools to understand your project structure without running arbitrary code, leading to safer, more predictable builds.

Photo by Timelab on Unsplash

Standardizing with pyproject.toml

To build a modern shared library, you need to define your metadata and build backend in pyproject.toml. I recommend using hatchling or setuptools (>= 61.0.0). Here is a standard configuration that ensures your library is installable and its dependencies are tracked:

[build-system]
requires = ["setuptools>=61.0"]
build-backend = "setuptools.build_meta"

[project]
name = "data-utility-lib"
version = "0.1.0"
authors = [
  { name = "Data Platform Team", email = "platform@example.com" },
]
description = "Shared utilities for data pipelines"
requires-python = ">=3.9"
dependencies = [
    "pandas>=2.0.0",
    "pydantic>=2.0.0",
    "pyarrow>=12.0.0",
]

[project.optional-dependencies]
dev = [
    "pytest",
    "black",
    "ruff",
]

By defining these requirements in a static file, you enable tools like pip or uv to resolve the entire dependency graph before installation begins. This prevents the "dependency drift" that occurs when an environment installs one version of a library at the start of a pipeline and a conflicting version halfway through a dependency resolution chain.

Enforcing consistent environments

Once your library is packaged, you should treat it as a primary dependency in your project-specific environments. In your downstream data pipelines, you no longer rely on shared folders; you reference the library in your pyproject.toml or requirements.txt via a private package registry or a direct git URL.

When you install your library, you are pulling a defined artifact. If you need to upgrade a core dependency like pyarrow, you update the pyproject.toml in your library, bump the version, and propagate that change to your pipelines. This creates an auditable trail of who changed which dependency and when—a critical requirement for any regulated environment where data lineage and software provenance are non-negotiable.

Photo by Testalize.me on Unsplash

The objections (and my answers)

"But we have a small team, and packaging feels like overkill."

It feels like overkill until you spend four hours debugging why a UDF in a Spark job works locally but crashes on the cluster. Packaging your code forces you to define exactly what your code needs to run. If you can't state your dependencies explicitly in a pyproject.toml, you don't actually know what your code is doing.

"Won't this slow down our development velocity?"

Initially, yes. You have to learn how to version your internal library. However, the alternative is "dependency hell," where every engineer has a slightly different version of numpy or pandas in their local environment, leading to "works on my machine" syndromes that cost far more developer time than a formal packaging process ever will.

"What about internal package management?"

You don't need a complex Artifactory setup to start. You can install directly from a private Git repository using pip install git+https://github.com/org/repo@v0.1.0. Once your team matures, you can move to a simple private PyPI mirror, but the pyproject.toml format remains the constant that ensures your setup is compatible with modern ecosystem tooling.

Conclusion

Packaging your shared code is not just about "cleaner project structure"; it is about platform stability. By moving away from imperative setup.py scripts and loose file structures toward declarative pyproject.toml configurations, you transform your shared library from a liability into a reliable piece of infrastructure.

Stop appending to sys.path. Start building artifacts. Your pipelines—and your future self—will thank you when you can finally deploy with confidence.

Cover photo by Juanjo Jaramillo on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Stop mocking your database for integration tests

Aniket Abhishek Soni — Sun, 25 Oct 2026 15:18:36 +0000

Why this matters: Mocking database drivers in application tests creates a false sense of security that ignores SQL syntax errors, constraint violations, and transaction behavior. Using transient, containerized Postgres instances ensures your integration tests run against the same engine as production, catching schema-level failures before they reach your CI/CD pipeline.

Most data engineers start by mocking psycopg2 or SQLAlchemy calls. This is a trap. You end up testing your ability to mock a library rather than the integrity of your queries. The alternative—a dedicated "test" database—is often a fragile, manually managed instance that drifts from the production schema, leading to "works on my machine" failures.

The robust approach is to treat your database as a disposable, versioned component of your testing lifecycle.

How it actually works

The goal is to spin up a clean, ephemeral Postgres instance for every test run, apply migrations, execute your test suite, and destroy the container immediately after.

We use docker-compose to handle the lifecycle. This ensures that the network stack, environment variables, and volumes are consistent across local development and CI environments like GitHub Actions.

Here is a minimal docker-compose.yml configured for test automation:

services:
  postgres-test:
    image: postgres:16.3-alpine
    environment:
      POSTGRES_USER: test_user
      POSTGRES_PASSWORD: test_password
      POSTGRES_DB: test_db
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U test_user -d test_db"]
      interval: 5s
      timeout: 5s
      retries: 5
    ports:
      - "5432:5432"
    tmpfs:
      - /var/lib/postgresql/data

Understanding the configuration

tmpfs: This is critical. By mounting the data directory to tmpfs (RAM), we bypass disk I/O latency. Since the database is disposable, we don't care about persistence after the container exits.
healthcheck: Never assume a container is "running" just because the process started. pg_isready ensures the application code only attempts to connect once the Postgres socket is actually accepting signals.
image: Pinned to a specific digest or tag. Never use latest, as a minor update to the Postgres image could change default collation settings or extensions, breaking your tests unpredictably.

Automating schema and seeding

Your test runner should handle the migration logic. Using a tool like alembic (for Python) or flyway (for JVM), your test initialization script should look like this:

# 1. Start the container
docker compose up -d postgres-test

# 2. Wait for the healthcheck to pass
until docker inspect -f '{{.State.Health.Status}}' postgres-test | grep -q "healthy"; do
  sleep 1
done

# 3. Run migrations
alembic upgrade head

# 4. Run your test suite
pytest tests/integration/

# 5. Teardown
docker compose down -v

The -v flag in the teardown command is vital; it removes the anonymous volumes created by the container, ensuring the next test run starts from a truly blank slate.

Photo by Daniil Komov on Unsplash

Design choices and tradeoffs

The biggest downside of this pattern is CI latency. Spinning up a container, waiting for the OS to boot, and running migrations takes time. If you have hundreds of small integration tests, running this lifecycle for every single test is inefficient.

I recommend a test-suite-level teardown rather than a test-level one. Group your integration tests together so the container boots once, processes all tests, and shuts down at the end.

Another consideration is security. Running containers in CI requires careful management of the Docker daemon. If your CI runner is shared, ensure you are not running the container as root. While the Postgres image defaults to postgres user, if you extend the image, ensure your Dockerfile includes:

FROM postgres:16.3-alpine
# Create a dedicated user for the app
RUN addgroup -S appgroup && adduser -S appuser -G appgroup
USER appuser

Avoid mounting local directories into the container during testing. It introduces dependency on the host's file system structure. Keep your migrations inside the container image or copy them in during the build phase.

When to reach for it (and when not to)

Use ephemeral containers when:

You are performing complex joins or window functions where the SQL dialect nuances matter.
You need to verify that your migrations actually execute without conflict.
Your application uses specific Postgres extensions (e.g., pg_trgm or postgis) that are difficult to mock.

Do not use ephemeral containers when:

You are testing pure business logic that happens in memory. Those should be fast, unit-level tests.
The database is massive. If your "seed" data is gigabytes in size, the time taken to restore that state into a new container will turn your CI pipeline into a bottleneck. In such cases, consider using a permanent, shared test database and use transaction-level rollbacks to clean up individual test states.

Conclusion

Mocking databases is a legacy habit from an era when spinning up infrastructure was slow and painful. Today, Docker handles the isolation and orchestration for us. By treating your database container as a disposable, health-checked component, you bridge the gap between development and production, effectively turning your test suite into a real-world validation of your schema and query logic.

Start by isolating your database interactions into a controlled container lifecycle. Your future self, debugging a production migration failure that you already caught in CI, will thank you.

Cover photo by Kier in Sight Archives on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Stop Writing Custom File Listeners: Use Auto Loader Instead

Aniket Abhishek Soni — Sat, 24 Oct 2026 16:56:52 +0000

You ship the job. It passes CI. Then, a source system upstream changes its naming convention, or a vendor pushes a malformed JSON file at 3 AM. Your custom file listener, built on top of listStatus or a polling mechanism, silently skips the file or crashes the entire streaming micro-batch. You are now spending your morning hunting down missing records in S3 or ADLS.

The industry has spent years reinventing the wheel with custom foreachBatch logic and manual checkpointing to track which files have been processed. Databricks Auto Loader (cloudFiles) exists to solve exactly this by offloading the file discovery and state management to the cloud provider's native notification services.

Prerequisites

To follow this tutorial, you need:

A Databricks workspace (AWS, Azure, or GCP).
Access to a UC-enabled catalog to store the target Delta table.
A local environment or Databricks notebook with a Spark runtime (Databricks Runtime 10.4 LTS or higher is recommended for full feature support).
Basic permissions to read from a cloud storage bucket (e.g., S3, ADLS Gen2, GCS).

Photo by Logan Voss on Unsplash

Step 1: Configuring the Stream with CloudFiles

Instead of using the standard spark.readStream.format("json"), which requires you to provide a schema upfront and assumes a stable file list, we use cloudFiles. This instructs Databricks to use native file notification services (like SQS/SNS for AWS or Event Grid for Azure) to detect new files.

# Hypothetical setup: reading JSON files from a landing zone
cloud_source = "s3://your-landing-bucket/raw-data/"
checkpoint_path = "s3://your-checkpoint-bucket/checkpoints/raw-data/"

df = (spark.readStream
      .format("cloudFiles")
      .option("cloudFiles.format", "json")
      .option("cloudFiles.schemaLocation", checkpoint_path) # Essential for schema inference
      .load(cloud_source))

# This DataFrame is now lazily evaluated and ready to write

By setting cloudFiles.schemaLocation, you trigger Auto Loader’s schema evolution engine. It will infer the schema from the files and store it in the checkpoint location. When a new file arrives with a different structure, Auto Loader tracks those changes automatically.

Step 2: Rescuing Malformed Data

In regulated environments, you cannot simply drop data that doesn't match your schema. If a field changes type—for example, a transaction_amount shifts from an integer to a string—you need a way to capture the "bad" data without stopping the pipeline.

Enable the "rescued data" column to capture any data that cannot be parsed into your defined schema.

df_rescued = (spark.readStream
              .format("cloudFiles")
              .option("cloudFiles.format", "json")
              .option("cloudFiles.inferColumnTypes", "true")
              .option("cloudFiles.schemaEvolutionMode", "addNewColumns")
              .option("cloudFiles.rescuedDataColumn", "_rescued_data") # The magic column
              .load(cloud_source))

# Write to a Delta table
query = (df_rescued.writeStream
         .option("checkpointLocation", checkpoint_path)
         .table("main.raw.transactions"))

When a record fails to map to your inferred schema, it isn't discarded. Instead, it is packaged into the _rescued_data column as a JSON string. Your data analysts can then query SELECT * FROM main.raw.transactions WHERE _rescued_data IS NOT NULL to identify and fix upstream issues without the pipeline ever failing.

Step 3: Optimizing for Performance

Auto Loader defaults to using file notification mode (cloudFiles.useNotifications). This is the most efficient method because it avoids expensive directory listings. In large buckets with millions of files, a listStatus call (the standard Spark approach) can time out or incur significant API costs.

However, if you are testing locally or in a sandbox without sufficient cloud permissions to create notification resources, you can fall back to fileDiscoveryMode.

# Use this only if you cannot configure cloud notifications
df = (spark.readStream
      .format("cloudFiles")
      .option("cloudFiles.format", "json")
      .option("cloudFiles.useNotifications", "false") # Fallback mode
      .load(cloud_source))

Mechanism Check: When useNotifications is true, Databricks creates an SQS queue (on AWS) or Event Grid (on Azure) to listen for storage events. This means the ingestion latency is near-instant, regardless of how many files exist in the bucket. When false, Spark performs a recursive list of the directory, which scales linearly with the number of files, leading to performance degradation as your volume grows.

Step 4: Testing Schema Evolution

To verify that your pipeline handles structural changes, simulate a schema drift. Create a JSON file with an unexpected column and drop it into your landing directory.

# Illustrative: Writing a file to trigger schema evolution
import json

new_data = {"id": 101, "amount": 50.0, "new_feature_flag": True}
dbutils.fs.put(f"{cloud_source}/drift_test.json", json.dumps(new_data), True)

Because you set cloudFiles.schemaEvolutionMode to addNewColumns, the Delta table will automatically alter its schema to include new_feature_flag. If you prefer a stricter approach, use failOnLoad to force the job to stop when the schema changes, preventing "dirty" data from entering your production tables.

Photo by Tania Melnyczuk on Unsplash

Common pitfalls

Checkpoint location is not optional: Auto Loader stores the state of "seen" files in the checkpoint directory. If you delete this directory, the ingestion will restart from the beginning, leading to duplicate records in your Delta table. Ensure your checkpoint paths are managed with the same lifecycle policies as your production code.
Permission Bloat: In production, ensure the IAM role or Service Principal running the Databricks cluster has PutObject and ListBucket permissions for the storage, but also permissions to create/delete queues and subscriptions if using useNotifications. If these are restricted, the pipeline will fail at startup.
Over-inferring Types: Schema inference is convenient but not free. It samples the files to determine data types. On very large, messy datasets, this can add latency to the stream startup. If you know your schema, define it explicitly using cloudFiles.schema.
Case Sensitivity: Auto Loader is case-insensitive by default regarding file names, but schema inference can be sensitive to JSON key casing. Ensure your upstream source is consistent if you want to avoid fragmented column names (e.g., user_id vs User_ID).

Conclusion

Building custom file listeners is a liability. By leveraging Auto Loader, you shift the burden of file discovery and schema management to a managed service designed for scale. You get the benefits of native cloud notifications, automatic schema evolution, and a safety net for malformed data—all while keeping your Spark code readable and maintainable.

Try it: Configure a small test stream using cloudFiles against a dummy S3/ADLS bucket. Drop a malformed JSON file into the bucket and observe how the _rescued_data column captures the error without interrupting the stream. Then, change the schema and watch the Delta table evolve automatically.

Cover photo by Roger Starnes Sr on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.

Why are your field parameters breaking report performance?

Aniket Abhishek Soni — Fri, 23 Oct 2026 05:03:34 +0000

Consider a hypothetical pipeline that delivers high-fidelity financial metrics into a Microsoft Fabric Lakehouse. To make the data accessible to diverse end-users, an engineer implements Field Parameters in a Power BI semantic model, allowing users to toggle between "Gross Revenue," "Net Revenue," and "Tax Adjustment" measures, as well as swapping dimensions like "Region," "Legal Entity," and "Business Unit" on the fly.

The implementation works in Power BI Desktop. Once published to a Fabric workspace, however, users report that the report hangs indefinitely or returns "Resource Governing" errors, even though the underlying Direct Lake model should be lightning-fast.

Symptoms

The symptoms usually manifest as inconsistent query performance. Some users experience a complete timeout when selecting certain combinations of parameters, while others see a "Visual has exceeded the available resources" error message.

A common false lead is to assume the issue lies with the underlying storage mode. Because the semantic model uses Direct Lake, the knee-jerk reaction is to check for table fragmentation or poorly performing DAX measures. While those are valid concerns, the issue often stems from how the storage engine generates expanded queries when field parameters are used in complex visual filters or multi-level hierarchies.

Photo by Logan Voss on Unsplash

1. The "Column Cardinality Explosion" Trap

When you use a field parameter for a dimension, you are essentially telling the engine to dynamically switch the grouping context. If a user selects "Legal Entity" and then chooses a high-cardinality "Transaction ID" via another parameter, the query plan changes significantly.

How to confirm: Open Performance Analyzer in Power BI Desktop and run the problematic visual. Copy the DAX query generated and paste it into DAX Studio. Check the "Query Plan" and "Server Timings." If you see a massive number of storage engine (SE) requests or high "Total" time compared to "Formula Engine" (FE) time, you are likely hitting an aggregation wall.

How to fix: Limit the options in your field parameter to columns that share a similar cardinality. If you must include high-cardinality columns, ensure the field parameter is not used in a way that forces a Cartesian product in the underlying query. Use the SELECTEDVALUE function in your DAX measures to define explicit behavior when a user has not yet made a selection, reducing the engine's need to evaluate all possible combinations.

2. Inconsistent Data Types in Parameters

Field parameters act as a wrapper. If you mix columns with different data types—for example, a String (Region) and a Decimal (Revenue Amount)—the parameter table's Value column will default to a type that can accommodate both, often leading to implicit type conversion during query evaluation.

How to confirm: Inspect the calculated table created by the field parameter in the Model view. Check the data type of the Value column. If it is Variant or String when it should be numeric, your DAX measures will perform implicit conversions on every row, slowing down the engine.

How to fix: Ensure that all columns included in a single field parameter are of the same data type. If you need to mix measures and dimensions, create separate field parameters for each category. Keep your measure parameters and dimension parameters distinct to maintain clean DAX type casting.

3. Excessive Filter Context on Parameters

Users often attempt to use field parameters inside slicers that also have complex row-level security (RLS) or object-level security (OLS) applied. When a field parameter is used as a filter in a visual, the engine must evaluate the visibility of every item in the parameter list.

How to confirm: Check the "Relationship View" to see if the field parameter table is incorrectly linked to your fact tables in a way that creates circular dependencies or ambiguous paths. If your model has multiple active relationships between the parameter source and the fact table, the engine may default to a cross-filter that is far more expensive than necessary.

How to fix: Explicitly set the Cross-filter direction to Single for the relationship between your field parameter table and your model's dimension/fact tables. Avoid using field parameters in complex CALCULATE filters; instead, use them as the primary axes in your visuals, allowing the engine's native slicing mechanism to handle the filtering.

Photo by Growtika on Unsplash

4. DAX Measure "Bloat"

When you create a field parameter for measures, you are essentially creating a proxy for a set of existing DAX measures. If those original measures are not optimized, the field parameter acts as a force multiplier for inefficiency.

How to confirm: If the report is slow, check the DAX code of the measures being swapped. If they contain FILTER(ALL(Table), ...) patterns, they are likely performing full table scans every time the parameter changes.

How to fix: Refactor your measures to use KEEPFILTERS or SUMMARIZE patterns that leverage the model's existing relationships. For example:

-- Instead of:
Revenue_Measure = CALCULATE(SUM(Sales[Amount]), ALL(Sales))

-- Use the relationship-aware version:
Revenue_Measure = SUM(Sales[Amount])

By ensuring the underlying measures are efficient, you remove the burden from the field parameter's evaluation context.

Preventing it

Systemic safeguards are vital when scaling self-service reporting.

Semantic Model Documentation: Use the "Description" field in the Model view to explain the intent of each field parameter. If an engineer sees "Do not use in high-cardinality visuals," they are less likely to break the report.
Performance Budgeting: Treat every field parameter as an additional level of complexity in your query plan. If a report requires more than five field parameters, consider splitting the report into multiple pages, each with a focused purpose.
Validation with Semantic Link: If you are working in Fabric, use the sempy (Semantic Link) library in a notebook to validate your semantic model. You can programmatically check for redundant relationships or data type mismatches that often plague field parameter tables.

Field parameters are a powerful tool for user empowerment, but they require a rigorous approach to data modeling. By treating them as dynamic query generators rather than simple UI elements, you can prevent the performance degradation that typically follows their introduction.

Tags: #powerbi #microsoftfabric #analytics #dataengineering

Cover photo by Kevin Segal on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.