DEV Community: Aniket Abhishek Soni

Stop relying on hope: why data contracts need CI enforcement

Aniket Abhishek Soni — Wed, 14 Oct 2026 02:05:01 +0000

You ship the job. It passes CI. Your dbt tests show green. You push to production, grab a coffee, and feel like a competent engineer. Then, at 3:00 AM, the downstream dashboard team wakes up to a PagerDuty alert because your service decided that user_id should now be a string instead of an integer. The schema registry was updated, but nobody read the Slack announcement, and your pipeline just broke three critical financial reporting models.

This is the state of most data engineering teams today. We call it "coordination," but it’s really just a polite term for guessing what the other team did. Data contracts aren't just fancy YAML files; they are the only thing standing between you and a weekend of debugging JSON parsing errors.

The real question isn't whether you need contracts, but how you enforce them. You have two real paths: you either integrate the contract into your CI/CD pipeline using schema-as-code, or you rely on runtime checks that catch the fire after the house is already burning down.

The contenders

On one side, you have Schema Registry-based enforcement (e.g., Confluent/Avro or AWS Glue Schema Registry). This approach treats the contract as a gatekeeper at the ingestion layer. If the producer doesn't match the registry, the message is dropped or rejected at the broker level.

On the other, you have CI-level contract testing (e.g., Great Expectations, dbt-checkpoint, or custom JSON Schema validation in GitHub Actions). This approach forces the producer to validate their data against a versioned contract before the code is even allowed to merge. You catch the breaking change at the PR stage, not in the Kafka topic.

Photo by Eyasu Etsub on Unsplash

The cost of failure

If you use a Schema Registry, your failure mode is "data loss." A producer updates their service, the registry rejects the message because of a minor type mismatch, and your data is effectively incinerated at the source. In a healthcare context, this is a HIPAA nightmare—you are failing to capture data that might be clinically significant.

With CI-level enforcement, the failure mode is "developer friction." The CI build fails, the PR stays blocked, and the developer has to fix the contract or the code before they can ship. It’s annoying, but it’s predictable. I’d take a blocked PR over a production outage any day.

In terms of cost, Schema Registries incur a per-message latency tax. If you’re high-frequency trading or doing real-time IoT ingestion, that millisecond overhead adds up. CI checks are "free" in terms of production overhead because they execute during development. You pay in compute minutes on GitHub Actions or GitLab CI, but those costs are negligible compared to the time spent on manual schema reconciliation.

Operational burden and complexity

Schema Registries are a massive operational lift. You are now managing a stateful service. If your registry goes down or experiences a partition, your entire ingestion pipeline stalls. I’ve spent countless hours debugging Kafka producer configurations where the registry URL was misconfigured in the environment variables, leading to weird SerializationException errors that are nearly impossible to trace in production logs.

CI-level enforcement is significantly cleaner because it’s stateless. You define a contract in a contracts/ directory in your repository. In your .github/workflows/main.yml, you run:

- name: Validate Contract
  run: |
    npm install -g ajv-cli
    ajv validate -s contracts/user-event-v1.json -d data/sample-payload.json

If the JSON doesn't match, the build fails. No state, no registry, no infrastructure to patch. You are just using the same tools you use for linting code. It’s portable, it’s version-controlled, and it’s transparent.

Photo by ANOOF C on Unsplash

The "Version 2.0" trap

The biggest issue with the Schema Registry approach is the "version bump" dance. You have to ensure that every downstream consumer is ready to handle the new schema version, or you have to support backward compatibility forever. The Schema Registry forces you into a specific evolution strategy—usually AVRO's BACKWARD_TRANSITIVE compatibility.

When you enforce contracts in CI, you can implement custom logic that the registry can't handle. For example, you can write a script that scans your downstream dbt models to see if a field is actually being used before allowing a breaking change.

I once worked on a team where we used a simple Python script in our CI pipeline to check for field deletions. If a field was marked deprecated in the contract, we’d allow the removal. If it wasn't, the CI build would fail with a link to the downstream data warehouse impact report. This level of context-aware enforcement is impossible if you’re just relying on a Kafka registry.

What I'd pick, and why

If you’re building a greenfield platform today, skip the heavy-weight Schema Registries unless you are working in a highly regulated, high-throughput environment where the broker must be the source of truth. For 90% of data teams, CI-based contract enforcement is the superior choice.

My recommendation: use JSON Schema or Protobuf definitions stored in the same repository as the producer code. Add a CI step that validates the sample payloads against these schemas. Use a tool like dbt-checkpoint if you’re deep in the warehouse stack.

The caveat? This requires discipline. If your team doesn't maintain the contracts/ directory, the system decays immediately. You have to treat the contract like production code. If a test fails, you don't "force push" to bypass it; you update the contract and notify the downstream teams.

Treating contracts as a shared library is the only way to scale. When you move the enforcement point to the PR, you turn "data quality" from a frantic, reactive firefighting exercise into a boring, routine part of the development lifecycle. And boring is exactly what you want when you’re managing mission-critical data.

Don’t wait for the schema registry to tell you that you’ve broken the world. Catch it when you commit. Your on-call self will thank you.

Cover photo by Tyler on Unsplash.

How I stopped cloning datasets for Vertex AI pipelines

Aniket Abhishek Soni — Mon, 12 Oct 2026 15:22:25 +0000

You ship the job. It passes CI. Then, the SRE team pings you on Slack because your pipeline just swallowed 400GB of storage in an hour, and your cost projection for the quarter looks like a down payment on a house. You’re duplicating data again.

Most of us treat the Lakehouse as a write-once repository. We ETL, we transform, and then we export the "features" into a brand-new table or GCS bucket so Vertex AI can ingest them. It’s a habit born of convenience, but it’s a death sentence for your budget and your data consistency. In regulated environments like healthcare or finance, having two versions of a truth—one in your production warehouse and one in your training set—is a compliance nightmare waiting to happen.

The secret is simple: stop moving the data. If your features live in BigQuery or a Delta Lake, let them stay there. Use BigQuery as your source of truth and connect Vertex AI Pipelines directly to your raw or processed tables.

How it actually works

The goal is to treat your existing warehouse as the feature store. We do this by leveraging BigQuery DataFrames (BQF) or direct SQL execution within the Kubeflow Pipelines (KFP) components.

Instead of writing a Spark job to dump features into a CSV, you write a KFP component that executes a CREATE OR REPLACE TABLE or, better yet, a CREATE VIEW that points directly to your refined features.

Here is what the pattern looks like in a standard kfp.dsl.component using the Google Cloud AI Platform SDK:

from kfp.dsl import component, Input, Output, Dataset

@component(base_image="python:3.9")
def train_model_op(project_id: str, query: str):
    from google.cloud import bigquery

    # Do not materialize. Query the existing lakehouse table directly.
    client = bigquery.Client(project=project_id)
    job = client.query(query)
    df = job.to_dataframe()

    # Proceed to training
    # ...

The real magic happens when you move from to_dataframe() to BigQuery ML (BQML) or Vertex AI’s BigqueryTabularDataset. By using aiplatform.TabularDataset.create(display_name=..., bq_source=...), you create a pointer. You are not creating a copy; you are creating a URI that tells Vertex AI: "Go look at this BigQuery table when you need to train."

When your pipeline runs, it doesn't wait for a data export job to finish. It simply validates that the schema exists. If you are using BigQuery Omni, this even works across clouds. Your pipeline triggers, reads the metadata, and maps the features into the training container. Your cost stays centered on compute, not storage.

Photo by Wilhelm Gunkel on Unsplash

The tradeoffs nobody mentions

If this were a silver bullet, everyone would do it. But there are jagged edges here that vendors love to gloss over.

First, performance is non-deterministic. If your feature engineering logic involves complex joins across massive partition-pruned tables, running that query during the training loop means you’re fighting the warehouse for concurrency slots. If a DBA runs a heavy ANALYZE command while your pipeline is trying to materialize features, your training job will hang. I’ve seen 4-hour training windows stretch to 12 hours because of resource contention in the underlying warehouse.

Second, the "Data Snapshot" problem. If you don't materialize, you are training on live data. This is usually what we want, but it’s dangerous. If your upstream pipeline updates a feature table mid-run, you’ve just introduced non-deterministic bias into your model. You need to implement strict partitioning—if you aren't using _PARTITIONTIME or a specific run_id as a partition key in your BigQuery tables, you are effectively training on a moving target.

Third, the Vertex AI SDK versioning is... volatile. If you are using google-cloud-aiplatform>=1.30.0, the way it handles bq_source in the Dataset resource is different from older versions. I’ve spent entire afternoons debugging an InvalidArgument error that turned out to be a deprecation of a specific BigQuery metadata mapping in the SDK. Always pin your SDK versions in your requirements.txt inside your container image. Do not rely on "latest".

