DEV Community: Aniket Abhishek Soni

Is Your Lakehouse Architecture Just a High-Priced Tax on Your Data Team?

Aniket Abhishek Soni — Wed, 01 Jul 2026 22:48:54 +0000

Ninety-two percent of data platform migrations I’ve audited in the last three years ended up costing more in "operational tax" than they saved in raw compute efficiency. We talk about TCO (Total Cost of Ownership) like it’s a math problem, but it’s actually a human behavior problem. The choice between BigQuery and Databricks SQL isn't about which engine can scan a petabyte faster; it’s about whether you want to spend your weekends debugging slot allocation or tuning Delta Lake vacuum intervals.

I’ve spent the last six years keeping financial services and healthcare workloads upright. I’ve seen BigQuery’s INFORMATION_SCHEMA save a QBR and I’ve seen Databricks’ OPTIMIZE commands accidentally lock a table during a critical financial close. If you’re choosing based on a vendor slide deck, you’re already behind. Here is the field guide to not blowing your cloud budget while trying to build a "lakehouse."

1. The "Slot" Trap vs. The "Warehouse" Mirage

BigQuery’s shift to Edition pricing (Standard, Enterprise, Enterprise Plus) was the industry’s way of saying "we want predictable, Databricks-style billing." But here’s the reality: if you aren't using Reservations, you aren't using BigQuery. I’ve seen teams blow $50k in a weekend because a rogue SELECT * on a multi-petabyte partitioned table hit on-demand pricing.

In Databricks, you’re buying "SQL Warehouses." The failure mode here is over-provisioning. If you leave a 2XL warehouse running 24/7 because your analysts "need it to be fast," you’re lighting money on fire. BigQuery is inherently multi-tenant; Databricks is isolated. If you have 50 different departments, BigQuery manages the concurrency better out of the box. If you have a few massive, complex jobs that need predictable performance, you want a dedicated Databricks SQL Warehouse.

Photo by Monisha Selvakumar on Unsplash

2. Partitioning Isn't Optional; It’s Your Only Defense

In BigQuery, if you don't filter by your partition column (usually _PARTITIONDATE or a timestamp), you are paying for a full table scan. Period. I’ve seen junior engineers write queries that scanned 40TB of data for a single dashboard refresh.

In Databricks, the Z-ORDER command is your best friend. If you aren't Z-ordering your high-cardinality columns, you’re missing the point of Delta Lake.

-- BigQuery: Never skip the filter, or get fired.
SELECT * FROM `my_project.my_dataset.events` 
WHERE _PARTITIONDATE >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY);

-- Databricks: Z-ORDER is the performance multiplier.
OPTIMIZE my_table 
ZORDER BY (customer_id, event_type);

If you ignore these, you’re paying for the vendor’s inefficiency. In BigQuery, you pay for the scan. In Databricks, you pay for the time the cluster spent scanning.

3. The "Vacuum" and "Snapshot" Tax

One of the biggest hidden costs in Databricks is storage bloat. Because Delta Lake keeps snapshots for time travel, if you don't run VACUUM regularly, your storage bill will grow indefinitely. I’ve seen terabytes of "deleted" data sitting in S3/ADLS buckets that Databricks users forgot to prune.

-- Databricks: Pruning old snapshots to save storage costs
VACUUM my_table RETAIN 168 HOURS; -- Keep 7 days of history

BigQuery handles this via internal TTLs on datasets and tables. It’s "set it and forget it." If you lack the discipline to manage a VACUUM schedule, Databricks will eventually bite your budget in the ass.

4. Concurrency is a Lie

Marketing teams love to talk about "limitless concurrency." Both platforms handle it, but they handle it differently. BigQuery uses a distributed scheduler that tries to fit your query into the available slots. If you have 2,000 slots and you trigger 5,000 slots worth of work, BigQuery will queue your queries. That's a latency hit, but not a failure.

Databricks SQL Warehouses (Serverless) have a "scaling out" threshold. When your cluster gets slammed, it spawns new clusters to handle the load. This is great until you hit your regional limit for cloud instances or your bill hits the stratosphere because you triggered five extra clusters to run a 2-second query. Monitor your dbr_sql_warehouse_scaling_events like a hawk.

5. The "Governance" Penalty

Healthcare data requires ironclad access control. BigQuery’s integration with IAM is native and absolute. If you are already deep in the Google Cloud ecosystem, BigQuery’s row-level security and column-level masking (via Policy Tags) are incredibly easy to implement.

Databricks uses Unity Catalog. It’s powerful, but it’s a second layer of governance you have to maintain outside of your cloud provider’s IAM. If your organization is already struggling with identity management, adding Unity Catalog adds another point of failure. Don't underestimate the "cognitive load" of managing two sets of permissions.

6. Cold Starts and Serverless Latency

BigQuery is always "warm." You send a request, it runs. Databricks SQL Serverless has gotten much faster, but there is still a spin-up time for those clusters if they’ve been idle. If your users are clicking around a Looker dashboard, they will notice the 3-5 second lag on the first click if your warehouse was cold.

If your users are impatient (and they are), you will end up keeping warehouses running longer than you need to, just to avoid the "Why is the dashboard slow?" Slack messages. That’s a hidden cost of the Databricks architecture.

Photo by Giancarlo Revolledo on Unsplash

7. Vendor Lock-in is a Myth; Portability is a Pipe Dream

People choose Databricks because they want to "own" their data in Parquet/Delta format. They choose BigQuery because they want it to "just work."

Here is the truth: you aren't going to migrate 500TB of data from BigQuery to Databricks because you had a bad quarter. You are locked in by your ingestion pipelines and your BI tool semantic layers. Pick the one that fits your current team’s skillset. If your team knows Spark, Databricks is the path of least resistance. If your team is SQL-first and hates infrastructure management, BigQuery is the only logical choice.

Conclusion

BigQuery is a managed service that demands you play by its rules—partitioning, slot management, and Google-native IAM. Databricks is a platform that gives you more control but demands you manage the complexity—vacuuming, Z-ordering, and catalog governance.

If you want a "lakehouse" that functions like a database, pay the BigQuery tax and embrace the simplicity. If you want a data science powerhouse that happens to run SQL, pay the Databricks tax and hire a good platform engineer to clean up your mess.

Which one is keeping your CFO up at night, and what are you going to do about it tomorrow morning?

Cover photo by Gavin Allanwood on Unsplash.

Beyond IAM Policies: How to Actually Secure Cross-Account Data Shares

Aniket Abhishek Soni — Tue, 30 Jun 2026 16:05:08 +0000

03:14 AM. The PagerDuty alert hit my phone like a physical blow. "Critical: Data Integrity Failure – Account B ETL Job Failing."

Six months ago, our cross-account data sharing strategy was a nightmare of IAM resource-based policies. We had S3 bucket policies spanning three accounts, thousands of lines of JSON, and a collective prayer that no developer accidentally added s3:GetObject to a Principal: * block. It was a ticking time bomb of "Access Denied" errors and "Wait, why can the marketing team see HIPAA-regulated PII?" queries.

Today, those S3 policies are gone. We use AWS Lake Formation governed tables. The difference isn't just cosmetic; it’s the difference between auditing a 4,000-line JSON file and reading a single, declarative grant statement.

What we saw

The incident started when an ETL job in our Analytics account (Account B) stopped pulling data from our Production account (Account A). The error wasn't a clean "Access Denied." It was an AccessDeniedException coming from the AWS Glue Data Catalog, even though the IAM role had explicit glue:GetTable and s3:GetObject permissions.

Our first instinct—the "false lead"—was to assume the S3 bucket policy was malformed. We spent 45 minutes diffing the JSON in bucket-prod-data against our internal "Gold Standard" template. We checked the Principal ARN. We checked the Condition: StringEquals: aws:PrincipalOrgID. Everything looked perfect.

The symptoms were deceptive. The job could list the partitions, but as soon as the Spark executor tried to read the underlying Parquet files, the job would hang for 30 seconds and then crash. We were looking at a classic "permission mismatch" between the Data Catalog layer and the Data Storage layer.

Photo by Katelyn G on Unsplash

Root cause

The root cause was the "confused deputy" problem masked by overly permissive IAM policies. We had been relying on s3:GetObject and a Glue resource policy that was essentially a catch-all.

