Sajag Goel

How do real-time analytics databases store data on disk? ⚡ Rockset build a super-interesting Index structure called Converged Index that is efficient for all kinds of lookups - row, column, and even inverted indexes; in the video, I have dissected and explained its internal details. give it a watch - youtu.be/xGaUJTHuehQ By the way, this is the 2nd video in the Rockset internals series and I dissected and researched through 18 of their blog posts and 2 whitepapers. Exploring the details was fun, and I am sure you will love it too. 1. Architecture deep-dive - youtu.be/I4eEEAgoCig 2. Converged Indexes - youtu.be/xGaUJTHuehQ ⚡ Subscribe to the channel and follow along; I keep things no-fluff. #AsliEngineering #RocksetInternals #DatabaseInternals

231

4 Comments

Snowflake Optimization Tip of the Day #5 ➡ Use incremental models in dbt There is no simpler way to reduce Snowflake cost than to process less data. If you are using dbt, try to write every model incrementally. Instead of reprocessing all data every time, incremental models only process new or changed data since the last run. This is achieved primarily by using date stamps or other markers to identify what data has been added or modified since the last run. Full-refreshes are very expensive and should be avoided if possible.

8

AVG(1) vs SUM(1) in tableau In Tableau, AVG(1) and SUM(1) are both calculations that involve a constant value (1). However, they serve different purposes in aggregating data: 1. AVG(1): - This calculation takes the average of the constant value 1 across the specified dimension or level of detail in your view. - The result is always 1, regardless of the number of records or rows. Example: AVG(1) = 1 2. SUM(1): - This calculation sums up the constant value 1 for each record within the specified dimension or level of detail. - The result is the total count of records within that particular dimension or context. Example: If you have 2323 records for Central Region in dataset the: SUM(1) = 2323 In summary, AVG(1) always results in 1, while SUM(1) gives you the count of records within the specified context. These are the hacks which helped you to create some custom charts, like if you want to create Progress Chart you can use AVG(1) along with the primary measure and then create the dual axis.

41

Power BI in Hindi- DAX Function: RANK - How It Differs from RANKX| How to break ties: https://lnkd.in/gkce99ti #powerbiinterview #PowerBI #dax #powerquery #AmitChandakDA #businessintelligence #PL300 #powerplatform #bi #powerbidesktop #datatodiscovery #powerbitutorial

20

You wont be able to convert analytics interview . yes you heard it right .Be it sql, python, visualisation if you are not comfortable and do not have right framework on case studies and guess estimations its hard to convert analytics role. I have been giving interviews for mid level experience and in every company they have either one separate round or they ask guess estimations or full case in managerial round . -People often relay on instinct for Case study and guess estimation but its always good to have a curated approach and proper prep for such rounds so that in interviews you don't feel lost or blank. estimation and cases that are frequently asked - 1- number of ac sold in delhi in the month of June . 2- footfall of tourist in the month of October . 3- monthly revenue of airtel. 4- annual revenue of pizza store in busy area of banglore. resourses  1. book-Case in Point 11: Complete Case Interview Preparation 2.Youtube Channels (a)Insider Gyaan by Soumi Datta (b)Technomanagers by @shailesh sharma Those recourses will be more then enough to ace your case round .   

104

7 Comments

