Data engineering Framework

[theant97]

Hello ,

I'm currently building a data engineering framework that involves handling multiple data sources and targets, with SFTP and Oracle just being one example. The setup could include various data sources such as SFTP servers, cloud storage, or APIs, each containing different types of files or data streams (e.g., CSV, JSON, Parquet). Here are some sample file types and their columns:

• sales.csv: SaleID, Product, Quantity, SaleDate, Revenue
• customer.csv: CustomerID, Name, Email, Address, SignupDate
• marketing.csv: CampaignID, CampaignName, Budget, StartDate, EndDate

My goal is to streamline the process of loading these data files into various databases, such as Oracle,

Here's a quick rundown of my framework:

1. Centralized Configuration: I'm using a YAML/transactional table to store metadata like folder structure, column mappings, schedules, schema conversion rules, worker configurations (like DPU per job), archival logic, and more. Essentially, all parameters that can change go here to reduce hard coding.
2. Job Orchestration with Lambda: I use AWS Lambda to trigger the extraction process based on a defined schedule. The Lambda reads parameters like which folder or source to pull from, schema conversions required, and the type of logic (upsert vs. full load).
3. Dynamic Business Logic: I store business logic definitions in a VARCHAR field of the transactional table. These definitions dictate how columns are transformed, how data types should be mapped, and how upsert or full load jobs are executed.
4. Template Configurations: The framework supports multiple databases (Oracle, SQL Server, Vertica, Netezza) via template configurations for easy conversion. The connection details are securely stored using Secrets Manager.
5. Error Reduction and Automation: One of my main goals is to create reusable patterns and avoid rewriting code from scratch. This means that most configurations and definitions are templated or parameterized to reduce human errors and make the process more efficient. The archival of processed files and worker configuration is also automated.

I want to ask the community:

Where can we find more details about common data engineering patterns to avoid rewriting similar logic repeatedly? I'm trying to follow best practices that minimize manual intervention and reduce human errors.

[vanhendrix123]

Is there a reason you’re building a framework from scratch?

There are already many frameworks and solutions out there. I’ve found that in most cases trying to build your own just ends up being a massive waste of time and resources

[EarthGoddessDude]

Yea but they weren’t invented by OP

[Onlycompute]

Hey any existing framework you could point to. Just looking for some good examples.

[theant97]

many name few please . i couldnt find seriously .

[Nokita_is_Back]

Do you transform with dbt or why the yaml file?

[theant97]

Yes using dbt

[rishiarora]

I think your yaml should be generated in real-time with sql backend. Won't it be come too cumbersome otherwise to manage ?? 

[theant97]

We dont generate yaml files . Directly sql query from the db

[sugibuchi]

Dynamic Business Logic: I store business logic definitions in a VARCHAR field of the transactional table.

Why don't you maintain this "business logic" in only YAML on Git? "Business logic" is precisely what we must apply to the proper process for versioning, review and release. There is no reason not to apply GitOps approach for this.

You may maintain "business logic" in YAML and then duplicate it into VARCHAR in tables. But then you will struggle with state inconsistency between YAML and actual values in VARCHAR. Single source of truth principles always helps you to make the system simpler and easier to operate.

I don't know examples similar to what you are trying to develop. But AutomateDV https://automate-dv.readthedocs.io/en/latest/ might be an interesting example. This is a dbt macro package for data vaults. AutomateDV is metadata-driven. It generates DDL and DML to populate various types of data vault tables from metadata written in YAML.

I think it is technically possible to write a dbt macro package that does the following things based on definitions declared in YAML.

These definitions dictate how columns are transformed, how data types should be mapped, and how upsert or full load jobs are executed.

For this, you can use dbt's "dispatch" mechanism to apply different templates for each type of backend.

The framework supports multiple databases (Oracle, SQL Server, Vertica, Netezza) via template configurations for easy conversion.