When we migrated to Lake Formation, we didn't fully sever the tie to the underlying S3 policy. We had a hybrid mess where Lake Formation was managing the metadata, but the S3 bucket policy was still trying to enforce access. Because we hadn't set up the LF-Tag based policies correctly, the Glue Service Role was struggling to resolve the identity of the cross-account requester.

Specifically, we were violating the "Lake Formation-managed S3 locations" requirement. If you register a path in Lake Formation (e.g., s3://data-prod-finance/), you must ensure that the IAM role used by the remote account has the lakeformation:GetDataAccess permission. We hadn't included this. We were trying to authenticate using standard IAM, while the bucket was configured to only accept requests through the Lake Formation engine.

The offending configuration was this:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {"AWS": "arn:aws:iam::ACCOUNT_B:role/AnalyticsRole"},
      "Action": "s3:GetObject",
      "Resource": "arn:aws:s3:::data-prod-finance/*"
    }
  ]
}

This looks fine to a junior engineer. But if the bucket has s3:BlockPublicAccess enabled and you’ve enabled Lake Formation, that policy is essentially being bypassed or, worse, ignored in favor of the Lake Formation grant. The failure happened because we didn't have the glue:GetTable and lakeformation:GetDataAccess dance fully synchronized.

Photo by FlyD on Unsplash

The fix

We nuked the bucket policies. Seriously. We stripped the cross-account S3 bucket policies down to only allow the Lake Formation service role to access the data.

Then, we implemented a proper Lake Formation Cross-Account Grant. Instead of sharing the S3 bucket, we shared the Data Catalog table.

We registered the S3 location in Account A under Lake Formation: aws lakeformation register-resource --resource-arn s3://data-prod-finance/.
We granted the SELECT permission on the specific Glue table to the external Account B ID: aws lakeformation grant-permissions --principal DataLakePrincipalIdentifier=arn:aws:iam::ACCOUNT_B:root --permissions SELECT --resource '{ "Table": { "DatabaseName": "finance", "Name": "transactions" } }'.
In Account B, we created a "Resource Link" pointing to the shared table in Account A.

By moving the permission logic from the S3 bucket policy into the Lake Formation GRANT command, we moved from "who can touch these files?" to "who can run queries on this table?" The auditors loved it because the Get-LFPermissions API response is structured, human-readable, and doesn't require a degree in IAM JSON parsing to verify.

What we changed so it never happens again

We stopped treating security as a post-deployment checklist.

First, we implemented a strict "No Manual IAM" policy for data access. If a team needs cross-account access, they submit a PR to our Terraform repository that defines a aws_lakeformation_permissions resource. If it's not in the HCL, it doesn't exist. This prevents the "drift" that caused our 3:00 AM incident.

Second, we moved to an LF-Tag based access control (LF-TBAC) model. Instead of granting access to specific tables, we tag tables with Classification: PII or Environment: Prod. The cross-account role gets access to everything tagged Environment: Prod. When a new table is added, it’s automatically shared if it carries the right tag. This eliminated the manual work of adding new tables to the grant list, which was a constant source of "Access Denied" tickets.

Finally, we use AWS CloudTrail to monitor GetDataAccess events. If an account attempts to query a table they don't have permission for, it fires an alarm in our SOC. Before, we were flying blind; now, we see the attempt in real-time.

The real lesson? Don't fight the platform. If you’re still trying to manage cross-account data sharing using S3 bucket policies and IAM roles, you’re just building technical debt that will eventually wake you up at 3:00 AM. Stop it. Move the governance to the layer that was built to handle it. Your auditors—and your sleep schedule—will thank you.

Cover photo by Albert Stoynov on Unsplash.

Why I Stopped Caring Which Table Format You Use

Aniket Abhishek Soni — Sat, 27 Jun 2026 23:18:18 +0000

Roughly 82% of the production data pipelines I audited in 2025 were still relying on legacy Hive metastores as their primary source of truth, despite moving to "modern" cloud data lakes.

That statistic matters because it explains why we’re all so obsessed with the Delta Lake vs. Apache Iceberg debate. We aren't fighting over technical superiority; we’re fighting over the fear of vendor lock-in. We keep choosing formats like they’re lifetime marriages, terrified that picking the "wrong" one will force a multi-petabyte migration in three years.

Why I chose this topic: After spending a decade migrating between proprietary formats and open-source standards, I’m tired of the tribalism. Interoperability is finally here, and it’s time we treated these formats as interchangeable storage layouts rather than religious identities.

Most engineers use the term "table format" to describe the metadata layer, but they rarely understand the actual transition from manifest files to snapshots. We treat delta.enableDeletionVectors or iceberg.engine.hive.enabled as magic flags, but when the commit lock fails at 3:00 AM on a Sunday, your deep-seated belief in one format over the other won't stop the partition skew.

How it actually works

The industry has moved past the "one format to rule them all" era. The reality of 2026 is that the storage format—whether it’s the Delta _delta_log or the Iceberg metadata/ directory—is increasingly becoming an implementation detail hidden behind abstraction layers like Databricks UniForm or the Iceberg REST catalog.

Take UniForm (Universal Format). It isn’t just a marketing slide; it’s a translation layer. When you enable it on a Delta table, you’re essentially running an asynchronous background process that writes Iceberg-compatible metadata alongside your Delta logs.

In code, it looks deceptively simple. You aren't rewriting your data; you're just updating the table properties:

ALTER TABLE my_table SET TBLPROPERTIES (
  'delta.universalFormat.enabledIceberg' = 'true'
);

When this runs, the engine maintains the Delta transaction log while simultaneously generating the Iceberg metadata.json and manifest files. An engine like Trino or StarRocks doesn't need to know the Delta log exists; it points to the Iceberg metadata and reads the Parquet files as if they were native Iceberg data.

This is the "interoperability" endgame. You keep the high-performance write features of Delta—like deletion vectors for CDC or Z-Ordering for query acceleration—while exposing the table to the entire Iceberg-compatible ecosystem. You aren't choosing a side; you’re choosing a storage engine that speaks two languages.

Photo by Viktor Talashuk on Unsplash

The tradeoffs nobody mentions

If this sounds like a free lunch, it isn't. The cost is "metadata bloat" and "write amplification."

When you enable UniForm, you are effectively doubling the metadata overhead of your table. If your pipeline performs frequent, high-concurrency writes, the asynchronous translation process can lag. I’ve seen cases where a downstream Iceberg-native reader (like a legacy Presto cluster) missed the most recent 15 minutes of data because the background manifest generation hadn't caught up to the Delta transaction.

Then there’s the failure mode of the "dual-writer" trap. If you have an application that attempts to write to the Iceberg metadata while your main pipeline is writing to the Delta log, you hit a consistency nightmare. You’ll see ConcurrentModificationException errors that are incredibly difficult to debug because the logs don't clearly state which format failed the commit.

Another issue: schema evolution. Delta’s schema evolution is notoriously permissive—you can add columns almost anywhere. Iceberg is stricter, enforcing partition evolution and column ID mapping. When you bridge them, you are forced to abide by the intersection of their constraints. If you try to perform a complex ALTER TABLE that Iceberg doesn't support but Delta allows, the translation layer breaks. You end up with a table that is valid in Delta but "corrupted" in the eyes of your Iceberg-based tools.

Finally, consider the maintenance tax. You now have two sets of vacuuming and snapshot expiration policies to manage. If you run VACUUM on your Delta table but don’t properly expire the corresponding Iceberg snapshots, you end up with "orphan" metadata files that cost you cloud storage money and confuse your catalog.

Photo by Tyler on Unsplash

When to reach for it (and when not to)

Reach for a cross-format architecture if your organization is fragmented. If you have a Databricks-heavy team of data engineers but an analytical layer (BI, ad-hoc SQL) that runs on Trino, StarRocks, or Flink, UniForm is a godsend. It prevents the "data siloing" that occurs when the BI team complains they can't see the latest metrics because they aren't on the Databricks cluster.

It is also the right move if you are planning a long-term migration. Instead of a "big bang" migration, you can flip the UniForm switch, test your secondary compute engine, and slowly shift workloads over months.