Is Marketing Mix Modeling (MMM) an Econometric Model or a Statistical Model? Short answer : It is a statistical model developed by econometricians with a lot of statistical guardrails. Why the demarcation? Well of late MMM is facing attacks for being an Econometric model. Some of the shortcomings of econometric models are being attribute to MMM as well (see Christian Hollums' post link in comments). 📌 How true are these criticisms ? Not much. The fundamental premise of attacking MMM seems to be that econometric models have not done a good job capturing economic realities. The core criticism seems to be that econometric models have failed to capture dynamics of market participants' behavior. Firstly people seems to be confused with economics and financial markets. Though related, they are different kettle of fish altogether. Econometric models can't model financial markets accurately. Financial markets follow somewhat a stochastic process governed more by fat tail properties and not only would econometricians would fail at it, any STEM person would fail at it.    Econometrics in a way is antithetical to core statistical principle of parsimony. Econometricians do indeed believe that they could model any behavior if they throw enough variables. Also some of the dependent variables econometricians deal with are estimated rather than being realized with certainty. For e.g. GDP, Household incomes are estimated. 📌 How is MMM different from typical Econometric models. MMM has one stark difference in comparison to typical econometric model. The DV in case of MMM (e.g. sales) are realized with certainty. Secondly, MMM has a lot of guard rails like having more data for each variables being estimated (p>n problem). Over and above this MMM has added a lot of checks and balances (see link in comments) to ensure that the models are accurate. 📌 Lets show some love for econometrics too. So given all these bashings of econometrics, let me also point out why marketing effectiveness vendors should be thankful to econometrics as a field. Causality - Causality is not an invention of econometrics but MMM and Econometrics face the problem of not being able to do controlled experiments to prove causality. The reasons vary from being unethical to impractical. One can't just segregate people into groups in a famine/war region and decide only 1 group gets certain welfare scheme and the other does not. So controlled experiments are not possible. Similarly in MMM one can't RCT because of sheer impracticality (see comments). So how does one prove casualty then? Ans: Quasi Experiments. Econometrics have leveraged techniques like DID, RDD and IV, to solve the causality problem. MMM of late have taken a leaf out of econometric way of proving causality (at least we have - see our DID paper). In summary: MMM does have a lot of reasons where it could go wrong but if done with care and vigil, it is an excellent tool for efficient marketing decision making.

97

8 Comments

Hi Community, I am back on my channel (to be regular this time ;)). Just shared my thoughts on how to build an analytics career. Freshers, Mid-Level Experienced and Experienced Folks can have a look I believe. Please put your comments in case you need any specific input in the field of Data Analytics/Data Science. Cheers!! #DataAnalytics #DataScience #Freshers #Mid-Level

31

3 Comments

🔰 Creating Date Table In PowerBi: Creating reports in Power BI often involves analyzing data based on date and time. Businesses need to track performance over different time frames like months, quarters, or fiscal years. To do this effectively, it's important to format date values correctly. While Power BI can detect date columns automatically, sometimes you need to do some extra work to get the dates just right for your organization. For instance, let's say you're making reports for the Sales team. You notice that tables like Sales and Orders have their own date columns (like ShipDate and OrderDate). You're asked to create a table showing total sales and orders by year and month, referencing these date columns. Here's how you can do it: 1/ Create a common date table: You can do this in three main ways: Using source data: If your database already has a date table, great! You can use it to identify holidays, separate calendar and fiscal years, and more. Using DAX: With Data Analysis Expressions, you can create a date table easily. You specify the start and end dates, and it generates a range of dates for you. Using Power Query (M-language): This is another option to define a date table. You can specify the start date and duration to create a list of dates. 2/ Add columns for year, month, week, etc.: Once you have your date table, you can add columns to break down the dates into different parts like year, month, and week. This helps in creating visualizations. 3/ Mark the table as the official date table: You need to tell Power BI that this is your main date table so it knows how to handle date-related calculations and formatting. 4/ Build your visual: Now that you have your date table, you can establish relationships between it and other tables (like Sales and Orders). This allows you to create visuals like total sales by month or year. 5/ Create measures for calculations: For example, you might want to calculate the total sales. You can use DAX to create measures like #Total Sales = SUM(Sales[Amount]). 6/ Create your visualization: Finally, you can use the Table visual to show total orders and sales by year and month. You can also explore hierarchies for drilling down into data. By following these steps, you can effectively use date and time data in Power BI to create insightful reports for your business. #powerbi #dataanalytics #datascience #copied #datafareer #data #datacommunity

3