Photo by Markus Spiske on Unsplash

When to reach for it (and when not to)

Use the "no-duplication" approach when your feature engineering is idempotent and your warehouse is the primary source of truth. If your BigQuery setup is already using dbt (data build tool) to manage dependencies, your Vertex AI pipeline should simply be an orchestrator that triggers a model build after the dbt run completes. This keeps the lineage clean and ensures you aren't reinventing the wheel.

Don't reach for this when you need extremely low-latency feature retrieval. If you are serving a model that requires sub-millisecond lookups for features, the overhead of querying a lakehouse—even with BigQuery’s caching—is too high. For that, you need a dedicated feature store like Feast or the Vertex AI Feature Store. The "don't duplicate" pattern is for training/batch inference, not for real-time inference.

Also, avoid this if your data scientists are still in the "exploration" phase. If they are constantly dropping columns, renaming features, and blowing up schemas, you don't want them messing with your production BigQuery tables. Let them work in a sandbox, duplicate all they want, and only transition to the "no-duplication" pipeline once the feature logic is hardened into a production dbt model.

Conclusion

Data duplication is a tax on your engineering team and your cloud bill. By treating BigQuery or your Lakehouse as an API for your training pipeline rather than a storage locker, you regain control over the lineage. It forces you to be disciplined about schemas and partitioning, which is a good thing for your sanity.

Ship the pipeline. Keep the data in place. Stop paying for the same bytes twice.

Cover photo by Albert Stoynov on Unsplash.

Is Your Text-to-SQL Agent Just a Fancy Way to Drop Your Production Tables?

Aniket Abhishek Soni — Sat, 10 Oct 2026 04:03:00 +0000

The most dangerous myth in the current AI hype cycle is that a RAG-based Text-to-SQL agent is "smart enough" to know the difference between a read-only reporting schema and your customer PII tables. Spoiler: it isn't, and it doesn't care.

Why I chose this topic: I spent the last three months watching an LLM try to "helpfully" join our users table with our transaction_logs in a way that would have leaked HIPAA-regulated data if the database user permissions hadn't been tighter than a vault. We need to stop pretending that prompt engineering replaces architectural guardrails.

If you believe that a clever system prompt and a few-shot examples are enough to put a Text-to-SQL agent in front of your business analysts, you are one hallucinated JOIN away from a compliance nightmare. I’ve seen teams ship agents that, when asked "How many users do we have?", helpfully executed a SELECT * on the entire metadata catalog, nearly crashing the warehouse in the process.

Why the common approach falls short

Most tutorials on this topic focus on the "magic" of getting the SQL right. They talk about LangChain agents, vectorizing schema definitions, and tweaking temperature settings. This is the wrong layer of the stack.

The common failure mode is treating the LLM as an authorized user. If your agent connects to your Snowflake or Databricks cluster using a service account with SELECT access to the entire RAW schema, you have effectively turned your LLM into a high-speed injection attack vector.

When you define a table context for an LLM—even with sophisticated RAG—you are essentially giving it a map of your house. If you don't lock the doors to the rooms that hold the sensitive data, the LLM will eventually walk into them because the user asked it to "find all info related to customer X."

Photo by REGINE THOLEN on Unsplash

The view-based gatekeeper pattern

Never point your agent at base tables. Ever. If you are doing SELECT * FROM production.users, stop. You need a dedicated "AI-Consumption" schema.

Create a specific layer of views that exist solely for the agent. In Snowflake, I use a combination of ROW_ACCESS_POLICY and SECURE VIEW.

-- The only view the LLM sees
CREATE OR REPLACE SECURE VIEW ai_reporting.daily_transactions AS
SELECT 
    transaction_id, 
    transaction_amount, 
    transaction_date, 
    category 
FROM prod.transactions
WHERE transaction_date >= DATEADD(month, -1, CURRENT_DATE());

By forcing the agent to query only this view, you achieve two things: you limit the scope of available columns to only what is necessary, and you apply a hard temporal filter. If the LLM tries to query customer_ssn, it literally can't find it. The schema doesn't exist to it. Do not rely on the LLM to "ignore" columns; rely on the database engine to "not provide" them.

The circuit breaker at the middleware layer

Even with restricted views, LLMs are prone to "runaway queries." An agent might decide that the best way to answer "What is the average transaction value?" is to pull 50 million rows into memory because it doesn't understand the performance implications of a table scan on an unclustered column.

You need a hard circuit breaker between the agent and the database. I use a custom wrapper in Python that intercepts the generated SQL before it hits the execute() method.

def validate_sql(sql_query):
    # Prohibit dangerous keywords
    forbidden = ['DROP', 'TRUNCATE', 'ALTER', 'GRANT', 'DELETE']
    if any(keyword in sql_query.upper() for keyword in forbidden):
        raise PermissionError("Illegal operation detected.")

    # Force a limit if one isn't present
    if "LIMIT" not in sql_query.upper():
        return sql_query + " LIMIT 100"
    return sql_query

Is this a hack? Yes. Does it prevent a junior engineer's LLM agent from nuking a production table? Absolutely. In a production environment, I also inject a QUERY_TAG into the SQL comment block so I can track the agent’s behavior in the warehouse query history: /* AGENT_ID: finance_bot_v2 */. If I see a spike in compute costs, I know exactly which agent is acting up.

The semantic proxy layer

If you are using something like LlamaIndex or LangChain's SQLDatabaseChain, you are likely passing the entire DDL to the prompt. This is a recipe for context window bloat and hallucination.

Instead, build a "Semantic Proxy." Create a YAML or JSON manifest that describes your data in human terms, but keep the actual column names abstracted.

# semantic_manifest.yaml
tables:
  - name: tx_summary
    description: "Contains aggregated transaction data for the last 30 days."
    columns:
      - alias: "total_revenue"
        column_name: "amount_usd"
        type: "numeric"

The LLM queries against total_revenue, and your proxy translates that to amount_usd before hitting the database. This decouples your internal schema changes from your agent’s capabilities. When you rename amount_usd to gross_revenue_base, you update the manifest, not the prompt. The agent doesn't even know the underlying column name changed.

Photo by Markus Spiske on Unsplash

The objections (and my answers)

Objection: "But this limits the power of the LLM. If I restrict the schema, the agent can't answer complex cross-functional questions."

My answer: That is a feature, not a bug. In financial services, "cross-functional" usually means "compliance violation." If you need to join HR data with marketing data, that should be a curated data product, not an ad-hoc query generated by a probabilistic model. If the answer isn't in the governed view, the agent should return "I don't have access to that information" rather than trying to guess a join path.

Objection: "Isn't the cost of maintaining these views and manifest files too high? Why not just use a semantic layer like dbt or Cube?"

My answer: You should absolutely use dbt or Cube. If you’re already using them, you’re halfway there. But those tools are for human analysts. An LLM doesn't have the context of why a table was built. You still need an explicit, LLM-optimized subset of those models. Don't dump your entire dbt dbt_prod schema into an LLM context window and call it a day.

Objection: "What if the LLM generates syntactically correct but logically wrong SQL (e.g., summing a column that shouldn't be summed)?"

My answer: This is the hardest problem to solve. My solution is the "Human-in-the-loop" validation for non-idempotent queries. If the query is just a SELECT, let it rip. If the agent generates a query that implies a complex aggregation on a sensitive metric, I force the agent to output the SQL to a UI where a human has to click "Approve" before it runs. If you aren't doing this for business-critical reporting, you're playing Russian Roulette with your KPIs.

Conclusion

Text-to-SQL is only viable if you treat the LLM as an untrusted user. Stop trying to make the LLM "smarter" and start making your database "dumber"—by which I mean, simpler, constrained, and incapable of executing anything outside of a predefined, safe sandbox.

If you aren't ready to build the middleware, the proxy, and the view layer, you aren't ready to deploy. Keep the LLM in the playground until you’ve built the fences.

Tags: #sql #llm #governance #data

Cover photo by Brice Cooper on Unsplash.

Death to the Hive Metastore: Why Iceberg REST Catalogs Are Your New Reality

Aniket Abhishek Soni — Thu, 08 Oct 2026 12:24:29 +0000

Why I chose this topic: Three years ago, I spent an entire weekend debugging a corrupted Hive Metastore (HMS) that locked up our daily reconciliation pipeline. I'm writing this because the industry is finally moving toward decoupled, REST-based cataloging, and I want you to avoid the migration headaches I dealt with by learning the specific tradeoffs of the big three providers.