Do not reach for it if you are a "single-stack" shop. If your entire pipeline—from raw ingestion to the final dashboard—lives inside the Databricks ecosystem, adding Iceberg translation is unnecessary complexity. You are adding a layer of risk and metadata overhead for zero business value.

Don't reach for it if you have severe latency requirements for ingestion. If you need sub-second visibility into your data, the asynchronous nature of the translation layer will be your enemy. The delay between the primary commit and the secondary metadata generation is a latency floor you cannot bridge.

Conclusion

The "Delta vs. Iceberg" war was useful for driving innovation, but it’s over. We have entered the era of the "multi-format lakehouse."

Stop treating table formats as your primary architectural decision. The real decision is how you manage your catalog and how much complexity you’re willing to trade for portability. In 2026, the best engineer in the room isn't the one who can recite the pros and cons of Parquet file layouts; it's the one who knows how to configure a translation layer so the business doesn't have to care about the underlying format at all.

Keep your schema tight, watch your metadata bloat, and stop choosing sides. The infrastructure is finally catching up to the reality that we just want our data to be readable by whatever tool we pick up today.

Cover photo by Intricate Explorer on Unsplash.

Is Snowflake-managed Iceberg the ultimate Trojan horse or your data’s salvation?

Aniket Abhishek Soni — Thu, 25 Jun 2026 22:34:52 +0000

Snowflake-managed Iceberg tables are the only way to escape the platform's proprietary storage format without sacrificing the performance that makes you pay the premium.

Why I chose this topic: I spent four years watching migration projects die in the "vendor lock-in" valley of death. I’m writing this because I’m tired of seeing engineers choose inferior tech just because they’re terrified of a data platform they actually like using.

It was 3:14 AM on a Tuesday. The PagerDuty alert hit my phone with the urgency of a heart attack: CRITICAL: External Table Latency Exceeded Threshold. Our internal dashboard, which pulls telemetry from an S3 bucket via Snowflake’s external tables, had effectively died. Queries that usually took 300ms were timing out at the 60-second mark.

I rolled out of bed, fired up the VPN, and saw the mess. We were trying to join a massive, partitioned Parquet dataset against a local Snowflake dimension table. The metadata overhead for the external table was choking the query engine. Every time we added a new partition, the MSCK REPAIR TABLE or the periodic metadata refresh would lag, and the query planner would go into a death spiral trying to reconcile the file manifest.

What we saw

The symptoms were classic: QUERY_HISTORY showed massive EXTERNAL_SCAN times. At first, my junior engineer assumed it was an S3 throttling issue. We checked the AWS CloudWatch metrics for the bucket. Nothing. The bandwidth was fine, the API requests were well within limits, and the latency was stable.

We then spent two hours chasing ghosts in the EXPLAIN plan. We thought the file format (Parquet with Snappy compression) was the problem, so we tried re-partitioning the data into smaller chunks. It made it worse. The overhead of Snowflake tracking thousands of small files in a standard external table was creating a metadata bottleneck that simply couldn't scale. We were fighting the platform's inability to reconcile the "source of truth" in S3 with the "state of the world" in Snowflake.

Photo by Willian Justen de Vasconcellos on Unsplash

Root cause

The root cause was the inherent fragility of standard External Tables. Snowflake doesn't "own" the metadata for standard External Tables; it has to infer it. When you rely on AUTO_REFRESH = TRUE, you’re essentially asking Snowflake to play a high-stakes game of catch-up with S3 event notifications.

We were using:

CREATE OR REPLACE EXTERNAL TABLE raw_events (
    data variant
)
PARTITION BY (event_date)
LOCATION = '@s3_stage/events/'
FILE_FORMAT = (TYPE = PARQUET);

The issue is that the metadata is detached from the data. If the S3 event notification fails—or if a backfill process bypasses the staging area—the manifest drifts. Snowflake’s query optimizer was forced to perform a full directory listing of the S3 prefix because it couldn’t trust its own stale manifest. That’s a 10-second metadata latency penalty on every query, regardless of the compute warehouse size.

Photo by Isaac Smith on Unsplash

The fix

We migrated the entire pipeline to Snowflake-managed Iceberg tables. The switch was surprisingly trivial. By using the CATALOG_INTEGRATION feature, we kept the physical files in our own S3 bucket while letting Snowflake manage the Iceberg metadata (the metadata.json files and snapshot pointers).

The SQL change looked like this:

CREATE OR REPLACE ICEBERG TABLE managed_events (
    id STRING,
    event_timestamp TIMESTAMP,
    data VARIANT
)
CATALOG = 'SNOWFLAKE'
EXTERNAL_VOLUME = 'my_s3_volume'
BASE_LOCATION = 's3://my-bucket/iceberg/events/';

Suddenly, the metadata was no longer a guessing game. Because Snowflake manages the Iceberg metadata, it holds the absolute state of the table. The query optimizer doesn’t need to scan S3 buckets; it reads the latest snapshot pointer from the Iceberg metadata file. The 10-second latency vanished instantly, dropping back to sub-second responses.

More importantly, because this is actual Iceberg (v2 spec), the data is perfectly readable by Spark, Trino, or any other engine that supports the Iceberg API. If Snowflake ever decides to hike their storage premiums or if we decide to move a portion of our workload to an EMR cluster, the data is already in an open, portable format. We didn't leave Snowflake; we just stopped letting them hold our data hostage in a proprietary silo.

What we changed so it never happens again

We implemented a strict "Iceberg-first" policy for all new analytical datasets. No more standard External Tables. Period.

We also automated the validation of our metadata. We now run a daily SYSTEM$GET_ICEBERG_TABLE_INFORMATION check against our critical tables to ensure the snapshot age is within acceptable bounds. If the metadata hasn't been updated in 24 hours, the job alerts the team—not because the query is slow, but because it indicates a break in the Iceberg commit log.

We also decoupled our storage from our compute by moving to an EXTERNAL_VOLUME architecture. This allows us to point Snowflake at an S3 bucket while maintaining full control over the lifecycle policies of the data itself. If we want to transition the data to Glacier or delete it after 90 days, we can do it directly via S3 lifecycle rules without Snowflake knowing or caring.

The biggest shift was psychological. We stopped treating Snowflake as a "black box" where data goes to die. By using managed Iceberg, we treat Snowflake as a high-performance compute engine that happens to be sitting on top of an open data lake.

The vendor lock-in narrative is often a crutch for bad architecture. You don't have to leave the platform to own your data. You just have to stop using the proprietary features that anchor you to the platform's specific storage implementation. Snowflake-managed Iceberg gives you the best of both worlds: the speed of a premium warehouse and the sovereignty of an open-source standard. Use it, or keep waking up at 3:00 AM to fix metadata drift. Your choice.

Cover photo by Nathan Anderson on Unsplash.

Why you should stop manual Z-ordering today

Aniket Abhishek Soni — Thu, 25 Jun 2026 12:31:38 +0000

If you are still manually running OPTIMIZE table ZORDER BY (col) in your production pipelines, you are wasting engineering cycles on a legacy pattern that Delta Lake has already solved.

I see teams treating Z-ordering like a permanent architectural requirement rather than a stopgap for inefficient storage layouts. Most engineers I talk to are terrified to switch to Liquid Clustering because they don't trust the black box. They prefer the illusion of control that comes with writing custom Spark jobs to re-cluster data every night.

It’s time to stop babysitting your partitions.

The contenders

Z-ordering is the manual transmission of the data world. You designate specific columns, you run a command, and you hope your data distribution remains somewhat balanced. It’s effective, but it’s brittle. If your query patterns shift—say, from filtering by user_id to filtering by event_timestamp—your Z-order becomes a liability, actively slowing down queries that don't match your original index.

Liquid clustering, introduced in Databricks Runtime 13.3 LTS, is the automatic transmission. It uses multi-dimensional clustering to adapt your data layout based on the actual usage patterns detected by the Delta engine. You define the clustering columns once, and the table optimizes itself as data flows in. No more OPTIMIZE jobs. No more guessing which columns matter most.

Photo by Logan Voss on Unsplash

The operational tax

When you run Z-ordering, you are paying a massive "Ops Tax." You need a dedicated cluster to run the OPTIMIZE job. You need to manage the frequency of that job. If you trigger it too often, you’re burning compute costs; trigger it too little, and your read performance degrades as the table accumulates small files and data skew.

