ETL

Meet SDP Rewind: An undo button for your ETL pipelines

Postgres data stored in Parquet on S3: LTAP architecture explained

--- top comments --- [dsauerbrun] Maybe I'm too stupid to understand the article... How does this achieve performant querying for olap and oltp purposes? Based on my understanding, olap queries will go to the parquet files which are stored in a columnar fashion and oltp style queries will go to a caching layer that sits on top of those parquet files? What's the special sauce here? Seems like they're just caching the data which, for all intents and purposes, seems like the same solution of storing another copy of the data which is what they say they're avoiding. [ianberdin] Surprisingly, I'm already encountering a second solution that involves storing data chunks on S3 — and this is all within the same week. This is becoming popular. At Playcode, we built what we believe is a revolutionary file system for our Playcode Cloud (https://playcode.io/cloud), which enables the creation of full-stack web software. The FS built completely from scratch using Rust. We thought we were the smartest ones around and that nobody else had figured this out. But it turns out Databricks, Neon, and several others have as well. The idea behind a *Bottomless File System* is really cool, and it works very well for us. Essentially, as described here: - There is a *page server* - A *Linux file system* split into chunks (let's call them chunks instead of pages) - A *cache on NVMe* - And of course, *object storage*, where everything is asynchronously synchronized It works quite well, though it has its downsides. One clear advantage is that NVMe drives have become expensive lately, while object storage remains cheap — so the benefits are undeniable. That said, latency is also a factor. On top of that, uplink costs are rising. To run an object storage-backed file system, you need a very strong uplink with consistent speed — 1 Gbps is simply not enough. Ideally, you want *5 to 10 Gbps*, depending on the load. We spend a lot of time optimizing and experimenting with different hosting providers — specifically bare metal hardware. The main challenges are: - *Slow disks* - *Slow uplink* - And as it turns out, *object storage can be unreliable* — unless you're using S3 But AWS hardware is expensive, so nothing in life is ever that simple. [Avalaxy] Super cool stuff. Being able to combine your analytical platform and transactional database into one storage layer without having to set up ETL pipelines in between is really a game changer. Especially since it's just postgres, instead of some proprietary database. [saisrirampur] But why? I’m skeptical of the idea of unifying storage just because it sounds “elegant” or “cool”. It’s not obvious to me how a single storage engine can compete with purpose-built OLTP and OLAP systems like Postgres and ClickHouse, without significant tradeoffs. You also mention removing CDC pipelines. I’m curious if the materialization (conversion across formats) can catchup to an OLTP workload that is heavy (50K+ tps), which is pretty common these days. Also CDC if done right and with care can be magical for users and stays native to the OLTP/OLAP data-store. Third, data Lakes and open formats are suitable for Data Warehousing / Data analyst use-cases than real-time customer facing apps. Sure, you might work on changing that, which is what you are upto, but you’ll always run into tradeoffs, which will make it hard to unleash the best performance, much needed for the latter category. [andrenotgiant] Here's what I don't understand: Part of the value of doing an ETL pipeline via streaming replication is you get the full history of data in a table. An SCD type 2 table where each row also has a valid_from and valid_to timestamp column. How would someone do the same thing with this architecture?

Anyone have insights on pivoting from cloud engineering with Databricks administration or other regular IT into a Databricks data engineering role?

I've been in IT for the last 24ish years - started from the helpdesk, got experience and certs, fits and starts, etc. I've been doing Azure cloud engineering for the last 8ish years. In my previous job, I was asked to spin up an Azure Databricks test environment for our data science/data engineering teams. It grew, it got more mature, and by the end of my time there I was doing a lot of the administrative stuff - cluster policies, cost management, provisioning through SCIM, and the occasional technical question. I don't really have a background in databases or development; I've never written anything in Python or my own SQL queries but I've had plenty of situations where a dev/DBA would walk me through their code or query and show me what it did, after which point I'd break it down for troubleshooting. My current role with Microsoft has a subject matter expert team in Azure Databricks. I joined up with the team, had a lot of training on how the back end operates and how the data science/eng functionality works with Python and otherwise. I've been taking tickets with this SME team and done pretty well. I took the beta exam for the DP-750 Azure Databricks data engineering cert and just found out yesterday that I passed. Cloud engineering has become a lot less lucrative or Azure-focused as it was a few years ago and I've been exploring pivoting into different parts of IT. Apparently I know Databricks decently, but I know that's not nearly enough to find a data engineering role. Has anyone else been in this situation? How did you make your pivot? Did you take on projects in your current roles and spin them on your CV as data engineering work? Did you take your experience with DevOps pipelines and parlay it over to ETL pipelines? Any guidance or input would be much appreciated.

Row count mismatch in time-bucketed fact table after joins and window fan-out (Databricks SQL)

I am building a Delta fact table where the intended grain is: one row per CaseID per SegmentType per 15-minute SegmentStart Expected behavior If a case lasts 90 minutes , I expect 6 rows . Actual problem The final fact table sometimes contains duplicates for the same grain. Example: CaseID | SegmentType | SegmentStart | cnt A | Room | 2024-01-01 10:15 | 2 A | Room | 2024-01-01 10:30 | 2 What I suspect I think I may already have more than one row per case before segmentation, and the fan-out multiplies it. Minimal reproducible example Step 1 – simulate the source case table CREATE OR REPLACE TEMP VIEW source_cases AS SELECT * FROM VALUES ('A', timestamp('2024-01-01 10:00:00'), timestamp('2024-01-01 11:30:00')) AS t(CaseID, InRoom, OutRoom); Step 2 – simulate a dimension that accidentally returns multiple rows CREATE OR REPLACE TEMP VIEW dim_procedure AS SELECT * FROM VALUES ('A', 'PROC1'), ('A', 'PROC2') -- duplicate match AS t(CaseID, ProcedureCode); Step 3 – join (this is my base dataset) WITH base AS ( SELECT s.*, p.ProcedureCode FROM source_cases s LEFT JOIN dim_procedure p ON s.CaseID = p.CaseID ) SELECT CaseID, COUNT(*) AS cnt FROM base GROUP BY CaseID; Result: A | 2 So I already have 2 rows. Step 4 – segment into 15-minute buckets (simplified) WITH base AS ( SELECT s.*, p.ProcedureCode FROM source_cases s LEFT JOIN dim_procedure p ON s.CaseID = p.CaseID ), segments AS ( SELECT CaseID, explode(sequence(InRoom, OutRoom, interval 15 minutes)) AS SegmentStart FROM base ) SELECT CaseID, SegmentStart, COUNT(*) AS cnt FROM segments GROUP BY CaseID, SegmentStart ORDER BY SegmentStart; Output Every segment now appears twice. My questions Is upstream multiplicity the typical reason time-bucket facts inflate like this? Should ETL pipelines guarantee one row per grain before segmentation ? In practice, do teams usually: fix dimension uniqueness, or deduplicate the base dataset with ROW_NUMBER() ? Where is the best place in the pipeline to enforce this protection? Environm […truncated]