Questions tagged [etl]
'Extract, Transform and Load': Data load processing, normally in the context of data warehouse systems.
224
questions
0
votes
0answers
36 views
Select Changed Rows in MySQL and Postgres without Timestamp column
I have the following scenario:
I have 2 source systems, one running Postgres, the other running MySQL. An ETL process (using Azure DataFactory and Azure Integration runtimes) runs the Extract for a ...
0
votes
0answers
15 views
Snowflake/S3 Pipeline: ETL architecture Questions
I am trying to build a pipeline which is sending data from Snowflake to S3 and then from S3 back into Snowflake (after running it through a production ML model on Sagemaker). I am new to Data ...
0
votes
0answers
5 views
How to modify the block size of my S3 parquet files that are being queried in Athena?
I read that adjusting the block size of the parquet files being queried with Athena can affect and possibly improve the performance of the queries. My parquet files for my database are currently ...
0
votes
0answers
38 views
Avoid transaction when reading data through a linked server
This is SQL Server related.
Is there other techniques that I can use for this? (Read data from a procedure in another server.)
I'm trying to re-use a stored procedure from another sever instance ...
0
votes
0answers
13 views
odi column lineage query
I have no odi experience and i have inherited an oracle data warehouse fed by odi implementations. I need to validate the source to target flows for several fields.My first attempt is to try to create ...
0
votes
1answer
51 views
Agent Job: Execute T-SQL on different server?
While migrating SQL Server databases on updated hardware/software we separated the ETL server from the database server (referred this link).
We were moving our SSIS packages and agent jobs on ETL ...
1
vote
1answer
45 views
Batch - Update vs Insert then Delete [closed]
There is a plan to create a new ETL job to do a particular task. It will retrieve data from database1, and from that data, it would update equivalent records in database2. Both database1 and database2 ...
0
votes
0answers
16 views
Efficient way to copy, using SQL DML, content's of a table between to Azure Sql Databases
Trying to optimize a copy of table's content between two azure sql databases.
Currently, one DB has an external table setup:
CREATE EXTERNAL TABLE [dbo].[Database2_TableA] (
[Col1] [varchar](...
0
votes
1answer
44 views
connect two sql server and import to CSV file (ETL)
I have two servers, both has a sql server . Let's say srv1 has a sql-srv1 and srv2 with a sql-srv2 , i want to get some informations within sql-srv2 but making requests from sql-srv1.
my project is ...
2
votes
1answer
22 views
Azure SSIS Deploying in SQL Azure
Does deploying SSIS really required an azure data lake house when deploying on Azure database?. I am currently working on an SSIS project that its database is currently deployed on Azure and I am ...
1
vote
0answers
37 views
Tool to help breaking multitenant into a single tenant [closed]
Oracle 11.2.0.4
I've been tasked with breaking out a single customer from our multitenant system into a separate system. And the data will have to be continuously updated. This is a large system ...
1
vote
2answers
82 views
Strategies/Resources for implementing data lineage in an ETL process?
I'm struggling to articulate my need here.
I've been tasked with developing a significant ETL process dealing with warehouse item costs in a major grocery chain using the full Microsoft Stack.
I'd ...
1
vote
0answers
89 views
Database - limit of reading a table with a lot of concurrent batch read and writes
We have this table. It contains data which will be picked up by an hourly batch job for processing. Five times a day, there are also multiple batch ETL jobs which get data from other source table and ...
0
votes
0answers
23 views
How can I run an ETL job only on rows added since the last run?
I currently have a flow in Google Dataprep (Trifacta) that operates on my datasource and stores the output in BigQuery. Running the job on the entire data is slow, expensive and unnecessary; I'd like ...
1
vote
1answer
20 views
Data Pipeline load bulk tables in one time
I am using AWS Data Pipeline for copying my RDS MySQL Database to Redshift. I need to create separate pipeline for each table and each pipeline create new EC2 instance. (Process takes time).
Problem
...
1
vote
0answers
7 views
Date getting truncated on PDI 7.1 for salesforce DB - Need workaround
We are working on transformation which inserts date into a salesforce DB and are seeing issues.
Below are the issues: A transformation working in PDI CE 6.1 do not work in CE 7.1 or CE 8.3.
Error :
...
0
votes
1answer
71 views
TIMESTAMP OR DateUpdate
I want to implement incremental ETL on some of my sql server tables, so I need a new column like UpdatedDate to track change dates of my records. I want to transfer only updated and inserted records, ...
1
vote
1answer
139 views
Does loading data to temp table improve performance?
When I write stored procedure I use views that have calculation inside them and so if I have to reuse the view multiple times in my procedure, I load it to a temp table and then use the temp table ...
1
vote
0answers
48 views
SSRS managed shared subscriptions data from from database
While running an ETL process we need to pause the shared managed SSRS subscriptions and then enable them and run them accordingly when our ETL has progressed. In order to do that we take a backup of ...
0
votes
2answers
989 views
SSIS Slow Data Transfer between Oracle and SQL Server 2016
I am trying to design a process that transfers data from Oracle to SQL Server 2016 by using SSIS.
It is done with the aim of removing productive processes from an SQL Server 2000 instance, where the ...
0
votes
2answers
769 views
Specifying Order of Executing Stored Procedures
Issue:
The order in which my stored procedures are called produces no errors if run independently, but when run as once, yields the following error:
Msg 2627, Level 14, State 1, Procedure ...
1
vote
0answers
168 views
Parallel insert and delete into staging tables
I work on a small system that receives quite a lot of data from multiple sources (hundreds of them). Depending on few factors I can get anywhere from few rows to a couple of thousands (in fact ...
0
votes
1answer
45 views
Transactional Replication data during rebuild
To offload reporting I have transactional replication running between my prod box and my reporting server (top image #1). This works great until the snapshot has to be rebuilt. During this time users/...
0
votes
1answer
474 views
SSIS data flow gets stuck reading table with no errors
When I'm doing ETL in SSIS, the data flow task "pauses" reading from the source transactional Live IO DB (the one on the left). It stops reading the data and "pauses" on random rows and then does ...
0
votes
3answers
88 views
Window Function to get pairs of records and compute the time difference
I'm working with an event dataset as the one in the example below:
Event_Type Event_Timestamp Is_Active
A 2010-10-01 00:00:00 1
B 2010-10-01 00:00:01 1
A ...
1
vote
1answer
42 views
Is it faster to truncate destination and copy all source data or lookup and keep only non-matching data with SSIS transfer packages?
I am building an Azure SQL DB database and to do this I will have to regularly (weekly-monthly) pull data from various other servers. I am designing SSIS packages to do this and whilst most of the ...
2
votes
2answers
2k views
Linked server returned message “Query timeout expired.”
We have two separate SQL Servers. On one server we have a data warehouse (DWH), on the other we have sales information database.
Now on the DWH server there is an ETL job that collects the ...
2
votes
1answer
133 views
SSIS comparison load between new, updated and deleted records and populate a staging database
So far,
My control flow looks like:
Execute SQL Task
- This executes a statement that creates a staging table. If one exists, it is dropped so everytime the package is run, the staging table will be ...
0
votes
1answer
115 views
What do I need to develop an integration service to extract data from an Oracle DB to a SQL Server DB using Visual Studio 2015? [closed]
I have to develop an ETL to extract some data from an Oracle DB 11g to a SQL Server 2017 DB. I am pretty new at all this technology so I would like some advices. The strongest problem so far is to ...
3
votes
3answers
848 views
How to handle schema changes in source systems when running an ETL?
How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.
3
votes
0answers
34 views
Migrating Access Saved Import Specifications to SSIS Package
We currently receive a multi-hundred column flat file from a vendor, and the tech prior to me had created an import specification in Microsoft Access to automatically split the file into the right ...
1
vote
2answers
443 views
Can Transactional Replication and triggers be used for near-realtime ETL [closed]
We want to have a data warehouse synced with our production OLTP database in near realtime. Is it possible to do this with Transactional Replication and triggers?
How we currently ETL
We use sql ...
2
votes
1answer
380 views
ETL, Production, Development — Should these be different servers? Instances?
You can say I've become an "accidental DBA" as people have left this company and I'm managing many aspects of the BI system now suddenly.
My predecessor left quite the mess regardless but I'm trying ...
0
votes
0answers
54 views
How to Represent a VARCHAR Column with XML Data in a Fact Table?
I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about ...
31
votes
4answers
1k views
What is a scalable way to simulate HASHBYTES using a SQL CLR scalar function?
As part of our ETL process, we compare rows from staging against the reporting database to figure out if any of the columns have actually changed since the data was last loaded.
The comparison is ...
1
vote
1answer
329 views
When to Disable and Recreate Indexes in Data Warehouse ETL?
We are moving data from Staging Database into Kimball Data Warehouse DB.
I am hearing different ideas regarding when to disable and rebuild indexes.
Stage tables are truncated every day for each new ...
-2
votes
1answer
277 views
Datawarehouse Staging Tables: Null or Not Null
We are loading data from OLTP into Staging Tables with SSIS, then into Kimball data warehouse with ETL process.
We are inquiring about staging tables, if they should have null/not null constraints. ...
3
votes
2answers
236 views
Structured .TXT Data To A Proper DBMS [closed]
I'm presented with multiple .txt documents (each with its own set of columns) containing rows separated by some type of delimiter (which is different across the files). Each .txt file has different ...
0
votes
1answer
1k views
Query or ETL process to insert data into fact tables in SSMS
I'm working on creating a star schema for my project. I want to use it for reporting. I have used Google Play Store dataset from Kaggle.
The thing that I'm having trouble loading data into my fact ...
1
vote
0answers
140 views
Increase frequency of updates (ETL process) to Data Warehouse
Our data warehouse is updated only once every 24 hours (at night). The ETL process takes a little over an hour to complete. I am wondering how to increase the frequency of the ETL process - so running ...
6
votes
2answers
1k views
SQL Staging Tables: Primary Key Clustered or Heap
We are taking legacy flat txt files and inserting them into stage tables with SSIS.The question arose whether table should have primary clustered key index or not. This is direct flat file import with ...
1
vote
1answer
264 views
SSIS Installation on Dedicated Server
We are planning on installing SSIS 2016 on a new dedicated ETL server.
The packages will be called from SQL Server Agent (on a separate server), or from an application (again on a separate server).
...
0
votes
1answer
73 views
What's your data ETL methodology? [closed]
I think this is more like an open question: How do you do the ETL for your company?
We are using MySQL as our database management system and we have a production schema. Every month, when we received ...
3
votes
1answer
2k views
Efficiently bulk upsert unrelated rows
As part of a manual replication process between databases with different but related schemas, for each table, we identify new and modified records in the source database and feed them via a table ...
1
vote
1answer
499 views
How to copy selected table data from sql server to mysql on a daily basis? [closed]
I am having a retail pos application with MSSQL database. Now I need to transfer selected table data from SQL server to MYSQL database on a daily basis like job.
How to achive this?
1
vote
2answers
823 views
SSIS ETL or CDC - real time DWH reporting
I am currently working on SSIS package for continuous incremental data loads of the tables (40 tables now and expected to increase in near future) from OLTP system to a Reporting database residing on ...
1
vote
1answer
3k views
SQL Fastest Way to find Data Difference Between Two Tables
I am trying to locate fastest way to find difference between two tables. The first table is populated from text files daily, the second table is maintained in our database. We need to add data that ...
1
vote
1answer
480 views
reseed auto_increment to specific number?
Is it possible to set the identity column of a table to start from a specific value?
I'm converting an ETL stage from mysql to a sql server database where a staging database loads records with a ...
-2
votes
1answer
104 views
Can ETL access Data while Rebuild Index Operation is running
Can I get some help to understand below questions:
We have a ETL job running (Datawarehouse Server) and weekly index operations (Production Server)(Ola Hallengren script) , below questions are ...
1
vote
2answers
63 views
How do I run a stored procedure only on newest data loaded?
My database has a table where the data is loaded from an Excel upload. Then, a stored procedure is run on the data to validate it and insert it to another table.
Currently, my stored procedure will ...
