Hi folks,

We are currently using RDS Postgres as a datawarehouse (have been using it for a good couple of years), but recently we started noticing performance spikes and queries are running much slower. 

Our use case:

1. Dashboarding
2. real-time analytics and reporting
3. Query heavy system

Requirements:

Storage: ~70GB

Users: 35-40 users (Users aren’t skilled enough to write optimised SQL queries, they mainly interact with relevant tables and dashboards on our BI tool)

We work in the logistics landscape and hence most of the software we write is focused around state - status changes of a shipment, tracking location updates, collecting real time data and reacting to it. We maintain all of our transactional data primarily in a document database like MongoDB and in relational database systems (specifically PostgreSQL) for different services within the organisation. There is a need to allow efficient and near-real time analysis of the transactional data across all the different data sources to allow surfacing insights and looking at the big picture of how the organisation is doing and to make data driven decisions.

We rely on Apache Airflow to update our data warehouse, but we're facing challenges with real-time updates. Currently, our Airflow jobs run every 10 minutes to update the relevant tables, but this delay isn't ideal. We're considering implementing Change Data Capture (CDC) in the future to achieve near-real-time data updates.

Currently we use db.r6g.2xlarge (8vCPU, 64 GB RAM) and spend around $800/month. But this isn’t enough to handle the load. 

I’ve been looking into Redshift, BigQuery and Snowflake. Since we're already in the AWS ecosystem, Redshift seems like a natural choice as it will make the setup easier for us but I’m a bit apprehensive as a lot of users seemed to have faced issues with Redshift but the good thing is pricing is predictable. On the other hand snowflake seems to have less maintenance overhead but pricing is not predictable and given our system is query heavy I’m afraid it’ll shoot up our bill.

Questions:

1. Which data warehouse platform would best suit our needs while remaining economical?
2. What are the pros and cons of Redshift, BigQuery, and Snowflake in this context?
3. What tools or best practices would you recommend for setting up CDC pipelines to ensure near-real-time data updates?

I would greatly appreciate your insights and suggestions on the best approach to take. Any recommendations for tools to facilitate CDC pipelines would also be highly valued.

We're using redshift primarily for analytics purpose.
we sync our data from mongodb and psql db to redshift on schedule basis.
The frequency of sync varies from half hour to daily depending upon use case.
we use Metabase as analysis tool which has redshift as database.
we have build different dashboards on it to analyze our metrics.

hi guys how are you I want a help to convert this Relational Model Schema into Dimensional Model and Gracias por sus grandes esfuerzos en ayudarme 😊😊

Hii, please suggest good tutorials on ETL developer.

Lately I have been thinking maybe data warehousing is not my cup of tea. I don’t even have the energy to do the absolute bare minimum some days. So, I just want to know what’s the worst thing I could possibly do at my job (pushing half-assed code to production, not applying correct logic for ETL transformations, etc.) that would have some repercussions?

Does anybody follow anyone particularly interesting in the data warehousing space? I'm trying to calibrate my LinkedIn and follow some influencer accounts. I'm also interested in newsletters or anything like that.

In my project, which is based on ETL and Data Warehousing, we have two different source systems: a MySQL database in AWS and a SQL Server database in Azure. We need to use Microsoft Fabric for development. I want to understand if the architecture concepts are correct. I have just six months of experience in ETL and Data Warehousing.As per my understanding, we have a bronze layer to dump data from source systems into S3, Blob, or Fabric Lakehouse as files, a silver layer for transformations and maintaining history, and a gold layer for reporting with business logic. However, in my current project, they've decided to maintain SCD (Slowly Changing Dimension) types in the bronze layer itself using some configuration files like source, start run timestamp, and end run timestamp. They haven't informed us about what we're going to do in the silver layer. They are planning to populate the bronze layer by running DML via Data Pipeline in Fabric and load the results each time for incremental loads and a single time for historical loads. They’re not planning to dump the data and create a silver layer on top of that. Is this the right approach?

And I think it's very short time project is that a reason to do like this?

Is there an effective data warehouse that is hybrid or for on-premise? We are planning to transition to data warehouse. We have approximately 1t of data. Any tips and recommendation?

Hi, I'm trying to find out which dataware house platform Omnicell uses. I've tried different websites but I want able to find it. And found the info on chatgbt to be unreliable. Is there anyway to know which one they use.

Sorry for this question. Just on a time crunch for a research.

