We’re rewarding the question askers & reputations are being recalculated! Read more.

Hot answers tagged

6

I believe most of the "modern" RDBMS implementations are based on the Cascades optimization framework. I shall talk about how Microsoft SQL Server handles this, as that is the DBMS with which I am most familiar. SQL Server is an implementation of the Cascades optimization framework, so it's workings and the ones for other "modern" RDBMS should be similar. ...


5

The answer discusses in general the aspect of sending large data over HTTP using REST. Make sure you also read the answer by Robert Jack Will which is more specific and suggests a way which makes it possible to avoid sending large data in the first place. REST has nothing to do with XML: the format of the data, would it be JSON, XML, plain text or binary ...


4

Most IT "patterns" deal with repeatable, reproducible situations. Yours is exactly the opposite. You've got truly dirty data: Not just dirty instances, but changing, inconsistent, and occasionally erroneous schema, varying over time. Oy vey! I don't know of a canonical, widely-accepted "best practice" name for dealing with such an irregular dataset, but ...


4

What patterns can I follow to represent the mostly-correlated but imperfectly-aligned data structures, such that the translation describes all the mapping complexity and all we need do then is connect the systems at each end? This summary question suggests there might be some kind of pattern or procedure to follow that will be simple, complete, and ...


4

Enterprise Service Bus is a "software architecture" model used for designing and implementing communication between mutually interacting software applications in a service-oriented architecture (SOA). Extract, Transform and Load (ETL) refers to a data warehousing process that extracts data from various data sources, transforms it into the proper format, ...


4

What you're describing is an end-to-end test. That is one way of validating your system, and it does have some benefits - it ensures that every part of the system is working. The downside is that it hits every part of your system, which can make it slow to set up, and it will tend to be brittle (changing one thing can break numerous other parts). A common ...


4

The fundamental problem here is that REST is not a standard. It is an architectural style for web APIs. While there are mechanisms to make these APIs self-describing, and there are web-service description formats, most APIs do not make use of these techniques – and why should they? That's a lot of enterprisey ceremony with very little value. Even once you ...


3

In many senses, the question seems too broad to me and it's going to be hard to give you an accurate answer to the question What each of these backends really does?. However, we can read the diagram and guess. A key concept here is separation of concerns. A secondary one could be distributed computing. For brevity, let's focus on the first1. What you see ...


3

I'm not sure if I should test the DB's values or the "transformers" functions in my code Do both. We don't know your design but, what we have here are three different concerns that -in theory- should be possible for you to test independently from one another. I assume that -at least- you have implemented the unit tests for every E(xtract), T(ransform) ...


3

You need to look at what the root problem is. Are you seeking data redundancy? Are you seeking minimal data access times? Are you seeking sharing data across separate environments? Are you seeking to minimize security vulnerabilities with access to the data? Once you decide what the highest priority is, then you can work on finding the best solution. For ...


3

Nope, you're spot on here. ETL is the wrong choice for this problem, ETL is for information transfers from complex proprietary formats, that are regular and or large and must integrate into other complex proprietary schemas. Also ETL is often the tool of choice for business types as they can be more user friendly for doing data imports/integrations and avoid ...


3

I strongly suggest you to have both artificial data in one or more small test files to check each requirement on its own (maybe for unit testing) one or more production files of a certain size to check things you did not think of when designing your artificial data (this gives you an integration test) To my experience, the chances are high that those two ...


2

From the warehouse side I would have a staging table from each (logical and temporal) source, so for all data coming from source 1, there would be a table for source 1-version 1, source 1-version 2 and so on. You then have a second schema (or database) which is 'reportable' and that is filled from the staging table(s). You can add, and not need to remove ...


2

Additionally to connectivity provided by REST interfaces or FTP file transfers, you will require a certain degree of data security and fault tolerance. Therefore, my recommendation is to use AS2 as secure and reliable message transfer. Mail transfer of XML messages (secured via S/MIME X.509 PKI certificates) might be a bit slow for you but would be an option ...


2

You have to adhere to these rules: OLTP applications don't change keys. ODS generates its own Business Intelligence keys. Datawarehouse database never references OLTP keys and must use keys generated by ODS (step 2). There is no way to go lean about any of the above rules unless you do a full load every night. Trying to get "deltas" (changed data only) ...


