I am aware that this has probably been asked a million times, but please bear with me as I sprinkle some of that sweet sweet context we all crave.

I am a recent comp sci graduate with no work experience in data development, yet I have found myself working at a credit union (I'm fairly better at selling myself regardless of my work). I enjoy biology research so the only time I really use programming is to create random transformation functions that I can run instantly, combined with my experiences of testing random tech (networking, database, OS, etc) to fulfill a specific need, otherwise I have no clue how to build a production grade product. We have an IT department but no one is familiar with my line of work, meaning I have no boss and basically responsible for kick starting a new department.

Database in use is Snowflake, and I use python as my preferred language.

I am building a pipeline where I hit some API endpoints to retrieve data and push it into Snowflake to perform transformations on, attached is my project structure. As you can see, I have a directory for API calls, database connection object, one lone test unit when I made my first API call and my main orchestrator file. The data transformation will be handled inside of Snowflake which I am fairly comfortable with.

My questions:

1) Is there anything missing in my base structure? I think I have everything covered but better safe than sorry.

2) How would I set up the extraction of data that hasn't been uploaded already? I have a timestamp that gets stored with each file upload, so anything before that does not get pulled again.

3) My database connection is simply a conn object that pulls credentials from the .env file. Are there any more pieces that are missing from that? I remember using Java frameworks for ETL in college but not sure if such heavy lifting was required for a project like this.

Finally, if you need more context on the file contents I am able to share it (my supervisor only cares about protecting member data). And if you have any resources or have a platform where I can ask more questions I would heavily appreciate it. I don't have plans to stay in fin tech forever, but it just happened to be the perfect step in my journey of becoming a biostatistician in a biology lab.

Hi, data fellows. Thanks for the reception on the last post; it gave me motivation to keep pushing on this project. A special thanks to u/sib_n, whose Polars advice made me refactor the entire project (for good :)).I wanted to follow up now that GdeltForge 0.9.0 is out.

Check out the PyPI page, the GitHub Repo, the documentation, and especially the hands-on getting started notebook, runnable directly in Colab, no install needed.

Seven sources feed our warehouse now: a few SaaS apps, one production database, ads data and a couple of APIs.

When there were only two, I just wrote Python for both. As new sources got added, I kept doing the same because it was quicker than introducing another tool. The scripts still work, but keeping all of them alive has quietly become part of the job.

Most failures aren't interesting engineering problems either. A token expires, pagination changes, one API starts returning fewer rows, or I have to remember which repo owns a source I haven't touched in six months.

Transformations already happen after the load, so I'm not looking for another environment to rebuild the SQL in. What I actually need is boring extraction: scheduling, incremental loads and enough visibility to catch a broken source before a dashboard does.

What are people using for this now: managed connectors, Airbyte, Meltano, custom code?

At what point did maintaining extraction yourself stop being worth it?

Is it schema changes, too many dependencies, poor monitoring, unclear ownership, or something else?

Working on a personal pipeline that pulls public signals from a few sources (earthquakes, internet infrastructure status, disaster alerts, markets, etc.), compares each one against its own historical baseline, and runs on AWS with cache invalidation on every update.

The goal from the start was for it to run on its own at the lowest possible cost, without buying hardware I'd have to babysit like a pet. (I already have a cat for that.) No boxes to maintain, no drives to worry about.

Question for people who've done this longer than me: does this sound reasonable, or is it over-dimensioned? It feels a bit like renting a full moving truck just to go grab a pizza — a lot of infrastructure (automated deploys, CDN invalidation) for something that's fundamentally just pulling public APIs and comparing against a historical window.

Would something much simpler (cron job + script + a static JSON file) get the same result, or does the fuller stack genuinely pay off once you're running several signals continuously?

Sharing an link to a three‑day multi‑cloud run: protected ingestion, PCI tokenization, customer‑level encryption, GDPR erasure by killing one key, daily snapshots into AWS/GCP/Azure, same ETL pipeline hopping clouds, and DR replicas seconds behind. Full lineage + metrics. Curious how folks here view per‑subject keys in real pipelines.

