Questions tagged [etl]
'Extract, Transform and Load': Data load processing, normally in the context of data warehouse systems.
249
questions
0
votes
1
answer
17
views
Postgres improvements in conversion functions for bulk load
I regularly import data from httparchive.org. The data is a MySQL CSV export and I use pgloader, which handles the quirks of this export (\N for NULL), etc.
I also need to do some additional ...
- 229
0
votes
1
answer
66
views
Bigint vs varchar datatype. Should I match source system? [closed]
Unfortunately, I think this is an opinion question. But I'd like to know if there is a best practice.
Our datawarehouse uses a bigint as a primary key for an address table. The existing records all ...
- 113
0
votes
0
answers
13
views
SSIS Deployment The locale identifier (LCID) 14348 is not supported by SQL Server
When trying to deploy a SSIS package on my local machine I get the following error message
The locale identifier (LCID) 14348 is not supported by SQL Server
And ideas ?
0
votes
0
answers
12
views
ODI scheduled load plans
I recently inherited on a data warehouse which uses ODI to integrate data. The mechanism is using scheduled ODI load plans.
The problem is that in the ODI environment we have thousands of load plans, ...
- 101
1
vote
1
answer
46
views
Bulk Updates on Normalised data present across several PostgreSQL tables
We have several websites from where we download csv files - say 100 such websites. We don't have a control over their schedules, so we download files from them every few hours. Each such file can be ...
- 13
0
votes
1
answer
23
views
Extracting multiple similar tables in different schemas
I am attempting to extract tables from multiple schemas in the same database. The tables are similar enough that they are easy to identify and work with using meta data queries like this one:
SELECT ...
- 101
0
votes
2
answers
55
views
From what volumes of data do data ingestion tools like apache nifi, flume, storm or tools like logstash become relevant?
I'm working in a company that has two legacy data warehouses, which have evolved to unmaintainable monoliths throughout the time. Therefore, they are in dire need of re-form.
I'm investigating a ...
- 103
0
votes
0
answers
27
views
Best approach to convert a non trivial function (CASE WHEN) into a lookup (decision) table?
Assume A, B, C etc are attribute values in the current rowset
Current function logic is similar to:
Case A = 'Y' then
Case B in (1,2,3,4,5) OR C in (3,4,5,6) then 'Sales'
Case B in (99,98,102) OR D ...
- 257
0
votes
0
answers
7
views
SSIS - Attunity 5.0 Oracle destination running for hours yet row count shows data has arrived so why does the dest task look in a hung state?
Using SSDT v14
A simple package that extracts data from a SQL Server view and sends via Attunity to Oracle takes about 5 hours to run, of which 4 hours seems to be the Oracle destination task showing ...
- 257
1
vote
1
answer
37
views
SSIS: Can't save "parallel load" option in Oracle destination with Attunity driver, how to solve this?
I am working on an existing SSIS package that has proven to run successfully, however, due to data volume issues, I would like to try enabling the "parallel load" option by selecting the ...
- 257
0
votes
2
answers
42
views
Selecting MySQL row during Batch Update
I currently have a database with 500K rows that could grow to 3 million in the future. The structure of the table looks like the following:
-id
-price
-sku
-version (denoted by a Date Time string ...
- 103
0
votes
1
answer
77
views
How to export data from AWS RDS MySQL database and import to an other AWS RDS MySQL database instance
I am looking for the best way to export data from one MySQL DB instance of RDS an import into a different one automatically.
Any ideas?
3
votes
1
answer
678
views
What is the best way to export/extract varbinary images in a SQL Server database to flat file via t sql, anything better than sp_OA methods?
I've been tasked to extract images saved in SQL Server (as varbinary) to flat files via t-sql. I haven't done ETL work for over a decade and I can't recall any other method of doing this via t-sql ...
- 759
0
votes
0
answers
32
views
ETL with Oracle: how to do an isolated run without overwriting previous results
Objective
I'm refactoring ETL. ETL is imlemented as a chain of queries submitted to Oracle one-by-one. While executing, ETL creates/overwrites about 20 output tables.
For sake of testing (including ...
- 387
3
votes
3
answers
138
views
ADF patterns to minimise batch run elapsed time
We use Azure Data Factory (ADF) to pull a number of source tables from an on-prem SQL Server DB into Azure Data Lake (DL). We've made this data-driven using the Lookup-ForEach pattern.
There is one ...
- 22.8k
1
vote
3
answers
152
views
Should the T and L phase of a well tuned ETL report process take the same amount of time regardless of the time bucket width and data size?
For an ETL reporting system, is it normal for the total execution time for a pull of 15 minutes with no data to be similar to a 24 hour pull with data?
I had expected that the total time for ETL when ...
- 443
1
vote
0
answers
24
views
History data in CUBE
Not sure if this is the right place to ask.
I am working on an ETL procedure that gathers the data about medication consumption and I have something like this:
Dim_Patient, the dimension for all the ...
- 111
0
votes
2
answers
99
views
How to "normalize" external data before insertion into "Data Warehouse" like database?
I want to be database agnostic but let's say we're inserting data into SQL Server which is to be used as a data warehouse.
There are MILLIONS of email records from an email platform that wasn't too ...
- 1,493
1
vote
1
answer
175
views
Is "ETL vs ELT" just "tables vs views"?
TL;DR; Is the difference between ETL and ELT just whether or not the transformed data is a concretion vs abstraction before being loaded into the warehouse?
So forgive me the broadness of the subject ...
- 207
0
votes
1
answer
84
views
Oracle - Exchange partitions between two List partitioned tables
Need some help here.
I have an ETL process which loads the data into a target table A. We have created another table B which is same as the target table in structure and this table is accessed by ...
1
vote
2
answers
587
views
Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?
I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero.
From the DBA I ...
- 443
0
votes
0
answers
97
views
How to ETL SQL Server resultset to mongo DB
Let's say I have multiple columns on sql server that I want to join and save the resultset into a single mongodb collection for faster lookup and calculations ( I'm talking about over 15 tables and ...
- 1
0
votes
1
answer
119
views
Truncate and insert: Heap and build index or directly into Clustered table
In ETL is it better to drop the index before inserting millions of rows and right after create the index again, or to simply insert into the empty table with the indexe in place.
I know that I can ...
- 587
1
vote
1
answer
131
views
ODI remove auto lock when object / datastore is opened
I was being stupid and enabled automatically lock object during edit / opened when I was asked the first time.
Now I want to remove the automatically lock from GUI but I couldn't seem to find it.
I ...
- 115
-1
votes
1
answer
52
views
Non-procedural way of implementing ELT dependency logic
I have two tables with identical schema, sales_primary and sales_secondary:
productID
trxnWeekStartDate
dollarAmt
123
2021-02-07
100
For a particular week, these two tables can both have a ...
- 313
1
vote
1
answer
79
views
RCSI & ETL processing compatibility
The Situation:
I have a write-heavy, read-light ETL workload with the basic process of:
get chunk of raw data (generally 1000-5000 records) from an import landing table.
pass the chunk of data from ...
- 35
2
votes
2
answers
275
views
What is the correct and performant pattern for loading (and querying) a large clustered columnstore table?
I am doing some test with clustered columnstore tables - both partitioned and not.
In my research, I've seen flavors of this pattern:
-- step 1: make new table
SELECT TOP 0 * INTO [Fact].[Order_CCI] ...
- 121
2
votes
1
answer
53
views
What are the disadadvantages of a datawarehouse with no datawarehouse tables, but only datamarts?
The ETL design pattern prescribes transformation from staging tables to a central data warehouse data model, followed by extracting data from the data warehouse data model to data marts.
Are there any ...
- 21
1
vote
1
answer
83
views
MYSQL Huge Data migration (5TB) from old server to new server
We have an old MYSQL server contains huge data ~5TB and wanted to migrate to the new server in order to minimize the costs and get rid of very old hardware.
My one and only idea in my mind is using ...
- 11
0
votes
2
answers
171
views
SQL join on col1 if present else use col2
There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup.
What I want is either join on T1.col1 = T2.col1 or T1....
- 101
0
votes
1
answer
129
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 ...
- 101
1
vote
0
answers
164
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 ...
- 24.3k
0
votes
0
answers
212
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 ...
- 101
0
votes
1
answer
521
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 ...
- 101
1
vote
1
answer
237
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 ...
- 11
0
votes
1
answer
76
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
1
answer
31
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 ...
- 29
1
vote
0
answers
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,529
1
vote
2
answers
184
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 ...
- 653
1
vote
0
answers
134
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 ...
1
vote
1
answer
81
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
0
answers
9
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 :
...
- 15
0
votes
1
answer
164
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,341
1
vote
1
answer
473
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 ...
- 111
1
vote
0
answers
105
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 ...
- 11
1
vote
3
answers
3k
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
2
answers
3k
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 ...
- 103
1
vote
1
answer
370
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
1
answer
89
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/...
1
vote
1
answer
907
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 ...
- 21