Two years ago, your data stack likely relied on a Hive Metastore (HMS) sitting behind a Thrift server. You know the pain: the "Thrift connection reset" errors at 3 AM, the sheer agony of schema mismatches, and the desperate need to perform MSCK REPAIR TABLE every time a bucket was touched. It was a brittle, monolithic bottleneck that made multi-engine access feel like a high-stakes gamble.

Today, life looks different. With the Apache Iceberg REST specification (OpenAPI), the "catalog" is no longer a database you babysit; it's an API layer that mediates between your compute engines (Spark, Trino, Flink) and your storage (S3, GCS, ADLS). You point your engine at a URL, provide an OAuth token, and the metadata just works. No more manual partition scanning. No more proprietary lock-in at the catalog layer.

The real problem

The industry calls this "decoupling," but let's be honest: it’s about avoiding the "I can't believe this engine just corrupted my data" conversation with your VP of Engineering.

The real problem with traditional metastores isn't just the protocol; it's the lack of fine-grained access control and the inability to handle concurrent writes safely. When you move to an Iceberg REST catalog, you aren't just changing a URL. You are moving to a world where the catalog defines the "source of truth" via atomic commits. If your catalog backend doesn't support the full Iceberg REST spec (version 1.0.0 and above), you’re just putting lipstick on a pig.

Photo by Yancy Min on Unsplash

Step: Deploying Apache Polaris for the pure-play approach

If you want to own your infrastructure and avoid vendor lock-in, Apache Polaris is the new gold standard. It’s built by Snowflake, but it’s open-source and specifically designed for the Iceberg REST spec.

When you deploy Polaris, you’re running a Java-based service that handles authorization and metadata management. You don’t need a backing RDBMS if you don’t want one—you can configure it to use local storage or cloud object stores. Here is the minimal configuration to get a Polaris service running in a containerized environment:

# polaris-config.yaml
server:
  type: default
  applicationConnectors:
    - type: http
      port: 8181
  adminConnectors:
    - type: http
      port: 8182

storage:
  type: s3
  bucket: my-iceberg-data-bucket
  region: us-east-1

auth:
  type: oauth2
  issuer: https://my-auth-provider.com/

The key here is the auth section. Polaris forces you to handle security properly. If you aren't using an OIDC provider, don't even bother starting. Polaris expects a token in the Authorization header, and it will reject any write request that isn't signed correctly.

Step: Leveraging Unity Catalog for the "it just works" path

If you are already in the Databricks ecosystem, Unity Catalog (UC) is the pragmatic choice. Unity Catalog has evolved to support the Iceberg REST spec, allowing you to use non-Databricks engines (like Trino or Starburst) against the same tables you manage in Databricks.

This is the "Unified Governance" play. You define your catalog and schema in the Unity Catalog UI, then pass the connection details to your external engine.

# Example Spark configuration for connecting to Unity Catalog as an Iceberg REST provider
spark.sql.catalog.uc_iceberg = org.apache.iceberg.spark.SparkCatalog
spark.sql.catalog.uc_iceberg.catalog-impl = org.apache.iceberg.rest.RESTCatalog
spark.sql.catalog.uc_iceberg.uri = https://<databricks-workspace-url>/api/2.0/unity-catalog/iceberg
spark.sql.catalog.uc_iceberg.token = <your-databricks-pat>
spark.sql.catalog.uc_iceberg.warehouse = <catalog-name>

The failure mode here is usually the token expiration. Databricks Personal Access Tokens (PATs) have a shelf life. If you’re running production pipelines, swap this for a Service Principal. If your pipeline suddenly stops writing, 9 times out of 10, the token rotated and your CI/CD environment variables didn't update.

Step: AWS Glue as the managed REST catalog

AWS Glue is the "lazy" choice, and I mean that in the best way possible. If your infrastructure is 100% AWS, using Glue as your Iceberg REST catalog backend eliminates the need to manage any servers. You get the benefit of IAM integration for free.

Glue now has a native Iceberg REST endpoint. Instead of the old GlueCatalog implementation that relied on the Hive-compatible GetTable API, you use the REST-based endpoint.

# Glue connection setup via Boto3/Environment
os.environ["ICEBERG_REST_URI"] = "https://glue.us-east-1.amazonaws.com"
os.environ["ICEBERG_CATALOG_TYPE"] = "rest"
os.environ["AWS_REGION"] = "us-east-1"

# In your Spark session:
spark.conf.set("spark.sql.catalog.glue_catalog", "org.apache.iceberg.spark.SparkCatalog")
spark.conf.set("spark.sql.catalog.glue_catalog.catalog-impl", "org.apache.iceberg.rest.RESTCatalog")
spark.conf.set("spark.sql.catalog.glue_catalog.uri", os.environ["ICEBERG_REST_URI"])

Warning: AWS Glue has hard limits on request rates. If you have a massive Flink job performing thousands of small commits per minute, you will hit the ThrottlingException wall. You’ll need to request a quota increase from AWS support before you go live, or you'll spend your first production day watching your pipelines crash.

Photo by Harshit Katiyar on Unsplash

Lessons learned from production

The "Namespace" trap: Iceberg REST catalogs handle namespaces differently than HMS. If you’re migrating, don't assume my_db.my_table in Hive will map 1:1 without testing the catalog-to-storage path. Use CALL system.migrate('db.table') carefully in a staging environment first.
Metadata location: Always keep your metadata files (.metadata.json) in the same bucket as your data. If you split them and your catalog backend loses connection to the metadata store, you are looking at a manual recovery process that involves editing manifest files by hand. Do not recommend.
Engine compatibility: Not all engines support all REST features. If you are using Trino, check the Iceberg version it supports against the specific features (like branching or tagging) you want to use. A version mismatch between the catalog's API response and the engine's client library will manifest as a cryptic 404 Not Found that actually means "I don't understand the JSON schema you just sent me."
Cost of observability: Unlike the old HMS which was just a MySQL database you could query, these REST catalogs are black boxes. You need to log the API requests. If you aren't logging the POST /v1/catalogs/{catalog}/namespaces/{namespace}/tables calls, you have zero visibility into what's failing when a write job dies.

Conclusion

The shift to Iceberg REST is the most significant improvement in data infrastructure since the invention of S3. We are finally moving away from the "database as a filesystem" hack that plagued the Hadoop era.

Whether you go with the self-hosted flexibility of Polaris, the unified governance of Unity Catalog, or the managed simplicity of AWS Glue, pick one and move your write-heavy workloads over. Your future self, sleeping soundly at 3 AM instead of debugging metastore corruption, will thank you.

Try it: Spin up a local instance of Polaris using Docker, hook it up to a local S3-compatible store like MinIO, and run a Spark job to write your first table. If you can move from a local file-based catalog to a REST-based one in under an hour, you're ready for the big leagues.

Tags: #iceberg #data #architecture #backend

Cover photo by Kirill Sh on Unsplash.

Stop Choosing Between BigQuery and Databricks Based on Marketing Slides

Aniket Abhishek Soni — Tue, 06 Oct 2026 16:37:14 +0000

03:42 AM. The PagerDuty alert didn't wake me up—the sound of the laptop fan spinning up to jet-engine levels in my sleep-deprived brain did.

The myth that "BigQuery and Databricks are both just SQL engines, so they perform the same" is the kind of professional lie we tell ourselves to avoid reading the documentation. I was looking at a dashboard that usually loads in 12 seconds, now spinning for 14 minutes before throwing a RESOURCE_EXHAUSTED error. We were mid-migration, testing our core reporting layer against 4TB of Parquet files sitting on S3.

The team assumed that since we were using Databricks SQL (Serverless) and BQ’s BigLake, the abstraction layer would handle the heavy lifting. It didn't. We were trying to join a massive fact table—denormalized, partitioned by day, and sitting in open-format delta tables—against a dimension table that someone had accidentally stored as a thousand tiny JSON files.

What we saw

The symptom was simple: the query scheduler in Databricks was timing out, and BigQuery was racking up a $400 bill for a single exploratory analysis.

We fell for the classic trap: "The engine will figure it out." In our Databricks environment, the SQL Warehouse was set to Pro with Auto-stop enabled. When we threw a complex window function over a non-partitioned column, the spill-to-disk metrics hit the roof. We saw io.file.write.bytes spiking in the Ganglia metrics, but the throughput was abysmal.

On the BigQuery side, the INFORMATION_SCHEMA.JOBS_BY_PROJECT view showed us that we were scanning the entire dataset because our partitioning key (event_date) was buried inside a CASE statement. The engine wasn't pruning partitions because the optimizer couldn't prove the predicate was constant at planning time. We spent three hours chasing "network latency" and "cold starts," convinced the cloud provider was throttling us. They weren't. We were just writing garbage SQL that assumed the engine had infinite compute and perfect metadata.