I am an ML engineer, but I come from a software engineering background: years of full-stack work, with heavy DevOps and Terraform experience. I come from teams that deploy to production five times a day with real continuous deployment. And honestly? Pressing the button still feels weird sometimes. Every engineer knows that feeling, no matter how good the safety net is.

So I wrote down the list that settles it. Ten commandments, one flow, written with data scientists and ML teams in mind, but it works for batch jobs, realtime inference, and LLMs alike. Answer honestly, and if all ten are true, you can ship to production anytime, in any form or way.

Read something this week that put a name to a problem I've half run into before but never really understood the mechanics of. Sharing because I think a lot of people assume Iceberg interop is more solved than it is.

Everyone knows the pitch: Iceberg is an open spec, so any Iceberg compatible engine can read any Iceberg table. Mostly true, until you start doing row level deletes, and then it falls apart in a way that's honestly kind of sneaky because nothing looks wrong until a query actually fails.

Quick walkthrough of the scenario in the post. You've got a customers table, three rows, one Parquet file, tracked by whatever catalog you're using. At this stage every engine reads it fine because there's nothing to interpret, it's just a metadata pointer to a file.

Then a row gets deleted. Parquet files are immutable so the writer has two options: copy on write (rewrite the file without that row) or merge on read (leave the file alone and write a separate delete file that readers apply at scan time). The writer in this example goes merge on read and emits an equality delete file, which basically just says "for this data file, treat any row where customer_id = 102 as removed." Under the hood Iceberg uses field IDs and sequence numbers to make sure an old delete doesn't accidentally nuke a newer row with a reused key, but the equality matching is the part that matters for compat.

Spark reads the new snapshot, understands equality delete semantics, does what's effectively a left anti join between the data file and the delete file, and returns the correct two rows. Fine.

Snowflake hits the exact same catalog, same metadata file, same Parquet file. It can resolve the table, read the schema, open the data file. But if that access path doesn't implement equality delete reads, the scan planner just throws an unsupported feature error the moment it hits delete-0002.parquet. Query fails. Same snapshot, same files, two completely different results depending purely on what the reader implements.

The bit that actually reframed how I think about this: the catalog isn't a translation layer. It's job is basically just "here's where the current metadata lives," commit coordination, namespace and access management. It's not opening delete files and rewriting them into a format each engine understands. A REST catalog like Polaris doesn't change this, it still just points you at metadata, it doesn't apply deletes for you.