I’ve seen teams with hundreds of tables spend upwards of 20% of their total DBU budget just on maintenance tasks like OPTIMIZE and VACUUM. In one healthcare project, we spent three days a month just tuning the Z-order columns for a 50TB fact table because the business changed their primary reporting dimensions.

Liquid clustering changes the math. Because it is incremental and integrated into the write path (via CLUSTER BY), the "maintenance" is baked into the ingestion. You pay a slight overhead during the write, but you stop paying the "maintenance tax" of running huge, batch-heavy OPTIMIZE jobs. If your write throughput is the bottleneck, this is a trade-off. If your compute costs are the bottleneck, this is a massive win.

The failure modes

Z-ordering is prone to "job explosion." If you have a large table and your OPTIMIZE job fails, you are often left with a partial state. If you aren't using Delta’s transaction logs effectively, or if your job fails mid-rewrite, you have to rollback or perform a full re-process. I’ve spent many early mornings debugging ConcurrentModificationException errors triggered by an OPTIMIZE job hitting a streaming ingestion window.

Liquid clustering is significantly more resilient. Because it handles clustering at the write layer, it is transactional by design. You don't have to worry about the "maintenance window" because there isn't one. The failure mode shifts from "my maintenance job crashed" to "my write job is slightly slower."

However, don't let the marketing fool you: Liquid Clustering isn't magic. It has a limit on the number of clustering columns—typically 4. If you have a table where you need to filter by 10 different dimensions, you’re going to hit a wall. In my experience, if you need more than 4 columns for clustering, your data model is the problem, not the engine.

Photo by bert b on Unsplash

Measuring the delta

How do you know if you should switch? Don't guess. Pull your DESCRIBE DETAIL stats and look at the numRecords and sizeInBytes. More importantly, dig into the delta.history and delta.operationMetrics.

If you are currently Z-ordering, look at the numRemovedFiles vs numAddedFiles in your OPTIMIZE metrics. If those numbers are consistently massive, you are churning your storage layer unnecessarily.

To measure the efficacy of Liquid Clustering, you need to use the EXPLAIN plan. Run a query before the switch and after the switch using EXPLAIN EXTENDED. Look for DataSkipping. If Liquid Clustering is working, you should see fewer PartitionFilters (because we’re moving away from hard partitions) and more DataSkipping stats.

Specifically, look at the min and max values in your file statistics. If Liquid Clustering is doing its job, the range of these values in individual files should be tighter. If you see wide, overlapping ranges for your high-cardinality columns, the engine isn't clustering effectively, and you might need to adjust your CLUSTER BY configuration.

What I'd pick, and why

If you are on Databricks Runtime 13.3 or higher, Liquid Clustering is the default choice. There is almost no scenario in a modern cloud-native stack where I would recommend starting a new project with manual Z-ordering.

The only caveat is cost: Liquid Clustering performs more work during the write phase. If you are doing extreme high-frequency, low-latency streaming (e.g., sub-second ingestion into a real-time dashboard), the added overhead of clustering on the write might impact your P99 latency. In those cases, you might want to ingest into a raw, non-clustered table and then use a background process to move it to a clustered table.

But for 95% of the financial services and healthcare data I’ve worked with—where we deal with massive batch loads and complex analytical queries—Liquid Clustering wins. It removes the human error element. Engineers are terrible at predicting query patterns three months in advance, and Z-ordering requires you to be a psychic.

Liquid clustering acknowledges that we don't know exactly how the data will be queried in the future. It’s an admission that the storage layout should be dynamic.

Stop managing your data like it's 2018. Move to Liquid Clustering, stop the manual OPTIMIZE jobs, and spend your time building features instead of tuning file sizes. You’ll sleep better, and your cloud bill will thank you.

Cover photo by Winston Chen on Unsplash.

Stop waking up at 3 AM: Why your data pipelines must be idempotent

Aniket Abhishek Soni — Sun, 21 Jun 2026 20:57:40 +0000

Why I chose this topic: In my first year as a junior engineer, I pushed a non-idempotent job that double-counted $2M in revenue because I didn't wrap a delete statement in a transaction. I haven't slept through a production alert since, and I’m tired of seeing junior engineers make the same career-limiting mistake.

You ship the job. It passes CI. It runs perfectly in staging. Then, at 3:14 AM, the Airflow scheduler hiccups, a network partition hits your Redshift cluster, or your upstream API returns a 500. The job fails halfway through. You wake up to a PagerDuty alert, a half-loaded partition in S3, and a duplicate record set in your main reporting table. You have two choices: manually clean up the mess for three hours, or click "Clear" on the task and pray it doesn't make things worse.

If you have to "pray" when you hit re-run, your pipeline is garbage.

Idempotency—the property where an operation produces the same result regardless of how many times it’s executed—is the only thing standing between you and a mid-life crisis.

The path you’re choosing

You are currently deciding between two fundamental ways to build data movement: "Append-Only" versus "Replace-by-Key."

Most engineers start with "Append-Only." It’s intuitive. You pull data from a source and write it to the sink. It’s fast. It’s cheap. It’s also a ticking time bomb. The "Replace-by-Key" approach is the boring, professional way to handle data. It requires more compute cycles and a bit of SQL gymnastics, but it guarantees that if you run the job ten times, the state of your database is identical to running it once.

Photo by ACatInABox on Unsplash

The contenders

In the Append-Only corner, we have the "Insert-Append" strategy. You fetch SELECT * from your source and INSERT INTO destination without checking for existing IDs.

In the Replace-by-Key corner, we have "Delete-then-Insert." You identify the window of data you are processing, wipe it from the destination table, and insert the new payload within a single atomic transaction.

The cost of doing business

Append-only pipelines are cheap on paper. You aren't burning CPU cycles checking for pre-existing keys or performing deletes. However, the "hidden cost" is the engineer’s salary. When your append-only job fails at 3 AM, you aren't just paying for the compute; you are paying for the two hours of your life you spend writing ad-hoc DELETE scripts to deduplicate the table.

With a Replace-by-Key strategy, your compute bill might tick up by 5-10%. You are running a DELETE FROM table WHERE processing_date = '2023-10-27' before your INSERT. In a system like Snowflake or BigQuery, this is negligible. If you are running on an old-school Postgres instance with a massive table, yes, you’ll need to partition the table by date to keep the DELETE operation performant. But that’s a design choice, not a failure.

Managing the ops burden

The operational burden of append-only is a nightmare of state management. You have to maintain an "audit" table or a complex WHERE NOT EXISTS clause that slows down as the table grows. I’ve seen developers use ON CONFLICT DO NOTHING in Postgres, which is a start, but it fails to account for updates. What if the source data changed? Now your database record is stale, and your pipeline is "idempotent" but factually wrong.

Replace-by-Key shifts the burden to the schema. You need to ensure your tables are partitioned. If you are using dbt (data build tool), this is the default behavior. A dbt run --select my_model with the --full-refresh flag or the default incremental strategy using delete+insert handles the atomicity for you. If you aren't using a tool that abstracts this, you are effectively building a custom database engine on top of your pipeline. Don't do that.

Photo by Growtika on Unsplash

Failure modes and recovery

Consider the "Partial Write" failure mode. Your job hits a timeout after inserting 50% of the rows.

If you used Append-Only, you now have a partial state. If you re-run it, you have 150% of the data. You now have to manually delete the rows that snuck in during the first failed attempt. It’s a guessing game of WHERE created_at > ....

With Replace-by-Key, the failure mode is clean. Because the operation is wrapped in a BEGIN; DELETE ...; INSERT ...; COMMIT; block, the failure means the database rolls back to the state before the job started. You can hit "Retry" in Airflow or Dagster a thousand times, and the end result remains exactly the same. The "atomicity" of the transaction is your insurance policy.

What I'd pick, and why

I choose the Replace-by-Key strategy every single time.

The caveat is that you must have a deterministic way to define the "key." If your source data doesn't have a natural key or a reliable updated_at timestamp, you’re in trouble. In those cases, I enforce a processing_batch_id or a partition date as the key. If I can't guarantee a clean wipe and replace, I don't build the pipeline.

Don't listen to the people who say "this is over-engineering." They are the same people who are currently fixing a production data quality issue on a Saturday morning.