Photo by luka Bruyninckx on Unsplash

Root cause

The failure wasn't the engine; it was the impedance mismatch between our file layout and the compute engine’s execution model.

In Databricks, we were hitting a classic Z-Order failure. Our date column was the partition key, but our queries were filtering by user_id. Without Z-Ordering the data on the user_id column, the engine performed a full table scan of every partition to find a single user's activity. The offending code looked like this:

-- The culprit: forcing a full scan because of the non-indexed filter
SELECT 
  user_id, 
  SUM(revenue) OVER(PARTITION BY user_id ORDER BY transaction_timestamp) as running_total
FROM delta.`s3://our-bucket/prod/transactions/`
WHERE user_id = '98273-a' -- This column is not Z-Ordered

On the BigQuery side, the issue was our reliance on BigLake. We had configured the table to point to S3, but we hadn't defined the metadata cache properly. BigQuery was spending more time listing files in the S3 bucket via the storage API than actually executing the sum. It was a "Metadata Bottleneck." Every time we queried, BigQuery was hitting the S3 ListObjectsV2 API thousands of times. The cost wasn't just the compute; it was the overhead of the cloud provider’s storage interface being treated like a local SSD.

Photo by Markus Spiske on Unsplash

The fix

We stopped trying to make the engine "do it all" and started managing the physical data layout.

For Databricks, we ran an OPTIMIZE transactions ZORDER BY (user_id) command. This isn't a silver bullet—it's a maintenance job that costs money—but it immediately reduced the amount of data read by 90%. We moved the user_id to a clustered index equivalent within the Delta Lake storage layer.

For the BigQuery side, we forced the issue by enabling metadata_cache_mode = 'AUTOMATIC' and setting the metadata_cache_ttl to 60 minutes. We also had to switch from raw S3 URIs to a managed BigQuery external table definition that explicitly defined the partition layout.

The change in performance was binary. The query dropped from 14 minutes to 4 seconds. The cost dropped from $400 to $0.12. We weren't fighting a platform limitation; we were fighting our own laziness regarding metadata management.

What we changed so it never happens again

We implemented a strict "No Raw File Access" policy. If a data engineer wants to query raw S3 files, they use a staging layer. If it’s for production, it must be a managed table with defined partitioning and Z-Ordering.

We also introduced a cost-budgeting script that triggers a Slack alert if any single query exceeds $10 in BigQuery or 30 minutes of compute time in Databricks. We treat these alerts as P1 incidents. No "I'll optimize it later." If it hits the limit, it gets killed, and the developer has to explain the EXPLAIN plan in the pull request.

The biggest lesson? Don't trust the marketing slides that say "BigQuery is serverless" or "Databricks handles all the tuning." Both platforms are incredibly powerful, but they both expect you to know how they handle physical storage.

If you treat a cloud data warehouse like a local Postgres instance, you will get billed for your ignorance. If you treat it like a distributed system, it’s the most powerful tool you have. Stop writing SQL for a laptop and start writing SQL for the cluster. If you don't know where your data lives and how it's partitioned, the engine is just going to scan the entire internet and charge you for the privilege.

Cover photo by Albert Stoynov on Unsplash.

Why Are You Treating Data Drift Like It’s Not A System Crash?

Aniket Abhishek Soni — Sat, 03 Oct 2026 20:58:06 +0000

Data drift isn't a "model quality issue"—it’s a production outage, and if your team treats it as a polite ticket for the data science squad, you’re failing.

Why I chose this topic: I’ve watched too many senior engineers spend hours debugging a pipeline failure in Airflow, only to realize later that the data was flowing perfectly but the model was making absolute garbage predictions. We need to stop pretending these are separate domains.

It was 3:14 AM on a Tuesday. PagerDuty didn’t just buzz; it screamed. My phone vibrated against the nightstand with an alert from our primary inference service: 5xx Error Rate > 5%.

I cracked open the laptop. The Grafana dashboard was a sea of red. Our Kubernetes cluster was throwing CrashLoopBackOff errors on the inference-api pods. My first instinct, forged by years of fighting legacy ETL, was to check the pipeline. I logged into Airflow. The DAG daily_feature_ingestion had completed successfully at 02:00 UTC. The Snowflake load logs were clean.

I shifted to the model server logs. K8s pods were OOM-killing. I checked the memory limits: memory: 4Gi. We had scaled up recently, so why now? I assumed it was a sudden spike in traffic, so I bumped the resource limits to 8Gi, redeployed via our Helm chart, and watched the pods restart. They stabilized for ten minutes, then slammed into the ceiling again.

What we saw

The symptoms were deceptive. The metrics showed a massive increase in payload size coming from the feature store. I looked at the feature store audit logs. Everything looked normal—the ingestion job was pulling the same number of rows as it did the day before.

The false lead was the traffic volume. We assumed a DDOS or a sudden spike in user activity. We spent forty minutes looking at WAF logs, blocking IP ranges, and blaming our upstream marketing API. We were chasing ghosts. Meanwhile, the models were consuming 8GB of RAM just to parse the incoming request objects.

The P99 latency had gone from 120ms to 4.2s. It wasn't the number of requests; it was the shape of the data inside them.

Photo by Leftfield Corn on Unsplash

Root cause

We weren't dealing with a software bug; we were dealing with a silent upstream schema evolution that triggered a massive drift in the input distribution.

A backend engineer had updated the user_profile service. They added a nested_json field called experimental_behavior_log to the payload. Our feature store, which used a generic JSONB column in Postgres, happily accepted the ingestion. The feature engineering script—a Frankenstein monster of Pandas code—saw this new, massive JSON blob and decided to flatten it into two thousand individual columns because of a naive pd.json_normalize() call.

The model, expecting 42 features, was suddenly receiving 2,042. The input vector was exploding in memory. The feature store wasn't failing; it was doing exactly what we told it to do. The pipeline wasn't failing; it was succeeding at moving garbage. The model didn't throw an error; it just tried to process a dataframe that grew exponentially with every request.

The offending code in our feature_transformation.py:

# The culprit
def extract_features(raw_data):
    df = pd.DataFrame(raw_data)
    # This was the death knell
    features = pd.json_normalize(df['payload']) 
    return features

We had no schema enforcement. We were treating "data quality" as a post-hoc analysis task rather than a runtime requirement.

Photo by Concha Mayo on Unsplash

The fix

I didn't have time for a clean architectural rewrite at 4:00 AM. I needed to stop the bleeding.

First, I implemented a hard constraint on the feature input using a Pydantic model at the entry point of the transformation service. If the schema didn't match the expected fields, the pipeline now throws a ValidationError and alerts immediately.

Second, I added a resource-constrained check on the feature vector size. If the resulting dataframe width exceeds a predefined threshold (in our case, 150 columns), the process kills itself and logs the schema diff.

# The emergency patch
class FeatureSchema(BaseModel):
    user_id: str
    session_count: int
    # Explicitly define what we allow
    class Config:
        extra = 'forbid' 

def transform(data):
    validated_data = FeatureSchema(**data)
    # ... proceed with transformation

I redeployed the service with these constraints. The pods stopped crashing because the pipeline now rejected the bloated payloads at the gate. The downstream model was saved from processing malformed data, and the 5xx errors vanished.

What we changed so it never happens again

We stopped treating "Pipeline Monitoring" (Is the job running?) and "Model Monitoring" (Is the prediction valid?) as two different jobs.

We moved to a unified observability stack. We now use Great Expectations integrated directly into our Airflow DAGs, but with a twist: the expectations are checked at the source and the model input. If the data distribution—specifically the feature count—drifts beyond a 2-sigma threshold, the pipeline marks the task as FAILED before the model ever sees it.

We retired the "let it flow and see" mentality. We treat data schema as code. If an upstream service changes their JSON structure, our CI/CD pipeline now fails during the integration test phase because our schema.json definitions are pinned and validated.

We also implemented "Circuit Breakers" in our inference API. If the input data shape deviates from the historical distribution cached in Redis, the API returns a 422 Unprocessable Entity instead of attempting to process the inference. This prevents the OOM-kill cycle entirely.

If you aren't failing your pipeline when your data distribution changes, you aren't doing observability. You’re just doing logging. And logging is just a way to look back at the wreckage once you’ve already crashed. Fix the schema, enforce the boundaries, and stop pretending that data drift is a "data science" problem. It’s an infrastructure problem. Treat it like one.