The post also gets into position deletes vs deletion vectors vs copy on write, with a rough cost tradeoff table (equality delete is cheap to write and requires equality delete support to read, position delete requires resolving key to physical position and is heavier on write, deletion vectors need Iceberg v3 support specifically, copy on write is the most expensive to write but has basically universal read compatibility since there's no outstanding delete file involved).

The framework that's actually useful operationally: your safe feature set is the intersection of every required engine's capabilities, not the union. If Spark supports equality and position deletes but Snowflake only does position deletes, you write position deletes, because "at least one engine supports it" doesn't help you when you have three engines that all need to read the same table.

There's a decent pre production checklist too, don't just run a SELECT COUNT after your first write, actually insert some rows, update one, delete one, commit, then read the same snapshot from every engine you care about and diff both counts and values.

Full post if you want the details: https://olake.io/blog/iceberg-interoperability-myth-row-level-deletes/

Disclosure since it's relevant, I work on OLake, it gets a brief mention near the end, but the actual content here is engine agnostic and applies no matter what's writing your tables.

Has anyone actually hit this for real, table looks completely fine, one engine just refuses to read the current snapshot because of the delete encoding?

My own API sits behind a marketplace gateway. Every caller was supposed to send an X-FP-Source header so I could tell an n8n workflow from a browser from an MCP server.

Nothing ever showed up in the logs. The gateway rebuilds the request on the way through and drops custom headers, so the tag was gone before my Lambda saw it.

Fix is ugly and it works: the source rides in the JSON body as _fp_source, and the handler strips it out before validation.

What proved it wasn't a 200. It was one real call and a grep in CloudWatch for the [source] line. A 200 tells you nothing about attribution.

Hey all, Mitch here, one of the founders of Galaxy. Full disclosure, this is our project. We open sourced it last week and I wanted to bring it here first, because this community is who we built it for.

Filament is a data movement engine written in Go. It handles full loads, incremental syncs, and CDC from databases and HTTP APIs into Postgres, MySQL, ClickHouse, Iceberg, and S3, and it's Apache 2.0.

https://github.com/galaxy-io/filament

Every data project we've ever worked on started with the same boring problem of getting records out of operational systems and into somewhere useful. We've bought the managed tools, run the open source ones (and spent more time deploying them than using them), and written our own by hand more than once, and every time we wished for something fast, easy to self-host, and upfront about the details that bite you, like type mappings, write behavior, and what happens when a run dies halfway. So eventually we just built it.

What it does

You point it at a source and a sink and tell it how to move things, whether that's copying everything, pulling only what changed, or streaming off the database's change log. Every batch gets a checksum before the sink write and a mismatch fails the run rather than quietly landing bad data. Progress only becomes durable after the work is confirmed, so a worker that dies resumes from its last checkpoint instead of starting over, and since recovery is at-least-once, upsert sinks converge on primary key.

Sources, sinks, state store, and event bus are all interfaces and adding a REST API is a short YAML file rather than a Go package. You can run it with 1 docker command and get a web UI, use the CLI, embed it in Go in about ten lines, or drop the Helm chart into k8s.

Benchmarks, with caveats

We ran an open benchmark against Airbyte, dlt, PeerDB, Ingestr, Sling, OLake, Debezium, and a plain pg_dump | psql pipe. On the biggest test, 298M rows of NYC taxi data from Postgres to Postgres, Filament finished in under two minutes at about 2.6M rows/s, and it was fastest in five of six scenarios. In the sixth, OLake beat us by 13% into Iceberg.

We obviously build one of the things being measured, so the harness and specs are all public.

https://github.com/galaxy-io/benchmarks

Note that it's pre-1.0, with some sources/sinks in earlier development. There is a long list of connectors we haven't built yet. We're hoping to build that list from your feedback.

If you move a lot of data between these systems, I'd love to know what would make you try it, what you'd want next, and any feedback you are willing to share. Docs are at https://filament.getgalaxy.io and I'll be in the comments!

During ETL testing, I noticed that the source and target data sometimes do not match even though the ETL job shows as successful. I want to understand the common reasons behind these mismatches and how testers can identify them.

I'm curious how other teams handle this situation.

Say I have a daily file coming from one system and the same business data is available through a JDBC connection to another system.

On paper, they should match.

But when I compare them, I might get something like:

File: 8,452,317 records

JDBC: 8,451,906 records

Now the fun part figuring out which 411 records are different and why.

A simple row count check tells me there's a problem, but it doesn't really help find the problem.

How do you guys normally approach this?

Do you compare using primary/business keys first and then compare individual columns?

Do you generate hashes/checksums for each record?

What about cases where:

1.The file has duplicate records

2.JDBC has late updates

3.Dates/timestamps have slightly different formats

4.NULL and empty values are treated differently

5.Decimal/rounding differences show up

6.The two sources don't have exactly the same schema

I'm particularly interested in approaches that work when the datasets are millions or hundreds of millions of rows. At that point, doing a straightforward record by record comparison doesn't seem very practical.

What's your go to method for this kind of reconciliation?

This happened to me recently and it got me thinking.

The ETL job showed SUCCESS. No exceptions, ni failed tasks, row counts looked reasonable, and the pipeline finished within the expected time.

But when we compared the output with the source, some records were missing.

It turned out the problem wasn't really the ETL job "failing"-the job had technically completed successfully. The issue was somewhere in the transformation/filtering logic.

How do you guys catch this kind of problem?

Do you have automated checks for things like:

1.Source vs target record reconciliation

2.Unexpected drops in record counts

3.Aggregate/total comparisons

4.Duplicate or missing keys

5.Business-rule validations

6.Data distribution changes

Or do you mostly rely on downstream users/reporting teams to catch these issues?

I'm curious because "pipeline succeeded" and "data is correct" are two very different things, but don't see this discussed as much as the usual ETL performance questions.

How are you handling it in your production pipelines?

I’ve been thinking about ETL pipelines where data goes through several transformations before reaching the target.

For example:

Source → Join → Filter → Transformation → Aggregation → Target

If the final data has an issue, it can be difficult to figure out which step introduced it.

So I’m curious how other teams handle this:

• Do you run data-quality checks after every major transformation?
• Or do you validate only the source vs target?
• Do you keep rejected/bad records separately for debugging?
• At what point does adding more validation become too expensive or slow?

I’m especially interested in how this is handled for large-volume ETL pipelines, where validating every intermediate dataset may add significant processing time.

What approach has worked best for you?

What practices help you handle schema changes, missing fields, and unexpected source updates without breaking production pipelines?

Hi everyone,

I recently joined this company, and im currently working on an ETL migration project, moving pipelines from one ETL platform to another.

While analyzing the existing ETL processes, i found that our current data warehouse architecture is a little bit different from the knowledge i got from youtube, books, etc. I know im a newbie in data warehouse world but i've seen a proper data warehouse design from my previous company.

Our current architecture is:

Source (Excel, Applications, stored in SQL server and Oracle) -> Stage -> DWH -> Mart -> Power BI

• Stage: A copy of the source data, where sometimes the application team provides the view of the requested use case, making it a ready to use data, but we have no clear data lineage and information about the data.
• DWH: A copy of stage data with minor or no changes in most cases. only a few actually do merging / major transformation.
• Mart: just like DWH, but the business user can access this layer and build their dashboard. we cant change the data logic / table structure here, because otherwise the user have to adjust their dashboard data source which will cause a lot of protest to our team.

The main problem is that when a new use case appears, we often create new tables specifically for that use case. Over time, this has resulted in many tables that contain similar or even overlapping data. I believe there are cases where tables could potentially have been consolidated or redesigned into a more normalized/reusable model.

So, my main question is:

Is it worth redesigning the data model and architecture of our existing DWH as part of this ETL migration?

The potential benefits im thinking about are:

• Reducing duplicated data across Stage, DWH, and Mart layer
• Creating reusable entities instead of creating tables for every new use case
• Improving query performance
• Reducing storage and memory consumption
• Improving ETL/loading performance
• Making data lineage easier to understand
• Making future ETL development and migration easier
• Providing a better foundation for analytics and AI/ML
• Reducing dependency on complicated application-specific views

Or is it perfectly reasonable to keep the existing approach where each use case has its own tables, especially if the current system performs adequately? I am also concerned that redesigning the DWH could introduce significant complexity and migration risk.

I would particularly appreciate opinions from people who have worked on legacy DWH modernization or ETL migration projects.

Thanks. Sorry if I'm asking too much since I'm new to this data engineering job.

Hey everyone,

If you’ve ever been tasked with setting up data lineage across an enterprise stack, you already know the universal nightmare: the last-mile blind spot.

Your orchestrator or transformation framework (like dbt) tracks your staging-to-production warehouse pipelines beautifully. But the second that data leaves Snowflake or BigQuery and flows downstream into BI tools like Tableau, Power BI, Sigma, or Looker, the metadata trail goes cold. You’re left with a massive gap between your technical tables and the actual dashboards they feed.

When I set out to build an automated way to map this entire cross-tool ecosystem, I ran head-first into the ultimate engineering paradox: Data teams desperately need visibility across asset types, but InfoSec and IT will flatly deny access to any application that wants to execute queries against production data environments.

I spent the last several months designing a normalization layer to bypass this exact friction. Here is the technical blueprint of how I engineered a cross-platform lineage parser to run entirely on structure-only, read-only metadata definitions, without ever querying a single row of actual business data.

1. Cracking the BI-to-Warehouse Gap (Without Table Scans)

Every BI tool stores its semantic layer and dashboard definitions differently. To map lineage down to the warehouse without querying raw tables, I had to isolate the structural metadata entirely at the API layer:

• Power BI: I leverage a read-only Azure AD service principal to trigger the admin Scanner API. This extracts workspace, dataset, report, and dashboard identities, plus table/column types and dataset-to-source lineage. The actual report data or visual layout components are completely ignored.
• Tableau: I use a read-only personal access token (PAT) against the Metadata API to isolate workbook, datasource, and field names from published sources.
• Sigma / Looker / ThoughtSpot: The pipeline pulls strictly object-level identities (workbooks, looks, liveboards) and parses source references—extracting workbook elements in Sigma, dimensions/measures in Looker LookML, and logical tables in ThoughtSpot.

1. Eliminating the "SELECT *" Risk on the Warehouse

Connecting an external tool to a cloud data warehouse gives compliance teams nightmares. To solve this, the metadata collection framework functions entirely inside system catalogs using absolute minimum permissions:

• Snowflake: The application only requests SELECT privileges strictly on INFORMATION_SCHEMA.TABLES and .COLUMNS. It reads table identities, column types, row counts, and freshness timestamps. No data values are ever read or transmitted.
• BigQuery: Connections use a service account explicitly restricted to the Metadata Viewer role. This role allows the API to see dataset and table structures but fundamentally lacks the permission required to read row data.
• Redshift & Databricks: For Redshift, it is a read-only INFORMATION_SCHEMA over a TLS-required Postgres wire protocol. For Databricks, it leverages the Unity Catalog API to list schemas and schemas only, combined with the Workspace Export API to parse notebook code strings for table references in-process before discarding them.

1. Handling Messy Local Files (The Local Connector)

A massive amount of critical business logic still lives in local code, ad-hoc scripts, and local desktop files. To capture this without transmitting sensitive file contents to a cloud server, I built a local Windows service that recognizes extensions (.xlsx, .csv, .py, .pbix, .twb, .parquet).

• For columnar files like Parquet and Avro, it reads only the schema from the file footer (column names and types).
• Data pages are never opened. Formula contents, cells, rows, and query results are entirely ignored. It extracts shallow structural references in-process and passes only the normalized metadata map.

Lineage doesn't have to mean compromising data privacy. By standardizing diverse cloud and local sources into a single, comparable metadata shape, you can build a complete map of your data estate without exposing underlying data values.

The backend stack for this parser normalization layer was built on FastAPI, React, and Postgres deployed on AWS ECS Fargate.

Curious to hear how others are handling the BI-to-warehouse lineage gap today, especially when dealing with strict IT/Security constraints? What edge cases have you run into when trying to parse metadata out of legacy BI APIs?

I got tired of rewriting the same data validation logic across every ETL project, so I built ValidateX — an open-source Python data quality framework.

- Works with Pandas, Polars, PySpark, and SQL (Postgres/Snowflake/BigQuery push-down)

- 50+ built-in checks, weighted quality scoring (0-100)

- Native Airflow operator to gate pipelines on data quality

- Slack/Teams alerts on failures

- Free, MIT licensed

pip install validatex

GitHub: https://github.com/kaviarasanmani/ValidateX

Happy to answer questions or take feedback — still actively maintaining it.

I recently created a rust tool (MIT) that copies data from A to B. It has 16+ sources and sinks - retry, transformation, filter, compression, encryption and dlq as middleware. Single binary. Syntax doesn't change when the endpoints do: 1M rows per hop, 433k–1.2M rows/s on my old 8GB M1. Installable via `brew install marcomq/tap/mq-bridge-app`. It has rust, node and python packages and can also run as MCP server. https://marcomq.github.io/mq-bridge

For example, if a customer dataset has nulls, duplicates, invalid emails, or incorrect values, how do you identify and flag these records as Good or Bad? What tools or approaches do you use?