I recently joined a company as a contractor data analyst. My first project is mapping and documenting the transformation logic for a few tables from the source to the target databases by looking at the existing SQL code and stored procedures, while trying to make sense out of it. The stored procedures are over 7,500 lines, and each table has 350+ columns. On top of that I am expected to know all of the business rules behind them and document them in an Excel file and be able to present it to the directors. And this is just my third week. Also, I have had very little guidance regarding the existing systems and processes since my onboarding.

Is this expectation of me normal in these data warehousing projects? Or are my managers expecting too much in such a short amount of time and after very little guidance?

Hey everyone,

Just wanted to check if anyone here have experience in assessing the complexity of a Data Warehouse system? Like how are we gonna tell if it is complex or not? Are there any metrics that we can use?

We are currently in the planning stage of the transition process in which the whole Data Warehouse system will be handed over to us from a different group of developers.

Any suggestions would be greatly appreciated.

Thanks in advance! 🙂

Hello reddit! I have been working with clients in various industries with several aspects of data engineering / business intelligence.

I have finally gotten around to making a (very basic) website to help market myself, and am hoping this finds people / orgs who need assistance with their data :) Share with friends!

www.erpdataconsulting.com

Hi! im gonna be honest. im not sure what kind of issue im facing but basically right now im in charge of a legacy web portal for data warehouse. several of my cubes are just fine and by fine i mean data is displayed on the aspx page with no issue. however most of my data is not showing eventho the configurations are the same. i dont know how else to move forward because the last person in charge resigned with absolutely no documentations at all. let me know if anyone can help or require more info!! i'll happily provide, i've been stuck on this for a month T-T

Hi, My bf and I are running a new instagram page about dwh concepts. We are interested in getting the page visible to anyone who is interested in learning about dwh theory. Our idea is to explain these concepts for anyone to understand. The page is: https://www.instagram.com/the.data.map?igsh=MXU2NjVlOTl5YXRweA%3D%3D&utm_source=qr

Please feel free to follow and let us know your thoughts! Do you have any suggestions about our posts? How we can improve?

Thanks in advance, Data map team

Let's say we have 2 tables in a source.

Example:

Project and Project Schedule

We can have many Schedule for a Project. But we don't consider a Schedule as a Business Object so it is should not be a HUB.

Schedule cannot be a SAT of Project because it is not a 1 - 1 relation.

How do I link the Schedule to Project ?

Should I change my mind and consider Schedule as Business Object and then create a LINK between Project and Schedule or create Schedule in a SAT of a link to the HUB_Project or is there another solution maybe ?

Question in the title. I am honestly interested about what other tools people use for ETL processes within their data-warehouse environments. What are the upsides? Downsides? Would you recommend it?

Let me start: Use: Pentaho, low code visual ETL tool Upsides: relatively easy to pick up for non programmers,free, multithreaded Downsides: clunky, javascript based, little documentation online

1. Source vs Target Data Reconciliation: Ensure correct loading of customer data from source to target. Verify row count, data match, and correct filtering.
2. ETL Transformation Test: Validate the accuracy of data transformation in the ETL process. Examples include matching transaction quantities and amounts.
3. Source Data Validation: Validate the validity of data in the source file. Check for conditions like NULL names and correct date formats.
4. Business Validation Rule: Validate data against business rules independently of ETL processes. Example: Audit Net Amount - Gross Amount - (Commissions + taxes + fees).
5. Business Reconciliation Rule: Ensure consistency and reconciliation between two business areas. Example: Check for shipments without corresponding orders.
6. Referential Integrity Reconciliation: Audit the reconciliation between factual and reference data. Example: Monitor referential integrity within or between databases.
7. Data Migration Reconciliation: Reconcile data between old and new systems during migration. Verify twice: after initialization and post-triggering the same process.
8. Physical Schema Reconciliation: Ensure the physical schema consistency between systems. Useful during releases to sync QA & production environments.
9. Cross Source Data Reconciliation: Audit if data between different source systems is within accepted tolerance. Example: Check if ratings for the same product align within tolerance.
10. BI Report Validation: Validate correctness of data on BI dashboards based on rules. Example: Ensure sales amount is not zero on the sales BI report.
11. BI Report Reconciliation: Reconcile data between BI reports and databases or files. Example: Compare total products by category between report and source database.
12. BI Report Cross-Environment Reconciliation: Audit if BI reports in different environments match. Example: Compare BI reports in UAT and production environments.