Cover photo by Tyler on Unsplash.

Cutting AWS Storage Costs: S3 Tables vs. S3 Express One Zone

Aniket Abhishek Soni — Thu, 01 Oct 2026 23:13:26 +0000

Last year, my team was fighting the "small file problem" in a standard S3 bucket. We were running massive Glue jobs that spent 40% of their execution time just doing LIST requests to aggregate millions of tiny Parquet files. We had s3:ListBucket throttled, our EMR clusters were idling during metadata scanning, and our monthly storage bill looked like a cry for help.

Today, we’ve shifted our hot-path workloads to S3 Express One Zone and our long-term cataloged tables to S3 Tables. The result isn't just "better performance." We went from 30-minute metadata listing times to sub-second responses. More importantly, we stopped paying for the privilege of our own inefficiency.

Most engineers think of S3 as a simple key-value store. It’s not. It’s a distributed system that behaves fundamentally differently depending on the storage class you pick. If you treat S3 Express One Zone like a standard Standard bucket, you’re setting fire to your AWS credits.

How it actually works

To understand why S3 Tables and S3 Express One Zone change the game, you have to look at the metadata tax. In standard S3, LIST operations are expensive and eventually consistent. When you have a table with 50 million files, your query engine (Trino, Spark, or Athena) has to crawl that bucket. That’s a network-heavy, latency-riddled nightmare.

S3 Express One Zone (the S3EX storage class) changes the architecture by co-locating compute and storage within a single AZ. It uses a custom directory-bucket structure. Instead of the flat namespace of standard S3, you are using a specialized API that provides single-digit millisecond latency.

The code change is trivial but massive in impact. You move from the standard s3:// URI to s3express:// (or use the bucket name with the --onezone suffix).

When you configure your Spark session, you aren't just changing a prefix. You are bypassing the standard S3 global endpoint.

// The old way: slow, throttled, expensive
val df = spark.read.parquet("s3a://my-bucket/data/logs/")

// The new way: S3 Express One Zone
val df = spark.read.parquet("s3express://my-bucket--use1-az1--x-s3/data/logs/")

S3 Tables, on the other hand, is the management layer. It’s essentially a managed Apache Iceberg engine built directly into S3. It abstracts the file management. You no longer worry about file compaction jobs because S3 Tables handles the vacuuming and snapshot management for you. You interact with it via the s3tables API, and it handles the underlying metadata manifest files. You effectively trade control for reduced operational toil.

Photo by 灿雄邱 on Unsplash

The tradeoffs nobody mentions

Let’s be honest: AWS doesn't give you these performance gains for free. There is a "Single Zone" tax, and it’s not just about durability.

With S3 Express One Zone, you lose regional redundancy. If your chosen AZ goes dark, your data is unavailable. In healthcare or financial services, that’s a non-starter for PII or critical audit logs unless you have an automated cross-region replication strategy in place—which, by the way, eats up all the cost savings you just gained.

Then there is the API cost. S3 Express One Zone charges per request, but those costs are bundled differently. If you are doing infrequent, massive sequential reads, standard S3 is still cheaper. If you are doing high-frequency, small-file random access (think real-time features engineering or feature stores), S3 Express One Zone is significantly cheaper because you aren't paying the overhead of the standard S3 request tax for millions of tiny metadata operations.

S3 Tables has its own headache: lock-in. Once you start using the S3 Tables managed format, moving that data out to a different provider or an on-prem cluster is significantly harder than moving standard Parquet files. You are tied to the AWS-managed Iceberg implementation. If you love open-source portability, this is a bitter pill to swallow.

Also, be warned about the "One Zone" prefix. If you are running your EMR or Glue jobs in us-east-1a and your S3 Express bucket is in us-east-1b, you are going to eat data transfer costs that will destroy your budget. You must ensure your compute and your S3EX bucket are pinned to the exact same AZ. If you don't have a strict infrastructure-as-code (IaC) policy enforcing this, your cloud bill will fluctuate wildly based on which AZ your spot instances happen to land in.

Photo by Agil Saputro on Unsplash

When to reach for it (and when not to)

Use S3 Express One Zone when your bottleneck is IOPS, not throughput. If your Spark jobs are stuck in "waiting for metadata" or "listing directory," this is the fix. It’s perfect for training machine learning models where you need to stream millions of small image files or feature vectors into a GPU cluster.

Do not use it for your primary "cold" data lake. Your quarterly historical reports don't need sub-millisecond access. Keeping those in S3EX is just bad engineering. Keep your cold data in S3 Standard or S3 Intelligent-Tiering.

Reach for S3 Tables when your team is drowning in Iceberg maintenance. If you find yourself writing custom scripts to run VACUUM and OPTIMIZE on your tables, S3 Tables is your escape hatch. It turns the "data engineer as a janitor" role into something more productive.

However, avoid S3 Tables if you require strict, fine-grained control over your partition evolution or if you have complex custom manifest requirements that the AWS managed service doesn't support yet. If you have a highly customized, non-standard Iceberg implementation, the migration process will be a weekend-ruining experience.

Conclusion

The era of just "dumping everything in a bucket" is over. We’ve been using S3 as a giant, undifferentiated blob store for a decade, and we’re paying for it with massive latency and wasted compute cycles.

By separating our storage into tiered access—S3 Tables for the managed, queryable lakehouse and S3 Express One Zone for the high-frequency IO path—we’ve actually started to see our storage costs move in the right direction. It requires more thoughtful architecture and a stricter handle on your AZ affinity, but that’s the job. Stop treating your data lake like a digital landfill and start treating it like a distributed database. Your infrastructure costs will thank you.

Cover photo by Albert Stoynov on Unsplash.

Spark Structured Streaming Can’t Save You From Bad Architecture

Aniket Abhishek Soni — Wed, 30 Sep 2026 03:06:08 +0000

Roughly 80% of the "exactly-once" guarantees I see implemented in production financial pipelines are actually "at-least-once" disguised by a fragile post-processing cleanup script. We obsess over the Spark checkpointing mechanism while ignoring the fact that our sink is a leaky bucket.

Why I chose this topic: I’ve spent the last six years cleaning up "exactly-once" messes that caused multi-million dollar reconciliation failures in healthcare billing systems. If you don't understand the physical limitations of your sink, your streaming job is just a very expensive way to generate duplicate records.

Most of my peers argue that Spark’s checkpointLocation is the gold standard for fault tolerance. They treat it like a holy relic. The reality is that Spark’s exactly-once guarantee is strictly confined to the boundary between the source offset management and the state store. Once your data hits an external database or a file system that doesn't participate in a distributed transaction with Spark, your "exactly-once" guarantee is effectively vaporware.

Why the common approach falls short

The industry standard is to use foreachBatch to sink data into a relational database or a cloud object store. Developers assume that because Spark retries the batch upon failure, they are protected against duplicates. This is a dangerous fallacy.

If your Spark executor writes 10,000 rows to a Postgres table and crashes at row 9,999, the driver will restart the task. If you aren’t using idempotent writes—where the sink itself knows how to deduplicate based on a business key—you now have a partial write. If your sink logic involves an INSERT statement without an ON CONFLICT DO UPDATE or a similar upsert mechanism, you have just injected garbage into your downstream analytical layer.

Take a look at a common, broken pattern I see in Spark 3.3.x:

streamingDF.writeStream
  .foreachBatch { (batchDF: DataFrame, batchId: Long) =>
    batchDF.write.format("jdbc").mode("append").save(...)
  }
  .start()

This code is a lie. If the save() operation fails midway, Spark reruns the batch. If the database processed 5,000 rows before the crash, those 5,000 rows remain. The retry will attempt to insert all 10,000 rows again. You have just doubled your data. Unless your schema has a strict unique constraint and you’ve configured your JDBC driver to handle the resulting exception, you’ve corrupted your downstream data.

Photo by CHUTTERSNAP on Unsplash

The illusion of checkpointing

Spark’s checkpointing is brilliant for maintaining the state of your streaming query. It stores the metadata of processed offsets in HDFS or S3. But people confuse "I know which offsets I’ve processed" with "I have committed the data to the sink."

When a worker node dies, Spark rolls back the state to the last successful offset recorded in the checkpoint directory. It then re-executes the batch. This is where the physical reality of your sink takes over. If you are writing to S3 using the default file sink, Spark uses a commit protocol that involves renaming temporary files. If that rename operation isn't atomic—and on many S3-compatible object stores, it isn't—you are left with "ghost" files that weren't cleaned up by the failed task but aren't technically part of the final manifest.

