3

No. What you describe are all variants of ELT. The difference between ETL and ELT is in where you do the "T". The "traditional" ETL flow would implement the "T" (data transformation) outside the DBMS, using a specialized tool like DataStage, Informatica, Talend, etc. The data transformed to the target model would then be simply ...


2

I think the Clustered Columnstore is probably overkill for your situation and working against you. Given the amount of data and how you're trying to query, you should just do a regular clustered index (primary key (ComponentId, Timestamp) and enable page compression if you're trying to save space/reduce disk i/o. The thing to remember about columnstore is ...


2

With that design it is expected that the query requires a full scan of the columnstore. That should be very fast at that scale, but the IO stats show that you're reading from disk. If you look at the wait stats for the actual execution plan you should see that it's more IO waits than CPU utilization. You could scale up the database to get more cache memory,...


2

You say "null should be avoided in a foreign key column," but are these dates really FKs? Most likely, these dates are just attributes of an order, and if the dates are unknown (and the reason they are unknown may be "because it hasn't happened yet"), then NULL is the best way to represent those attributes. If the Manufacturing Date or ...


2

First, you seem to be missing a few zeros in the numbers you mentioned before you should start seeing problems (IMO) Second, I've only seen Kafka as part of a data loading solution for ingesting data from multiple IoT devices. In these instances, Kafka was used to solve the IoT problem. ACID Compliant databases have problems ingesting a bunch of single row ...


1

Data volume is one of the last criteria for choosing the ingestion pipeline implementation. You choose the tool for what it can do that your current tool cannot, and then you test it to see if can handle the volume (spoiler alert: it can; the database will be the bottleneck in 99.9% of the cases).


1

Even if your source data has an id as business key you should use your own surrogate key to manage slow changing dimensions. For example,you can, in this way, follow the changes made on the product numer 25 in you source database having different versions of this product. Example of dimension: id bk desc from to 1 25 product25 ...


1

As mustaccio already indicated, frequent small updates and high-volumes querying are fundamentally incompatible. The main reason is that "frequent small updates" is a characteristic of an operational database, and that typically requires the DBMS to do whatever it takes (e.g. locking) to guard data integrity, and that typically gets in the way of ...


1

Data warehouse is a concept, not a product, so the primary question, "how do modern data warehouses tackle <whatever>" is this: however you design them. To date, in my opinion, none of the major DBMSes are able to simultaneously allow fast trickle inserts and high performance analytic queries. Vertica and ClickHouse come quite close, by way ...


1

Having multiple fact tables in a model is pretty common and is sometimes referred as a Fact Constellation. The shared dimensions are referred as Conformed Dimensions. As @bbaird said in the comment there shouldn't be a direct one-to-many relationship between dim_a (hotel_rooms) and dim_d (customers) as this relationship should be modeled through a fact table ...


1

This is what I say about MySQL/MariaDB without columnstore: Not good: "sum up to 1 TB" versus "1,25 TB of SSD storage". As a Rule of Thumb, you should have half your disk free for maintenance and growth. As a minimum, there should be enough room for an extra copy of the largest table -- data+indexes. This allows any ALTER to run ...


1

i'm not sure to have a good view of what you are doing... But i can say that SASS service is not included with sql server express. You can only have the reletional database service .\SQLEXPRESS, not the analytics database that is SSAS. https://docs.microsoft.com/en-us/analysis-services/analysis-services-features-supported-by-the-editions-of-sql-server-2016?...


1

After some research I've managed to come up with an answer. Kudos to @a_horse_with_no_name for pointing me in the right direction. Real-time sync between different data sources can be done using Kafka Connect. Kafka Connect allows to send data from various systems to a Kafka cluster via Source Connectors and read data from Kafka using Sink Connectors. All ...


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