2

As for if and how to use HTTP for the data transfer, I completely agree with MainMa's answer. However, independently of the size of the data, the process which you describe doesn't sound a like a typical application for Rest. One of the main ideas of Rest is having named resources like mycompany.com/claims/customer/{number}/claim/{number} which can be ...


2

In Java there is a project called Dozer that handles such cases. You have an XML file where you declare what in the source corresponds to what in the destination and Dozer does the job. It handles conversions between data types, if the types have the same fields you don't need to specify anything it just matches them by name, if you need custom conversion ...


2

You combine two questions: shall standardisation performed before ETL and shoudl ETP steps be performed separately. Here some thoughts to help you: Data standardisation in order to process data in a known format is part of the ETL (Extract Transform Load) process. So it is not needed to perform this before handling to an ETL framework, unless the ...


1

Looking more into these questions, think I have some answers: From the article here (https://docs.bitnami.com/azure-templates/infrastructure/apache-airflow/configuration/sync-dags/) and discussions on the airflow mailing list, it appears that something like a git-sync on a cron schedule for dags and other required code across worker nodes is the norm. From ...


1

I can think of two potential reasons why it could make sense to have the "transformation into a standardized format" a separate step: this first step may fail because the quality of the input data is not good enough. So a user of the ETL process may want to run the transformation first, check the results, then correct the input data and repeat the former ...


1

If the number of data elements is small enough to cache in memory, you can load the data in a structure to keep track of it before you start the ETL. At that point, you are querying your local structure before queuing up your insert statements. If the number of data elements is too large to cache, then you have no choice but to query before insert. Even ...


1

Analysis of the system components: a main backend called Confidential data backend: it acts as a facade to the external world, receiving front-end requests and communicating with external services through a sub-component called Data bus. It also interacts with a database. a Backend queue : the queue receives all its input from the Data bus but delivers ...


1

"only that the resulting DB matches what is expected" So you need to take some fixed sets of test data, run the ETL process and compare the content of the resulting DB with a set of reference data. That's it. The test data sets should be small enough to let you verify the result in a reasonable amount of time, and complex enough to cover all important steps ...


1

Yes, that is how I interpret that. Data stored in data warehouses is imported from primary sources. The effort required to bring in data is non-trivial so therefore much of the source data is not available in the data warehouse. Typically this means that you might have every address of every customer but you don't have any birth dates, for example. It's ...


1

Have you considered using one of the database replication methods? There are several main approaches for MySQL database replication. The approach that can suite yout case is "Binlog replication". This method (sometimes referred to as change data capture - CDC) utilizes MySQL’s binlog. MySQL’s binlog keeps an ordered log of every operation that was ...


1

It seems as though you want to keep your existing etl logic pretty much the same but need some new process to divert the data in a more dynamic way. Some type of software agent may act as a bridge between the transformation layer and db load layer, providing the extra functionality you need. I am sure something like this exists and what I would do is ...


1

Triggers fire synchronously with the transaction which wrote the data initially. That Tx will incur further latency because of the trigger. This may be acceptable to you, or not. If the trigger code fails the original Tx will fail too. Change Data Capture may be a reasonable approach. It can be asynchronous at runtime. The application will be ignorant of ...


1

The cost of ETL should be cheaper because there's some level of batching involved, rather than doing work for every INSERT/UPDATE. But as the time requirement becomes smaller, trigger becomes more suitable answer. On the other hand, if it can be implemented using trigger (e.g. copy to another table), maybe you don't really need to use ETL. You need ETL to ...


1

Well...just focusing on the practical aspects of the general ETL problem, without too much additional effort you could separate the backing logic into one or more reference-able assemblies, and create a simple windows service to call into your ETL process in an identical way. It's trivial to set up a windows service application to also be runnable via ...


1

Data warehouses typically can be "refilled" completely from some kind of OLTP database. It is not unusual to do this once per night, asynchronously. So whenever you have a new requirement where you have to make a schema change, one possible strategy is just to define that as a new "version" of your data warehouse, adapt your ETL process to the new version, ...


Only top voted, non community-wiki answers of a minimum length are eligible