In my experience, relying on checkpointLocation without a high-performance, atomic storage layer is like trying to build a skyscraper on a swamp. You aren't doing exactly-once; you are doing "at-least-once plus a prayer that the infrastructure handles the retry gracefully."

The sink is the bottleneck

True exactly-once in a streaming system requires a two-phase commit protocol or an idempotent sink. Spark Structured Streaming implements this only when both the source and the sink support it natively. For example, if you are reading from Kafka and writing to Kafka, Spark can participate in the transaction.

But when you write to a sink like Delta Lake, you are relying on the Delta protocol to handle the atomicity. Delta Lake is excellent—it uses a transaction log to ensure that a batch is either fully committed or not at all. If you are using Delta Lake 2.0+, the writeStream API will correctly ignore duplicate batches because it checks the batchId against the transaction log before committing.

The problem arises when engineers try to shoehorn this into non-transactional sinks. I’ve seen teams try to implement "exactly-once" against Redis or a legacy REST API. They write custom logic inside foreachBatch to track batchId in a side-table. This is a recipe for a race condition. If your side-table update and your sink write aren't in the same transaction, you are fundamentally broken. You’ve just shifted the failure mode from "duplicate data" to "stale state."

Photo by Lukas Tennie on Unsplash

The objections (and my answers)

"But the documentation says Spark guarantees exactly-once!"

The documentation says Spark guarantees exactly-once processing semantics. That is a technical term of art, not a business guarantee. It means that the state of the Spark job will be consistent with the offsets of the source. It explicitly avoids making promises about the side effects of your code. If your side effect is "inserting a row into a database," Spark isn't responsible for that database's internal state.

"Can't I just use a deduplication buffer?"

Sure, you can use dropDuplicates() on your streaming DataFrame. But dropDuplicates() is stateful. It has to keep a history of keys in memory or in the state store. If your window is large or your data volume is high, you will OOM (Out of Memory) your executors or blow up your RocksDB state store. You’re trading a duplication problem for a scaling problem.

"What about the newer Spark versions with better fault tolerance?"

Spark 3.5+ has improved the way it handles state store snapshots and recovery, but the sink problem remains. No matter how much you optimize the Spark engine, if you point it at a sink that doesn't support transactions, you are living in a world of at-least-once. The only way to achieve truly robust pipelines is to embrace idempotent sinks and design your schema so that re-processing the same batch is a no-op, not a disaster.

Conclusion

Stop chasing the ghost of "exactly-once" in your application code. You cannot force a non-transactional system to become transactional just by wrapping it in a Spark job.

Instead, build for idempotency. Use unique business keys in your database. Use Delta Lake or Hudi if you need transactional file storage. If you are writing to an API, ensure your request includes a client-side idempotency token that the API respects.

Spark Structured Streaming is a phenomenal tool for stateful processing and offset management. But it is not a magic wand that absolves you from the responsibility of handling data integrity at the sink. If you assume the framework handles everything, you’ll spend your weekends doing manual data reconciliation. Don’t trust the abstraction—trust the physical limitations of your infrastructure.

Cover photo by Tyler on Unsplash.

How we stopped breaking production pipelines with Snowflake zero-copy cloning

Aniket Abhishek Soni — Mon, 28 Sep 2026 13:11:06 +0000

Ninety-eight percent of production data pipeline incidents happen because developers tested their code against a "subset" of data that didn’t actually look like production. We spend hours writing synthetic data generators or manual sampling scripts, only to have our transformation logic blow up the second it hits the real-world mess of nulls, schema drifts, and malformed strings waiting in the actual warehouse.

We treat production data like a radioactive isotope: too dangerous to touch, so we create elaborate, sterile imitations. But in modern data engineering, the most dangerous thing you can do is test against an imitation. If you’re building pipelines in Snowflake and not using zero-copy cloning, you are effectively flying a plane while guessing the altitude.

The mechanics of metadata manipulation

Most engineers hear "clone" and think "copy." They imagine a background process chewing through TBs of storage, racking up compute credits, and taking an hour to finish. That is how traditional databases work. That is not how Snowflake works.

When you run CREATE TABLE clone_table CLONE prod_table, Snowflake doesn't move a single byte of data. It creates a new entry in the metadata layer that points to the existing micro-partitions of the source table.

Think of it as a git branch for your data. The clone shares the underlying S3/Azure Blob/GCS storage files with the original table. Only when you perform a DML operation—an UPDATE, DELETE, or INSERT—does Snowflake write new micro-partitions. It’s copy-on-write at the storage layer.

I’ve cloned 5TB production tables in under three seconds. The command is trivial:

CREATE OR REPLACE TABLE dev_schema.orders_clone 
CLONE prod_schema.orders AT (TIMESTAMP => '2023-10-27 10:00:00'::timestamp_ntz);

Because it uses the Time Travel feature, you aren't just cloning the current state; you are cloning a specific moment in time. If a bad job ran at 10:05 AM and corrupted your table, you can clone the table from 09:59 AM, verify your fix, and deploy the pipeline change without ever actually touching the corrupted production data.

Photo by Egor Komarov on Unsplash

The tradeoffs nobody mentions

If this sounds like magic, it’s because it’s a brilliant abstraction. But like any abstraction, it leaks.

The first issue is the "Clone Lifecycle." If you drop the source table, the clone loses its reference to the underlying micro-partitions. While Snowflake allows you to keep cloned data via TIME_TRAVEL_RETENTION_PERIOD, you are essentially holding the source table hostage in the metadata layer. If your DBA team has a strict policy of dropping and recreating staging tables, your clones will break.

Then there is the "Storage Inflation" trap. Because the clone shares the same micro-partitions as the original, any operation that modifies data—even if it's just a RECLUSTER or a DELETE—will cause the micro-partitions to deviate. If you have a clone that sits there for months while the original table undergoes massive churn, the clone will effectively "own" a copy of the old partitions. You won't see this in your compute credits; you’ll see it in your storage bill. I once saw a junior dev clone a massive fact table for a "quick test" and leave it running for six weeks. The storage bill for that account tripled because we were essentially versioning the entire fact table history twice.

Finally, there is the "Security Blindspot." Cloning is a metadata-level operation. If a user has SELECT privileges on the source, they can usually clone it. If your organization has strict PII masking policies, you have to be incredibly careful. If you don't have your MASKING POLICY applied at the account level or correctly assigned to the roles, a clone gives a dev full access to raw, unmasked production data. I’ve seen compliance audits fail specifically because a developer cloned a production table to debug a join issue, inadvertently exposing PII to the development environment.

Photo by Craftsman Concrete Floors on Unsplash

When to reach for it (and when not to)

Use zero-copy cloning when you need to perform "destructive" testing. If you are rewriting a complex MERGE statement or refactoring a DBT model that touches millions of rows, there is no substitute. Cloning allows you to run the job exactly as it would run in production, against the actual data, without risking a single row of live data.

Use it for "Point-in-Time" forensic analysis. When a pipeline fails at 3 AM, don't try to query the broken state directly. Clone the table to the state exactly one second before the failure. It turns an emergency "fix it in prod" disaster into a calm, reproducible debugging session.

Do not use it for long-term development environments. If you need a sandbox for a feature that will take three weeks to build, do not use a zero-copy clone. Use a subsetted table or a mocked dataset. Clones are meant to be ephemeral. If you find yourself keeping a clone for more than a few days, you are just building technical debt into your storage costs.

Avoid it if you have complex, cross-database constraints or if your underlying data is stored in External Tables (S3/GCS buckets). Cloning works beautifully for native Snowflake tables, but it handles external stages differently. You can’t "clone" a file sitting in an S3 bucket; you can only point to it. If the pipeline change involves shifting the underlying file structure, your clone might not behave the way you expect.

Conclusion

Zero-copy cloning is the difference between an amateur pipeline and a professional one. It moves the testing phase from "I hope this works" to "I have verified this against the production state."

But remember: just because it’s fast and cheap doesn't mean it’s free. Treat your clones with the same lifecycle discipline as your code branches. Create them, test them, verify your logic, and then drop them. If you’re still testing by sampling 1,000 rows and praying, you aren't an engineer—you're a gambler. Stop gambling with your data and start using the tools that let you see the future of your pipeline before you deploy it.

Tags: #snowflake #data #pipelines #engineering

Cover photo by Albert Stoynov on Unsplash.

Stop building your own vector index: Why managed services are eating the Postgres ecosystem

Aniket Abhishek Soni — Mon, 28 Sep 2026 13:10:58 +0000

Two years ago, adding semantic search to our internal document analysis tool involved a bloated Python script, a local HNSW index on a disk-heavy EC2 instance, and a prayer that the index wouldn't explode when we hit 500k embeddings. Updating the index meant a full offline rebuild, which resulted in 20 minutes of "Search currently unavailable" errors every Tuesday.