My advice: Use dbt to manage your incremental models. If you’re writing raw Python or Spark, force yourself to use the DELETE ... WHERE partition = X pattern. If your database doesn't support transactions (looking at you, some early S3-based data lakes), use a "staging-to-production" swap. Write to a temporary table, then use an atomic rename to swap it with the production table.

Sleep is worth more than the few extra dollars in compute costs. Make your jobs boring, make them transactional, and stop waking up in the middle of the night.

Cover photo by Tyler on Unsplash.

What building HIPAA-compliant lakehouses taught me about real-world encryption

Aniket Abhishek Soni — Sat, 20 Jun 2026 17:50:41 +0000

Eighty-two percent of data breaches in healthcare don't happen because of a sophisticated nation-state actor; they happen because a junior engineer accidentally left an S3 bucket open or pushed a cleartext JSON blob containing social security numbers to a shared staging environment.

We obsess over "zero trust" and "encryption at rest," but we rarely talk about the reality of the data lifecycle. If your lakehouse isn't architected for granular, row-level access control, you aren't HIPAA compliant—you’re just waiting for a forensic audit to end your career.

Most engineers treat AES-256 like a magic wand. They check the box for "Server-Side Encryption" on their S3 buckets and assume they’ve satisfied the Privacy Rule. They haven't. Compliance isn't about whether the disk is encrypted; it’s about who can see the decrypted contents and where that data manifests in your logs.

The mechanics of the pipeline

When building a lakehouse (think Databricks on Delta Lake or Snowflake), the "Gold" layer is where compliance goes to die. You have clean, joined, enriched data that happens to contain PHI. If you are still using simple IAM roles to govern access, you are doing it wrong.

You need to implement column-level masking and row-level security (RLS) at the storage abstraction layer. In Databricks, for example, you shouldn't just be granting SELECT on a table. You should be using MASKING FUNCTIONS on columns containing identifiers.

Here is what the actual implementation looks like in a production environment:

CREATE FUNCTION redact_ssn(ssn STRING)
  RETURN CASE WHEN is_member('data_scientists') THEN '***-**-****'
              ELSE ssn END;

ALTER TABLE silver_health_records 
ALTER COLUMN ssn SET MASKED WITH (FUNCTION redact_ssn(ssn));

This is the baseline. But the real "gotcha" happens when your Spark job kicks in. When you run a df.write.mode("overwrite") operation, Spark creates temporary files in your staging directory. If you aren't careful, these temporary files contain the raw, unmasked data. Even if you have masking on the table, the raw data sits in an S3 prefix that your monitoring tools or data discovery crawlers might index.

To fix this, you must enforce ephemeral encryption keys for the shuffle service. In your Spark config, you need:

spark.io.encryption.enabled true
spark.hadoop.fs.s3a.encryption.algorithm SSE-KMS
spark.hadoop.fs.s3a.server-side-encryption.key <your-kms-key-id>

Without spark.io.encryption.enabled, your shuffle files—those bits of data written to disk during a join or a sort—are written in plain text. If a node is decommissioned and the underlying EBS volume isn't wiped immediately, you’ve just created a HIPAA violation.

The tradeoffs nobody mentions

The primary downside of a locked-down, encrypted lakehouse is "performance tax." Every time you introduce a UDF (User Defined Function) for masking or enforce RLS, you break the query optimizer.

When you run a SELECT *, the engine has to evaluate the masking function for every single row. If you’re doing a join across a 50TB dataset, the cost of these functions adds up. Your query latency will spike. I’ve seen teams move from a 10-minute job to a 45-minute job just by adding RLS.

Then there is the issue of "key rot." If you’re using AWS KMS, you’re likely using Customer Managed Keys (CMKs). Managing the lifecycle, rotation, and—God forbid—re-encrypting the data when a key is compromised, is a nightmare. If you lose access to the KMS key, your data is effectively incinerated. There is no "I forgot my password" for an encrypted lakehouse.

Also, logging becomes significantly harder. If you are masking data, your logs need to account for why a user saw a masked value versus the raw value. You end up with a massive metadata overhead. You’re no longer just storing the data; you’re storing the audit trail of who requested the data, what their clearance level was, and which specific masking policy was triggered.

When to reach for it (and when not to)

Use granular masking and RLS when you are building a multi-tenant platform. If your lakehouse serves both clinical researchers and internal billing analysts, you have no choice. The billing team needs the SSN; the researcher needs the diagnosis code but shouldn't know the patient's name. In this scenario, the lakehouse is a tool for data minimization, and these features are your primary defense.

Don't use it when you are running a purely internal, high-throughput analytics pipeline where the "users" are just automated microservices. If you are building a feature engineering pipeline for a machine learning model, and the model only needs the anonymized vector, do the masking before the data hits the lakehouse.

Why? Because if you wait until the data is in the lakehouse to mask it, you’ve already failed the principle of "least privilege." The raw, sensitive data is already sitting in your storage layer. If a developer needs to debug the raw data, they’ll have access. Move the transformation upstream. Perform PII/PHI scrubbing in your ingest layer (your Lambda or Fargate tasks) before the data ever touches the bronze table.

The best way to pass a HIPAA audit isn't to build a fancy gatekeeper at the end of the pipeline; it's to ensure the data is effectively neutralized before it enters your environment.

Conclusion

Compliance is often treated as a bureaucratic checkbox, but in the world of high-scale data engineering, it’s a technical constraint. If you treat PHI as just "another string column," you’re setting yourself up for a catastrophic failure.

Focus on the mechanics: encrypt your shuffle, use native masking functions rather than application-level logic, and always, always scrub at the ingest point. The goal is to make the data useless to anyone who doesn't have the explicit, logged, and audited right to see it. If you can do that while keeping your query times under an hour, you're doing better than most of the industry.

Cover photo by Tyler on Unsplash.

Stop letting bad data break your production pipelines

Aniket Abhishek Soni — Fri, 19 Jun 2026 17:10:46 +0000

Two years ago, a bad batch of FX rates from a vendor hit our silver layer, wiped out the historical aggregates, and triggered a margin call for a client. We spent 48 hours manually re-processing files, running DELETE statements on partitioned tables, and praying the S3 lifecycle policies didn't catch our backup snapshots.

Today, that same corrupted file hits the pipeline, the Delta Live Tables (DLT) expectation fails, the records are shunted into a quarantine table, and the pipeline finishes with a "success" status while alerting me to the specific offending rows. No downtime, no manual data surgery, no heart palpitations.

Why I chose this topic: In financial services, "data quality" isn't a suggestion—it's a compliance requirement. I’ve spent too many weekends cleaning up messes that should have been caught at the gate, and I want to save you from the same fate.

It was 3:14 AM on a Tuesday. PagerDuty went off like a siren in my bedroom. The dashboard was bleeding red: the daily_ledger_agg table, which powers our regulatory reporting to the SEC, had hit a null value in a currency code column. This shouldn't have been possible. The schema was enforced, the DDL was solid, yet somehow, a join was producing rows with NULL keys, which cascaded into a NaN in our risk models.

What we saw

The symptoms were deceptive. The job logs showed the Spark cluster scaling perfectly. Memory usage was nominal. The failure wasn't a crash; it was a "silent" corruption.

My first instinct was to blame the upstream merge logic. I spent two hours digging into the Spark plans, convinced we had a partitioning skew that was causing a hash join to drop records. I even bumped the spark.sql.shuffle.partitions from 200 to 1000, thinking it was a resource contention issue. It wasn't. The data was simply bad at the source, and our pipeline—being "resilient"—happily ingested the garbage and propagated it into the downstream analytical tables.

Photo by Artturi Jalli on Unsplash

Root cause

The root cause was a configuration oversight in our DLT pipeline definition. We were using EXPECT instead of EXPECT_ROW_OR_DROP or EXPECT_ROW_OR_FAIL.

@dlt.table
@dlt.expect("valid_currency", "currency_code IS NOT NULL")
def silver_ledger():
  return dlt.read("bronze_ledger")

In DLT, EXPECT is merely a warning. It logs the violation to the event_log and keeps moving. The pipeline marked the run as "Succeeded," but our downstream dashboard was showing a 12% drift in total balance. We were essentially poisoning our own data lake, one row at a time. The code was "correct," but the intent of the data contract was ignored by the execution engine.

