Advanced Data Analysis in Snowflake | SQL & BI Dashboards

Explore the data analyst role within the data pipeline, covering data collection, cleaning, exploration, analysis, visualization, and reporting using SQL, Excel, and dashboards in Tableau or Power BI.

Assess data volume and ingestion frequency to choose batch or streaming data and plan Snowflake loading with internal or external stages, bulk loading, and Snowpipe for real-time updates.

Explore csv, parquet, and json formats in Snowflake: csv is plain and readable, parquet provides columnar, compressed storage for analytics, and json supports semi-structured nested data.

Sign up for Snowflake with the 30-day free trial, activate your account via email, create it, choose Azure as the cloud provider, and set SQL as the language.

Explore the Snowflake home page and data workspace, learn how to load data into tables, and review dashboards, projects, notebooks, and data management features.

Set up a Snowflake stage within the customer details schema, enable client-side encryption, upload CSV, JSON, and Parquet files, and load them into a new table.

Learn how to load json and parquet data into Snowflake from stages, create and rename tables, handle data type mismatches, and ensure correct column matching for reliable analytics.

Connect Azure blob storage to Snowflake as an external stage, configure the url and sas token, load three csvs into a table, and use copy into with csv format.

Learn data manipulation in Snowflake by inserting records into a customers CSV table, updating date formats and fields, deleting rows, and understanding truncation versus table structure.

Explore how to enrich datasets by using the Snowflake marketplace and data sharing, adding weather, geospatial, and demographic data, and performing joins to enhance analytics.

Automate data processing in Snowflake by creating tasks that schedule SQL statements. Run a daily cron at 1 a.m. to update the sales by time table with recent orders.

Log and monitor Snowflake query and task history to track runs, durations, and failures by user and warehouse; create views to surface failed tasks and error messages.

Explore Snowflake constraints, including inline and out-of-line definitions, primary keys, foreign keys, not null, unique rules, with single-column and multi-column examples from employees, orders, and enrollments.

Explore Snowflake scalar, aggregate, and window functions by transforming data with upper, computing new columns like age in months, and applying a discount and rounding to orders.

Learn how to use aggregate functions in Snowflake to compute sum, avg, max, and min on the orders table, grouping results by non-aggregate columns like gender or order date.

Explore two common window functions, rank and dense_rank, in Snowflake to rank customers by purchase amount within regions, illustrating gaps versus continuous ranks.

Explore window functions in Snowflake, including rank, dense_rank, and cumulative sums using partition by and order by. Learn lead and lag to compare current rows with previous and next records.

Explore parsing JSON with parse_json into a Snowflake variant column to extract keys, nested objects, and arrays, and query fields like employee id, name, contact email, and project details.

Learn to flatten JSON data with Snowflake table functions, using lateral flatten on a variant column to turn order and customer details into readable rows of products and amounts.

Explore Snowflake system functions, including control and information functions, to view current date, user, role, and warehouse, inspect data types with system$typeof, and cancel queries.

Explore geospatial functions in Snowflake used by data analysts, including startpoint to derive coordinates from latitude and longitude, and compute distances between points, converting to kilometers.

Explore the differences between ETL and ELT, and see how Snowflake enables raw data loading, in-database transformation, and scalable, cost-efficient data pipelines with tools like dbt.

Analyze Snowflake cost drivers, from compute warehouses and storage, to time travel, temporary and transient tables, and data egress, while optimizing batch loads and file formats.

Learn to use case statements in snowflake to transform and categorize data, with examples of product type grouping, binary flags for high sales, and age-based data quality checks.

Identify duplicates by counting records per customer ID with group by and having, then keep the earliest using row_number over partition by customer ID order by registration date and qualify.

Learn to validate emails in Snowflake using like and not like with % wildcards, detecting missing at signs, missing dots, multiple at symbols, and length checks.

Explore standardizing the status column by identifying distinct values, mapping 1/0 to active or inactive, and handling case variations to ensure consistent data for analysis.

Apply regexp replace in Snowflake to remove digits from names, collapse extra spaces, trim, and standardize capitalization for clean, analyzable data.

Learn to clean phone numbers in Snowflake with regexp_replace. Convert blanks and zeros to null, strip non-numeric characters, handle 11-digit numbers by removing a leading one, and reformat to 3-3-4.

Clean and standardize the dataset by normalizing customer categories to basic, premium, and standard; fix ages, registration dates, and statuses; impute null ages with the average age and lowercase emails.

Load multiple datasets into a Snowflake relational database, organizing them into products, orders, order details, and customers schemas, and prepare for aggregation and validation.

Create a sql worksheet on the retail orders database to aggregate products by category, showing counts, average, min, max prices, standard deviation, and invalid or missing values; then study joins.

Join order items with products and orders using inner, left, and right joins; use rollup to generate subtotals by category, subcategory, and status with revenue and average selling price.