#SQL Basics #4: Answering complex business questions using CTEs. 🔍 Scenario: Imagine you are asked to find out how many employees in the Product team got a year-end performance rating of 4+ and got a salary hike in March'24. How will you go about it? 🗂 Dataset: - year_end_rating: employee_id, department, rating - salary_hike: employee_id, old_salary, new_salary, date_of_revision Instead of multiple JOINs that may make your query difficult to read, let's use CTEs for clarity. In simple terms, CTEs (Common Table Expression) are query blocks within your main query. You can spot a CTE in a code by scanning for the word "WITH" as that's what it starts with. 💡 Basic CTE Structure: WITH CTE_name AS ( SELECT col_1, col_2... FROM table_1 . . ) 👇 Full code breakdown below in the image Using CTEs, not only can you answer the original question, but you can also get into exploratory data analysis. 📊 Exploratory Analysis: - What's the average hike per rating? - How many employees didn't get a hike with the same rating? - What's the average salary revision days per employee? CTEs are my go-to whenever I encounter business queries that are dependent on multiple data points or seek deeper insights. ♻ Bookmark it or share it with someone who wants to learn more about SQL! #SQL #DataAnalytics #CTEs #BusinessIntelligence #Insights

16

𝐏𝐨𝐬𝐭 𝐍𝐨.𝟖𝟏 📊 𝐄𝐬𝐬𝐞𝐧𝐭𝐢𝐚𝐥 𝐃𝐚𝐭𝐚 𝐕𝐢𝐬𝐮𝐚𝐥𝐢𝐳𝐚𝐭𝐢𝐨𝐧 𝐓𝐞𝐜𝐡𝐧𝐢𝐪𝐮𝐞𝐬 𝐄𝐯𝐞𝐫𝐲 𝐃𝐚𝐭𝐚 𝐀𝐧𝐚𝐥𝐲𝐬𝐭 𝐒𝐡𝐨𝐮𝐥𝐝 𝐊𝐧𝐨𝐰 📊 As a data analyst, effectively communicating insights is key to driving informed decisions within organizations. Here are five essential data visualization techniques that can help you present your findings in a clear and impactful way: 1 Bar Charts : Ideal for comparing categories of data, such as sales figures by region or product category. 2.Line Charts : Useful for showing trends over time, such as stock prices or website traffic. 3.Pie Charts : Effective for displaying parts of a whole, such as market share or budget allocation. 4.Scatter Plots: Great for visualizing relationships between two variables, like the correlation between temperature and ice cream sales. 5.Heatmaps: Useful for displaying data in a matrix format, where colors represent values, such as in geographic data or user behavior analysis. Mastering these techniques can enhance your ability to convey complex information clearly and make your insights more impactful. #DataVisualization #DataAnalytics #DataScience

16

1 Comment

I have a very simple POWER BI question which will require you to think logically. Que- I have "Table1", in which there is a column "Month" in which 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 are the inputs(Consider 1-Jan, 2-Feb, 3-Mar and so on). "Month" column is in Whole Number data type. I have column "Year" in which -2021, 2022, 2023, Q1 2024 are the inputs. Find the Cummulative sum of ratio of Female inactive to Female active for year Q1 2024 only. Count of Female Inactive- Jan- 2, Feb-3,Mar-7 Count of Female Active- Jan- 800, Feb- 1045, Mar- 1100 In line graph it should be shown like this- Jan- 2/800 Feb- 2+3/800+1045 Mar- 2+3+7/800+1045+1100 The line graph will be increasing showing Ratio value in Y-axis and Month(1,2,3) in X-axis for Q1 2024 Do let me know the logic, you can also use your dummy data to find out the answer in POWER BI. #PowerBI #Dataanalyst #Dataanalytics

104

11 Comments