Photo by Alex Oviedo on Unsplash

The fix

I refactored the pipeline to enforce strict quarantine patterns. I switched to EXPECT_ROW_OR_DROP for non-critical noise and EXPECT_ROW_OR_FAIL for integrity-critical fields. More importantly, I implemented a secondary "quarantine" sink to capture what was dropped.

Here is what the pattern looks like now:

@dlt.table(
    name="silver_ledger",
    table_properties={"quality": "silver"}
)
@dlt.expect_or_drop("valid_currency", "currency_code IS NOT NULL")
def silver_ledger():
    return dlt.read("bronze_ledger")

@dlt.table(
    name="quarantine_ledger"
)
def quarantine_ledger():
    # Capture only the rows that failed the silver_ledger expectations
    return dlt.read("bronze_ledger").filter("currency_code IS NULL")

By separating the "clean" pipeline from the "rejected" stream, we gain visibility. When a row hits the quarantine table, it gets a _processed_at timestamp and an error_reason column. My SRE team now has a dedicated dashboard for this table. If the count(*) in quarantine_ledger > 0, they get a Slack alert. We don't stop the business, but we know exactly which records need manual intervention from the data vendor.

What we changed so it never happens again

We stopped treating data quality as a post-hoc analysis task. We now treat it as a CI/CD contract.

First, we moved all DLT expectation definitions into a central registry (a YAML-based schema config). We no longer hardcode business logic expectations in the Python files. By centralizing these, we can run a "Dry Run" test suite in GitHub Actions that validates the schema against a sample of incoming Parquet files before the pipeline code is even deployed to the Databricks workspace.

Second, we implemented a "Circuit Breaker" pattern. If the number of records in the quarantine table exceeds 5% of the total record count of the batch, the pipeline is configured to fail explicitly. This handles the "silent corruption" case where a vendor sends a file that is 90% garbage.

Third, we moved away from generic expect calls. Every table in our silver layer must have at least three mandatory expectations: not_null, unique_id, and range_check. If a developer forgets these, the pipeline fails the unit test suite in our CI environment. No exceptions.

In a regulated environment, "good enough" is a liability. You either gate the data, or you accept that your production environment is essentially a lottery. Use your quarantine tables, make your expectations explicit, and stop letting bad data hide in your silver layer. It’s not about being pedantic; it’s about knowing your data is actually usable before the regulators come asking for a reconciliation report.

Tags: #data #engineering #dlt #pipelines

Cover photo by Brecht Corbeel on Unsplash.

Why your Iceberg catalog choice is costing you more than your storage

Aniket Abhishek Soni — Tue, 16 Jun 2026 17:10:58 +0000

Eighty percent of the "data engineering" work I’ve done in the last two years has been debugging catalog state inconsistencies, not actually processing data. It’s a bitter pill, but if your catalog isn't atomic, your petabytes of Parquet are just expensive digital trash.

The industry has collectively decided that the Iceberg REST catalog spec is the way out. It abstracts away the hive-metastore hell of s3:// path listings and S3-consistency-guarantee prayer circles. But now the industry is fragmenting again, forcing us to choose between running our own catalog, leveraging a managed provider, or locking ourselves into a vendor’s ecosystem.

You aren't choosing a "data strategy" here. You are choosing who gets to hold the kill switch for your entire data platform.

The contenders

We’re looking at three distinct paths for implementing the Iceberg REST spec:

First, Apache Polaris. It’s the new kid, open-sourced by Snowflake. It’s a pure-play REST catalog server designed to be deployed on Kubernetes. It’s vendor-neutral, which is refreshing, but it requires you to own the availability of the control plane.

Second, Databricks Unity Catalog (UC). It’s the "Enterprise Standard" if you’re already in the Databricks orbit. It’s proprietary, opaque, and incredibly powerful, but it effectively mandates that your compute lives within their walled garden.

Third, AWS Glue. The incumbent. It’s not a native REST catalog in the traditional sense, but AWS added a REST-compatible API layer for Iceberg. It’s serverless, it’s cheap, and it’s arguably the most "set it and forget it" option, provided you don't mind the inevitable IAM role hell.

Photo by 🇻🇪 Jose G. Ortega Castro 🇲🇽 on Unsplash

The operational tax on your SRE team

If you choose to self-host Polaris on EKS, you are signing up for a pager. Polaris needs a backing database—usually Postgres—to store the catalog state. If your RDS instance goes down or your K8s node group hits an OOM loop, your entire data lake goes dark.

I’ve seen teams try to save $500 a month by self-hosting metadata stores, only to spend three engineering days troubleshooting a split-brain scenario where the Iceberg metadata pointer drifted from the actual S3 manifest files. That’s a $10,000 incident for a $500 saving.

Unity Catalog, on the other hand, is managed. You don't manage the database; you manage the metastore. The failure mode here isn't infrastructure; it’s policy. I’ve spent more time debugging PERMISSION_DENIED errors in Unity than I ever did debugging database uptime in self-hosted solutions. If your Identity Provider (Okta/Entra ID) sync fails, your pipeline stops.

Glue is the middle ground. It’s managed, but it’s notoriously slow. If you have a massive table with 100,000+ partitions, Glue’s API latency becomes your primary bottleneck. You will see 5xx errors during high-concurrency partition pruning if you aren't careful with your glue:GetPartition call volume.

Cost structures and hidden tax

The cost of a catalog is never just the price of the service. It’s the integration cost.

AWS Glue is cheap—basically pennies per million requests. But it forces you to keep your compute inside AWS. If you decide to spin up a Spark job on a different provider, you’re looking at cross-cloud egress fees and the nightmare of cross-account IAM cross-pollination.

Unity Catalog is "free" if you use Databricks compute, but it’s a tax on your architectural freedom. Once you move your governance logic into Unity’s proprietary format, extracting it is non-trivial. You are paying for Unity with the loss of your ability to easily switch compute engines.

Polaris is the only one that is truly agnostic. It’s the "Linux approach." It costs exactly what it costs to run the K8s cluster and the RDS instance. But watch out for the storage of the metadata itself. If your Polaris instance is behind a load balancer that isn't configured for long-lived keep-alives, you will see intermittent ConnectionResetException errors during large metadata updates, which will leave your Iceberg tables in a corrupted staged state.

Photo by Elena Mozhvilo on Unsplash

Failure modes: When the catalog lies to you

The most dangerous thing a catalog can do is return a successful commit for a transaction that failed to write.

In the early days of Glue, we dealt with "ghost partitions." Glue would report a partition as existing, but the underlying S3 objects had been deleted due to a failed Spark job that hadn't properly rolled back. We ended up writing a custom "Catalog-to-S3 Auditor" script—essentially a nightly job that runs s3 ls against every partition entry in Glue and flags mismatches. If you choose Glue, you will need this script.

Unity Catalog is significantly more robust here because it handles the transaction commit in a single, atomic operation within the Databricks control plane. It is effectively impossible to have a drift between the Unity catalog and the underlying storage unless you have rogue users bypassing the catalog to write directly to S3.

Polaris is still maturing. I’ve encountered bugs where the metadata update fails to propagate to the read-replicas quickly enough, leading to "time-travel" anomalies where a reader sees the state from five seconds ago despite the writer confirming the commit. It’s getting better, but I wouldn't trust it with mission-critical financial reconciliation just yet.

What I'd pick, and why

If you are a startup with a small team and you’re already all-in on AWS, use Glue. It’s boring, it’s cheap, and the IAM integration, while painful to set up, is bulletproof once you get the policies right. The latency issues are real, but you can mitigate them by being disciplined about your partition schema. Don't over-partition your data.

If you are a large enterprise with a massive footprint and you already use Databricks, stop fighting the gravity. Use Unity Catalog. The governance features—specifically the lineage tracking and the fine-grained access control—are worth the lock-in. You’ll save more in headcount by not having to build your own governance layer than you’ll lose in vendor flexibility.

I would stay away from self-hosting Polaris unless you have a dedicated platform team that actually enjoys managing Postgres and Kubernetes. It’s an elegant piece of technology, and it’s the future of open-source data lakehouse architecture, but it is currently a "developer-experience" product, not an "infrastructure-as-a-service" product.