Today, that same stack uses a managed vector service. I push vectors via API, the index updates in near real-time, and my PagerDuty alerts for "Index Memory Pressure" have vanished. The transition wasn't just about moving to the cloud; it was about accepting that the vector index is a specialized piece of infrastructure, not just another table in your database.

Why I chose this topic: I’ve spent the last six years fighting with database performance in highly regulated environments where downtime isn't an option. I’m writing this because I keep seeing teams try to shoehorn vector indexing into their monolithic Postgres instances without understanding the operational tax of doing so at scale.

The contenders

We are comparing three distinct philosophies here:

pgvector (v0.7.0+): The "it's just Postgres" approach. It uses the ivfflat or hnsw access methods. It’s perfect if your data already lives in RDS/Aurora and your embedding counts are in the low millions.
Pinecone: The "serverless-first" managed service. It abstracts away the index type, the shard management, and the hardware provisioning. You pay for throughput and storage, not instances.
Databricks Mosaic AI Vector Search: The enterprise-grade engine. This is for the team that already has their data in Unity Catalog. It’s the "I don't want to think about data movement" choice.

Photo by Luke Jones on Unsplash

The operational tax

When you run pgvector, you are running Postgres. That means you are managing shared_buffers, work_mem, and the vacuuming cycle. If you hit a peak in query volume, your vector operations are competing for CPU cycles with your standard transactional UPDATE and SELECT statements.

In version 0.7.0, pgvector added support for hnsw indexes, which is a massive performance win. However, if you trigger a reindex on a table with 5 million vectors, your CPU will spike to 100% and your query latency will tank. I’ve seen production databases lock up because of an ill-timed CREATE INDEX CONCURRENTLY in an environment where the IOPS limit on an AWS gp3 volume was already near saturation.

Pinecone and Mosaic AI treat the index as a separate service. You aren't managing the memory pressure of the index; the vendor is. When I use Pinecone, I don't care about memory fragmentation or disk latency. I care about the p1 latency of my API calls.

The cost of complexity

Let’s talk money. pgvector on AWS RDS is cheap until it isn't. You’ll eventually hit the ceiling of a single instance, and then you’re looking at read replicas or partitioning strategies. Managing Postgres partitioning for vectors is a specialized skill that most teams don't have. If your team’s DBA time costs $200/hr, that "cheap" Postgres extension becomes the most expensive line item in your cloud bill.

Pinecone is billed by the "pod" or via serverless throughput. It looks expensive on a spreadsheet—until you calculate the labor cost of a single engineer spending three days debugging a hung Postgres process during a peak traffic event.

Mosaic AI is only "cheap" if you are already in the Databricks ecosystem. If your data is in Delta Lake, using anything else is madness. Exporting, embedding, and syncing to Pinecone adds latency and introduces a point of failure (the ETL pipeline). Mosaic AI stays within the perimeter.

Photo by Markus Stickling on Unsplash

Failure modes and recovery

The nightmare scenario for pgvector is a corrupted index or a vacuum loop that kills your database performance. Recovery means taking a snapshot of the entire multi-terabyte DB and restoring it to a new instance.

With Pinecone, the failure mode is usually an API timeout or rate limiting. These are handled with standard retry logic and exponential backoff. It’s a distributed systems problem, not a storage engine problem. If the index fails, I don't care about the underlying state; I just re-sync from my source of truth.

Mosaic AI’s failure mode is almost always related to the sync trigger between Delta Lake and the vector store. If your Delta table updates, but the vector index doesn't, you have a consistency issue. The fix is usually a TriggerNow call in the Mosaic console. It’s a cleaner failure mode than a database index crash.

What I'd pick, and why

If you have fewer than 1 million vectors and your data is already in Postgres, use pgvector. Period. The overhead of adding another vendor to your stack isn't worth it. Just make sure you tune your hnsw.ef_search correctly—start at 40 and move up only if recall drops below your SLA—and keep your vector column dimension count reasonable (e.g., 768 or 1536).

If you are scaling past 5 million vectors or you need high-concurrency search across multiple microservices, stop using Postgres for this. Move to Pinecone. The serverless performance is predictable, and the lack of operational overhead is a force multiplier for a small engineering team.

If you are a large enterprise already using Databricks for your ETL and AI lifecycle, use Mosaic AI. The integration with Unity Catalog is the "killer feature." It turns vector search from a separate infrastructure project into a simple data governance task.

Don’t be a hero. Unless you are at the scale where you need to manage your own hardware for latency reasons (sub-5ms), you shouldn't be managing your own vector indices. Spend that time on your model fine-tuning instead.

Tags: #postgres #databases #ai #infrastructure

Cover photo by Albert Stoynov on Unsplash.

Don't Let Unity Catalog Migration Become Your Resume Generating Event

Aniket Abhishek Soni — Thu, 24 Sep 2026 14:29:20 +0000

Why I chose this topic: I spent three days last quarter fixing a "simple" metadata sync that locked 200 production jobs out of their own tables because of a misconfigured metastore credential. I’m writing this so you don’t have to drink the same lukewarm coffee at 3:00 AM that I did.

Two years ago, a junior engineer on my team ran a GRANT ALL script on the wrong Hive metastore schema. It took forty minutes for the monitoring alerts to fire, and by then, the ETL pipelines had already overwritten four months of regulatory reporting data with nulls. We spent a week restoring from snapshots and explaining to the CISO why our lineage tracking looked like a crime scene.

Migrating to Unity Catalog (UC) is the industry-standard fix for this chaos, but the migration itself is a minefield. You aren't just moving pointers; you are fundamentally changing how Spark identifies data. If you treat this like a simple "lift and shift," you’re going to wake up to a wall of TABLE_OR_VIEW_NOT_FOUND errors across your entire job fleet.

The real problem

The problem isn't the technology—it's the namespace. Hive metastore uses a two-level namespace: database.table. Unity Catalog introduces a three-level namespace: catalog.schema.table.

When you migrate, you aren't just changing a config; you are rewriting the identity of every single asset in your environment. Most teams try to do this by alias-shifting or partial migrations. Don't. You will end up with "split-brain" syndrome, where your legacy jobs are looking at the Hive metastore while your modern jobs are looking at UC, and your internal governance team is having a heart attack because they can’t find the lineage for 40% of the data.

Photo by Werzk Luuuuuuu on Unsplash

Step One: Inventory and namespace mapping

You cannot migrate what you cannot count. Do not start by moving tables. Start by dumping your existing Hive metastore into a CSV. You need a source-of-truth mapping file that links your legacy db.table to your new catalog.schema.table.

I use a simple PySpark script to iterate through the legacy catalog and generate a JSON manifest. If a table doesn't have a clear home in the new structure, delete it. If you have legacy junk, use this migration as the excuse to purge it.

# Extract current state to mapping file
databases = spark.catalog.listDatabases()
mapping = []

for db in databases:
    tables = spark.catalog.listTables(db.name)
    for t in tables:
        mapping.append({
            "legacy_name": f"{db.name}.{t.name}",
            "new_name": f"prod_catalog.{db.name}.{t.name}"
        })

import json
with open('migration_map.json', 'w') as f:
    json.dump(mapping, f)

Step Two: Testing the UC connectivity layer

Before you touch the production jobs, verify your cluster can actually talk to the UC-enabled workspace. Unity Catalog requires specific service principal permissions. If your cluster is still using Instance Profiles, you are going to hit an authentication wall.

Ensure your spark-conf includes the necessary authorization settings. If you’re using Databricks, verify that your cluster is running a runtime version that supports UC—anything below 11.3 LTS is a non-starter.

# Verify spark configuration for UC
spark.databricks.io.cache.enabled true
spark.sql.catalog.spark_catalog com.databricks.sql.managedcatalog.UnityCatalog
spark.databricks.acl.dfAcls.enabled true

If you don't set spark.sql.catalog.spark_catalog, your jobs will keep trying to default to the legacy Hive metastore, and you will spend hours debugging why your SELECT statements are returning empty results for tables you know exist.

Step Three: The phased transition with aliases

Don't do a "Big Bang" migration. Use a phased approach where you re-point the spark_catalog to UC, but keep a legacy catalog alias available for a fallback.

Modify your production job deployment pipeline to accept a CATALOG_NAME variable. If something goes sideways, you can flip the environment variable back to hive_metastore in the CI/CD pipeline and redeploy in seconds, rather than manually editing 200 jobs.