𝗔𝗿𝗲 𝘆𝗼𝘂 𝗿𝗲𝗮𝗱𝘆 𝗳𝗼𝗿 𝘁𝗵𝗲 𝘂𝗹𝘁𝗶𝗺𝗮𝘁𝗲 𝗮𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗹𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗲𝘅𝗽𝗲𝗿𝗶𝗲𝗻𝗰𝗲? To all folks in the Delhi NCR region, today we present "𝐓𝐡𝐞 𝐔𝐥𝐭𝐢𝐦𝐚𝐭𝐞 𝐀𝐧𝐚𝐥𝐲𝐭𝐢𝐜𝐬 𝐁𝐨𝐨𝐭𝐜𝐚𝐦𝐩" Whether you're a student eager to explore the world of analytics or a working professional looking to deepen your knowledge, join us for an entire day dedicated to kickstarting your analytics journey. 𝐖𝐡𝐲 𝐭𝐡𝐢𝐬 𝐩𝐫𝐨𝐠𝐫𝐚𝐦? 1️⃣ Practical Learning: Engage with multiple case studies developed from our extensive experience across various sectors and functions. 2️⃣ Expert Insights: Learn directly from a consultant who works with cross functional teams and industry leaders. 3️⃣ Comprehensive Coverage: Gain practical skills to tackle complex analytical challenges confidently. 𝐏𝐫𝐢𝐜𝐞? 🎯 Fair and Transparent: 𝗜𝗡𝗥 𝟮𝟵𝟵𝟵 🆓 Special Offer: Enroll now and get free lifetime access to our 30+ online courses worth 𝗜𝗡𝗥 𝟰𝟵𝟵𝟵 📍 𝐖𝐡𝐞𝐧 𝐚𝐧𝐝 𝐖𝐡𝐞𝐫𝐞? 25th May 2024, from 9am to 5:30pm @ Sandal Suites by Lemon Tree Hotels, Noida Sector 135 🔗 To register click on the link in the comments, or while you're going through the Program PDF, click on "Join the Bootcamp" button on Page 9. Limited Capacity: Seats are available on an early registration basis. 📱 If you want to bring a friend or colleague along drop us a message or call us at +91-9168311562 for customized pricing. This is more than just a workshop – it’s a transformative experience that will equip you with the skills to tackle complex analytical challenges confidently. Havish M Consulting Havish Madhvapaty

28

2 Comments

Hi All, Here are some important tableau questions which helps in understanding tableau also will be helpful if one is preparing for Tableau Interviews 1. Start with mastering the definition of measures and dimensions. 2. Understand filters, including their types and order of execution. Prepare examples and scenarios to explain them. Context filters are among the most important ones, so make sure to learn about their applications and use cases. 3. Know the difference between parameters and filters. 4. Learn the basics of custom charts such as Donut chart, Funnel chart, and combination chart (Bar & line combined). 5. Understand how to use Top N and Bottom N using different methods. 6. Learn how to calculate YOY (Year on Year Growth) or MOM (Month on Month Growth) by using calculated fields or table calculations. 7. Get familiar with date-related functions such as DATEDIFF, DATETRUNC, DATEADD, DATESUB, etc. Be prepared to answer questions like finding the difference between order date and shipped date for a product, excluding the weekends, or calculating how many years an employee has stayed in the company if the date of joining is given. 8. Be ready to explain the most recent dashboard you have worked on. 9. Understand the difference between Live and Extract, and learn how to do incremental refresh. Also, know which has better performance: Live or Extract Refresh? 10. Know the string-related functions in Tableau such as Split(), Proper(), LTRIM(), RTRIM(), UPPER(), LOWER(), STARTSWITH(), STR(), REPLACE, etc. 11. Master performance optimization in Tableau. 12. Learn how to calculate rolling sales for Year, quarter, Month. 13. Understand rank use cases and the types of rankings. 14. Get familiar with LOD's (Level of Detail) and the order of LOD expressions. Prepare an example for all three types of LOD functions and how other calculations will impact LOD's. 15. Know the difference between Blending and joins 16. Know about bins and hierarchies. 17. Learn about custom colour palette in Tableau. 18. Learn how one can swap sheets in tableau. 19. Learn about relationship and its types. 20. Learn about logical and Physical layer in Tableau and what operations are being performed in both the layers. #tableau #Dashboarding #Visulazation #Datafam #Reporting #Dashboards

45

Snowflake Optimization Tip of the Day #1 ➡ Apply pruning The golden rule for reducing Snowflake cost is to process the least amount of data (duh!). Understanding and applying the concept of partition pruning is the most important aspect of reducing Snowflake cost. Snowflake stores data in micro partitions and every micro partition has metadata associated with it like maximum and minimum values of a column. For example, let’s say you are querying event data. Applying a filter on the event date = ‘2024-04-22’ would make sure that a significantly large amount of data is never processed because today’s date will not be within the minimum and the maximum of almost 99% of the data you have. This filter not only makes sure that the costs are lower but you'll also get the results quicker. In the Snowflake world, money = time. If you can find a way to reduce the time for a query, you save money. Partition pruning is a win-win in both aspects.

18