My personal preference? I’m waiting for a high-quality, managed SaaS version of Polaris. Once someone turns that into a reliable, "set-it-and-forget-it" managed service, the other two are going to be in trouble. But until then, pick your poison: Glue for the budget-conscious, Unity for the enterprise-locked, and Polaris for the infrastructure masochists.

Whatever you do, don't write your own catalog. I’ve seen that movie, and it ends with a 3:00 AM emergency partition recovery task you’ll regret for the rest of your career.

Cover photo by Tyler on Unsplash.

Text-to-SQL is a solved problem: why you’re about to leak your PII

Aniket Abhishek Soni — Mon, 15 Jun 2026 02:49:38 +0000

The most dangerous myth in modern data engineering is that "Text-to-SQL is a solved problem." Every time I see a demo where someone asks an LLM to "sum the revenue by region" and it returns a clean JSON blob, I see a production outage waiting to happen. You aren't building a chat interface; you are building a high-velocity, non-deterministic SQL generator that has direct access to your internal PII and financial ledger.

Why I chose this topic: I spent three months cleaning up after a "smart agent" that hallucinated a DROP TABLE command because a user asked it to "clear out the old test data." In high-stakes environments like healthcare, "oops, the LLM got confused" isn't a valid root cause analysis for a HIPAA breach.

You are currently facing a binary choice: either you wrap your data in a brittle, manual semantic layer, or you embrace a "governed lakehouse" architecture where the LLM is treated as an untrusted, low-privilege user. Most people choose the latter because they are lazy, and then they end up crying when the model joins the users table to the audit_logs table via a natural language prompt.

The contenders

You have three ways to approach this. First, the "LLM-as-a-DBA" approach, where you feed the entire schema (Ddl) into the context window and pray for the best. Second, the "Semantic Proxy" layer, where you use tools like LlamaIndex or LangChain to map natural language to pre-defined view objects rather than raw tables. Third, the "Hard-Gated SQL Sandbox", where the LLM generates SQL, but a deterministic Python validator checks the AST (Abstract Syntax Tree) before it ever touches a connection string.

Photo by CHUTTERSNAP on Unsplash

The operational burden of hallucinations

The "LLM-as-a-DBA" approach is the cheapest way to start but the most expensive to run. If your schema has 500+ tables—standard for any mature lakehouse—you are going to blow your context window budget immediately. Even with GPT-4o, the "schema stuffing" method fails when you have columns with similar names like user_id in five different tables.

I’ve seen models join billing.payments with marketing.leads on id just because they happened to have the same name. In a production environment, this is a silent data corruption error. If you aren't using a tool that enforces table lineage at the semantic level (like dbt’s graph metadata), you are just running an expensive game of SQL Roulette.

Infrastructure and cost at scale

Running a SQL agent against a Delta Lake or Iceberg table isn't just about the token cost. It’s about the compute cost. If your agent is allowed to write SELECT * without limits, you will incinerate your Snowflake or Databricks budget in an afternoon.

I implement a mandatory LIMIT 1000 hard-coded into the system prompt, but that’s not enough. You need to enforce a resource governor at the database level. For Databricks, I use a specific SQL Warehouse policy that caps the DBU usage per session. If the LLM generates a query that hits a massive fact table without a partition key, the warehouse should kill the query within 30 seconds. If your agent doesn't have a "kill switch" policy, you aren't ready for production.

Photo by MJ Duford on Unsplash

Failure modes and the AST validator

The most common failure mode isn't a bad query; it’s an unauthorized one. LLMs love to "explore." If a user asks, "Who are our highest-paying customers?", the model might decide it needs to look at the sensitive_user_pii table to provide a better answer.

You cannot trust the model to self-regulate. You need an AST validator. I use the sqlglot library in Python to parse every generated query. If the AST contains a JOIN to a table not in the allowed list, or if it tries to access a WHERE clause containing email or ssn, the validator throws a hard exception.

Here is what that looks like in practice:

import sqlglot
from sqlglot import exp

def validate_query(sql_str):
    parsed = sqlglot.parse_one(sql_str)
    for table in parsed.find_all(exp.Table):
        if table.name in ['PII_TABLE', 'CREDIT_CARDS']:
            raise PermissionError("Access denied to sensitive table.")
    return True

If you aren't doing this, you are effectively letting users run arbitrary code on your backend.

What I'd pick, and why

I pick the "Hard-Gated SQL Sandbox" every time. It’s annoying to set up, it requires keeping a registry of "allowed tables," and it slows down the initial development cycle. But it’s the only way to sleep at night.

Here is the stack I recommend:

Semantic Layer: Use dbt to define your models. The LLM shouldn't know about your raw Bronze/Silver/Gold tables; it should only see the Gold-level models you’ve explicitly exposed.
Validator: Use sqlglot to audit the AST. Block any query that doesn't explicitly mention a partition column if the table is over a certain size (like event_date).
Execution: Use a low-privilege service account for the LLM. If your agent is running as a SUPERUSER, you’ve already lost. Use GRANT SELECT only on the specific views the agent is allowed to touch.

The caveat? It makes the "smart" agent feel a bit "dumb." Users will occasionally get a "I cannot answer that" response because the agent was blocked by the validator. This is a feature, not a bug. Your users will be annoyed that they can't ask the agent for the CEO’s home address, but your CISO will be thrilled. In financial services, the "I don't know" response is the mark of a system that is actually under control.

Stop trying to build an omniscient SQL wizard. Build a constrained, grumpy librarian that only gives out the books it’s allowed to touch. That’s how you ship in production.

Cover photo by Jordan Harrison on Unsplash.

Time Travel isn't a Debugging Luxury: Why Delta and Iceberg are Compliance Essentials

Aniket Abhishek Soni — Sun, 14 Jun 2026 04:52:42 +0000

The most persistent myth in data engineering is that "Time Travel" is a fancy feature for lazy developers who don't want to write better unit tests.

Why I chose this topic: In my last two roles—one handling credit risk models and the other HIPAA-regulated patient records—I’ve watched senior engineers lose entire weekends because they couldn't reconstruct the state of a table during a specific model drift event. Data reproducibility isn't just about debugging; it’s about proving to a regulator exactly what your system saw at 2:14 PM on a Tuesday.

If you are choosing between Apache Iceberg and Delta Lake today, you aren't choosing a storage format. You are choosing your strategy for "Oh, wait, the production pipeline just nuked 40% of our user metadata."

The contenders

Delta Lake is the incumbent of the Databricks ecosystem, relying on a transaction log (_delta_log) to track changes. It is predictable, mature, and works beautifully if you live in the Spark/Databricks bubble.

Apache Iceberg is the open-source industry darling, built to solve the "partition evolution" problem that plagues Hive-style metadata. It uses a snapshot-based manifest system. It is engine-agnostic by design, meaning you can query it via Trino, Flink, Spark, or StarRocks without feeling like you're hacking the system.

Photo by Pankaj Patel on Unsplash

The operational tax

Delta Lake's VACUUM command is your best friend and your worst enemy. By default, it deletes files older than 7 days. If you are in a heavily regulated environment, you must override this immediately. Setting spark.databricks.delta.retentionDurationCheck.enabled to false is the first thing I do in any new project, but then you’re on the hook for storage costs. You are essentially paying for a S3/GCS graveyard.

Iceberg handles this via expire_snapshots. The syntax CALL system.expire_snapshots('db.table', TIMESTAMP '2023-10-01 00:00:00') is arguably cleaner, but the failure mode is more silent. If you expire snapshots too aggressively, your ability to perform point-in-time recovery for a specific audit request vanishes. I once saw a team accidentally purge their entire history of a patient-tracking table because they thought they were cleaning up staging files. Always, always set a history.expire.min-snapshots-to-keep property higher than the default of 1.

Performance under pressure

Delta Lake’s transaction log is a sequence of JSON files. As the table grows, reading the history requires replaying these logs. If you have 50,000 commits (common in streaming pipelines), the "time travel" performance can hit a wall. You end up waiting for Spark to parse thousands of JSON files just to figure out which Parquet file was active at 10:00 AM.