# CI/CD variable structure
jobs:
  etl_process:
    parameters:
      target_catalog: "prod_catalog"
    spark_conf:
      spark.sql.catalog.my_legacy_hive: "hive_metastore"

In your code, switch from SELECT * FROM db.table to SELECT * FROM {target_catalog}.db.table. This abstraction layer is the only thing that saved my team from a total outage during our migration.

Step Four: Validating the lineage and permissions

Once the jobs are running on UC, you need to verify that your IAM roles moved correctly. Hive metastore is notoriously lax; Unity Catalog is strict. A common failure mode is that your job has permission to read the data in S3/ADLS but lacks the USE CATALOG or USE SCHEMA permissions in UC.

Run this audit script immediately after your first successful job run. It checks if the principal actually has access to the underlying storage volume.

# Permission audit snippet
def check_access(catalog, schema, table):
    try:
        spark.sql(f"DESCRIBE TABLE {catalog}.{schema}.{table}").show()
        return True
    except Exception as e:
        print(f"FAILED ACCESS: {catalog}.{schema}.{table} - {e}")
        return False

Lessons learned from production

The "External Location" Trap: If you don't explicitly define your S3/ADLS storage paths as External Locations in UC, your jobs will fail with a cryptic PERMISSION_DENIED error, even if the underlying cloud IAM role is correct. You must grant CREATE EXTERNAL TABLE on the location to the metastore admin.
Views are not Tables: If you have complex Hive views, they will not migrate automatically. You have to recreate the DDL. If you try to copy the definition directly, you will hit issues with Hive-specific UDFs that don't exist in the UC namespace.
The Metadata Sync Lag: When you sync your legacy metastore to UC, there is a synchronization window. Do not run any writes during this window. I once had a job write data to the legacy path while the sync was moving the schema to UC, resulting in a dual-path write that corrupted the partition metadata.
Global Temp Views: If your jobs rely on GLOBAL_TEMP_VIEW, be aware that these are not automatically migrated. They are session-bound in the legacy metastore. You need to refactor these into temporary tables or persistent schema tables.

Conclusion

Migrating 200 production jobs is not a technical challenge; it is a discipline challenge. If you automate your mapping, abstract your catalog names, and test your permissions before you flip the switch, you can do this without downtime. If you try to hard-code your way through it, you’ll be the one explaining to management why the dashboard is blank on Monday morning.

Try it: Take your lowest-traffic, non-critical production job today. Refactor it to support a variable catalog parameter. If you can move that one job to Unity Catalog without breaking the output, you have a blueprint for the other 199.

Tags: #databricks #spark #engineering #migration

Cover photo by Tyler on Unsplash.

The Snowflake Iceberg Pivot: Why Your Data Warehouse Should Be a Storage Engine

Aniket Abhishek Soni — Tue, 22 Sep 2026 10:18:24 +0000

Ninety percent of the "performance" you pay for in Snowflake is actually just a tax on your inability to move your data. If you’re like most engineers I’ve worked with in fintech, you’ve convinced yourself that moving to an open table format like Iceberg means you have to go build a bespoke Spark/Trino stack on top of an S3 bucket and deal with the operational headache of catalog synchronization.

You are wrong. And that laziness is costing your company a fortune.

For years, we treated Snowflake as both the engine and the prison. We stored data in proprietary micro-partitions that were functionally invisible to every other tool in the stack. If you wanted to run a quick Python script to do some ML training, you had to clone the table, unload to CSV/Parquet, or pay for an expensive Snowflake compute cluster just to read a few gigabytes of rows.

Snowflake-managed Iceberg tables change the math. They allow you to maintain the convenience of a managed service while storing the actual data files—the Parquet files—in your own cloud storage. You aren't just saving on storage costs; you are effectively decoupling your state from your compute.

Why the common approach falls short

The "classic" Snowflake workflow is a black box. You CREATE TABLE and hope for the best. When you leave, you’re forced to perform an UNLOAD operation, which is essentially a massive export job that burns compute credits, messes with your time-travel metadata, and leaves you with a pile of flat files that lack the schema evolution capabilities your downstream apps need.

I’ve seen junior engineers try to "optimize" this by spinning up AWS Glue crawlers and manual Athena partitions. The failure mode is predictable: the Snowflake metadata and the Glue catalog drift apart within three weeks. You end up with a "source of truth" that isn't true, a bunch of broken dashboards, and a panicked Friday afternoon spent patching manifest files.

The common approach relies on the vendor’s proprietary binary format. If Snowflake goes down—or, more likely, if your bill hits a threshold where Finance starts asking questions—you are locked. You can't just point a Trino cluster at your S3 bucket and expect it to work. You need a migration strategy. With managed Iceberg tables, that migration strategy is just a standard SQL command.

Photo by Andris Gangis on Unsplash

The mechanics of the shift

Implementing this isn't a complex migration. It's a configuration change. You move from the default TABLE type to an ICEBERG table managed by Snowflake.

When you execute:

CREATE TABLE my_iceberg_table (
  id STRING,
  event_time TIMESTAMP,
  payload VARIANT
)
USING ICEBERG
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'my_s3_external_volume'
BASE_LOCATION = 'my_iceberg_data';

You are essentially telling Snowflake: "I want you to handle the heavy lifting of metadata management, compaction, and schema evolution, but keep the actual Parquet files in my bucket."

The EXTERNAL_VOLUME is the key here. It defines the bridge between Snowflake and your S3/GCS bucket. Once this is set up, Snowflake acts as the "Engine," but the "Storage" lives in your VPC. If you decide to add a Trino cluster or a local DuckDB instance for quick testing, you just point them at the same S3 bucket. Because the table is using the Iceberg spec, your downstream engines understand the manifest files, the snapshot history, and the schema evolution without you needing to do a single EXPORT job.

Real-world failure modes and how to fix them

I’ve seen teams try to force this into every table they own. Don't do that. Managed Iceberg tables come with trade-offs.

First, the "Snowflake-managed" label means exactly that. If you manually delete a Parquet file from your S3 bucket, Snowflake’s catalog will get corrupted. You cannot treat your underlying S3 storage as a playground. You must use Snowflake’s ALTER TABLE ... REFRESH command to sync metadata if you’ve been messing with the files outside of the Snowflake engine.

Second, consider your write patterns. If you have a high-frequency streaming ingestion (think 50,000 inserts per second), Snowflake’s native format is still optimized for that. Iceberg introduces overhead because it has to generate new manifest files for every transaction. If you try to run massive, small-batch transactions against an Iceberg table, you will see performance degradation compared to standard Snowflake tables.

The strategy I use is "Tiered Persistence." My high-velocity staging tables remain in the proprietary format. Once the data reaches a state of "rest" (e.g., daily aggregates or cleaned facts), I move them into Iceberg managed tables for long-term storage and cross-engine accessibility.

Photo by David Trinks on Unsplash

The objections (and my answers)

The most common pushback I hear is, "But if I use Iceberg, I lose the proprietary Snowflake performance features like Search Optimization or Clustering Keys."

Yes, you do. And that's the point. If you need Search Optimization for a dataset, that dataset is likely part of your core product loop. You should pay the premium for that. But for 80% of the data sitting in your warehouse—the cold data, the audit logs, the historical facts—you aren't actually using those features. You’re paying for them because you didn't have a choice. Now you do.

Another objection: "It introduces complexity in IAM roles and bucket policies."

True, setting up the STORAGE INTEGRATION and the EXTERNAL VOLUME is more work than just running CREATE TABLE. You need to manage bucket policies, trust relationships, and VPC endpoints. But welcome to being a senior engineer. If you’re complaining about setting up an IAM role once to save your company 40% on their storage bill and gain absolute portability, you’re in the wrong seat.

Finally, some fear that Snowflake will "deprioritize" Iceberg performance to push users back to native tables. My read on the market is the opposite. Snowflake is terrified of the "Databricks effect." They know they have to play nice with open standards or they become the new Oracle. Supporting Iceberg as a first-class citizen is their way of keeping you from leaving entirely.

Conclusion

The goal of a senior engineer isn't to be a fanboy of a specific platform; it's to build a resilient system. Using Snowflake-managed Iceberg tables gives you the best of both worlds: the operational ease of a SaaS warehouse and the architectural sovereignty of open storage.

Stop paying for storage hostage-taking. Start moving your production tables to Iceberg, configure your external volumes, and give your organization the option to pivot when the economics stop making sense. You get to keep your tooling, your query history, and your dashboards, but you stop being an indentured servant to a single vendor's storage format.

In this business, control is the only currency that matters. If you can’t move your data without a massive, multi-week project, you don't own your data. Fix that today.

Cover photo by Alex Pudov on Unsplash.