I was frequently asked if an MS in business analytics was a good investment, and that's when my content creation journey began. Encouraged by such messages, it's fulfilling to know that I'm making a positive impact in the data analytics community. How has your analytics journey been? Please do add your comments. #dataanalytics #data

8

𝘼𝙯𝙪𝙧𝙚 𝘿𝙖𝙩𝙖𝙗𝙧𝙞𝙘𝙠𝙨 𝘾𝙡𝙪𝙨𝙩𝙚𝙧𝙨: Databricks clusters are fundamental units in the Databricks platform, providing the necessary resources to run various data analytics and machine learning workloads. There are several types of clusters designed to cater to different use cases: In Databricks, there are two primary kinds of clusters: 𝙄𝙣𝙩𝙚𝙧𝙖𝙘𝙩𝙞𝙫𝙚: An interactive cluster is one that we manually establish using the cluster user interface. Usually, several people share this type of cluster across various laptops. 𝙅𝙤𝙗 𝙘𝙡𝙪𝙨𝙩𝙚𝙧: is a cluster that has a Databricks job associated with it. When the work is running, it automatically spins up and down. 𝐒𝐭𝐚𝐧𝐝𝐚𝐫𝐝 𝐂𝐥𝐮𝐬𝐭𝐞𝐫𝐬: Purpose: General-purpose clusters for various workloads such as ETL, data processing, and ad-hoc queries. Features: Can be manually or automatically started. Supports autoscaling and auto-termination. Can run various workloads concurrently. 𝐇𝐢𝐠𝐡 𝐂𝐨𝐧𝐜𝐮𝐫𝐫𝐞𝐧𝐜𝐲 𝐂𝐥𝐮𝐬𝐭𝐞𝐫𝐬: Purpose: Designed for concurrent execution of multiple jobs, ideal for interactive and BI workloads. Features: Provides fine-grained sharing of resources. Optimized for high concurrency. Supports multiple users and jobs running simultaneously. Enhanced security features like credential pass through. 𝐒𝐢𝐧𝐠𝐥𝐞 𝐍𝐨𝐝𝐞 𝐂𝐥𝐮𝐬𝐭𝐞𝐫𝐬: Purpose: Ideal for lightweight tasks, development, and debugging. Features: Contains only a driver node without any worker nodes. Useful for small-scale development, testing, and debugging tasks. 𝐉𝐨𝐛 𝐂𝐥𝐮𝐬𝐭𝐞𝐫𝐬: Purpose: Specifically created for running a job and terminated once the job is done. Features: Automatically created when a job is run. Auto-terminated upon job completion. Can be customized with job-specific configurations. 𝐏𝐨𝐨𝐥-𝐛𝐚𝐜𝐤𝐞𝐝 𝐂𝐥𝐮𝐬𝐭𝐞𝐫𝐬: Purpose: Optimizes the usage of resources by leveraging instance pools. Features: Reduces cluster start and auto-scaling times by using pre-configured pools of instances. Efficient resource management and cost savings by reusing idle instances. 𝐂𝐨𝐦𝐦𝐮𝐧𝐢𝐭𝐲 𝐄𝐝𝐢𝐭𝐢𝐨𝐧 𝐂𝐥𝐮𝐬𝐭𝐞𝐫𝐬: Purpose: Available in the Databricks Community Edition for free. Features: Limited to certain capabilities and resources. Suitable for educational purposes and small-scale development. 𝗦𝘂𝗺𝗺𝗮𝗿𝘆: Each type of cluster is designed to address specific needs in terms of performance, cost efficiency, and ease of use, allowing users to choose the appropriate cluster type based on their workload requirements.

33

1 Comment

#Tableau #Analytics Let’s re-visit = Refresh Data source in Extract and Live connection. Scheduling for Data source refresh in Tableau Server and in Tableau online space. Considering - We are done with the Dashboard. Now we have to ensure the data should get refreshed on timely manner. a) Refresh are of two types = 1) Extract Refresh  2) Live Refresh at Desktop level b) Refresh can be do able at the following space = 1) In Tableau Desktop 2) In Tableau server 3) In Tableau Online. c) Refresh at Extract level gives us the leverage to perform the Incremental refresh. This option is available at workbook level. - Thank you for your valuable time.

41

4 Comments