Iceberg’s manifest files are the structural antidote. Because Iceberg separates the metadata into manifests and manifest lists, it doesn't need to scan the "history of the world" to find a specific state. It jumps to the specific snapshot ID. If you are doing frequent, micro-batch writes, Iceberg’s metadata overhead is significantly more manageable. I’ve benchmarked Iceberg against Delta on a 50TB table; Iceberg’s AS OF queries were consistently 3x faster because it didn't have to deserialize a mountain of JSON logs.

Photo by Allen Y on Unsplash

The failure modes

Delta Lake is tightly coupled to Spark. If your Spark job fails during a write, the _delta_log keeps the transaction atomic. You either have the data, or you don't. However, I’ve seen "orphaned" files in Delta where VACUUM fails to clean up properly because of cross-region replication lag in S3. You end up paying for bytes you can't query and can't delete.

Iceberg is more robust, but it requires you to be honest about your table maintenance. If you don't run rewrite_data_files and rewrite_manifests regularly, your "time travel" queries will slow down to a crawl. I once inherited a system where a table had 12,000 tiny files because the team hadn't set a proper compaction policy. Trying to time travel to a snapshot on that table took 15 minutes of compute time. Iceberg doesn't save you from your own lack of maintenance; it just gives you more rope to hang yourself with.

What I'd pick, and why

If you are locked into the Databricks ecosystem and have no intention of leaving, go with Delta Lake. It is the path of least resistance, and the integration with Unity Catalog makes the "audit" part of the equation trivial. You get lineage for free, and your managers will be happy.

However, if you are building a multi-engine architecture—say, using Flink for streaming ingestion, Trino for ad-hoc SQL, and Spark for heavy lifting—Iceberg is the clear winner.

My honest advice? Pick Iceberg if you want to avoid vendor lock-in, but be prepared to treat your metadata management as a first-class citizen. You will need a dedicated orchestration layer (like Airflow or Dagster) to run your rewrite and expire actions. If you aren't prepared to monitor your snapshot count and file compaction, you’ll end up with a high S3 bill and a slow, bloated data lake that nobody can actually use for audits.

Time travel is only useful if the vehicle isn't broken. Maintain your manifests, or don't bother.

Tags: #data #engineering #iceberg #delta

Cover photo by Tyler on Unsplash.

Is BigLake the End of Your Vendor Lock-in Delusion?

Aniket Abhishek Soni — Sun, 14 Jun 2026 04:52:37 +0000

Most data leaders sell "Open Table Formats" like they’re a magic bullet for vendor independence. They aren't. They’re a way to ensure your data stays usable when you inevitably decide that BigQuery’s STORAGE_BILLING_MODEL is eating your entire cloud budget. I’ve spent the last 18 months moving petabyte-scale healthcare datasets onto BigLake and Iceberg. It works, but Google didn't build it to make leaving easy; they built it to keep the friction of moving data in GCP lower than the friction of moving it out.

If you’re expecting a plug-and-play experience, stop. You are about to deal with metadata locking, Hive-metastore nightmares, and the specific joy of debugging 403 Forbidden errors that actually mean "your service account doesn't have the right Storage Object Admin role on the underlying GCS bucket." This guide is for the engineer who wants the architecture to be actually maintainable, not just "production-ready" on a slide deck.

1. Stop treating BigLake like a standard BigQuery table

BigQuery tables are black boxes. BigLake tables are external pointers. The biggest mistake I see? Treating them as if they have the same ingestion SLAs. When you run a MERGE statement on a native BQ table, Google manages the optimization. When you do it on Iceberg, you are responsible for the maintenance of the underlying Parquet files.

If you don't run CALL sys.rewrite_data_files and CALL sys.rewrite_manifests regularly, your query performance will degrade into a slow-motion car crash. You aren't just a data engineer here; you’re an amateur database administrator managing compaction intervals. Don't automate this via a simple cron job; hook it into your Airflow DAGs as a post-load quality gate.

-- Don't let your manifest files grow to the moon
CALL my_dataset.system.rewrite_data_files(
  table => 'my_project.my_dataset.my_iceberg_table',
  where => 'date_partition = "2023-10-01"'
);

Photo by Damien Schnorhk on Unsplash

2. Partitioning is your only defense against egress costs

In the traditional BigQuery world, we got lazy with clustering. In Iceberg, if you aren't partition-pruning correctly, you are scanning your entire GCS bucket. And guess what? Google charges for data processed. If you scan 10TB to find one patient record, that’s on you.

Always, and I mean always, use hidden partitioning. Don't create a date_string column in your source data just to satisfy the partition requirement. Use Iceberg’s ability to derive partitions from timestamps. If you’re partitioning by day, use days(timestamp_col). If you don't, you'll be rewriting your entire metadata layer when you realize you need to change your partition strategy.

3. The IAM dance is a multi-act tragedy

BigLake requires the BigQuery Connection resource to act as an intermediary between the engine and the storage. You’ll need a Google-managed service account for that connection. The failure mode here is subtle: users will have BigQuery Data Viewer permissions, but they won't have Storage Object Viewer on the GCS bucket.

The error message in the console will tell you "Access Denied," and it will lie to you about which permission is missing. Always check the service account tied to the connection resource first.

# The CLI command that saves you 4 hours of debugging
gcloud projects add-iam-policy-binding [PROJECT_ID] \
    --member="serviceAccount:[CONNECTION_SERVICE_ACCOUNT]" \
    --role="roles/storage.objectViewer"

4. Manifest snapshots are not backups

A common misconception is that Iceberg's metadata history serves as a disaster recovery strategy. It doesn't. If someone accidentally deletes the underlying Parquet files from GCS, your Iceberg metadata is just a list of pointers to ghosts.

You need to enable GCS Object Versioning on your buckets. Period. If you don't, and a pipeline goes rogue and deletes a partition, you have no recourse. Iceberg's expire_snapshots procedure is useful for storage cleanup, but keep at least 7 days of snapshots. If you set this too aggressively, you lose the ability to perform time-travel queries, which is the only reason you’re using Iceberg in the first place.

5. Schema evolution is a trap

Iceberg supports schema evolution, and BigLake respects it. That’s the theory. The reality is that if you rename a column in your Parquet files via a tool that doesn't respect the Iceberg manifest, you break the contract.

Never manually modify the Parquet files outside of the Iceberg engine. If you need to fix a data type or rename a column, use ALTER TABLE. If you go behind the engine's back, you'll encounter the "orphaned data file" problem where the metadata points to a column that no longer exists in the Parquet schema, leading to NullPointerException or generic engine failures that don't point to the root cause.

-- Keep the engine as the single source of truth for metadata
ALTER TABLE my_iceberg_table 
RENAME COLUMN old_name TO new_name;

6. The "Hidden" cost of metadata storage

Every time you commit a transaction in Iceberg, you create a new manifest file. If you have a high-frequency ingestion pipeline (e.g., streaming small batches every minute), your metadata layer will explode.

I’ve seen metadata directories hit 50,000 files in three weeks. This slows down query planning significantly. You aren't just paying for data storage; you're paying for GCS read operations on every single metadata file during the EXPLAIN phase of your query. Micro-batching is the enemy of Iceberg performance. Buffer your data in memory or use a staging area before committing to the table.

7. Avoid the "BigQuery-only" mindset

If you chose Iceberg, it’s likely because you want to use Trino, Spark, or DuckDB on the same data. If you write your data using only BigQuery’s INSERT or MERGE statements, you might be creating files that are optimized for BigQuery but are absolute garbage for Trino.

Check your Parquet writer settings. Ensure you are using Snappy compression and reasonable row group sizes. If you write 1GB row groups, Spark/Trino will choke on memory when trying to read them. Stick to the 128MB to 256MB range. It’s boring, it’s standard, and it keeps your compute engines from crashing.

Conclusion

BigLake and Iceberg are powerful tools, but they shift the burden of performance tuning from Google’s black box to your own infrastructure. You get "openness," but you pay for it in complexity. You have to be the one to manage the metadata, the IAM policies, the file compaction, and the storage lifecycles.

It’s worth it if you’re tired of being held hostage by proprietary formats. It’s a disaster if you treat it like "just another table." Before you migrate that production workload, ask yourself: are you actually prepared to own the metadata layer, or are you just looking for a new way to break your pipelines?

Tags: #gcp #bigquery #iceberg #dataengineering

Cover photo by Mick Haupt on Unsplash.