🍉 James D. Bohrman’s Post

🚀🚀 I spent 5 hours learning how ClickHouse built their internal data warehouse. You might have heard of ClickHouse. You might know that ClickHouse is fast for both real-time and batch analytics. But here’s something you might not know: how the engineers at ClickHouse — the company behind one of the world’s most powerful OLAP systems — build their internal data warehouse. From a 10,000-foot view, here’s the initial tech stack that ClickHouse used to build their data warehouse: ◉ Airflow was used as the scheduler. ◉ AWS S3 served as the intermediate data layer. ◉ Superset was their BI tool and SQL interface. ◉ And, of course, ClickHouse Cloud was used as the database and processing engine. Here’s the data flow: ◉ They captured 19 data sources into S3 buckets. ◉ The data was then inserted into the raw layer, maintaining the same structure as the source tables. ◉ Data transformations were performed using the ClickHouse engine, orchestrated by the Airflow scheduler. ◉ Transformed data was stored in mart tables, which represented business entities and met the needs of internal stakeholders. ◉ For data consumption, internal users queried the mart tables and created charts and dashboards using Superset. 💪 After a year, they introduced dbt into their stack to centralize data transformation logic. You can find my detailed article here: https://lnkd.in/gUeATcxu ♻️ If you find my work valuable, please repost it so it can reach more people. #dataengineering #dataanalytics #datawarehouse #clickhouse #olap 

Hello everyone! Time for a quick new post. With limited time on my hands lately, I've decided to share my thoughts in shorter articles that I can refer back to in the future. ClickHouse has long been part of my data routine. I'm quite fond of this tool and its capabilities. This post is about the functionality ClickHouse offers to make it an integral part of your ETL processes or even replace them entirely. https://lnkd.in/eVerGf6h #clickhouse #data #etl #dataengineering

🚀 What is ClickHouse and Why It Stands Out Among Data Warehouses? 🚀 The data world is abuzz with ClickHouse, a standout among data warehouses for its exceptional capabilities. If you deal with massive datasets and crave swift, scalable analytics, ClickHouse is your go-to solution. Here's why ClickHouse shines bright in the realm of data warehouses: ⚙️ Key Advantages of ClickHouse: 1️⃣ Blazing Fast Queries 🔥: Leveraging columnar storage and vectorized execution, ClickHouse processes billions of rows in mere seconds, delivering prompt insights. 2️⃣ Real-Time & High-Frequency Data 📈: Seamlessly handles streaming data and intricate aggregations, making it perfect for dashboards, app metrics, and monitoring needs. 3️⃣ Cost-Efficient & Scalable 💰: With its distributed, high-compression storage architecture, ClickHouse maintains a balance between low costs and high performance. 4️⃣ Optimized for Complex Queries 🌐: Particularly adept in scenarios with extensive data volumes and frequent aggregations, ClickHouse stands out in handling complex queries efficiently. 💡 Why Choose ClickHouse Over Traditional DWH? While options like Redshift, BigQuery, and Snowflake boast power, ClickHouse takes the lead in ultra-low latency and real-time analysis at scale, making it the ultimate choice for swift, cost-effective OLAP workloads. While not tailored for transactional tasks, ClickHouse reigns supreme in the realm of fast, economical OLAP. 🚀 #DataEngineering #RealTimeAnalytics #ClickHouse

You know what gets me excited about data - performance! Hard to explain, it's like solving a puzzle. I love it. At Matatika, we saved all our clients switching from FiveTran a massive 99.4% on their GA4 costs. Awesome. But we also hugely improved performance for everyone moving data from Big Query to Snowflake! You can read all the details in Reuben's article here: https://hubs.li/Q02XntVl0 Millions of rows an hour to millions a minute! Why does this matter? If your data platform uses row-based pricing, you could be paying far more than necessary. Fivetran charges based on the number of rows processed, which leads to inflated costs. At Matatika, we focus on cost-based pricing, meaning you only pay for the infrastructure you actually use. So with this huge performance improvement - you win and we win! With 99.99% uptime and real-time data insights, Matatika delivers high performance without the hidden fees or added complexity. Ready to stop overpaying for your data platform? #DataCostSavings #ETLPerformance #GA4Savings #MatatikaSuccess

Yet another specific example of the performance improvements that Snowflake is making all the time. It is neat to have runtime decisions for joins based not just on compile-time estimates. There are also some great technical descriptions of joins in Snowflake in this article. https://okt.to/E9aQU6

One of our founding engineers (GRANT P.) put out a great post recently on how we run "split queries" at Cotera. Often in analytics you run very similar queries back to back. For example, you might roll up some data by week at first, but then change your mind and what to go by month. Normally, both these queries have to go all the way back to the data warehouse, despite 90% of the query being identical. This makes for slow and frustrating experiences for data consumers. So we built split queries to make this better. The general idea is that we identify the chunk of query to reuse, and then cache it as a parquet file. The follow up query will then use the parquet file with duckdb in the browser to "finish off" the query, which feels almost instantaneous. It's somewhat similar to Cube's aggregation cache if you're familiar with that. Check out the post if you're into this kind of thing! https://lnkd.in/d7cibcVM

Super excited to share Gigasheet's new write-back capabilities for Snowflake and Databricks. Gigasheet now not only allows users to query/analyze data in real-time (from 8+ data stores), but now allows users to post back clean or prepared data. Gigasheet helps unify fragmented data, allowing users to join nearly any file into the warehouse (in a governed way), with support for over 40 file types, from CSVs and Excel to JSON and Apache logs and 250GB+ files. https://lnkd.in/e8eJcfgT

There is a lot of dynamics in the #Lakehouse stack these days. It is high time that the community comes to an agreement for a common open catalog standard that can replace the increasingly dated #hivemetastore. For instance, Databricks (#DeltaLake ecosystem) open sourced #UnityCatalog last week: https://lnkd.in/d48Qvm44. Now, this catalog here by HANSETAG is an open source implementation of the #Iceberg catalog REST API, which comes with many bells and whistles for #enterprise deployments such as secure credential mapping. These two are not necessarily just disjunct alternatives when you take into account #Databricks acquisition of Tabular (now part of Databricks) and the plans they shared last week for convergence of #DeltaLake and #Iceberg: https://lnkd.in/dSC2zEf7 At the end of the day people will want a catalog that supports all established table formats and comes with the critical enterprise bells and whistles!

🔔 Exciting Announcement! In collaboration with dataMinds.be and hosted by Schoenen Torfs, we warmly welcome all data enthusiasts for our next knowledge-sharing event: "The Analytical Landscape at Torfs & Database, the SeQueL: Should I Still Go SQL?" 🚀 Curious about what's in store? 🌟 Session 1: Join Jasper Verhulst, product owner and data platform specialist at Torfs, to learn from Torfs' journey of becoming more data-driven. Explore data sources, architecture evolution, analytical reports, and delve into Torfs’ merchandising engine. 🌟Session 2: Dive into SQL's relevance with Sander Allert, data platform architect. Discover SQL's evolution from traditional platforms to cloud-based ones like Azure. Explore trends like dbt and applications like Microsoft Fabric. Beware of Python in the garden of Eve! 🐍 🗓️ Tuesday 16th of April 🚩 De Zaat (Temse) ✍️ Don't miss this opportunity to expand your analytical horizons! Register now and secure your spot. #DataAnalytics #SQL #Azure #Torfs #Databricks #Networking

The future of enterprise BI? It's as simple as using a spreadsheet. Check out Gigasheet!