Apply window functions to analyze completed orders: compute running totals by customer, rank orders within each customer, and use dense_rank for global order value to identify the highest value order.

Explore window functions percent_rank and ntile in Snowflake to rank and group products by category and price, generating price percentile and price quartile insights.

Learn how common table expressions (CTEs) and subqueries simplify SQL in Snowflake, using examples like creating a customer stats CTE, casting dates, and classifying customers by value and recency.

Learn to change data types in snowflake with sql cast and try_cast, transforming fields such as age, registration date, and customer id into varchar, timestamp, date, and number.

Explore partition pruning in Snowflake to optimize queries by clustering data on frequently filtered columns, enabling the system to skip irrelevant micro partitions, leading to faster queries and reduced costs.

Use time travel in Snowflake to query, clone, and restore data as it existed in the past, enabling recovery from mistakes and auditing past states, including zero copy clone.

Learn to view the query profile to identify bottlenecks, such as large window functions and self-joins, and optimize SQL queries, reducing runtime from 6.5 to 4.1 seconds.

Materialized views boost query performance by precomputing and storing results, unlike regular views that run queries on demand, enabling one-time computation for expensive queries.

Create temporary and transient tables to store intermediate results, drop them at session end, or persist briefly for lower storage costs, while handling column name duplicates.

Create a Snowflake user defined function to calculate final price from unit price, quantity, and discount percent using SQL, and adjust the orders table to a date type for calculations.

In Snowflake, build stored procedures to run multi-statement SQL, compare them with user defined functions, and automate monthly data tasks with scheduled tasks that run the procedure.

Compare regular, secure, and materialized views in Snowflake to understand their definitions, data storage behaviors, security protections, and performance implications for query optimization.

Visualize data in Snowsight with bar charts, comparing category and subcategory total sales, and tailor charts by axis labels, orientation, and stacking to highlight top items.

Visualize customer data in Snowsight using bar and 100% stacked bar charts to explore age, tier, and spend, adjust axes, and compare platinum, silver, and bronze segments.

Visualize sales by product using scorecards to compare 2022 and 2023, calculate the sum of sales and percentage change, and highlight how 2023 outperforms 2022.

Explore how to visualize data with a scatter plot in Snow Sight, compare cost and unit price, count unique orders, identify outliers, and color dots by product category for insights.

Visualize data in Snowsight with a heat grid using two dimensions—product category and customer tier—and total sales, then interpret color intensity to spot higher sales across electronics, platinum, and books.

Visualize monthly trends in Snowsight by converting order dates to the first of the month, then plotting total orders or total sales as a line or area chart.

Dimensional modeling splits data into fact and dimension tables for fast querying in data warehouses, linking facts to dimensions via foreign keys to provide descriptive context.

Explore the star schema in data warehousing, with a central fact table linked to dimension tables like students, courses, and date to enable joins for descriptive details and enrollment metrics.

Learn how the snowflake schema normalizes dimensions into subdimensions linked to a fact table by foreign keys to reduce data redundancy and require more joins.

Explore diagnostic analysis to identify anomalies, patterns, and relationships across a new e-commerce data set by loading campaigns, customers, products, and sales into Snowflake and analyzing demographics and tenure.

Analyze seasonality by extracting year and month names, counting orders, and measuring revenue and average order value to reveal peak months and weekday versus weekend trends.

Identify the causes of data anomalies by analyzing weekends, months, and campaigns using a left join and case checks to flag unmatched campaigns and potential outages.

Analyze sales distribution by age group and product category using counts, average transaction value, and percent of total revenue to identify top revenue drivers like home and garden across the dataset.

Analyze product performance by revenue, quantity, and price, joining product data to compute units sold and total revenue. Rank products by revenue to reveal top sellers, electronics and headphones.

Forecast house prices from square footage with a simple linear regression in Snowflake, compute the slope and intercept, and predict and compare results.

Create a multi-tile Snowflake dashboard by adding SQL worksheet tiles to visualize revenue, orders, and customers, using charts, heat grids, scorecards, and line charts in dark mode.

Add filters to Snowsight dashboards by creating a category filter with a SQL reference, then apply it to visuals and enable multi-select plus an all option.

Load employee and engagement data, create three roles, grant usage on databases and schemas, and grant select on two roles for controlled access and masking policy.

Create a masking policy on the employee name and apply it to the name column in absence and survey tables; create a row access policy for HR lead read only.

Learn how to build views that surface employee engagement and absenteeism data, and apply masking and row access policies to control who can see names and sensitive details.

Download power BI desktop, install the 64-bit snowflake ODBC driver, create a system DSN, test the connection, then connect Power BI to Snowflake via ODBC to access databases and schemas.

Learn to measure correlation between numeric variables using Snowflake's corr function. See examples with hours absent and engagement score, and with unit price and sales value.

Explore creating dynamic tables in Snowflake to keep dashboard queries up to date with a configurable target lag, including privileges and management steps.