Difference between Row_number(), Rank() and Dense_rank() is
In case of value is same then
> Row_number() gives : sequential number
> Rank () : Assigns same Rank but the (next rank )= (total previous ranks +1 )
> Dense_Rank() : Assigns same rank and next rank = next sequential number
=========================================
a) Rownum :
( To find 2nd highest salary using ROW_NUMBER)
select empid,salary from (
select e.*,row_number() over (order by salary desc)
as row_num from emp e )
where row_num = 3 ;
======================
b) using Rank :
-- To show only 2nd highest --
select * from
(select e.*,rank() over (order by salary desc) as rank
from emp e )
where rank = 2;
-- ** To display ranks ** --
select rank() over (order by salary desc) as rank,e.*
from emp e ;
======================
-- To show only 2nd highest --
select * from
(select e.*,dense_rank() over (order by salary desc) as rank
from emp e )
where rank = 2;
-- ** To display ranks ** --
select dense_rank() over (order by salary desc) as rank,e.*
from emp e ;
======================
select empno ,sal from emp e1
where 3 =
(select count(distinct sal )
from emp e2
where e2.sal > e1.sal )
- neglecting stakeholder input,
- inconsistent naming conventions,
- and failing to adequately test and
- validate the model.
- not considering data quality,
- ignoring business requirements,
- and overlooking future scalability needs.
VARCHAR uses single-byte character sets (like ASCII),
while NVARCHAR
uses Unicode, allowing for a wider range of characters including those from multiple languages. NVARCHAR typically requires more storage space and has a shorter maximum character length compared to VARCHAR
ODS is a temportary data store from all sources for volatile data like resumes of interview, quotation from suppliers ,etc. which need not be stored permanently
Possible Reasons for Slow SQL Queries
- Lack of Indexes: I
- Complex Joins:
- Large Data Volume:
- Inefficient Query Structure:
- Database Configuration: Configuration settings such as memory allocation, cache size, and connection limits can affect performance.
- Server Load: High server load due to concurrent queries or insufficient resources (CPU, memory, I/O) can slow down execution times.
- Network Latency: If the database is remote, network latency can add to the time it takes to execute a query.
- Statistics Outdated: Database optimizers rely on statistics to create execution plans.
- Locking and Blocking: If a query is waiting for locks held by other transactions, it can slow down execution.
Method 1. using id Column (if ID column is unique, but title is duplicate)
( Delete all rows other than min id value ..(used when id column is not duplicate but other columns values are duplicate)
================================delete filmswhere film_idnot in (select min(film_id) from films group by title, rel_date)Method 2. using CTE ( Two steps)============================================step 1 :with CTE as(select id ,name , email , row_number()over (partition by name , email order by name , email ) rownumfrom salesstep 2 :Delete from CTE where rownum > 1===========================================Method 3. using ROWID============================================Delect from table1where rowid not in (select min(rowid) from table 1group by col1,col2)
what is inner join and outer join count of below
A B
1 1
1 1
2 2
2
3
null null Inner join = count of matching rows
outer join = inner join + left side unmatched
inner = 6 rows
left outer = 6 + 1 = 7 rows
========================================================
========================================================
- get the data from data source
- identify connect the data source
- pick a share data set
- select storage mode identify the query performance issues
- use or create a data flow
- profile the data
- identify data anomalies
- examine data structures
- clean transform load data
- resolve inconsistencies and unexpected values null values data quality issues
- apply value replacements identify the keys for joints
- identify and transform column data types
- apply naming conventions two columns
- perform data loading
- resolve data input errors
- model the data
- designer data model
- define the tables
- configure table and column properties
- flatten the parent child hierarchy
- role playing dimensions
- define relationships and cardinality
- design the data model
- resolve many to many relationships
- design a common date table
- define the data level granularity
What is Data governance
- Data governance is
- a set of processes, policies, roles, metrics, and standards
- that help management processes to keep
data secure, private, accurate, and usable throughout its life cycle.
SELECTId, empname, deptid, Salary,SUM(Salary) OVER(PARTITION BY deptid) AS SUM_SALFROMemp ORDER BY id===========================# Id, empname, deptid, Salary, SUM_SAL'1', 'john', '1', '20000', '50000''2', 'kem', '1', '30000', '50000''3', 'chan', '2', '10000', '30000''4', 'henry', '2', '20000', '30000'
cumulative salary by each dept
SELECT deptid,salary ,
Sum(salary)
OVER(
partition BY deptid
ORDER BY id) AS CUMulative_SAL
FROM emp
ORDER BY id
===============================
# deptid, salary, CUMulative_SAL
'1', '20000', '20000'
'1', '30000', '50000'
'2', '10000', '10000'
If you have a few dimensions and low cardinality (less unique values in dims ) , but you require fast query execution, a star schema is the right choice.
However, if you have several dimensions and high cardinality, a snowflake schema will be a better scheme
List of Snowflake Features
- Decoupling of storage and compute in Snowflake
- Auto-Resume, Auto-Suspend, Auto-Scale
- Workload Separation and Concurrency
- Snowflake Administration
- Cloud Agnostic
- Semi-structured Data Storage
- Data Exchange
- Time Travel
- Cloning
- Snowpark
- Snowsight
- Security Features
- Snowflake Pricing
Create a Delta Version
You create a delta version when you save a model initially or when you incrementally save an existing model.
Follow these steps:
- Click File, Mart, Open >
> The Open Model dialog opens. > Select a model and click OK.
> The model opens.
>Make necessary changes to the model.
>Click File, Mart, Save. - A delta version of the model is created with the incremental changes.
=========================================================================Create a Named Version
A named version of a model represents a milestone in the development of the model. You create a named version to keep that model version indefinitely.
Follow these steps:
- Click File, Mart, Catalog Manager .
> The Catalog Manager opens. > Select a model version,
>right-click and click Mark Version. A named version is created with a default name.
> Edit the name of the named version (new name ) and press enter.A new named version is created in the catalog.
EDW and DW diff
- Both are central locations for storing and managing large amounts of data.
- Both involve gathering data from various sources, although an EDW does so at an organizational level.
- Both are designed to support business intelligence, reporting, and analytics, enabling data-driven decisions.
- Both typically store structured data, such as from databases, but can also handle semi-structured data.
- A standard data warehouse is often departmental or focused on a specific business process, while an EDW is designed for the entire organization.
- An EDW aims to integrate data from many different operational systems (ERPs, CRMs, etc.) across the entire company, rather than from a limited set of sources.
- A standard data warehouse provides specific insights for a department, whereas an EDW offers a holistic view of organizational perform
- Data quality: Data may be missing, incorrect, or inconsistent, and it can be difficult to maintain data quality over time.
- Data security: The many interconnected data sources make it vulnerable to attacks from hackers.
- Integrating diverse data sources: Data from different sources may be in different structures, schemas, and formats. It's important to make sure the data is cleaned and transformed correctly before loading it into a hub.
- Scalability: Big data can be enormous, and the system may run too slowly or be unable to handle heavy pressure. Cloud computing can help with this challenge.
- Choosing the right data model: It can be challenging to choose the right data model.
- Balancing normalization and denormalization: It can be challenging to balance normalization and denormalization.
- Handling data changes and evolution: It can be challenging to handle data changes and evolution.
- Communicating and collaborating with stakeholders: It can be challenging to communicate and collaborate with stakeholder
- ==================================================
The key difference is that
UNION removes duplicate records,
whereas UNION ALL includes all duplicates.
- Avoid using
SELECT *and explicitly list the columns you need to retrieve only the relevant data. - Apply WHERE clauses as early as possible in the query to reduce the amount of data processed.
- Create indexes on frequently used columns in WHERE clauses to speed up lookups, but be cautious of over-indexing which can slow down writes.
- Choose the appropriate JOIN type (INNER JOIN, LEFT JOIN, etc.) based on your data relationships and avoid unnecessary joins.
- Try to rewrite queries to avoid nested subqueries where possible as they can be inefficient.
- Use the most appropriate data type for each column to optimize storage and comparison operations.
- Regularly review the query execution plan to identify potential bottlenecks and optimize accordingly.
- For complex logic or frequently used queries, consider using stored procedures to improve performance and maintainability.
- For very large datasets, consider partitioning tables by date or other relevant criteria to improve query performance on specific subsets.
- Perform calculations only when needed and avoid redundant computations within the query.
- When combining results from multiple queries, use UNION ALL if you don't need to remove duplicates.
- Implement monitoring tools to track query execution times and identify potential performance issues.
- Understand the specific optimization features and best practices available for your database platform.
===============================================
Why do we need Data warehouse when Application database already exists ?
- Data warehouses consolidate large amounts of data from multiple sources into a single, central database. This makes it easier to analyze the data and gain valuable insights.
- Data warehouses help organizations make faster, more informed decisions by providing easy access to high-quality data.
- Data warehouses can store months or years of information, which can be useful for trend analysis and forecasting.
- Data warehouses store data in a single location, which makes it easier to control access and keep data secure.
- Data warehouses can help solve big data challenges by making large amounts of information more usable.
- Data warehouses are usually optimized for read access, which can result in faster report generation.
4 Best Practices for Data Modelling
There are four principles and best practices for data modeling design to help you enhance the productivity of your data warehouse:
Data Modeling Best Practices #1: Grain
Indicate the level of granularity at which the data will be kept. Usually, the least proposed grain would be the starting point for data modeling. Then, you may modify and combine the data to obtain summary insights.
Data Modeling Best Practices #2: Naming
Naming things remains a problem in data modeling. The ideal practice is to pick and adhere to a naming scheme.
Utilize schemas to identify name-space relations, such as data sources or business units. For instance, you might use the marketing schema to hold the tables most relevant to the marketing team, and the analytics schema to store advanced concepts such as long-term value.
Data Modeling Best Practices #3: Materialization
It is one of the most important tools for constructing an exceptional data model. If you build the relation as a table, you may precompute any required computations, resulting in faster query response times for your user base.
If you expose your relation as a view, your users’ queries will return the most recent data sets. Nonetheless, reaction times will be sluggish. Depending on the data warehousing strategy and technologies you employ, you may have to make various trade-offs according to actualization.
Data Modeling Best Practices #4: Permissions and Governance
Data modelers should be aware of the varying rights and data governance requirements of the enterprise. Working collaboratively with your security team to verify that your data warehouse adheres to all applicable regulations would be beneficial.
For instance, firms that deal with medical data sets are subject to HIPAA data authorization and privacy rules. All customer-facing internet firms should be aware of the EU General Data Protection Regulation (EU GDPR), and SaaS enterprises are frequently constrained in their ability to exploit client data depending on the terms of their contracts.
No comments:
Post a Comment